In many cases Microsoft Excel can do much for you and reduce your programming efforts. For example, if you must provide a printable version of a data collection or you need to provide a report with grouping, you may send all information to Excel and let the end-user do it with Excel. Therefore, knowing how to export a data collection (i.e. a DataTable) to Microsoft Excel can be helpful and sometimes reliving!
In ASP.NET, you may export a GridView control to excel but I do not think it is a good way (except in certain cases) because we mostly do not display the whole columns and rows in a grid (i.e. for paging reason). Plus, exporting a GridView control needs to re-render the grid into response output stream and it might be time/resource consuming.
In the rest of this article we will write a handy function to export a DataTable to Microsoft Excel. The features of this function are as follows:
1. Is very quick
2. Is very lightweight
3. You may choose which columns to be exported
4. You may specify your desired encoding
Ok let’s get started. The method we are going to write is named ExportToSpreadsheet and have the following signature:
public static void ExportToSpreadsheet(DataTable table, string name, string ColumnList)
The 1st argument is the DataTable object we are going to export. The 2nd one is the name of generated target file and ColumnList is a comma-separated list of columns to be exported. Not always do we export all columns. Thus we should be able to choose which columns to be exported.
The file we are going to generate is a Comma Separated Value (.csv) file because Excel knows this file and opens it like a spread-sheet. In the other hand, we do not have to create a real .xls file.
Ok, for the implementation part we first convert the ColumnList argument to an IList collection. This is necessary because we need to find a column name in the list later.
IList<string> collist = new List<string>();
string columnstr = ColumnList.ToUpper().Split(‘,’);
foreach (string s in columnstr)
The above code splits the ColumnList argument into a string array and adds each string item to an IList object. I had to use foreach statement because the code was written in C# 2.0. In C# 3.0 you may omit Foreach and use .ToList() extension instead. I mean the above code will get shorter in this way:
IList<string> collist = ColumnList.ToUpper().Split(‘,’).ToList();
Since we are sending the .CSV file to HttpResponse, we create a new HttpContext instance, and set the ContentEncoding property. We would better set ContentEncoding since the encoding used in clients might be different from the response encoding! For example suppose you have a website whose response encoding is UTF-8 but your clients use Windows-1256 encoding. If you export the .CSV file in UTF-8 format clients will not show the strings correctly. In this code I have used Encoding.Default but you may let the the encoding to be chosen as a new argument for ExportToSpreadsheet method.
context.Response.ContentEncoding = Encoding.Default;
Afterwards, we start to loop over the DataTable.Columns collection to create the titles. For column titles I have used DataColumn.Caption property (and not DataColumn.ColumnName) since the title is not necessarily equal to the column name. For instance a column name might be Cust_Title but the caption should be “Customer’s Full Name”. I recommend to use typed datasets because setting such properties are very easy in VS.:
foreach (DataColumn column in table.Columns)
if (ColumnList==”” || collist.Contains(column.ColumnName.ToUpper()))
context.Response.Write(column.Caption + “;”);
After creating column titles, we loop over rows and add them to the .csv :
foreach (DataRow row in table.Rows)
foreach(DataColumn column in table.Columns)
if (ColumnList == “” || collist.Contains(column.ColumnName.ToUpper()))
context.Response.Write(row[column].ToString().Replace(“;”, string.Empty) + “;”);
At the end, we alter the http header in following way to get the download process started:
context.Response.ContentType = “text/csv”;
context.Response.AppendHeader(“Content-Disposition”, “attachment; filename=” + name);
That’s it. You may download a .PDF version of this article HERE.