Found out many programs to export to Excel, but poor performance was a pain in the (you know what!!!!) finally came across with the following snippet
Params
- DataTable
- Output file path
I've added a Excel Cell format function (kindly bare with my formatting skills)
public static void ExportToExcel(System.Data.DataTable dataTable, string outputPath)
{
// Create the Excel Application object
Application excelApp = new Application();
// Create a new Excel Workbook
Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
// Copy the DataTable to an object array
object[,] rawData = new object[dataTable.Rows.Count + 1, dataTable.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dataTable.Columns.Count; col++)
{
rawData[0, col] = dataTable.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int col = 0; col < dataTable.Columns.Count; col++)
{
for (int row = 0; row < dataTable.Rows.Count; row++)
{
rawData[row + 1, col] = dataTable.Rows[row].ItemArray[col];
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dataTable.Columns.Count > colCharsetLen) {
finalColLetter = colCharset.Substring(
(dataTable.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dataTable.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
Worksheet excelSheet = (Worksheet) excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing,1, XlSheetType.xlWorksheet);
excelSheet.Name = "Excel details";
int count = 0;
foreach (Microsoft.Office.Interop.Excel.Worksheet work in excelWorkbook.Worksheets)
{
if (work.Name.ToString() .Contains("Sheet"+count))
{
work.Delete();
//delete worksheet
}
count++;
}
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dataTable.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
excelSheet.get_Range(excelRange, Type.Missing).VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
excelSheet.get_Range(excelRange, Type.Missing).Columns.AutoFit();
setFormat(excelSheet.get_Range(excelRange, Type.Missing).Columns);
// Mark the first row as BOLD
((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
((Range)excelSheet.Rows[1, Type.Missing]).Font.Size = 12;
// Save and Close the Workbook
excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
// Release the Application object
excelApp.Quit();
excelApp = null;
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
}
// Set the format for the Excel Cells
private static void setFormat(Excel.Range range)
{
range.Select();
range.Font.Size = 11;
range.WrapText = true;
range.HorizontalAlignment = Excel.Constants.xlLeft;
range.Interior.ColorIndex = 34;
range.Borders.Weight = 3;
range.Borders.LineStyle = Excel.Constants.xlSolid;
range.Cells.RowHeight = 15;
}
neato , I don;t understand it but its cool
ReplyDelete