也谈Excel导出
吐槽
Excel导出在天朝的软件大环境下，差点成为软件开发必备。俺就遇到过，所有报表不提供导出功能，就能不验收的囧事。报表能查看能打印能形成图表已经完美，实在搞不懂导出excel有个毛用，但是公司依靠客户盈利俺们码农依靠公司发工资过活，哎！不得不吐槽下。
博客园有篇文章提到Excel导出10W条数据几秒钟内搞定，看到后很诧异，Excel最大支持6W多条数据，强行插入这么长，人家Excel小姑娘如何受得了？你整这么多数据你爽了，但是客户导出后打不开，碰到小白客户可能还得重启电脑，然后你就爽不下去了。俺后来试了下导出5W多条数据（20列）后打开Excel慢的要死，而且我的wps2012个人版的神器竟然直接Game Over 了。
好了不扯淡了，下面整理下excel导出的三种方法，来源自网络，后又加上俺的整理,后两种已经在项目中实际运用。
方法一:
用Microsoft.Office.Interop.Excel 组建方式导出，直接遍历excel每个单元格插入。
吐槽下此方法慢如蜗牛，不喜欢的跳过。
程序准备
1、在项目中添加Microsoft.Office.Interop.Excel的引用,制作一个需要导出的excel样式模版放到项目某个文件夹下，
2、用此方法导出需要添加Microsoft.Office.Interop.Excel的引用，且服务器上需要安装excel，建议服务器上安装Excel版本低一些，2003就不错。
3、服务器权限设定，出现类似检索 COM 类工厂中 CLSID 为 {00024500-0000-0000-C000-000000000046}的组件时失败 的错误多半是服务器上的Excel权限没设好。打开控制面板=>管理工具=>组建服务=>组建服务=>计算机=>我的电脑=>DCOM配置=>Microsoft Excel。右击属性标志选择交互式用户 ，安全标签中的启动与激活权限选择自定义然后点击编辑按钮，点添加ASP.NET。
具体程序如下
生成的方法
public void CreateExcel(System.Data.DataTable dt, string creatName, string MoName) { // 输入文件名 string inputFilePath = System.Web.HttpContext.Current.Server.MapPath(MoName); string outFilePath = System.Web.HttpContext.Current.Server.MapPath(creatName); //如果文件不存在，则将模板文件拷贝一份作为输出文件 if (!File.Exists(outFilePath)) { File.Copy(inputFilePath, outFilePath, true); } GC.Collect(); ApplicationClass myApp = new ApplicationClass(); Workbook myBook = null; Worksheet mySheet = null; myApp.Visible = false; object oMissiong = System.Reflection.Missing.Value; myApp.Workbooks.Open(outFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong
, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); myBook = myApp.Workbooks[1]; mySheet = (Worksheet)myBook.ActiveSheet; DataTableToExcel(dt, mySheet); myBook.Save(); myBook.Close(true, outFilePath, true); System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp); GC.Collect(); }
public void DataTableToExcel(System.Data.DataTable dt, Worksheet excelSheet) { int rowCount = dt.Rows.Count; int colCount = dt.Columns.Count; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { mySheet.Cells[i + 1, j] = dt.Rows[i - 1][j - 1].ToString(); } } }
调用的方法
CreateExcel ce = new CreateExcel(); string MoName = "UserFiles/DataIn.xls";//模板的路径 string creatName = "UserFiles\\" + "数据导出.xls" ;//生成excel的路径 ce.Create(tbl, creatName, MoName); Response.Redirect(creatName);
方法二:
用Microsoft.Office.Interop.Excel 组建方式导出，数据依照二维数组方式存放，和第一种方法虽然只有几行代码区别，但是速度快了几十倍，10W条数据15秒钟左右（小白pc机）具体的服务器配置如方法一
程序如下
生成方法
public void Create(System.Data.DataTable dt, string creatName, string MoName) { // 输入文件名 string inputFilePath = System.Web.HttpContext.Current.Server.MapPath(MoName); string outFilePath = System.Web.HttpContext.Current.Server.MapPath(creatName); //如果文件不存在，则将模板文件拷贝一份作为输出文件 if (!File.Exists(outFilePath)) { File.Copy(inputFilePath, outFilePath, true); } GC.Collect(); ApplicationClass myApp = new ApplicationClass(); Workbook myBook = null; Worksheet mySheet = null; myApp.Visible = false; object oMissiong = System.Reflection.Missing.Value; myApp.Workbooks.Open(outFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong
, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); myBook = myApp.Workbooks[1]; mySheet = (Worksheet)myBook.ActiveSheet; DataTableToExcel(dt, mySheet); myBook.Save(); myBook.Close(true, outFilePath, true); System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp); GC.Collect(); } public void DataTableToExcel(System.Data.DataTable dt, Worksheet excelSheet) { int rowCount = dt.Rows.Count; int colCount = dt.Columns.Count; object[,] dataArray = new object[rowCount + 1, colCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { dataArray[i, j] = dt.Rows[i][j]; } } //从A2开始导入写入数据即可以避开表头被重写 excelSheet.get_Range("A2", excelSheet.Cells[rowCount + 1, colCount]).Value2 = dataArray; }
调用方法
CreateExcel ce = new CreateExcel(); string MoName = "UserFiles/DataIn.xls";//模板的路径 string creatName = "UserFiles\\" + "数据导出.xls" ;//生成excel的路径 ce.Create(tbl, creatName, MoName); Response.Redirect(creatName);
方法三:
此方法不需要在服务器上安装Excel，采用生成xml以excel方式输出到客户端，可能需要客户机安装excel（心虚没试过）所以也不会有乱七八糟的权限设定，和莫名其妙的版本问题。而且此种方法比第二种更快试了下10W条数据（20列）不到10秒（小白pc）。
方法如下
生成方法：
public static string CreateExcel(DataTable dt, List<string> columnNames) { 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=\"http://www.w3.org/TR/REC-html40\""); strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=gb2312'>"); strb.Append(" <style>"); strb.Append(".xl26"); 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(" </style>"); strb.Append(" <xml>"); strb.Append(" <x:ExcelWorkbook>"); strb.Append(" <x:ExcelWorksheets>"); strb.Append(" <x:ExcelWorksheet>"); strb.Append(" <x:Name>Sheet1 </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> <table align=\"center\" style='border-collapse:collapse;table-layout:fixed'> <tr>"); //if (ds.Tables.Count > 0) //{ //写列标题 int columncount = columnNames.Count; for (int columi = 0; columi < columncount; columi++) { strb.Append(" <td> <b>" + columnNames[columi] + " </b> </td>"); } strb.Append(" </tr>"); //写数据 for (int i = 0; i < dt.Rows.Count; i++) { strb.Append(" <tr>"); for (int j = 0; j < dt.Columns.Count; j++) { strb.Append(" <td class='xl26'>" + dt.Rows[i][j].ToString() + " </td>"); } strb.Append(" </tr>"); } //} strb.Append(" </body> </html>"); return strb.ToString(); }
调用方法：
protected void Button1_Click(object sender, EventArgs e) { string strSql = “select * from table1"; System.Data.DataTable tbl = SqlHelper.GetDateTable(strSql); List<string> names = new List<string>() { "序号","固定资产管理码","品名","资产大类","资产小类","品牌","规格型号","编码1","编码2","使用部门","责任人","使用人","放置地点","价格"
,"购置日期","供应商","保修截至日期","预计使用月份","备注","其他" }; string str = ExcelHelper.CreateExcel(tbl, names); Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename=1213.xls"); Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 //this.EnableViewState = false; Response.Write(str); Response.End(); }
