dataset导出EXCEL文件

关注

原创最新推荐文章于 2020-05-13 08:20:40 发布 797 阅读 AI 写同款· GEO 优化 ›

        public void CreateExcel(DataSet ds, string FileName)
        {
            HttpResponse resp;
            resp = Page.Response;
            resp.ContentEncoding = System.Text.Encoding.UTF8;
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName));//attachment 参数表示作为附件下载，您可以改成 online在线打开

            resp.Write("<?xml version=\"1.0\"?>\r\n");
            resp.Write("<?mso-application progid=\"Excel.Sheet\"?>\r\n");

            resp.Write("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
            resp.Write(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n");
            resp.Write(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n");
            resp.Write(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
            resp.Write(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n");

            resp.Write("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
            resp.Write("<ProtectStructure>False</ProtectStructure>\r\n");
            resp.Write("<ProtectWindows>False</ProtectWindows>\r\n");
            resp.Write("</ExcelWorkbook>\r\n");
            #region styles
            resp.Write("<Styles>");
            resp.Write("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n");
            resp.Write("<Alignment ss:Vertical=\"Center\"/>\r\n");
            resp.Write("<Borders/>\r\n");
            resp.Write("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>\r\n");
            resp.Write("<Interior/>\r\n");
            resp.Write("<NumberFormat/>\r\n");
            resp.Write("<Protection/>\r\n");
            resp.Write("</Style>\r\n");
            resp.Write("</Styles>\r\n");
            #endregion
            #region worksheet
            foreach (System.Data.DataTable dt in ds.Tables)
            {
                resp.Write(string.Format("<Worksheet ss:Name=\"{0}\">\r\n", dt.TableName));
                resp.Write(string.Format("<Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">\r\n", dt.Columns.Count, dt.Rows.Count+1));
                resp.Write("<Row ss:AutoFitHeight=\"0\">\r\n");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    resp.Write(string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", dt.Columns[i]));
                }
                resp.Write("</Row>\r\n");
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    resp.Write("<Row ss:AutoFitHeight=\"0\">\r\n");
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        resp.Write(string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", dr[i].ToString().Replace("<","《").Replace(">","》")));
                    }
                    resp.Write("</Row>\r\n");
                }
                resp.Write("</Table>\r\n");
                resp.Write("</Worksheet>\r\n");
            }
            #endregion
            resp.Write("</Workbook>\r\n");
            resp.End();
        }