导出DataSet 到多个Excel工作薄

来源：互联网发布：网络综合布线工程编辑：程序博客网时间：2024/04/30 02:02
导言
我想导出一个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);
    }
}