大数据量，在线生成excel的解决方案

2014-12-31 03:21:34 736 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

poi大数据量生成excel 使用poi生成excel，当遇到大数据量时，会有几个问题： 1. 目前office2003 excle每个sheet最大支持行数为65536，当数据超过65536行时，会报异常2. 当列为17列左右,数据行数达到50w条左右时，会包OOM错误Exception in thread "15124183@qtp-16902686-1" java.lang.OutOfMemoryError: Java heap space Exceptio 阅读详情

数据量10万级，需要在web上在线生成excel，从用户体验和后台技术方案这两个角度，探讨一下应该如何解决，讨论思路即可，具体的转换代码手头已有，不过多讨论了，谢谢！

...全文

SXSSFWorkbook导出大数据量Excel文件最近在导出大数据量进excel文件时，之前用的XSSFWorkbook在写文件的时候，项目一直报内存溢出。在查询POI后发现新出的SXSSFWorkbook正是为处理大数据量而生，在苦苦找了两天SXSSFWorkbook导出带模板的excel未果后，决定采用excel表头自己打印的方法来形成表头字段。 String[] assetHeadTemp = {"批次","供应商代码","商品名称"}; 阅读详情

gzpepco

.NET社区 62,272 成员 668,968 内容

+ 关注 + 加入社区

切换为时间正序

huaneramn 2015-01-04

打赏
举报

NB啊。。。。。。。。。。。。。。。。。。。。。。

wula0010 2015-01-04

打赏
举报

十万级别的excel用POI轻松搞定的。很多时候，不是程序员说什么就什么的，客户有很多愚蠢的要求，你没办法的..................

一天到晚游泳的鱼2016 2015-01-04

打赏
举报

对，大数据量操作excel不太可以去，需要查看，你让他在线查看不就得了，按时间段查询，按时间段导出用NPOI操作，服务器不用安装office

qzyf1992 2015-01-04

打赏
举报

Aspose,epplus,openxml,npoi,spire.xls几个三方都测测看哪个速度快点用哪个

我叫小菜菜 2015-01-04

打赏
举报

如上面所言，就是把10w级的数据导出到Excel文件（当然是.xlsx）而已。因为web网页是对于所有公众开放的，随意访问的，那实时生成Excel就不是好办法了，每次访问页面都执行一次导出，这是严重的设计错误啊，除非附加了动态的筛选条件的，否则都应该后台导出，然后提供URL下载。我只坐过剪切板copy方式导出Excel，是COM组件实现的。 Oracle+winform。查询数据-创建COM示例-创建工作表-打开sheet-复制粘贴数据-保存xlsx-释放COM资源-返回。 12个字段，6w行，20秒左右。xlsx文件大小约5MB

  /// <summary>
        /// 使用粘贴板进行写入
        /// 此函数需要依赖office实例，运行的OS要安装office
        /// </summary>
        /// <param name="data"></param>
        /// <param name="filename"></param>
        /// <param name="exportHeader"></param>
        public void Export2Xls(DataTable data, string filename, bool exportHeader = true)
        {
            if (System.IO.File.Exists(filename))
                System.IO.File.Delete(filename);

            Excel14._Application xlsApp = null;
            Excel14._Workbook xlsBook = null;
            Excel14._Worksheet xstSheet = null;
            try
            {
                //xlsApp = new Excel14.ApplicationClass();
                //xlsApp = new Excel14.Application();

                xlsApp = new Excel14.Application();

                xlsBook = xlsApp.Workbooks.Add();
                xstSheet = (Excel14._Worksheet)xlsBook.Worksheets[1];

                var buffer = new StringBuilder();
                if (exportHeader)
                {
                    // Excel中列与列之间按照Tab隔开
                    foreach (DataColumn col in data.Columns)
                        buffer.Append(col.ColumnName + "\t");

                    buffer.AppendLine();
                }
                foreach (DataRow row in data.Rows)
                {
                    foreach (DataColumn col in data.Columns)
                        buffer.Append(row[col].ToString() + "\t");

                    buffer.AppendLine();
                }
                System.Windows.Forms.Clipboard.SetDataObject("");
                // 放入剪切板
                System.Windows.Forms.Clipboard.SetDataObject(buffer.ToString());
                var range = (Excel14.Range)xstSheet.Cells[1, 1];
                range.Select();
                xstSheet.Paste();
                // 清空剪切板
                System.Windows.Forms.Clipboard.SetDataObject("");

                xlsBook.SaveAs(filename);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                try
                {
                    if (xlsBook != null)
                        xlsBook.Close();

                    if (xlsApp != null)
                        xlsApp.Quit();

                    // finally里清空Com对象
                    lock(this)
                    {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xstSheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsBook);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsApp);

                    xstSheet = null;
                    xlsBook = null;
                    xlsApp = null;

                    GC.Collect();  
                    }

                }
                catch (System.Exception ex)
                {
                    throw ex;
                }

            }
        }

leoAAAA 2015-01-03

打赏
举报

引用 11 楼 sp1234 的回复:

excel单个工作表早就可以放160多万行了。而且一个工作簿可以放多个工作表。

多谢帮我更新知识点，我还停留在excel2003，嘿嘿客户提这种要求其实是为了留个凭据（如果只是备份，我想客户不会关心你用什么方式的），万一数据库里的数据出现问题，用这个凭据去对照，如果是单表容量能达到160多万，那导出是没什么问题，不过接下来的问题是，用户可能会跟你提要求，要在excel里查找数据，对应上了，可能还要更新回数据库，这个情况我以前是碰到过，挺麻烦的

ffg_2460057139 2015-01-03

打赏
举报

用自动递增变化的数据填充Excel private void tsbtn_Query_Click(object sender, EventArgs e) { CloseProcess("EXCEL");//关闭所有Excel进程 string P_str_Excel = tstxt_Excel.Text;//记录Excel文件路径 string P_str_SheetName = tscbox_Sheet.Text;//记录选择的工作表名称 object P_obj_Start = tstxt_Start.Text;//记录开始单元格 object P_obj_End = tstxt_End.Text;//记录结束单元格 object missing = System.Reflection.Missing.Value;//定义object缺省值 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(P_str_Excel, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表 worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[P_str_SheetName]);//获取选择的工作表 Microsoft.Office.Interop.Excel.Range searchRange = worksheet.get_Range(P_obj_Start, missing);//定义查找范围 switch (tscbox_Type.Text)//按照下拉列表中的文本进行条件判断 { case "数字"://以数字序列填充 searchRange.AutoFill(worksheet.get_Range(P_obj_Start, P_obj_End), Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillSeries); break; case "工作日"://以工作日填充 searchRange.AutoFill(worksheet.get_Range(P_obj_Start, P_obj_End), Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillWeekdays); break; case "月份"://以月份填充 searchRange.AutoFill(worksheet.get_Range(P_obj_Start, P_obj_End), Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillMonths); break; case "年份"://以年份填充 searchRange.AutoFill(worksheet.get_Range(P_obj_Start, P_obj_End), Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillYears); break; default://以默认方式填充 searchRange.AutoFill(worksheet.get_Range(P_obj_Start, P_obj_End), Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillDefault); break; } excel.DisplayAlerts = false;//设置保存Excel时不显示对话框 workbook.Save();//保存工作簿 workbook.Close(false, missing, missing);//关闭工作簿 CloseProcess("EXCEL");//关闭所有Excel进程 WBrowser_Excel.Navigate(P_str_Excel);//在窗体中重新显示Excel文件内容 }

以专业开发人员为伍 2015-01-03

打赏
举报

如果你说你只是做网页 OA 的，不擅长开发企业 c/s 软件，那么我建议你就把这个功能“收费”，然后找人设计一个 c/s 版的客户端软件。这样，你们公司还多一项产品呢，可以推广给几千个、上万企业使用。不要在网页上净搞一些高大上的项目。

以专业开发人员为伍 2015-01-03

打赏
举报

引用楼主 gzpepco 的回复:

数据量10万级，需要在web上在线生成excel，从用户体验和后台技术方案这两个角度，探讨一下应该如何解决，讨论思路即可，具体的转换代码手头已有，不过多讨论了，谢谢！

web主要是给互联网“冲浪”用户看的网页。如果给熟练的统计人员，你应该提供一个高性能的c/s程序。

winner2050 2015-01-02

打赏
举报

笨啊，人家说什么就什么啊，就为了备份数据，至于导出excel吗？这种低性能，弱类型，还得小心被篡改数据类型。合适用来备份数据吗？

以专业开发人员为伍 2015-01-02

打赏
举报

excel单个工作表早就可以放160多万行了。而且一个工作簿可以放多个工作表。

leoAAAA 2015-01-02

打赏
举报

数据量太大了，excel一个表才65536行，超过64M就打不开了，还是说服客户换成access，导出还快些，开销也小

吉普赛的歌 2015-01-01

打赏
举报

对Excel的导出，稍有研究。如果数量不大（一万条以下），随便怎么样都可以。象你这样在十万条以上左右，如果还要并发，服务器绝对死掉 —— 一个用户导出就可能就占几个G的内存。 NPOI和CloseXML导出都很占内存的。如果数据并不复杂，你可以试着导出 .CSV 文件。这样导出的文件并不大，而且速度非常快，下载时用户体验也很好。

於黾 2014-12-31

打赏
举报

试试这个第三方组件,直接对文件进行操作,比调用office进程是快多了而且服务器上也不需要安装office组件,兼容32位和64位,支持webform和winform

xdashewan 2014-12-31

打赏
举报

反正逐行写入是最慢的一种，其他需要你自己尝试，比如数据流写入，OLEDB写入，RANGE写入，剪切板copy，其中剪切板方式速度很快，但不一定满足格式要求，总之快慢不是凭空想，你得做出来实际测试才知道

於黾 2014-12-31

打赏
举报

引用 5 楼 gzpepco 的回复:

[quote=引用 4 楼 Z65443344 的回复:] 大数据填充excel慢,下载到客户机也慢用户除非是闲的蛋疼,否则不会弄10W+数据自己没事在那看的,看一天也看不完

因为需要一个存档留底的功能，尽管几乎不会有人用office打开去看，但针对该应用的需求，作为对历史业务的留底，是有必要的。退一步，要是以后出什么问题，客户有个原始数据留底，相当于备份。[/quote] 那你就做个服务程序,或者控制台程序,放服务器上,设置任务计划,定时每天执行一次,导出一个excel,然后定期copy走就好了不要等用户访问你网站的时候才去导出

gzpepco 2014-12-31

打赏
举报

引用 4 楼 Z65443344 的回复:

大数据填充excel慢,下载到客户机也慢用户除非是闲的蛋疼,否则不会弄10W+数据自己没事在那看的,看一天也看不完

因为需要一个存档留底的功能，尽管几乎不会有人用office打开去看，但针对该应用的需求，作为对历史业务的留底，是有必要的。退一步，要是以后出什么问题，客户有个原始数据留底，相当于备份。

於黾 2014-12-31

打赏
举报

大数据填充excel慢,下载到客户机也慢用户除非是闲的蛋疼,否则不会弄10W+数据自己没事在那看的,看一天也看不完

於黾 2014-12-31

打赏
举报

引用 2 楼 gzpepco 的回复:

[quote=引用 1 楼 Z65443344 的回复:] 不管是从用户体验还是从技术方案上,都应该避免大数据放到excel里,用户看不过来,没有存在的意义

但用户确实有这样的需求。[/quote] 那你就必须弄明白用户为什么会有这样的需求是否是因为你们数据库备份功能不完善,还是数据查询筛选功能不完善,还是图表功能不完善,总之就是你提供的系统里没有用户想要的功能,所以他们才想把那么多数据导出到excel里自己去玩

gzpepco 2014-12-31

打赏
举报

引用 1 楼 Z65443344 的回复:

不管是从用户体验还是从技术方案上,都应该避免大数据放到excel里,用户看不过来,没有存在的意义

但用户确实有这样的需求。

加载更多回复（1）

大数据量，在线生成excel的解决方案

相关推荐