export excel file with using data table

 

{

 

public string ExportDataTable( DataTable dataTable) try

{

 

 

object missing = Type .Missing; //Create Excel

excel =

excel.DisplayAlerts =

workbooks = excel.Workbooks;

workbook = workbooks.Add(missing);

 

new Excel.ApplicationClass(); false ; //excel.Visible = true;

Excel.Sheets Ver = (Excel.Sheets)excel.Worksheets;

Excel.Worksheet ws = (Excel.Worksheet)Ver.Add(

 

ws.Name = dataTable.TableName;

Excel.Range cel;

 

Type .Missing, Type .Missing, Type .Missing, Type .Missing); //Add Data Header

 

{

ws.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;

 

cel1.ColumnWidth = 25;

Excel.Range cel2 = (Excel.Range)ws.get_Range(ws.Cells[dataTable.Rows.Count + 1, 4], ws.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);

cel2.ColumnWidth = 15;

for ( int i = 0; i < dataTable.Columns.Count; i++) Excel.Range cel1 = (Excel.Range)ws.get_Range(ws.Cells[dataTable.Rows.Count + 1, 1], ws.Cells[dataTable.Rows.Count + 1, 3]);

}

 

 

 

 

{

 

{

objVal[j, k] = dataTable.Rows[j][k].ToString().Trim();

}

}

 

 

 

 

{

cel = (Excel.Range)ws.get_Range(ws.Cells[2, 1], ws.Cells[rowsCount + 1, columnsCount]);

cel.Value2 = objVal;

}

 

int rowsCount = dataTable.Rows.Count; int columnsCount = dataTable.Columns.Count; object [,] objVal = new object [rowsCount, columnsCount]; for ( int j = 0; j < dataTable.Rows.Count; j++) for ( int k = 0; k < dataTable.Columns.Count; k++) int count = 3000; int n = objVal.GetLength(0) / count; int modulo = objVal.GetLength(0) % count; if (n <= 0) else

{

 

{

 

 

{

 

{

objValue[i, j] = objVal[i + k * count, j];

}

}

cel = (Excel.Range)ws.get_Range(ws.Cells[2 + k * count, 1], ws.Cells[1 + (k + 1) * count, columnsCount]);

cel.Value2 = objValue;

}

 

{

 

 

{

 

{

objValue[i, j] = objVal[i + n * count, j];

}

}

cel = (Excel.Range)ws.get_Range(ws.Cells[2 + n * count, 1], ws.Cells[1 + n * count + modulo, columnsCount]);

cel.Value2 = objValue;

}

}

cel = (Excel.Range)ws.get_Range(ws.Cells[1, 1], ws.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);

cel.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

cel.Borders.ColorIndex =

cel = (Excel.Range)ws.get_Range(ws.Cells[1, 1], ws.Cells[1, dataTable.Columns.Count]);

cel.Font.Bold =

cel.Font.ColorIndex =

cel.Interior.ColorIndex =

 

 

for ( int k = 0; k < n; k++) object [,] objValue = new object [count, columnsCount]; for ( int i = 0; i < count; i++) for ( int j = 0; j < dataTable.Columns.Count; j++) if (modulo != 0) object [,] objValue = new object [modulo, columnsCount]; for ( int i = 0; i < modulo; i++) for ( int j = 0; j < dataTable.Columns.Count; j++) ExcelColor .Black; true ; ExcelColor .White; ExcelColor .HeaderColor;

#region

 

Delete unwanted sheet (win 2003) try

{

((Excel.Worksheet)workbook.Worksheets[

}

 

"Sheet1" ]).Delete(); catch

{

}

 

try

{

((Excel.Worksheet)workbook.Worksheets[

}

 

"Sheet2" ]).Delete(); catch

{

}

 

try

{

((Excel.Worksheet)workbook.Worksheets[

}

 

"Sheet3" ]).Delete(); catch

{

}

#endregion

 

 

 

workbook.SaveAs(_path, Excel.XlFileFormat.xlWorkbookNormal, missing, missing,

 

}

 

{

 

}

 

string bufferFolder = @"BulkUploadFiles/" ; string _path = string .Format( "{0}{1}.xls" , Server.MapPath(bufferFolder), dataTable.TableName); false , false , Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); return _path; catch ( Exception ee) throw ee; finally

{

excel.Workbooks.Close();

excel.Quit();

workbook =

workbooks =

excel =

 

 

 

 

}

}

null ; null ; null ; if (workbook != null ) Marshal .ReleaseComObject(workbook); if (workbooks != null ) Marshal .ReleaseComObject(workbooks); if (excel != null ) Marshal .ReleaseComObject(excel); GC .Collect();