Click here to Skip to main content
15,440,964 members
Articles / Programming Languages / Visual Basic
Article
Posted 1 Dec 2007

Stats

349.2K views
15.7K downloads
98 bookmarked

Fast Exporting from DataSet to Excel

Rate me:
Please Sign up or sign in to vote.
4.70/5 (54 votes)
1 Dec 2007CPOL2 min read
A fast method for exporting large amounts of data from a DataSet to Excel
Screenshot - FastExporting DEMO App

Introduction

Exporting data from a .NET application to Excel is a very common requirement. A simple search on the Web results in several examples that show us the method to copy data and put it into the Excel cells. However, there is a payload with this method: each interaction to put a value into an Excel cell requires an InterOp invocation. If the amount of data to transfer is huge, we have a problem with the performance of the method. Is there a better way to accomplish this?

Traditional "COPY CELL-BY-CELL" Method

Searching the Web for a method to transfer data to Excel, the most commonly used method consists of copying the values cell by cell into Excel. The following C# code shows how to transfer data from a DataTable to an Excel sheet, copying each value cell by cell:

C#
// Copy the values from a DataTable to an Excel Sheet (cell-by-cell)
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    for (int row = 0; row < dataTable.Rows.Count; row++)
    {
        excelSheet.Cells[row + 1, col + 1] = 
                dataTable.Rows[row].ItemArray[col];
    }
}

Each InterOp invocation has an associated payload in performance, so a large amount of data can degenerate our application.

A "Fast Bulk-Copy" Method

Our method consists of using the Value2 property for the Range class provided by the Microsoft Excel Object Library. We can select a range of cells, and assign a value for all of them, with just one InterOp invocation. To correctly assign a value to a range of cells, we can use a bi-dimensional object array. The following C# code shows how to transfer data from a bi-dimensional object array to a range of cells:

C#
// Copy a bi-dimensional object array to an Excel cell range
excelSheet.get_Range("A1:H25", Type.Missing).Value2 = 
    bidimensionalObjectArray;

Measuring the Performance

The source code included with this article shows a small Windows application which uses the two described methods to export the same data to Excel. It shows the time that it takes for each method to finish. This DEMO uses the Northwind database to create an SQL Server local connection. It generates a DataSet with the content of the Customers table. To make the amount of data more significant, we duplicate the DataTable to obtain 24 copies from it. Then we apply the two methods to generate two Excel books, one for each method.

The source code includes a C# and a VB.NET version for the DEMO application. My own testing shows me that this method is about 35 times faster. Test it and arrive at your own conclusions.

History

  • November 28, 2007: First publication

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Ecuador Ecuador
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralThanks for posting this... Pin
stopgo6-Mar-09 22:29
Memberstopgo6-Mar-09 22:29 
GeneralExcel 2007 Row Limit Problem Pin
returnofjedi4-Mar-09 4:29
Memberreturnofjedi4-Mar-09 4:29 
GeneralRe: Excel 2007 Row Limit Problem - Solved Pin
returnofjedi4-Mar-09 5:09
Memberreturnofjedi4-Mar-09 5:09 
GeneralI am getting this error each time i run the code Pin
Mohammad Al Hoss20-Feb-09 2:14
MemberMohammad Al Hoss20-Feb-09 2:14 
GeneralRe: I am getting this error each time i run the code Pin
PeterMoon20-Feb-09 15:40
MemberPeterMoon20-Feb-09 15:40 
GeneralProblem solved Pin
Luthandoman9-Feb-09 23:43
MemberLuthandoman9-Feb-09 23:43 
GeneralRe: Problem solved Pin
PeterMoon20-Feb-09 15:42
MemberPeterMoon20-Feb-09 15:42 
QuestionThe type or namespace name 'ApplicationClass' could not be found Pin
ravindra akella12-Dec-08 2:39
Memberravindra akella12-Dec-08 2:39 
Hi,

I am getting the error "The type or namespace name 'ApplicationClass' could not be found",
Also i included Microsoft Office 11.0 Object Library reference
i don't have the namespace "Microsoft.Office.Interop.Excel;".

It was giving error on the line "using Microsoft.Office.Interop.Excel;"
and when i checked i can see this is available "Microsoft.Office.Core;".

Kindly let me know what i am missing.

Regards
Ravindra
AnswerRe: The type or namespace name 'ApplicationClass' could not be found Pin
PeterMoon12-Dec-08 14:36
MemberPeterMoon12-Dec-08 14:36 
GeneralBut what if the data is in DataSet format Pin
Aleksandra Czajka18-Jun-08 10:42
MemberAleksandra Czajka18-Jun-08 10:42 
GeneralRe: But what if the data is in DataSet format Pin
CikaPero15-Jul-10 2:26
MemberCikaPero15-Jul-10 2:26 
GeneralValue instead of Value2 Pin
kneckedeck28-May-08 21:15
Memberkneckedeck28-May-08 21:15 
GeneralRe: Value instead of Value2 Pin
CodeProjectCraig19-Feb-10 1:14
MemberCodeProjectCraig19-Feb-10 1:14 
Generalerror Pin
Member 47346183-May-08 1:49
MemberMember 47346183-May-08 1:49 
GeneralRe: error Pin
PeterMoon3-May-08 5:56
MemberPeterMoon3-May-08 5:56 
QuestionRe: error Pin
Member 47346184-May-08 17:53
MemberMember 47346184-May-08 17:53 
GeneralExcel components Pin
FilipKrnjic28-Apr-08 21:56
MemberFilipKrnjic28-Apr-08 21:56 
GeneralRe: Excel components Pin
Niyazi Yarar25-Sep-10 17:36
MemberNiyazi Yarar25-Sep-10 17:36 
QuestionExporing data to Excel 2007 (.xlsx file format) Pin
sameerkhole2-Mar-08 18:22
Membersameerkhole2-Mar-08 18:22 
GeneralFast Excel Exporting Pin
yahyacode27-Jan-08 6:03
Memberyahyacode27-Jan-08 6:03 
GeneralRe: Fast Excel Exporting Pin
PeterMoon27-Jan-08 15:40
MemberPeterMoon27-Jan-08 15:40 
GeneralRe: Fast Excel Exporting Pin
Railbird21-Nov-08 4:41
MemberRailbird21-Nov-08 4:41 
QuestionMS Excel Installed? Pin
gongchengshi17-Jan-08 13:50
Membergongchengshi17-Jan-08 13:50 
GeneralRe: MS Excel Installed? Pin
PeterMoon17-Jan-08 14:02
MemberPeterMoon17-Jan-08 14:02 
QuestionError : Attempted to read or write protected memory... Pin
ar9705-Dec-07 1:59
Memberar9705-Dec-07 1:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.