dataset导出EXCEL文件

最新推荐文章于 2017-04-04 20:47:43 发布

原创最新推荐文章于 2017-04-04 20:47:43 发布 · 748 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#dataset #excel #spreadsheet #office #styles #alignment

WEB(html、js、C#) 专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍了如何利用C#编程语言，通过特定代码段创建并导出Excel文件的过程。该方法涉及到HTTP响应设置、XML结构构建、以及数据集到Excel的映射，旨在提供一种简便且高效的文件生成解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

        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();
        }