首页 > 代码库 > asp.net c# 网页 导出excel 多表格 多个sheet

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

    /// <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=http://www.mamicode.com/"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();    }

 

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