DataTable to Excel With Bulk Insert

Sometimes you may need to generate report as Excel document from your ASP.NET application. This article describes about exporting .NET DataTable into Excel document.

The Excel report can be generated in different ways. (i)Using XML Spreadsheet (ii) Using Excel COM objects.

In XML spreadsheet, the content of the Excel report is written using XML tags. When the file created using XML spreadsheet tags is opened, the Excel application renders the content in Excel format. The Excel Spreadsheet XML format supports element tags and attributes for Excel functionality such as multi-sheet workbooks, formulas, and cell formatting.


The other way is generating Excel report using Excel object model. The followings objects are important to generate an Excel document.
1. Application
2. Workbook
3. Worksheet
4. Range

To make the above objects available to your project, you need to add reference to the assembly “Microsoft.Office.Interop.Excel” version 12.0. This assembly uses Excel COM objects to generate Excel reports. To generate report using Excel object model, Excel application has to be installed and configured in the server where the ASP.NET application is deployed.

The following code snippet can be used to write the contents of a DataTable into Excel document using the Excel object model.


using System;
using System.Data;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

public partial class TestPage1 : System.Web.UI.Page
{

protected void Button1_Click(object sender, EventArgs e)
{
ApplicationClass objExcel = null;
Workbooks objBooks = null;
_Workbook objBook = null;
Sheets objSheets = null;
_Worksheet objSheet = null;
Range objRange = null;
int row = 1, col = 1;
try
{

//Get your database records into a DataTable
System.Data.DataTable dtCustmer = GetAllCustomers();


objExcel = new ApplicationClass();
objBooks = objExcel.Workbooks;
objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);

//Print column heading in the excel sheet
int j = col;
foreach (DataColumn column in dtCustmer.Columns)
{

objSheets = objBook.Worksheets;
objSheet = (_Worksheet)objSheets.get_Item(1);
objRange = (Range)objSheet.Cells[row, j];
objRange.Value2 = column.ColumnName;
j++;
}
row++;

//Write the content of each row into Excel cells
int count = dtCustmer.Columns.Count;
foreach (DataRow dataRow in dtCustmer.Rows)
{
int k = col;
for (int i = 0; i < count; i++)
{
objRange = (Range)objSheet.Cells[row, k];
objRange.Value2 = dataRow[i].ToString();
k++;
}
row++;
}

//Save Excel document
objSheet.Name = "Sample Sheet";
object objOpt = Missing.Value;
objBook.SaveAs(@"C:\TestSheet1.xls", objOpt, objOpt, objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt);
objBook.Close(false, objOpt, objOpt);

}
catch
{
}
finally
{
objExcel = null;
objBooks = null;
objBook = null;
objSheets = null;
objSheet = null;
objRange = null;
ReleaseComObject(objExcel);
ReleaseComObject(objBooks);
ReleaseComObject(objBook);
ReleaseComObject(objSheets);
ReleaseComObject(objSheet);
ReleaseComObject(objRange);
}
}

//Release COM objects from memory
public void ReleaseComObject(object reference)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(reference) <= 0)
{
}
}
catch
{
}
}

//Returns a DataTable. Modify as you needed
public System.Data.DataTable GetAllCustomers()
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add(new DataColumn("Customer_Id", typeof(int)));
dt.Columns.Add(new DataColumn("Name", typeof(string)));
dt.Columns.Add(new DataColumn("Email_Id", typeof(string)));
dt.Rows.Add(new object[] { 1001, "Customer 1", "customer1@TestSite.com" });
dt.Rows.Add(new object[] { 1002, "Customer 2", "customer2@TestSite.com" });
dt.Rows.Add(new object[] { 1003, "Customer 3", "customer3@TestSite.com" });
dt.Rows.Add(new object[] { 1004, "Customer 4", "customer4@TestSite.com" });
dt.Rows.Add(new object[] { 1005, "Customer 5", "customer5@TestSite.com" });
return dt;
}



}



In the above code snippet, each row of DataTable is read using a ‘for’ loop and then written to particular row and column of the Excel datasheet. The ‘for’ loop is executed by .NET code and then pass the DataRow value to Excel COM object. Then the Excel object writes the content to Spreadsheet.
If the DataTable contains thousands of records, then it may take considerable amount of time to generate the Excel report. In web applications, the users will not like to have long time to generate a report.

A work around solution is available to address this problem. We need to convert the DataTable into a two dimensional array and the pass the array to the Excel COM object. The Excel object modal accepts arrays and writes the content into Excel worksheet. The Excel object itself automatically loops through the array and then writes the content into worksheet. Since the looping through the array is executed by Excel COM object, this approach will take less time to generate the report as comparing with the above approach. The following code snippet will help to generate the Excel report from a DataTable using this work around solution.


protected void Button2_Click(object sender, EventArgs e)
{
ApplicationClass objExcel = null;
Workbooks objBooks = null;
_Workbook objBook = null;
Sheets objSheets = null;
_Worksheet objSheet = null;
Range objRange = null;
int row = 1, col = 1;

try
{
System.Data.DataTable dtCustmer = GetAllCustomers();

objExcel = new ApplicationClass();
objBooks = objExcel.Workbooks;
objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);

objSheets = objBook.Worksheets;
objSheet = (_Worksheet)objSheets.get_Item(1);
object objOpt = Missing.Value;

//Print column heading in the excel sheet
int j = 1;
foreach (DataColumn column in dtCustmer.Columns)
{
objRange = (Range)objSheet.Cells[row, j];
objRange.Value2 = column.ColumnName;
objRange.Interior.ColorIndex = 5;
j++;
}
row++;

//declare an object array
object[,] objData = new Object[dtCustmer.Rows.Count, dtCustmer.Columns.Count];

//Copy datatabe into array
int r = 0, c = 0;
foreach (DataRow dataRow in dtCustmer.Rows)
{
objData[r, c] = dataRow["Customer_Id"];
objData[r, c + 1] = dataRow["Name"];
objData[r, c + 2] = dataRow["Email_Id"];
r++;
}

objRange = (Range)objSheet.Cells[row, col];
if (objData.GetUpperBound(0) > 0)
objRange = objRange.get_Resize(objData.GetUpperBound(0) + 1, objData.GetUpperBound(1) + 1);
objRange.Value2 = objData;

objSheet.Name = "Sample Sheet";
objBook.SaveAs(@"C:\TestSheet2.xls", objOpt, objOpt, objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt);
objBook.Close(false, objOpt, objOpt);

}
catch
{
}
finally
{
objExcel = null;
objBooks = null;
objBook = null;
objSheets = null;
objSheet = null;
objRange = null;
ReleaseComObject(objExcel);
ReleaseComObject(objBooks);
ReleaseComObject(objBook);
ReleaseComObject(objSheets);
ReleaseComObject(objSheet);
ReleaseComObject(objRange);
}
}


Comments

Author: D.Jeya kumar(JK)29 Apr 2009 Member Level: Gold   Points : 1

Hi,

Good post. Nice article. keep posting more Good articles

Regards
JK

Author: aradhya09 Jun 2009 Member Level: Silver   Points : 1

Hello Ramesh...... above example is very good. But for at least some important code lines you write, how it works in comment line.
Other wise the article is V.Good.

Guest Author: Lorraine13 Jan 2012

You should come up with that. Excellent!



  • 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: