DataTable to Excel - CodeProject

Download demo project - 15.7 KB

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.

You must Sign In to use this message board.


Per page

Great Help.. Thanks
hardikprogram

5:17 20 May '09

a
Sign In·View Thread·PermaLink

How to add formating to Excel after Exporting
Sachin Pisal

8:05 19 Jan '09

This method of exporting is really awesome meanwhile I have one question how to keep apply formating for excel , I means the date time is exporting as time part 00:00 or if any column as currency then how to apply currency formate Regards Sac
Sign In·View Thread·PermaLink	1.50/5

Great and simple Solution. Thanks!
Member 1431966

14:32 8 Aug '08

I'ved worked with several methods to export tables to excel and all of them suck! Plus most of them have bad memory management and are more slow. This is just Great for a simple task. Thanks a lot! Ariel
Sign In·View Thread·PermaLink	2.00/5

5 from me, too
Scott Bruno

10:12 27 Jun '08

Clever use of existing stuff. The people crying about this article should take a deep breath and consider that sometimes you just need to dump a simple table to xls. And given that the final product is in fact a table dumped to xls, I'm gonna' have to go ahead and say it works just fine. -- Saving the world, one line of code at a time
Sign In·View Thread·PermaLink

Alternative way
BarbaMariolino

3:33 17 Jun '08

Hi,

As you probably know using third party components is the best way to import/export Excel files. I recommend you to try GemBox.Spreadsheet - .NET component for importing/exporting Excel files (XLS, XLSX and CSV) and writing to HTML[^]. Here is an example that shows how to import/export DataTable to Excel[^].

There is also GemBox.Spreadsheet free version[^] available. Free version comes with 150 rows limit and can be used in commercial applications.

Mario
GemBox Software[^]
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX or CSV)
or export to HTML files from your .NET apps[^].
--

5 from me!
Duong Tran

21:47 14 Jun '08

Great ideal. Thanks! Something should remember but some thing should forget. Your life will brighter
Sign In·View Thread·PermaLink

downloading this file
Frank Silva

3:35 3 Jan '08

I implemented this way in an ASP.NET (here they does VB, I don't) application I'm developing. IT is working correctly, however the path is kinda burnt in code. I try using a fileUpload control to allow the user to chose a location, and it did not work. I also try using some environment variables but they are server related, so I can not access a path in the client machine.

How it is possible to make it work as a download process?

thanks a lot.

btw many ppl uses this or other similar ways to export to excel. since you don't need fancy files and you don't have lot of time it works just fine with datatables of more than 3000 rows. So I have to thank you.

Frank Silva

Re: downloading this file
Jacob Klint

9:25 3 Jan '08

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.

Hello
farhit

2:18 26 Oct '07

You are the man... Regards, Farrukh Malik +92-300-2564099
Sign In·View Thread·PermaLink

may need flush and close, too
CathieA

11:45 31 Jul '07

This article helped me a lot, but then I found that my input spreadsheet (717 rows, 7 columns) lost some rows when I saved it after adding 6 columns to it. The solution was to flush and close the streamwriter. That gave me the entire output spreadsheet. (My apologies to the C# folk, but I work in VB.NET.)

			Dim grid As System.Web.UI.WebControls.DataGrid
			grid = New System.Web.UI.WebControls.DataGrid
			grid.DataSource = objDataset2
			grid.DataMember = objDataset2.Tables(0).TableName
			grid.DataBind()
			Dim sw As StreamWriter
			sw = New StreamWriter(savNewSpreadsheetDialog.FileName)
			Dim hw As HtmlTextWriter
			hw = New HtmlTextWriter(sw)
			grid.RenderControl(hw)
			sw.Flush()
			sw.Close()

Re: may need flush and close, too
Jacob Klint

14:08 14 Aug '07

Excellent point! I will add that info if/when I update the article. For those using C#, if you utilize the "using" blocks as in the example, manually flushing the streamwriter is not necessary since the runtime will automatically handle that when exiting the using block.
Sign In·View Thread·PermaLink

Supported by Excel 2000?
lizrd825

11:36 12 Jun '07

Does anyone know if this works with Excel 2000? I'm searching for a export to excel solution supported by Excel 2000+. XML Spreadsheet format is only supported by Excel 2002/2003+ I believe, and I want to refrain from csv just because I need to export multiple tables (ideally a workbook with multiple sheets) and having some formatting would be nice. Thanks!
Sign In·View Thread·PermaLink

Re: Supported by Excel 2000?
Jacob Klint

14:18 12 Jun '07

I don't have office 2000 handy but according to one site "Excel 97+ can import (and export) an HTML table" (http://www.dpawson.co.uk/xsl/sect4/spreadsheet.html)

Other sites seem to concur.

Also, I kinda doubt it's possible to create a workbook w/ multiple sheets using this method, but I could be wrong! You might try creating a multisheet excel doc and exporting it to HTML, then take a look at what it spits out. You might be able to parse the proper format from there.

Re: Supported by Excel 2000?
lizrd825

11:10 13 Jun '07

Hi- Thanks for your reply. I found online this bit of code that will create a second worksheet but then couldn't figure out how to actually write to this second sheet:

<html xmlns Red faced

="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
   <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
   <meta name=ProgId content=Excel.Sheet>
   <meta name=Generator content="Microsoft Excel 9">
</head>

<body link=blue vlink=purple>


<xml>
<x:ExcelWorkbook>
   <x:ExcelWorksheets>
   <x:ExcelWorksheet>
      <x:Name>data1</x:Name>
      <x:WorksheetOptions>
      <x:Selected/>
      <x:ProtectContents>False</x:ProtectContents>
      <x:ProtectObjects>False</x:ProtectObjects>
      <x:ProtectScenarios>False</x:ProtectScenarios>
      </x:WorksheetOptions>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
      <x:Name>data2</x:Name>
      <x:WorksheetOptions>
      <x:Selected/>
      <x:ProtectContents>False</x:ProtectContents>
      <x:ProtectObjects>False</x:ProtectObjects>
      <x:ProtectScenarios>False</x:ProtectScenarios>
      </x:WorksheetOptions>
   </x:ExcelWorksheet>
   </x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>

<table x:str border=0 cellpadding=0 cellspacing=0 width=128 style='border-collapse:
collapse;table-layout:fixed;width:96pt'>
<col width=64 span=2 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
   <td height=17 width=64 style='height:12.75pt;width:48pt'>S1_R1_C1</td>
   <td width=64 style='width:48pt'>S1_R1_C2</td>
   <td width=64 style='width:48pt'>S1_R1_C3</td>
</tr>
<tr height=17 style='height:12.75pt'>
   <td height=17 width=64 style='height:12.75pt;width:48pt'>S1_R2_C1</td>
   <td width=64 style='width:48pt'>S1_R2_C2</td>
   <td width=64 style='width:48pt'>S1_R2_C3</td>
</tr>
</table>

<xml>
<x:ExcelWorkbook>
   <x:ActiveSheet>1</x:ActiveSheet>
</x:ExcelWorkbook>
</xml>



<table x:str border=0 cellpadding=0 cellspacing=0 width=128 style='border-collapse:
collapse;table-layout:fixed;width:96pt'>
<col width=64 span=2 style='width:48pt'>
<tr height=17 style='height:12.75pt'>
   <td height=17 width=64 style='height:12.75pt;width:48pt'>S2_R1_C1</td>
   <td width=64 style='width:48pt'>S2_R1_C2</td>
   <td width=64 style='width:48pt'>S2_R1_C3</td>
</tr>
<tr height=17 style='height:12.75pt'>
   <td height=17 style='height:12.75pt'>Row2</td>
   <td>Col2</td>
</tr>

</table>

</body>
</html>

Then I found that if you have multiple worksheets, then you have to use the <x:WorksheetSource> tag to specify the corresponding worksheet HTML file. This is an excerpt from the MS Office HTML and XML Reference:

"When a workbook contains a single worksheet, the Name, WorksheetOptions, and WorksheetSource subelements are required and stored in the workbook HTML file.

When a workbook contains multiple worksheets, only the Name and WorksheetSource subelements are required in the workbook HTML file. The WorksheetOptions element is specified in the corresponding worksheet HTML file as a subelement of the XML element."

Here is an example:

<x:ExcelWorkbook>
   <x:ExcelWorksheets>
   <x:ExcelWorksheet>
      <x:Name>Sheet1</x:Name>
      <x:WorksheetSource HRef="./Book4_files/sheet001.htm"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
      <x:Name>Sheet2</x:Name>
      <x:WorksheetSource HRef="./Book4_files/sheet002.htm"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
      <x:Name>Sheet3</x:Name>
      <x:WorksheetSource HRef="./Book4_files/sheet003.htm"/>
   </x:ExcelWorksheet>
   <x:ExcelWorksheet>
      <x:Name>MyChart</x:Name>
      <x:WorksheetSource HRef="./Book4_files/chart001.htm"/>
      <x:WorksheetType>Chart</x:WorksheetType>
   </x:ExcelWorksheet>
   </x:ExcelWorksheets>
</x:ExcelWorkbook>

Decided to give my users the option to export as Excel 2003+ (generate XML Spreadsheet format file) or export a set of .csv files. Thanks- Liz

Thanks - saved me loads of time!
nzmike

13:32 8 May '07

This is ingenious - I had screeds of code by which I was trying to do this same sort of thing (without much success) and I ended up removing all of it and using your very neat little piece of code instead!
Sign In·View Thread·PermaLink	4.00/5

covert to VB
bobcarrretta

7:32 17 Apr '07

I tried converting to VB:

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim path As String
path = "c:\\test.xls"
Dim sw As StreamWriter = New StreamWriter(path)
Dim hw = New HtmlTextWriter(sw)
gvEditDetail.RenderControl(hw)
end sub

though it compiled , the app yields an error:

Control 'ctl00_ContentPlaceHolder1_gvEditDetail' of type 'GridView' must be placed inside a form tag with runat=server.

I am using a content_placeholder on the page and apparently it contains it's own <form runat="server"></form> tag
This routine seems to have difficulty with that.

Any suggestions about using it with a Masterpages's content placeholder?
Thanks.

Re: covert to VB
Ben[dog]

12:55 17 Apr '07

It seems that you have actually inserted the DataGrid into your page, within a placeholder "ContentPlaceHolder1".

You need not do this. This generates an output to a file, not a page.

If you look at the original code, the DataGrid is created temporarily in memory, then its RenderControl method is used to generate the right output into a file through an HtmlTextWriter to a stream.

Remove your gvEditDetail from the page and try inserting the VB equivalent of the first lines of code that create the Datagrid and binds it into your Button2_Click handler.

Ben

This is not how you export data!
yang yu 1799999

7:48 16 Apr '07

This is not how you export any kind of data to any other data format. There are various data transformation methods including xml transformer or DTS packages, SSIS, or simply Data reconstruction. But, you do not ever export to html then hope an external application will understand html then save it using its own transform method. Horrible horrible article.
Sign In·View Thread·PermaLink	2.31/5

Re: This is not how you export data! [modified]
Jacob Klint

9:23 16 Apr '07

"horrible" - that's a little harsh, n'est-ce pas? Smile

Excel has official support for HTML data - just go to File->Open, and you'll see "*.html" files in its list of openable formats. All I've done here is to change the file extension to force Excel to open the file rather than a web browser.

Certainly this method doesn't work for all situations, but for straightforward, one-way transfer of raw data from code to Excel (along with style options), I've yet to find a simpler method. If you have, however, then please write it up in an article for all our benefit.

Re: This is not how you export data!
michael1watson

2:04 18 Apr '07

What about just creating a .csv file?
It's only basic text though without formatting or graphics, but opens in Excel just fine.
I'd imagine you'd just be looping through the rows and cells (nested loop!) of the DataTable and writing out to a text file (via a StringBuilder), simply comma-separating the cell values and watching out for quotes in the cell content... ?

Oherwise, thank you for your very nice article presenting a handy, quick way to export data via a datagrid! Saving it to my snippets right now...

Re: This is not how you export data!
Ben[dog]

4:40 17 Apr '07

Sorry sir, but this is a really nice technique, on complement of being a cuning use of existing controls and their rendering capabilities using very little coding, it does the job just fine for a basic export.
I've had to work on quick fixes like this to give some clients a simple export to excel function, all that in a cost effective development manner and this is just right.

Yes one could spend 30 hours doing it proper with DTS packages, XML Decepticons transformers, whatever one's into, but for a sweet sweet 20 minutes on-the-fly solution, there you have it.

Spot on Jacob.

Great Great article. Cool

Re: This is not how you export data!
Jacob Klint

9:56 17 Apr '07

Thanks Ben, glad to see someone else understands the usefulness of this. Hmm..."XML Decepticons transformers"...that's in the "System.Xml.StuffThatWasCoolInTheEighties" namespace, right?
Sign In·View Thread·PermaLink	5.00/5

Re: This is not how you export data!
TiamatPt

5:12 18 Apr '07

Like already mentioned, when you are simply trying to export a structure or data, this method is very very good. I don't know about other people, but my time is short and if I can have just what I need with so little work, then all I can say is thank you.
Sign In·View Thread·PermaLink	3.00/5

using an existing xls file
Seishin#

22:52 15 Apr '07

Hi! this is really useful, but what if I'm using an already made xls file as a template with few sheets and formulas? right now i just copy the template file and fill proper cells row-by-row.. life is study!!!
Sign In·View Thread·PermaLink	2.75/5

Re: using an existing xls file
Jacob Klint

14:48 17 Apr '07

I'm guessing you're opening the template file in code as just a text stream, then doing a search and replace to fill in the proper cells? Or are you doing something else?

Off the top of my head, I don't think the method in this article would help you much. It's really meant for situations where you already do all the processing within a dataset and just want to send that data off to the outside world, not where you want to modify an existing spreadsheet.