Friday, September 3, 2010

Export to Excel

You display data in a grid with formatting and feel you have completed the task, but find out that you have been asked to export the data to excel!!!???!!! Confused...Want to kill the guy who gave the idea..Even i did had the same idea.


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
  1. DataTable
  2. 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;
        }

1 comment: