Click here to Skip to main content
11,567,926 members (46,708 online)
Click here to Skip to main content

Fast Exporting from DataSet to Excel

, 1 Dec 2007 CPOL 205.6K 11.2K 93
Rate this:
Please Sign up or sign in to vote.
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:

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

// 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)

Share

About the Author

PeterMoon
Software Developer (Senior)
Ecuador Ecuador
No Biography provided

You may also be interested in...

Comments and Discussions

 
SuggestionThere also another efficient way of exporting data to excel Pin
Senthil Sambandam14-Nov-14 16:50
professionalSenthil Sambandam14-Nov-14 16:50 
Questionsolution for this? Pin
NekoNao21-Sep-14 20:01
memberNekoNao21-Sep-14 20:01 
GeneralAmazing Pin
RickoT26-Aug-14 12:21
memberRickoT26-Aug-14 12:21 
QuestionSee also this: Pin
Dietmar Schoder29-Jul-14 5:23
professionalDietmar Schoder29-Jul-14 5:23 
QuestionREALLY FREAKING FAST THANKS SO MUCH! Pin
Member 1055846615-Apr-14 5:39
memberMember 1055846615-Apr-14 5:39 
QuestionJFIGUEROA Pin
jfigueroa212-Apr-14 8:04
memberjfigueroa212-Apr-14 8:04 
QuestionHi Pin
Mylog.2522-Feb-14 22:53
memberMylog.2522-Feb-14 22:53 
GeneralMy vote of 5 Pin
rensq880829-Aug-13 20:06
memberrensq880829-Aug-13 20:06 
GeneralThank you! Pin
Barbara_IT_6828-Jul-13 1:47
memberBarbara_IT_6828-Jul-13 1:47 
QuestionGreat !!! Fastest !!! Pin
nhwaynhwaycho20-Jun-13 17:03
membernhwaynhwaycho20-Jun-13 17:03 
QuestionSUPER FAST. Pin
AliN Indrei29-Apr-13 9:52
memberAliN Indrei29-Apr-13 9:52 
QuestionGREAT! Pin
Daniel Carvalho Liedke17-Aug-12 7:28
memberDaniel Carvalho Liedke17-Aug-12 7:28 
GeneralGreat Project Pin
we6art19-Apr-12 1:44
memberwe6art19-Apr-12 1:44 
Suggestiona close-safe approach [arranged code] Pin
Arnaud Dovi6-Mar-12 5:11
memberArnaud Dovi6-Mar-12 5:11 
GeneralRe: a close-safe approach [arranged code] Pin
bgsjust11-Jun-13 14:07
memberbgsjust11-Jun-13 14:07 
GeneralMy vote of 5 Pin
JohnCollett14-Jan-12 5:02
memberJohnCollett14-Jan-12 5:02 
GeneralMy vote of 5 Pin
Oshtri Deka21-Oct-11 4:24
memberOshtri Deka21-Oct-11 4:24 
QuestionMy vote of 5 Pin
paolo.balbarini23-Sep-11 3:33
memberpaolo.balbarini23-Sep-11 3:33 
QuestionAlternative Pin
CikaPero24-Jul-11 20:51
memberCikaPero24-Jul-11 20:51 
AnswerRe: Alternative Pin
adilson2105-Jan-12 14:10
memberadilson2105-Jan-12 14:10 
GeneralMy vote of 5 Pin
dharaneendra13-Mar-11 21:34
memberdharaneendra13-Mar-11 21:34 
Generalgetting stuck at excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData; Pin
john_17264-Jan-11 10:15
memberjohn_17264-Jan-11 10:15 
GeneralMy vote of 5 Pin
Stefano Manni15-Oct-10 1:18
memberStefano Manni15-Oct-10 1:18 
GeneralExcelente ejemplo! Pin
vicentehg30-Sep-10 8:55
membervicentehg30-Sep-10 8:55 
GeneralTIP: Cell value starts with equal symbol Pin
GQ201015-Sep-10 12:40
memberGQ201015-Sep-10 12:40 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150624.2 | Last Updated 1 Dec 2007
Article Copyright 2007 by PeterMoon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid