导出DataSet 到多个Excel工作薄

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

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