将数据导出到多个工作表的 Excel 中
3.05/5 (13投票s)
如何将数据导出到 Excel 的多个工作表中。
引言
所有开发人员都会遇到将 DataGrid 数据放入 Excel 表格的情况。 在本文中，我将向您展示如何将 DataGrid 数据导出到 Excel 文件。
将 DataGrid 导出到 Excel
在这里，导出到 Excel 是通过动态构建 Excel XML 代码来实现的。 这种方法的优点是可以创建任意数量的工作表。 这解决了 Excel 在记录数超过 63000 时截断记录的问题。
Using the Code
下面简要描述了如何使用该代码。 在您的网页的代码隐藏文件或单独的类中使用以下函数。 该函数动态生成 Excel 的 XML 代码。 它返回一个 StringWriter 对象。
public System.IO.StringWriter ExportToExcelXML(DataSet source)
{
System.IO.StringWriter excelDoc;
excelDoc = new System.IO.StringWriter();
StringBuilder ExcelXML = new StringBuilder();
ExcelXML.Append("<xml version>\r\n<Workbook ");
ExcelXML.Append("xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
ExcelXML.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n ");
ExcelXML.Append("xmlns:x=\"urn:schemas- microsoft-com:office:");
ExcelXML.Append("excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:");
ExcelXML.Append("office:spreadsheet\">\r\n <Styles>\r\n ");
ExcelXML.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n ");
ExcelXML.Append("<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");
ExcelXML.Append("\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>");
ExcelXML.Append("\r\n <Protection/>\r\n </Style>\r\n ");
ExcelXML.Append("<Style ss:ID=\"BoldColumn\">\r\n <Font ");
ExcelXML.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n ");
ExcelXML.Append("<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat");
ExcelXML.Append(" ss:Format=\"@\"/>\r\n </Style>\r\n <Style ");
ExcelXML.Append("ss:ID=\"Decimal\">\r\n <NumberFormat ");
ExcelXML.Append("ss:Format=\"0.0000\"/>\r\n </Style>\r\n ");
ExcelXML.Append("<Style ss:ID=\"Integer\">\r\n <NumberFormat ");
ExcelXML.Append("ss:Format=\"0\"/>\r\n </Style>\r\n <Style ");
ExcelXML.Append("ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
ExcelXML.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n ");
ExcelXML.Append("<Style ss:ID=\"s28\">\r\n");
ExcelXML.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\"
ss:ReadingOrder=\"LeftToRight\" ss:WrapText=\"1\"/>\r\n");
ExcelXML.Append("<Font x:CharSet=\"1\" ss:Size=\"9\" ss:Color=\"#808080\"
ss:Underline=\"Single\"/>\r\n");
ExcelXML.Append("<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/>
</Style>\r\n");
ExcelXML.Append("</Styles>\r\n ");
string startExcelXML = ExcelXML.ToString();
const string endExcelXML = "</Workbook>";
int rowCount = 0;
int sheetCount = 1;
excelDoc.Write(startExcelXML);
excelDoc.Write("<Worksheet ss:Name=\"Report_Sheet" +
sheetCount + "\">");
excelDoc.Write("<Table>");
///Header Part
// Add any Header for the report
///
excelDoc.Write("<Row ss:AutoFitHeight=\"0\" ss:Height=\"6.75\"/>\r\n");
excelDoc.Write("<Row><Cell ss:MergeAcross=\"10\" ss:StyleID=\"s34\">
<Data ss:Type=\"String\">");
excelDoc.Write("HEADER TEXT");
excelDoc.Write("</Data></Cell>");
excelDoc.Write("<Cell ss:MergeAcross=\"1\" ss:StyleID=\"BoldColumn\">
<Data ss:Type=\"String\">");
excelDoc.Write("Report Date");
excelDoc.Write("</Data></Cell>");
excelDoc.Write("<Cell ss:MergeAcross=\"1\" ss:StyleID=\"DateLiteral\">
<Data ss:Type=\"String\">");
excelDoc.Write(GetDate(DateTime.Now.ToShortDateString()));
excelDoc.Write("</Data></Cell></Row>");
excelDoc.Write("<Row ss:AutoFitHeight=\"0\" ss:Height=\"10\"/>\r\n");
///Complete
excelDoc.Write("<Row>");
for (int x = 0; x < source.Tables[0].Columns.Count; x++)
{
excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
excelDoc.Write("</Data></Cell>");
}
excelDoc.Write("</Row>");
foreach (DataRow x in source.Tables[0].Rows)
{
rowCount++;
//if the number of rows is > 63000 create a new page to continue output
if (rowCount == 63000)
{
rowCount = 0;
sheetCount++;
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write("<Worksheet ss:Name=\"Report_Sheet" +
sheetCount + "\">");
excelDoc.Write("<Table>");
excelDoc.Write("<Row>");
for (int xi = 0; xi < source.Tables[0].Columns.Count; xi++)
{
excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write(source.Tables[0].Columns[xi].ColumnName);
excelDoc.Write("</Data></Cell>");
}
excelDoc.Write("</Row>");
}
excelDoc.Write("<Row>");
for (int y = 0; y < source.Tables[0].Columns.Count; y++)
{
string XMLstring = x[y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "&");
XMLstring = XMLstring.Replace(">", ">");
XMLstring = XMLstring.Replace("<", "<");
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write(XMLstring);
excelDoc.Write("</Data></Cell>");
}
excelDoc.Write("</Row>");
}
///Ending Tag
///
excelDoc.Write("<Row ss:Height=\"15\"><Cell ss:HRef=\http://www.sachin" +
"kumark.com\ss:MergeAcross=\"2\" ss:StyleID" +
"=\"s28\"><Data ss:Type=\"String\">");
excelDoc.Write("www.sachinkumark.com");
excelDoc.Write("</Data></Cell></Row>");
excelDoc.Write("<Row ss:Height=\"15\"><Cell ss:MergeAcross=\"6\"
ss:StyleID=\"s28\"><Data ss:Type=\"String\">");
excelDoc.Write("Copyright © 2007");
excelDoc.Write("</Data></Cell></Row>");
///Complete
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write(endExcelXML);
return excelDoc;
}
//
上述函数可以像下面这样从按钮事件中调用
private void lnkExcel1_Click(object sender, System.EventArgs e)
{
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=Report.xls");
this.EnableViewState = false;
Response.Charset = string.Empty;
System.IO.StringWriter myTextWriter = new System.IO.StringWriter();
myTextWriter = ExportToExcelXML(Dataset);
Response.Write(myTextWriter.ToString());
Response.End();
}
将 DataSet 作为输入传递给该函数。 如有任何疑问，请在下面的讨论区发帖。
关注点
一旦您理解了模式，就可以操作代码以格式化 Excel 内部的行。