Introduction
I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets, and I couldn't find anything that actually works. So, I wrote one to help people who might be in the same situation. The complete code in ExcelHelper.cs is shown below.
This only works for Excel 2003 and later versions. If a table in the dataset has more than 65,000 rows, it will break it into multiple sheets for the table with sheet name (tableNameX). replaceXmlChar() function is added to escape XML reserved characters.
Limitations of the Code
It does not handle data tables with more than 256 columns (Excel 2003 column limit), and when data tables have very large rows count, it might throw OutOfMemory exception.
Using the Code
To export a DataSet, just call the ExcelHelper.ToExcel() function as follows:
var ds = new DataSet();
var dt = new DataTable("TableName For Sheet1");
dt.Columns.Add("col1");
dt.Columns.Add("col2");
dt.Rows.Add("Value1", "Value2");
var dt2 = new DataTable("TableName For Sheet2");
dt2.Columns.Add("col1");
dt2.Columns.Add("col2");
dt2.Rows.Add("Value1", "Value2");
ds.Tables.Add(dt);
ds.Tables.Add(dt2);
ExcelHelper.ToExcel(ds, "test.xls", Page.Response);
Here is the code that does the exporting:
public class ExcelHelper
{
const int rowLimit = 65000;
private static string getWorkbookTemplate()
{
var sb = new StringBuilder();
sb.Append("<xml version>\r\n<Workbook
xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n
xmlns:x=\"urn:schemas- microsoft-com:office:excel\"\r\n
xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n");
sb.Append(" <Styles>\r\n
<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n
<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");
sb.Append("\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>\r\n
<Protection/>\r\n </Style>\r\n
<Style ss:ID=\"BoldColumn\">\r\n <Font ");
sb.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n
<Style ss:ID=\"s62\">\r\n <NumberFormat");
sb.Append(" ss:Format=\"@\"/>\r\n </Style>\r\n
<Style ss:ID=\"Decimal\">\r\n
<NumberFormat ss:Format=\"0.0000\"/>\r\n </Style>\r\n ");
sb.Append("<Style ss:ID=\"Integer\">\r\n
<NumberFormat ss:Format=\"0\"/>\r\n </Style>\r\n
<Style ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
sb.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n
<Style ss:ID=\"s28\">\r\n");
sb.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\"
ss:ReadingOrder=\"LeftToRight\" ss:WrapText=\"1\"/>\r\n");
sb.Append("<Font x:CharSet=\"1\" ss:Size=\"9\"
ss:Color=\"#808080\" ss:Underline=\"Single\"/>\r\n");
sb.Append("<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/>
</Style>\r\n</Styles>\r\n {0}</Workbook>");
return sb.ToString();
}
private static string replaceXmlChar(string input)
{
input = input.Replace("&", "&");
input = input.Replace("<", "<");
input = input.Replace(">", ">");
input = input.Replace("\"", """);
input = input.Replace("'", "'");
return input;
}
private static string getWorksheets(DataSet source)
{
var sw = new StringWriter();
if (source == null || source.Tables.Count == 0)
{
sw.Write("<Worksheet ss:Name=\"Sheet1\"><Table><Row>
<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data>
</Cell></Row></Table></Worksheet>");
return sw.ToString();
}
foreach (DataTable dt in source.Tables)
{
if (dt.Rows.Count == 0)
sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) +
"\"><Table><Row><Cell ss:StyleID=\"s62\">
<Data ss:Type=\"String\"></Data></Cell></Row>
</Table></Worksheet>");
else
{
var sheetCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if ((i % rowLimit) == 0)
{
if ((i / rowLimit) > sheetCount)
{
sw.Write("</Table></Worksheet>");
sheetCount = (i / rowLimit);
}
sw.Write("<Worksheet ss:Name=\"" +
replaceXmlChar(dt.TableName) +
(((i / rowLimit) == 0) ? "" :
Convert.ToString(i / rowLimit)) + "\"><Table>");
sw.Write("<Row>");
foreach (DataColumn dc in dt.Columns)
sw.Write(
string.Format(
"<Cell ss:StyleID=\"BoldColumn\">
<Data ss:Type=\"String\">{0}</Data></Cell>",
replaceXmlChar(dc.ColumnName)));
sw.Write("</Row>\r\n");
}
sw.Write("<Row>\r\n");
foreach (DataColumn dc in dt.Columns)
sw.Write(
string.Format(
"<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">
{0}</Data></Cell>",
replaceXmlChar
(dt.Rows[i][dc.ColumnName].ToString())));
sw.Write("</Row>\r\n");
}
sw.Write("</Table></Worksheet>");
}
}
return sw.ToString();
}
public static string GetExcelXml(DataTable dtInput, string filename)
{
var excelTemplate = getWorkbookTemplate();
var ds = new DataSet();
ds.Tables.Add(dtInput.Copy());
var worksheets = getWorksheets(ds);
var excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}
public static string GetExcelXml(DataSet dsInput, string filename)
{
var excelTemplate = getWorkbookTemplate();
var worksheets = getWorksheets(dsInput);
var excelXml = string.Format(excelTemplate, worksheets);
return excelXml;
}
public static void ToExcel
(DataSet dsInput, string filename, HttpResponse response)
{
var excelXml = GetExcelXml(dsInput, filename);
response.Clear();
response.AppendHeader("Content-Type", "application/vnd.ms-excel");
response.AppendHeader
("Content-disposition", "attachment; filename=" + filename);
response.Write(excelXml);
response.Flush();
response.End();
}
public static void ToExcel
(DataTable dtInput, string filename, HttpResponse response)
{
var ds = new DataSet();
ds.Tables.Add(dtInput.Copy());
ToExcel(ds, filename, response);
}
}
History
- 7th December, 2008: Initial post
- 8th December, 2008: Source code and article updated
- 11th December, 2008: Source code and article updated
