Generating and Exporting an Excel File in ASP.NET - Using Excel COM Object


I am going to explain how togenerated and export an Excel file using using Excel COM Object.

Here is the code


Microsoft.Office.Interop.Excel.ApplicationClass objExcelApp = null;
Microsoft.Office.Interop.Excel.Workbook objWBook = null;
Microsoft.Office.Interop.Excel._Worksheet objWorksheet = null;

try
{
objExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//Hiding Excel Application
objExcelApp.Visible = false;
//Hiding All the alert messages
objExcelApp.DisplayAlerts = false;

// Add Work Book

objWBook = objExcelApp.Application.Workbooks.Add(System.Reflection.Missing.Value);

objWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)objWBook.Sheets[1];

objWorksheet.Name = "WorkSheet Name";

string[] sExcelRow = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

// write column names

for (int intCounter = 0; intCounter < 2; intCounter++)
{
objExcelApp.Cells[1, intCounter + 1] = “ColumnName”;

// Specify Number format – Excel considers ‘01’ as number and truncate leading
// zero. To retain leading zero, mention number format.
objWorksheet.get_Range(Convert.ToString(sExcelRow[intCounter]) + Convert.ToString(intCounter + 1), Convert.ToString(sExcelRow[intCounter] + Convert.ToString(intCounter + 1))).EntireColumn.NumberFormat = "@";

}

for (int intCounter = 0; intCounter < DataTable/Collection.Rows.Count; intCounter ++)
{
objExcelApp.Cells[intCounter + 2, 1] = “Data “;
objExcelApp.Cells[intCounter + 2, 2] = “Data “;
}
// AutoFit Columns
objExcelApp.Columns.AutoFit();
//Store Excel file at any physical location
String strFilePath = System.IO.Path.Combine(DIRECTORY,FILENAME);
objWBook.SaveAs(Server.MapPath(strFilePath), Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,System.Reflection.Missing.Value, System.Reflection.Missing.Value,System.Reflection.Missing.Value, System.Reflection.Missing.Value,System.Reflection.Missing.Value);

//Close Excel Work Book and Quit Application
objWBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
objExcelApp.Workbooks.Close();
objExcelApp.Quit();

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/excel";
HttpContext.Current.Response.WriteFile(Server.MapPath(strFilePath));
HttpContext.Current.Response.End();
}

catch (Exception ex)
{
CommonUtility.DisplayExceptionMessage(ex);
}
finally
{
//Release Excel COM Objects System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorksheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(objWBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcelApp);
objWorksheet = null;
objWBook = null;
objExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}


Pros:

Uses native Microsoft Libraries.
Works fine with any Excel version.
Exported Excel files can be updated and imported in SQL Server and ASP.NET.

Cons:

The code uses Excel Object library which is required at the development server.
Since the code creates objects of the COM components through Interop services, if the components are not efficiently released, it may result in memory leakage.
Very slow.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: