DataTable to Excel With Bulk Insert

Sometimes you may need to generate report as Excel document from your ASP.NET application. I have written this article in dotnetspider.com that describes about exporting .NET DataTable into Excel document. Click DataTable to Excel With Bulk Insert.

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);
}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: