[WebMethod]
[ScriptMethod]
public void ExportToExcel(GNode[][] ExpandedNodes, GNode[][] AllNodes, GNode[] Sender, string[] TableName, DataFilter[] Filter, DataSortOrder[] SortExpression, int[] CurentPage, int[] PageSize)
{
DataTable Displayed_DataTable = null;
Displayed_DataTable = DisplayedDataTableSimulator(ExpandedNodes, AllNodes, Sender, TableName, Filter, SortExpression, CurentPage, PageSize);
int TableColumnCount = Displayed_DataTable.Columns.Count;
int TableRowCount = Displayed_DataTable.Rows.Count;
string objDirPath = Server.MapPath("tempDocs");
Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook wb = null;
try
{
string fileName = objDirPath + "\\Mdd.xls";
wb = xl.Workbooks.Open(fileName, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, false, false);
DataColumn[] dc = new DataColumn[TableColumnCount];
DataRow[] dr = new DataRow[TableRowCount];
Displayed_DataTable.Columns.CopyTo(dc, 0);
Displayed_DataTable.Rows.CopyTo(dr, 0);
Microsoft.Office.Interop.Excel.Sheets xlsSheets = wb.Sheets;
Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsSheets[1];
ExcelWorkSheet.Rows.Clear();
int NoColumnInSheet = (TableColumnCount - 1) / 2;
for (int i = 0, j = 0; i < TableColumnCount - 1; i = i + 2, j++)//Get The Column Header
{
char c = (char)(65 + j);
Microsoft.Office.Interop.Excel.Range ExcelHeader = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.get_Range(c + "1:" + c + "1", Type.Missing);
ExcelHeader.Cells.Value2 = dc[i].ColumnName.ToString();
wb.Save();
}
object beforeRow = Type.Missing;
for (int i = 2; i <= TableRowCount + 1; i++)// fill the cells with data
{
for (int j = 0, jc = 1; j < TableColumnCount - 1; j = j + 2, jc++)
{
char c = (char)(65 + (jc - 1));
Microsoft.Office.Interop.Excel.Range ExcelHeader = (Microsoft.Office.Interop.Excel.Range)ExcelWorkSheet.get_Range("" + (c) + (i) + ":" + (c) + (i), Type.Missing);
ExcelHeader.Cells.Value2 = dr[i - 2][dc[j].ColumnName.ToString()].ToString();
wb.Save();
}
}
object Save_changes = true;
wb.Close(Save_changes, fileName, Missing.Value);
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wb = null;
try
{
xl.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xl);
}
catch (Exception ex) { }
finally { xl = null; }
GC.Collect();
}
}