请选择 进入手机版 | 继续访问电脑版

无忧编程_ASP.NET  / C# / PHP 程序员的软件世界

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 99|回复: 0

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

[复制链接]

349

主题

379

帖子

1565

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
1565
发表于 2018-6-25 17:29:14 | 显示全部楼层 |阅读模式
asp.net c# 网页 导出excel 多表格 多个sheet
[C#] 纯文本查看 复制代码
/// <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();
    }

文章来自:https://www.cnblogs.com/diose/p/7213846.html
windows .net(C#+MSSQL) linux(php+mysql)  
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

Archiver|手机版|小黑屋|软件编程 ( 鄂ICP备11006601号鄂公网安备 42011102001337号 |

GMT+8, 2018-7-23 07:54 , Processed in 0.123077 second(s), 24 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表