欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#导出生成excel文件的方法小结(xml,html方式)

程序员文章站 2024-02-13 15:34:46
直接贴上代码,里面都有注释复制代码 代码如下:///        ...

直接贴上代码,里面都有注释

复制代码 代码如下:

/// <summary>

        /// xml格式生成excel文件并存盘;
        /// </summary>
        /// <param name="page">生成报表的页面,没有传null</param>
        /// <param name="dt">数据表</param>
        /// <param name="tabletitle">报表标题,sheet1名</param>
        /// <param name="filename">存盘文件名,全路径</param>
        /// <param name="isdown">生成文件后是否提示下载,只有web下才有效</param>
        public static void createexcelbyxml(system.web.ui.page page, datatable dt, string tabletitle, string filename, bool isdown)
        {
            stringbuilder strb = new stringbuilder();
            strb.append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
            strb.append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            strb.append("xmlns=\"");
            strb.append(" <head> <meta http-equiv='content-type' content='text/html; charset=utf-8'>");
            strb.append(" <style>");
            strb.append("body");
            strb.append(" {mso-style-parent:style0;");
            strb.append(" font-family:\"times new roman\", serif;");
            strb.append(" mso-font-charset:0;");
            strb.append(" mso-number-format:\"@\";}");
            strb.append("table");
            //strb.append(" {border-collapse:collapse;margin:1em 0;line-height:20px;font-size:12px;color:#222; margin:0px;}");
            strb.append(" {border-collapse:collapse;margin:1em 0;line-height:20px;color:#222; margin:0px;}");
            strb.append("thead tr td");
            strb.append(" {background-color:#e3e6ea;color:#6e6e6e;text-align:center;font-size:14px;}");
            strb.append("tbody tr td");
            strb.append(" {font-size:12px;color:#666;}");
            strb.append(" </style>");
            strb.append(" <xml>");
            strb.append(" <x:excelworkbook>");
            strb.append(" <x:excelworksheets>");
            strb.append(" <x:excelworksheet>");
            //设置工作表 sheet1的名称
            strb.append(" <x:name>" + tabletitle + " </x:name>");
            strb.append(" <x:worksheetoptions>");
            strb.append(" <x:defaultrowheight>285 </x:defaultrowheight>");
            strb.append(" <x:selected/>");
            strb.append(" <x:panes>");
            strb.append(" <x:pane>");
            strb.append(" <x:number>3 </x:number>");
            strb.append(" <x:activecol>1 </x:activecol>");
            strb.append(" </x:pane>");
            strb.append(" </x:panes>");
            strb.append(" <x:protectcontents>false </x:protectcontents>");
            strb.append(" <x:protectobjects>false </x:protectobjects>");
            strb.append(" <x:protectscenarios>false </x:protectscenarios>");
            strb.append(" </x:worksheetoptions>");
            strb.append(" </x:excelworksheet>");
            strb.append(" <x:windowheight>6750 </x:windowheight>");
            strb.append(" <x:windowwidth>10620 </x:windowwidth>");
            strb.append(" <x:windowtopx>480 </x:windowtopx>");
            strb.append(" <x:windowtopy>75 </x:windowtopy>");
            strb.append(" <x:protectstructure>false </x:protectstructure>");
            strb.append(" <x:protectwindows>false </x:protectwindows>");
            strb.append(" </x:excelworkbook>");
            strb.append(" </xml>");
            strb.append("");
            strb.append(" </head> <body> ");
            strb.append(" <table style=\"border-right: 1px solid #ccc;border-bottom: 1px solid #ccc;text-align:center;\"> <thead><tr>");
            //合格所有列并显示标题
            strb.append(" <td style=\"text-align:center;background:#d3eeee;font-size:18px;\" colspan=\"" + dt.columns.count + "\" ><b>");
            strb.append(tabletitle);
            strb.append(" </b></td> ");
            strb.append(" </tr>");
            strb.append(" </thead><tbody><tr style=\"height:20px;\">");
            if (dt != null)
            {
                //写列标题
                int columncount = dt.columns.count;
                for (int columi = 0; columi < columncount; columi++)
                {
                    strb.append(" <td style=\"width:110px;;text-align:center;background:#ccc;\"> <b>" + dt.columns[columi] + " </b> </td>");
                }
                strb.append(" </tr>");
                //写数据
                for (int i = 0; i < dt.rows.count; i++)
                {
                    strb.append(" <tr style=\"height:20px;\">");
                    for (int j = 0; j < dt.columns.count; j++)
                    {
                        strb.append(" <td style=\"width:110px;;text-align:center;\">" + dt.rows[i][j].tostring() + " </td>");
                    }
                    strb.append(" </tr>");
                }
            }
            strb.append(" </tbody> </table>");
            strb.append(" </body> </html>");


            string excelfilename = filename;
            //string excelfilename = path.combine(page.request.physicalapplicationpath, path+"/guestdata.xls");
            //报表文件存在则先删除
            if (file.exists(excelfilename))
            {
                file.delete(excelfilename);
            }
            streamwriter writer = new streamwriter(excelfilename, false);
            writer.writeline(strb.tostring());
            writer.close();
            //如果需下载则提示下载对话框
            if (isdown)
            {
                downloadexcelfile(page, excelfilename);
            }
        }
---------
/// <summary>
        /// web下提示下载
        /// </summary>
        /// <param name="page"></param>
        /// <param name="filename">文件名,全路径</param>
        public static void downloadexcelfile(system.web.ui.page page, string filename)
        {
            page.response.write("path:" + filename);
            if (!system.io.file.exists(filename))
            {
                messagebox.showandredirect(page, "文件不存在!", filename);
            }
            else
            {
                fileinfo f = new fileinfo(filename);
                httpcontext.current.response.clear();
                httpcontext.current.response.addheader("content-disposition", "attachment; filename=" + f.name);
                httpcontext.current.response.addheader("content-length", f.length.tostring());
                httpcontext.current.response.addheader("content-transfer-encoding", "binary");
                httpcontext.current.response.contenttype = "application/octet-stream";
                httpcontext.current.response.writefile(f.fullname);
                httpcontext.current.response.end();
            }

        }


需要cs类文件的可以去下载  点击下载