将 DataSet 导出到多个 Excel 工作表
将 DataSet 中的多个表导出到 Excel 文件中的多个工作表。
引言
我需要将 DataSet 中的多个表导出到带有多个工作表的 Excel 文件中，但找不到真正可用的解决方案。因此，我编写了一个来帮助可能遇到相同情况的人。完整的代码在 ExcelHelper.cs 中展示如下。
这仅适用于 Excel 2003 及更高版本。如果数据集中某个表有超过 65,000 行，它将为该表拆分成多个工作表，工作表名称为 (tableNameX)。添加了 replaceXmlChar() 函数来转义 XML 保留字符。
代码的限制
它无法处理列数超过 256 的数据表（Excel 2003 的列限制），并且当数据表具有非常大的行数时，可能会抛出 OutOfMemory 异常。
Using the Code
要导出 DataSet，只需调用 ExcelHelper.ToExcel() 函数，如下所示
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);
这是执行导出的代码
//ExcelHelper.cs
public class ExcelHelper
{
//Row limits older Excel version per sheet
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
{
//write each row data
var sheetCount = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if ((i % rowLimit) == 0)
{
//add close tags for previous sheet of the same data table
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>");
//write column name row
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);
}
}
历史
- 2008 年 12 月 7 日：初始发布
- 2008 年 12 月 8 日：更新了源代码和文章
- 2008 年 12 月 11 日：更新了源代码和文章