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 DataSet
s 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
":
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();
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.