导出DataSet 到多个Excel工作薄

导言

  我想导出一个DataSet中的多个表到一个有多个工作薄的EXCEL文件中去。但是我无法找到一个真正的解决此问题的工程项目.所以。我就写一个能够帮助人们可能在同样的情况下的完整的代码ExcelHelper.cs

代码

  要导出一个DataSet ，只需调用ExcelHelper.ToExcel()函数如下：

 

1. //ExcelHelper.cs
3. public class ExcelHelper
4. {
5.     
6.     const int rowLimit = 63000;
8.     private static string getWorkbookTemplate()
9.     {
10.         var sb = new StringBuilder();
11.         sb.Append("<xml version>/r/n<Workbook xmlns=/"urn:schemas" + 
12.                   "-microsoft-com:office:spreadsheet/"/r/n");
13.         sb.Append(" xmlns:o=/"urn:schemas-microsoft-com:office:" + 
14.                   "office/"/r/n xmlns:x=/"urn:schemas- microsoft-com:" + 
15.                   "office:excel/"/r/n xmlns:ss=/"urn:schemas-microsoft" + 
16.                   "-com:office:spreadsheet/">/r/n");
17.         sb.Append(" <Styles>/r/n <Style ss:ID=/"Default/" ss:Name" + 
18.                   "=/"Normal/">/r/n <Alignment " + 
19.                   "ss:Vertical=/"Bottom/"/>/r/n <Borders/>");
20.         sb.Append("/r/n <Font/>/r/n <Interior/>/r/n " + 
21.                   "<NumberFormat/>/r/n <Protection/>/r/n " + 
22.                   "</Style>/r/n <Style ss:ID=/"BoldColumn/">/r/n <Font ");
23.         sb.Append("x:Family=/"Swiss/" ss:Bold=/"1/"/>" + 
24.                   "/r/n </Style>/r/n <Style ss:ID=/"StringLiteral/">" + 
25.                   "/r/n <NumberFormat");
26.         sb.Append(" ss:Format=/"@/"/>/r/n </Style>/r/n " + 
27.                   "<Style ss:ID=/"Decimal/">/r/n " + 
28.                   "<NumberFormat ss:Format=/"0.0000/"/>/r/n </Style>/r/n ");
29.         sb.Append("<Style ss:ID=/"Integer/">/r/n " + 
30.                   "<NumberFormat ss:Format=/"0/"/>/r/n " + 
31.                   "</Style>/r/n <Style ss:ID=/"DateLiteral/">" + 
32.                   "/r/n <NumberFormat ");
33.         sb.Append("ss:Format=/"mm/dd/yyyy;@/"/>/r/n " + 
34.                   "</Style>/r/n <Style ss:ID=/"s28/">/r/n");
35.         sb.Append("<Alignment ss:Horizontal=/"Left/" ss:Vertical" + 
36.                   "=/"Top/" ss:ReadingOrder=/"LeftToRight/" " + 
37.                   "ss:WrapText=/"1/"/>/r/n");
38.         sb.Append("<Font x:CharSet=/"1/" ss:Size=/"9/" " + 
39.                   "ss:Color=/"#808080/" ss:Underline=/"Single/"/>/r/n");
40.         sb.Append("<Interior ss:Color=/"#FFFFFF/" ss:Pattern=/"Solid/"/>" + 
41.                   "</Style>/r/n</Styles>/r/n {0}</Workbook>");
42.         return sb.ToString();
43.     }
45.     private static string getWorksheets(DataSet source)
46.     {
47.         var sw = new StringWriter();
48.         if (source == null || source.Tables.Count == 0)
49.         {
50.             sw.Write("<Worksheet ss:Name=/"Sheet1/">" + 
51.                      "<Table><Row><Cell  ss:StyleID=/"StringLiteral/">" + 
52.                      "<Data ss:Type=/"String/"></Data>" + 
53.                      "</Cell></Row></Table></Worksheet>");
54.             return sw.ToString();
55.         }
56.         foreach (DataTable dt in source.Tables)
57.         {
58.             if (dt.Rows.Count == 0)
59.                 sw.Write("<Worksheet ss:Name=/"" + dt.TableName + 
60.                          "/"><Table><Row><Cell  " + 
61.                          "ss:StyleID=/"StringLiteral/"><Data " + 
62.                          "ss:Type=/"String/"></Data>" + 
63.                          "</Cell></Row></Table></Worksheet>");
64.             else
65.             {
66.                 //write each row data                
67.                 var sheetCount = 0;
68.                 for (int i = 0; i < dt.Rows.Count; i++)
69.                 {
70.                     if ((i % rowLimit) == 0)
71.                     {
72.                         //add close tags for previous sheet of the same data table
73.                         if ((i / rowLimit) > sheetCount)
74.                         {
75.                             sw.Write("</Table></Worksheet>");
76.                             sheetCount = (i / rowLimit);
77.                         }
78.                         sw.Write("<Worksheet ss:Name=/"" + dt.TableName +
79.                                  (((i / rowLimit) == 0) ? "" : 
80.                                  Convert.ToString(i / rowLimit)) + "/"><Table>");
81.                         //write column name row
82.                         sw.Write("<Row>");
83.                         foreach (DataColumn dc in dt.Columns)
84.                             sw.Write(
85.                                 string.Format(
86.                                     "<Cell ss:StyleID=/"BoldColumn/">" + 
87.                                     "<Data ss:Type=/"String/">{0}</Data></Cell>",
88.                                     dc.ColumnName));
89.                         sw.Write("</Row>/r/n");
90.                     }
91.                     sw.Write("<Row>/r/n");
92.                     foreach (DataColumn dc in dt.Columns)
93.                         sw.Write(
94.                             string.Format(
95.                                 "<Cell ss:StyleID=/"StringLiteral/">" + 
96.                                 "<Data ss:Type=/"String/">{0}</Data></Cell>",
97.                                 dt.Rows[i][dc.ColumnName]));
98.                     sw.Write("</Row>/r/n");
99.                 }
100.                 sw.Write("</Table></Worksheet>");
101.             }
102.         }
104.         return sw.ToString();
105.     }
107.     public static void ToExcel(DataSet dsInput, 
108.            string filename, HttpResponse response)
109.     {
110.         var excelTemplate = getWorkbookTemplate();
111.         var worksheets = getWorksheets(dsInput);
112.         var excelXml = string.Format(excelTemplate, worksheets);
113.         response.Clear();
114.         response.AppendHeader("Content-Type", "application/vnd.ms-excel");
115.         response.AppendHeader("Content-disposition", 
116.                               "attachment; filename=" + filename);
117.         response.Write(excelXml);
118.         response.Flush();
119.         response.End();
120.     }
122.     public static void ToExcel(DataTable dtInput, 
123.            string filename, HttpResponse response)
124.     {
125.         var ds = new DataSet();
126.         ds.Tables.Add(dtInput.Copy());
127.         ToExcel(ds, filename, response);
128.     }
129. }