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();
}
}
}