export to excel in asp.net from dataset or datatable

export to excel in asp.net from dataset or datatable

public void ExportToExcel(DataSet dataset, string DestFileName)

{

if (System.IO.File.Exists(DestFileName))

{

System.IO.File.Delete(DestFileName);

}

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook excelWorkBook = default(Microsoft.Office.Interop.Excel.Workbook);

excelWorkBook = excelApp.Workbooks.Add(Type.Missing);

int sheetIndex = 0;

int col = 0;

int row = 0;

Microsoft.Office.Interop.Excel.Worksheet excelSheet = default(Microsoft.Office.Interop.Excel.Worksheet);

foreach (System.Data.DataTable dt1 in dataset.Tables)

{

sheetIndex += 1;

object[,] rawData = new object[dt1.Rows.Count + 1, dt1.Columns.Count];

for (col = 0; col <= dt1.Columns.Count - 1; col++)

{

rawData[0, col] = dt1.Columns[col].ColumnName;

}

for (col = 0; col <= dt1.Columns.Count - 1; col++)

{

for (row = 0; row <= dt1.Rows.Count - 1; row++)

{

rawData[row + 1, col] = dt1.Rows[row].ItemArray[col];

}

}

string finalColLetter = string.Empty;

string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

int colCharsetLen = colCharset.Length;

if (dt1.Columns.Count > colCharsetLen)

{

finalColLetter = colCharset.Substring((dt1.Columns.Count - 1) / colCharsetLen - 1, 1);

}

finalColLetter += colCharset.Substring((dt1.Columns.Count - 1) % colCharsetLen, 1);

excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets.get_Item(sheetIndex);

excelSheet.Name = dt1.TableName;

string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt1.Rows.Count + 1);

excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

//int colcount = excelSheet.Cells.CurrentRegion.Columns.Count;

string excelRange1 = string.Format("A1:{0}{1}", finalColLetter, 1);

excelSheet.get_Range(excelRange1, Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);

excelSheet.get_Range(excelRange1, Type.Missing).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);

excelSheet.get_Range(excelRange1, Type.Missing).Font.Bold = true;

excelSheet = null;

}

string e = System.IO.Path.GetExtension(DestFileName);

if (e == ".csv")

{

excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVMSDOS, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing);

}

else if (e == ".xls")

{

excelWorkBook.SaveAs(DestFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, Type.Missing);

}

GC.Collect();

GC.WaitForPendingFinalizers();

excelWorkBook.Close(true, Type.Missing, Type.Missing);

Marshal.FinalReleaseComObject(excelWorkBook);

excelApp.Quit();

Marshal.FinalReleaseComObject(excelApp);

}