Asp.net 将DataGrid分多个Sheet导出Excel

来源：互联网发布：桃子直播软件编辑：程序博客网时间：2024/05/21 10:24
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Text;using System.IO;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;/// <summary>///ExcelUtil 的摘要说明/// </summary>public class ExcelUtil{    public ExcelUtil()    {        //        //TODO: 在此处添加构造函数逻辑        //    }    string _Author;    string _Company;    StringBuilder sbBody = new StringBuilder();    StringBuilder sbSheet = new StringBuilder();    public ExcelUtil(string Author, string Company)    {        _Author = Author;        _Company = Company;        sbBody.AppendFormat(            "MIME-Version: 1.0\r\n" +            "X-Document-Type: Workbook\r\n" +            "Content-Type: multipart/related; boundary=\"-=BOUNDARY_EXCEL\"\r\n\r\n" +            "---=BOUNDARY_EXCEL\r\n" +            "Content-Type: text/html; charset=\"big5\"\r\n\r\n" +            "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" +            "xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" +            "<head>\r\n" +            "<xml>\r\n" +            "<o:DocumentProperties>\r\n" +            "<o:Author>...{0}</o:Author>\r\n" +            "<o:LastAuthor>...{0}</o:LastAuthor>\r\n" +            "<o:Created>...{1}</o:Created>\r\n" +            "<o:LastSaved>...{1}</o:LastSaved>\r\n" +            "<o:Company>...{2}</o:Company>\r\n" +            "<o:Version>11.5606</o:Version>\r\n" +            "</o:DocumentProperties>\r\n" +            "</xml>\r\n" +            "<xml>\r\n" +            "<x:ExcelWorkbook>\r\n" +            "<x:ExcelWorksheets>\r\n"            , _Author            , DateTime.Now.ToString()            , _Company);    }    private string ExportExcel()    {        StringBuilder sb = new StringBuilder(sbBody.ToString());        sb.Append("</x:ExcelWorksheets>\r\n" +            "</x:ExcelWorkbook>\r\n" +           "</xml>\r\n" +            "</head>\r\n" +            "</html>\r\n\r\n");        sb.Append(sbSheet.ToString());        sb.Append("---=BOUNDARY_EXCEL--");        return sb.ToString();    }   public void AddGrid(DataGrid grid, string sheetName)    {        string gid = Guid.NewGuid().ToString();        sbBody.AppendFormat("<x:ExcelWorksheet>\r\n" +            "<x:Name>...{0}</x:Name>\r\n" +            "<x:WorksheetSource HRef=\"cid:...{1}\"/>\r\n" +            "</x:ExcelWorksheet>\r\n"            , sheetName.Replace(":", "").Replace("\\", "").Replace("/", "").Replace     ("?", "").Replace("*", "").Replace("[", "").Replace("]", "").Trim()            , gid);        StringWriter sw = new StringWriter();        HtmlTextWriter htw = new HtmlTextWriter(sw);        grid.RenderControl(htw);        sbSheet.AppendFormat("---=BOUNDARY_EXCEL\r\n" +         "Content-ID: ...{0}\r\n" +         "Content-Type: text/html; charset=\"big5\"\r\n\r\n" +         "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n" +         "xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n" +         "<head>\r\n" +         "<xml>\r\n" +         "<x:WorksheetOptions>\r\n" +         "<x:ProtectContents>False</x:ProtectContents>\r\n" +         "<x:ProtectObjects>False</x:ProtectObjects>\r\n" +         "<x:ProtectScenarios>False</x:ProtectScenarios>\r\n" +         "</x:WorksheetOptions>\r\n" +         "</xml>\r\n" +         "</head>\r\n" +         "<body>\r\n"         , gid);        sbSheet.Append(sw.ToString());        sbSheet.Append("</body>\r\n" +            "</html>\r\n\r\n");        sw.Close();        htw.Close();    }    public void Export(Page page, string FileName)    {        page.Response.Clear();        page.Response.Buffer = true;        page.Response.Charset = "";        page.Response.AddHeader("content-disposition", string.Format     ("attachment;filename=...{0}.xls", FileName));        page.Response.ContentEncoding = Encoding.GetEncoding("big5");        page.Response.ContentType = "application/vnd.ms-excel";        page.Response.Write(ExportExcel());        page.Response.End();    }    public void Clear()    {        sbBody.Remove(0, sbBody.Length);        sbSheet.Remove(0, sbBody.Length);    }}
调用方法：
           ExcelUtil eu=new ExcelUtil("Jeff_Yeh","隨手記");
           eu.AddGrid(givw, "TestA");
           eu.AddGrid(givw, "TestB");
           eu.Export(this,"Jeff");