Click here to Skip to main content
11,432,119 members (50,221 online)
Click here to Skip to main content

DataTable to Excel

, 15 Apr 2007 MIT
Rate this:
Please Sign up or sign in to vote.
A quick and easy way to format and export a DataTable to an Excel spreadsheet

Introduction

A common task I've run across is the need to export a DataTable to an Excel spreadsheet. It's a great way to distribute reports, etc. to users. It also seems like an obvious translation, considering the way that DataSets are usually presented in DataGrid controls.

Unfortunately, the methods I have found so far for doing this have been rather ugly - they involve iterating through the DataTable items and manually writing to the output file, line-by-line, in the correct format. Then, in a stroke of luck, I was assigned a simple ASP.NET project at work (I normally work in Windows Forms), and while working on that, I found a very simple way of accomplishing this task, with the added bonus of being able to easily format the output spreadsheet.

Using the code

The basic trick is that Excel can open HTML tables, so the idea is to create a DataGrid web control (which you can use whether or not your project is in ASP.NET), use databinding to fill the control with information from your DataSet, set any formatting (bolded text, background colors, etc), then have the DataGrid render itself to an output file with Excel's ".XLS" extension.

Please note that you need to add a reference to "System.Web" in your project in order to use the System.Web.UI.WebControls namespace!

Here is an example. I am using a DataSet named "data" containing a Table named "Stats":

// create the DataGrid and perform the databinding
System.Web.UI.WebControls.DataGrid grid = 
            new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = data;
grid.DataMember = data.Stats.TableName;

grid.DataBind();

// render the DataGrid control to a file
using(StreamWriter sw = new StreamWriter("c:\\test.xls"))
{
    using(HtmlTextWriter hw = new HtmlTextWriter(sw))
    {
        grid.RenderControl(hw);
    }
}

If you then open the output file in a text editor, you will see that it just contains simple HTML.

Points of Interest

As someone who works mostly with Windows Forms, some of the above code seems non-obvious. Here are some important differences I found between the regular Windows Forms controls and the Web controls:

  • Databinding with Web controls must be performed manually! You set the DataSource and DataMember properties as usual, but the databinding will not occur until you make the "DataBind()" call yourself.
  • Rendering the control requires two output streams: one is the HtmlTextWriter, which is what the Web grid can render itself to, and the second is the StreamWriter, which the HtmlTextWriter writes itself to. Compare this to a normal ASP.NET webpage, where instead of using a StreamWriter to write to a file, the HtmlTextWriter would probably write to a web page in memory.

Finally, a word of warning - once the user has opened the file in Excel and saved changes to it, the file will no longer contain a simple HTML table as Excel will rewrite it with its own file format.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

Jacob Klint
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
QuestionException :( Pin
suvsuv27-Nov-12 20:28
membersuvsuv27-Nov-12 20:28 
GeneralSimple and effective Pin
QCJim6-Jan-12 3:31
memberQCJim6-Jan-12 3:31 
QuestionProblem while exporting special characters like ü Pin
vishal pareek1-Sep-11 3:41
membervishal pareek1-Sep-11 3:41 
QuestionA proper way Pin
CikaPero20-Jul-11 23:29
memberCikaPero20-Jul-11 23:29 
GeneralBest Way Pin
abhijitwalke25-May-11 17:53
memberabhijitwalke25-May-11 17:53 
GeneralMy vote of 4 Pin
Amar Karandikar6-Sep-10 0:39
memberAmar Karandikar6-Sep-10 0:39 
GeneralIt works. Thanks! Pin
larno8-May-10 6:10
memberlarno8-May-10 6:10 
GeneralGreat Help.. Thanks Pin
hardikprogram20-May-09 5:17
memberhardikprogram20-May-09 5:17 
QuestionHow to add formating to Excel after Exporting Pin
Sachin Pisal19-Jan-09 8:05
memberSachin Pisal19-Jan-09 8:05 
GeneralGreat and simple Solution. Thanks! Pin
Member 14319668-Aug-08 14:32
memberMember 14319668-Aug-08 14:32 
General5 from me, too Pin
Scott Bruno27-Jun-08 10:12
memberScott Bruno27-Jun-08 10:12 
GeneralAlternative way Pin
BarbaMariolino17-Jun-08 3:33
memberBarbaMariolino17-Jun-08 3:33 
General5 from me! Pin
Duong Tran14-Jun-08 21:47
memberDuong Tran14-Jun-08 21:47 
Generaldownloading this file Pin
Frank Silva3-Jan-08 3:35
memberFrank Silva3-Jan-08 3:35 
GeneralRe: downloading this file Pin
Jacob Klint3-Jan-08 9:25
memberJacob Klint3-Jan-08 9:25 
This code runs only on the server, so offering a file download has two parts:
1. Someone clicks the "download" link or button on your page, and in the handler for that you run the code to create the excel file and temporarily stash it somewhere on the SERVER (as you saw, you can use variables to set the file path).

2. As with any other file download, you can redirect the Response to this file you just created (if it's in a web-accessible location), or just write the binary file to the Response with appropriate MIME info. When the client's web browser sees a file coming back it will let the user select a place to save it on his own computer.
GeneralHello Pin
farhit26-Oct-07 2:18
memberfarhit26-Oct-07 2:18 
Generalmay need flush and close, too Pin
CathieA31-Jul-07 11:45
memberCathieA31-Jul-07 11:45 
GeneralRe: may need flush and close, too Pin
Jacob Klint14-Aug-07 14:08
memberJacob Klint14-Aug-07 14:08 
QuestionSupported by Excel 2000? Pin
lizrd82512-Jun-07 11:36
memberlizrd82512-Jun-07 11:36 
AnswerRe: Supported by Excel 2000? Pin
Jacob Klint12-Jun-07 14:18
memberJacob Klint12-Jun-07 14:18 
GeneralRe: Supported by Excel 2000? Pin
lizrd82513-Jun-07 11:10
memberlizrd82513-Jun-07 11:10 
GeneralThanks - saved me loads of time! Pin
nzmike8-May-07 13:32
membernzmike8-May-07 13:32 
Questioncovert to VB Pin
bobcarrretta17-Apr-07 7:32
memberbobcarrretta17-Apr-07 7:32 
AnswerRe: covert to VB Pin
Ben[dog]17-Apr-07 12:55
memberBen[dog]17-Apr-07 12:55 
GeneralThis is not how you export data! Pin
yang yu 179999916-Apr-07 7:48
memberyang yu 179999916-Apr-07 7:48 
GeneralRe: This is not how you export data! [modified] Pin
Jacob Klint16-Apr-07 9:23
memberJacob Klint16-Apr-07 9:23 
GeneralRe: This is not how you export data! Pin
michael1watson18-Apr-07 2:04
membermichael1watson18-Apr-07 2:04 
GeneralRe: This is not how you export data! Pin
Ben[dog]17-Apr-07 4:40
memberBen[dog]17-Apr-07 4:40 
GeneralRe: This is not how you export data! Pin
Jacob Klint17-Apr-07 9:56
memberJacob Klint17-Apr-07 9:56 
GeneralRe: This is not how you export data! Pin
TiamatPt18-Apr-07 5:12
memberTiamatPt18-Apr-07 5:12 
GeneralRe: This is not how you export data! Pin
CikaPero27-Apr-11 0:03
memberCikaPero27-Apr-11 0:03 
Questionusing an existing xls file Pin
Seishin#15-Apr-07 22:52
memberSeishin#15-Apr-07 22:52 
AnswerRe: using an existing xls file Pin
Jacob Klint17-Apr-07 14:48
memberJacob Klint17-Apr-07 14:48 
GeneralRe: using an existing xls file Pin
Seishin#17-Apr-07 20:12
memberSeishin#17-Apr-07 20:12 
AnswerRe: using an existing xls file Pin
gg423712-Nov-09 2:13
membergg423712-Nov-09 2:13 
GeneralThe idea is good! Pin
Nice Life15-Apr-07 22:10
memberNice Life15-Apr-07 22:10 

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.150428.2 | Last Updated 15 Apr 2007
Article Copyright 2007 by Jacob Klint
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid