excel导出优化
0
悬赏园豆：20
[已解决问题]
解决于 2013-06-28 08:59
public static void DownloadExcel(string fileName, string sheetName, DataTable myDataTable) { StringWriter sw = new StringWriter(); sw.WriteLine("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"); sw.WriteLine("<head>"); sw.WriteLine("<!--[if gte mso 9]>"); sw.WriteLine("<xml>"); sw.WriteLine(" <x:ExcelWorkbook>"); sw.WriteLine(" <x:ExcelWorksheets>"); sw.WriteLine(" <x:ExcelWorksheet>"); sw.WriteLine(" <x:Name>" + sheetName + "</x:Name>"); sw.WriteLine(" <x:WorksheetOptions>"); sw.WriteLine(" <x:Print>"); sw.WriteLine(" <x:ValidPrinterInfo />"); sw.WriteLine(" </x:Print>"); sw.WriteLine(" </x:WorksheetOptions>"); sw.WriteLine(" </x:ExcelWorksheet>"); sw.WriteLine(" </x:ExcelWorksheets>"); sw.WriteLine("</x:ExcelWorkbook>"); sw.WriteLine("</xml>"); sw.WriteLine("<![endif]-->"); sw.WriteLine("</head>"); sw.WriteLine("<body>"); sw.WriteLine("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=GB2312\"/>"); sw.WriteLine("<table>"); sw.WriteLine(" <tr>"); //Write Title sw.Write(String.Format("<td><b>{0}</b></td>", "序号")); //Write Title foreach (DataColumn col in myDataTable.Columns) { sw.Write(String.Format("<td><b>{0}</b></td>", col.Caption)); } sw.Write("<td><b>正直</b></td>"); sw.Write("<td><b>负值</b></td>"); sw.WriteLine(" </tr>"); //Write Data int i = 1; foreach (DataRow dr in myDataTable.Rows) { sw.WriteLine(" <tr>"); sw.Write(String.Format("<td>{0}</td>", i)); foreach (DataColumn col in myDataTable.Columns) { sw.Write(String.Format("<td>{0}</td>", dr[col.ColumnName])); } if (dr["remain"] == null || dr["remain"].ToString() == "") { sw.Write("<td></td>"); sw.Write("<td></td>"); } else if (Convert.ToDouble(dr["remain"].ToString()) >= 0) { sw.Write(String.Format("<td>{0}</td>", dr["remain"])); sw.Write("<td></td>"); } else { sw.Write("<td></td>"); sw.Write(String.Format("<td>{0}</td>", dr["remain"])); } sw.WriteLine(" </tr>"); i++; } sw.WriteLine("</table>"); sw.WriteLine("</body>"); sw.WriteLine("</html>"); sw.Close(); var Response = System.Web.HttpContext.Current.Response; Response.Clear(); Response.Buffer = true; Response.Charset = "UTF-8"; //this.EnableViewState = false; Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls"); //Response.AddHeader("Content-Disposition", "attachment; filename=\"" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls" + "\""); //("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(disFileName, System.Text.Encoding.UTF8) + "\"" Response.ContentType = "application/ms-excel"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.Write(sw); Response.End(); }
数据记录有1245条，列数是175。导出excel速度很慢，各位，这段代码该怎么优化
最佳答案
0
为什么要拼接呢。直接把DataTable输出即可。这样还是不够快，如果数据量在65536以下，你可以用NPOI导出，在服务器生成文件，然后浏览器下载。淡然Aspose.Cell这款商业工具也可以。
收获园豆：10
其他回答(4)
0
可以使用第三方导出工具，例如 aspose.cell
0
FYI
http://www.cnblogs.com/yipeng-yu/archive/2013/03/21/2972720.html
0
数据量在65536的情况下推荐用NPOI吧，这个也比较好，那个Aspose.Cell是收费的，
收获园豆：10
0
不使用插件解决方法：1.往excel中写入内容时，使用二维字符窜数组保存数据，然后根据WorkSheet.Range的特性将二维数组的数据直接批量写入excel,此方式效率比WorkSheet.Cell高很多。http://www.cnblogs.com/Ihaveadream/archive/2009/01/04/1368525.html
2.下载excel文档，分块下载，参照文档中的button3;http://blog.csdn.net/gzmysky/article/details/5824499
导出用时：2分钟之内
