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

最新推荐文章于 2020-09-14 18:29:42 发布

Karl_zhujt

最新推荐文章于 2020-09-14 18:29:42 发布

阅读量3.9k

点赞数

分类专栏： NET Office 文章标签： datagrid asp.net string excel office xml

NET 同时被 2 个专栏收录

58 篇文章 0 订阅

订阅专栏

Office

13 篇文章 0 订阅

订阅专栏

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");

Karl_zhujt

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
3
评论
Asp.net 将DataGrid分多个Sheet导出Excel

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.
复制链接

扫一扫