using System; using System.Text; namespace CCC.Utility { /// <summary> /// Summary description for JSExcel. /// </summary> public class Excel4JavaScript { private string _ReportTemplate_Excel_FileName = null; private int _Used_WorkSheet_MaxIndex = 0; StringBuilder _SB_JavaScript_Text = new StringBuilder(); private int _WorkSheetCount = 1; private int _OriginalSheetCount = 1; public Excel4JavaScript(string strFileName) { _ReportTemplate_Excel_FileName = strFileName; } public Excel4JavaScript(string strFileName, int originalSheetCount) { _WorkSheetCount = originalSheetCount; _OriginalSheetCount = originalSheetCount; _ReportTemplate_Excel_FileName = strFileName; } public void SetWorkSheetName(int sheetIndex, string strWorkSheetName) { _SB_JavaScript_Text.Append("objSheet").Append(sheetIndex).Append(".Name='").Append(strWorkSheetName).Append("';").Append("\r\n"); } public void CopyWorkSheet(int sheetIndex, string sheetName) { _WorkSheetCount += 1; _SB_JavaScript_Text.Append("objSheet").Append(sheetIndex).Append(".Copy(null, objWorkBook.Worksheets(objWorkBook.Worksheets.count));").Append("\r\n"); _SB_JavaScript_Text.Append("objSheet").Append(_WorkSheetCount).Append(" = objWorkBook.Worksheets(").Append(_WorkSheetCount).Append(");").Append("\r\n"); _SB_JavaScript_Text.Append("objSheet").Append(_WorkSheetCount).Append(".Name='").Append(sheetName).Append("';").Append("\r\n"); } public void HideSheet(int sheetIndex) { // _WorkSheetCount -= 1; _SB_JavaScript_Text.Append("objSheet").Append(sheetIndex).Append(".Visible = false;").Append("\r\n"); } public void WriteCell( int sheetIndex, int row, int col, string strCellValue) { if(_Used_WorkSheet_MaxIndex < sheetIndex) { _Used_WorkSheet_MaxIndex = sheetIndex; } _SB_JavaScript_Text.Append("objSheet").Append(sheetIndex).Append(".Cells(").Append(row).Append(",").Append(col).Append(").value = '").Append(strCellValue).Append("';").Append("\r\n"); } public void WriteComment( string strCellValue, int RowStart, int ColStart, int sheetIndex) { WriteCell(sheetIndex,RowStart,ColStart,strCellValue); } public void DeleteColumn(int col, int sheetIndex) { if(_Used_WorkSheet_MaxIndex < sheetIndex) { _Used_WorkSheet_MaxIndex = sheetIndex; } _SB_JavaScript_Text.Append("objSheet").Append(sheetIndex).Append(".Columns(").Append(col).Append(").Delete").Append(";").Append("\r\n"); } public void InsertRow(int row, int sheetIndex) { _SB_JavaScript_Text.Append("objSheet").Append(sheetIndex).Append(".Rows(").Append(row).Append(").Insert();").Append("\r\n"); } public void DeleteRows(int row, int sheetIndex) { if (_Used_WorkSheet_MaxIndex < sheetIndex) { _Used_WorkSheet_MaxIndex = sheetIndex; } _SB_JavaScript_Text.Append("objSheet").Append(sheetIndex).Append(".Rows(").Append(row).Append(").Delete").Append(";").Append("\r\n"); } public string ToExcelString() { StringBuilder sbJsText = new StringBuilder(); sbJsText.Append("<script language='JavaScript'>").Append("\r\n"); sbJsText.Append("var idTmr = null;").Append("\r\n"); sbJsText.Append("function openExcel() {").Append("\r\n"); for( int i = 1; i <= _WorkSheetCount; i++) { sbJsText.Append(" var objSheet" + i.ToString() + ";\r\n"); } sbJsText.Append(" var objExcel = new ActiveXObject('Excel.Application');").Append("\r\n"); sbJsText.Append(" try {").Append("\r\n"); sbJsText.Append(" objExcel.DisplayAlerts = false;").Append("\r\n"); sbJsText.Append(" var objWorkBook = objExcel.Workbooks.Open('").Append(this._ReportTemplate_Excel_FileName).Append("');").Append("\r\n"); for( int i = 1; i <= _OriginalSheetCount; i++) { sbJsText.Append(" objSheet" + i.ToString() + " = objWorkBook.Worksheets(" + i.ToString() + ");").Append("\r\n"); } sbJsText.Append(this._SB_JavaScript_Text.ToString()); sbJsText.Append(" objWorkBook.Worksheets(1).Activate();").Append("\r\n"); sbJsText.Append(" objExcel.Visible = true;").Append("\r\n"); sbJsText.Append(" } catch (ex) {").Append("\r\n").Append("\r\n"); sbJsText.Append(" } finally {").Append("\r\n"); sbJsText.Append(" }").Append("\r\n"); sbJsText.Append("}").Append("\r\n").Append("\r\n"); sbJsText.Append("openExcel();").Append("\r\n"); sbJsText.Append("</script> ").Append("\r\n"); return sbJsText.ToString(); } } }