我想导出一个DataSet中的多个表到一个有多个工作薄的EXCEL文件中去。但是我无法找到一个真正的解决此问题的工程项目.所以。我就写一个能够帮助人们可能在同样的情况下的完整的代码ExcelHelper.cs
代码
要导出一个DataSet ,只需调用ExcelHelper.ToExcel()函数如下:
- //ExcelHelper.cs
- public class ExcelHelper
- {
- const int rowLimit = 63000;
- 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=/"StringLiteral/">" +
- "/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 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=/"StringLiteral/">" +
- "<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=/"" + dt.TableName +
- "/"><Table><Row><Cell " +
- "ss:StyleID=/"StringLiteral/"><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=/"" + 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>",
- 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=/"StringLiteral/">" +
- "<Data ss:Type=/"String/">{0}</Data></Cell>",
- dt.Rows[i][dc.ColumnName]));
- sw.Write("</Row>/r/n");
- }
- sw.Write("</Table></Worksheet>");
- }
- }
- return sw.ToString();
- }
- public static void ToExcel(DataSet dsInput,
- string filename, HttpResponse response)
- {
- var excelTemplate = getWorkbookTemplate();
- var worksheets = getWorksheets(dsInput);
- var excelXml = string.Format(excelTemplate, worksheets);
- 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);
- }
- }