asp.net c# 网页导出excel 多表格多个sheet

最新推荐文章于 2023-03-31 14:03:19 发布

weixin_30438813

最新推荐文章于 2023-03-31 14:03:19 发布

阅读量648

点赞数 1

文章标签： c# 测试

原文链接：http://www.cnblogs.com/diose/p/7213846.html

版权

    /// <summary>
    ///可导出多个sheet表
    /// </summary>
    /// <param name="Author">作者</param>
    /// <param name="Company">公司</param>
    /// <param name="dt">多个DataTable</param>
    /// <param name="fileName">文件名</param>
    public static void PushExcelToClientEx(string Author, string Company, DataTable[] dt, string fileName)
    {
        if (!fileName.Contains(".xls"))
        {
            fileName += ".xls";
        }

        StringBuilder sbBody = new StringBuilder();
        StringBuilder sbSheet = new StringBuilder();

        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=\"gbk\"\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);

        foreach (var d in dt)
        {
            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"
                , d.TableName.Replace(":", "").Replace("\\", "").Replace("/", "").Replace("?", "").Replace("*", "").Replace("[", "").Replace("]", "").Trim()
                , gid);


            sbSheet.AppendFormat(
             "---=BOUNDARY_EXCEL\r\n" +
             "Content-ID: {0}\r\n" +
             "Content-Type: text/html; charset=\"gbk\"\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("<table border='1'>");
            sbSheet.Append("<tr style='background-color: #CCC;'>");
            for (int i = 0; i < d.Columns.Count; i++)
            {
                sbSheet.AppendFormat("<td style='vnd.ms-excel.numberformat: @;font-weight:bold'>{0}</td>", d.Columns[i].ColumnName);
            }
            sbSheet.Append("</tr>");
            for (int j = 0; j < d.Rows.Count; j++)
            {
                sbSheet.Append("<tr>");
                for (int k = 0; k < d.Columns.Count; k++)
                {
                    sbSheet.AppendFormat("<td style='vnd.ms-excel.numberformat: @;'>{0}</td>", Convert.ToString(d.Rows[j][k]));
                }
                sbSheet.Append("</tr>");
            }
            sbSheet.Append("</table>");
            sbSheet.Append("</body>\r\n" +
                "</html>\r\n\r\n");
        }

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

        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        HttpContext.Current.Response.Buffer = true;

        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gbk");
        HttpContext.Current.Response.Write(sb.ToString());
        HttpContext.Current.Response.End();
    }