首页 新闻 搜索 专区 学院

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速度很慢,各位,这段代码该怎么优化

偶不是大叔的主页 偶不是大叔 | 初学一级 | 园豆:140
提问于:2013-06-21 09:46
< >
分享
最佳答案
0

为什么要拼接呢。直接把DataTable输出即可。这样还是不够快,如果数据量在65536以下,你可以用NPOI导出,在服务器生成文件,然后浏览器下载。淡然Aspose.Cell这款商业工具也可以。

收获园豆:10
幻天芒 | 高人七级 |园豆:36662 | 2013-06-21 12:17
其他回答(4)
0

可以使用第三方导出工具,例如 aspose.cell

Bob He | 园豆:208 (菜鸟二级) | 2013-06-21 10:49
0

FYI

 

http://www.cnblogs.com/yipeng-yu/archive/2013/03/21/2972720.html

Yu | 园豆:12950 (专家六级) | 2013-06-21 13:31
0

数据量在65536的情况下推荐用NPOI吧,这个也比较好,那个Aspose.Cell是收费的,

收获园豆:10
twistting | 园豆:575 (小虾三级) | 2013-06-21 14:56
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分钟之内
偶不是大叔 | 园豆:140 (初学一级) | 2013-06-28 08:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册