征求方案-关于多个excel，大数据量同时导入

以前我们的管理系统用poi可以通过一个excel文件批量导入手机号码，在这个过程中需要验证号码格式、是否重复、系统是否存在等，因为效率问题，所以最后限定只导入excel文件的第一列，也就是65535行数据。

有一点要说一下，系统的数据量非常大，关联表的数据都是亿级。

现在有这么一个需求，要支持多个excel文件同时导入，不知道各位是否有好的建议？要从效率和空间上考虑，还好我们的服务器是4G内存。

我能想到的仅是多线程来处理，不至于处理过程要等半天。

any others？

评论

35 楼 chao121 2012-03-12  

[*]

啊顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶1212

34 楼 eddysheng 2009-08-05  

看来大家在这方面经验还不少啊
看了大家的回复，我准备按照以下几步来改进：
1。用户选择完excel文件提交后，程序将各个文件保存到server，返回提示：文件已经接收，正在等待处理。。。请返回列表页面查看处理状态。
2。文件保存到server后立刻触发一线程处理这些文件。
2.1。改进poi读取excel的方式，采用更加高效的方法。
2.2。采用正则表达式进行合法性校验，失败的批量入失败表。
2.3。数据存入临时表，调用存储过程进行存在性等其他校验，失败的存入失败表，正确的导入成功表。
2.3。处理完毕，更改状态字段。

3。用户可以查看这个号码组的状态，同时也可以导出出错的号码列表。

33 楼 tigerlg 2009-08-05  

把excel 程序处理成csv文件，在做批量导入处理，这样比excel效率要高的多，验证可以用其他方式处理 比如：利用存储过程或其他纯sql语句来验证临时表中的数据，验证有错，清空临时表中数据，返回错误信息。就可以

32 楼 lzrzhao 2009-08-05  

试试ODBC

31 楼 lzg3267373 2009-08-04  

ican 写道

1、把excel文件上传到服务器，提示用户上传成功；
2、启动一个线程，把数据导入到临时表，返回导入成功，删除上传excel文件；如果导入失败，提示用户导入失败，删除上传excel文件；
3、利用存储过程或其他纯sql语句来验证临时表中的数据，验证有错，清空临时表中数据，返回错误信息。

呵呵 以前做移动的项目的时候也遇到过一样的情况，也是采用这样的方案，效率还可以。

30 楼 尔今尔后 2009-08-03  

你好，你的问题我曾经遇到过。
那个时候也在寻求好的解决办法，因为数据过多导致内存不足，添加硬件明显是解决不了问题。

目前有2个解决的办法：
1.把execl转换成CSV格式，在程序里转换，这个很好解决。这个格式导入大量数据的时候不至于一下子崩溃。

2.批量导入。读入数据后，每次导入5000行 或者1千行。只有在导入数据库的时候才会造成内存的不足。
效验应该没有关系。
可以试试批量导入，这是比较可行的方法。其他目前没有 具体的可行性。JXL也可以 比poi要稳定些

29 楼 troyconder 2009-08-03  

排重检查之类 先做好 然后 直接导入到数据库 看你用的是什么库了 这是效率最高的

28 楼 WorldHello 2009-08-03  

eddysheng 写道

以前我们的管理系统用poi可以通过一个excel文件批量导入手机号码，在这个过程中需要验证号码格式、是否重复、系统是否存在等，因为效率问题，所以最后限定只导入excel文件的第一列，也就是65535行数据。

有一点要说一下，系统的数据量非常大，关联表的数据都是亿级。

现在有这么一个需求，要支持多个excel文件同时导入，不知道各位是否有好的建议？要从效率和空间上考虑，还好我们的服务器是4G内存。

我能想到的仅是多线程来处理，不至于处理过程要等半天。

any others？

试试openadaptor，里面有excel组件，可以直接解析excel，然后将其内容倒到其他地方（DB、File.......）

这个应该是最简单的，基本不用你写几行代码，配置下就行了

27 楼 坏孩子 2009-08-01  

ican 写道

1、把excel文件上传到服务器，提示用户上传成功；
2、启动一个线程，把数据导入到临时表，返回导入成功，删除上传excel文件；如果导入失败，提示用户导入失败，删除上传excel文件；
3、利用存储过程或其他纯sql语句来验证临时表中的数据，验证有错，清空临时表中数据，返回错误信息。

我就是用上面的方法做的速度很快，注意导入临时表的时候要用批量提交

26 楼 ican 2009-08-01  

1、把excel文件上传到服务器，提示用户上传成功；
2、启动一个线程，把数据导入到临时表，返回导入成功，删除上传excel文件；如果导入失败，提示用户导入失败，删除上传excel文件；
3、利用存储过程或其他纯sql语句来验证临时表中的数据，验证有错，清空临时表中数据，返回错误信息。

25 楼 whaosoft 2009-08-01  

我也是不建议用程序做

24 楼 laomie 2009-07-31  

1,从Excel读取数据放入hashtable
2,对hashtable的数据进行格式验证
3,将hashtable里的数据放入数据库的临时表1
4,union all 临时表1和真实数据表，将结果只有一条的数据放入临时表2
5,删除临时表1中和临时表2相同的数据

23 楼 eddysheng 2009-07-31  

谢谢各位。综合以上意见，
我决定暂时这样，当用户页面上选择多个excel文件后，单线程读取多个excel文件进行解析，解析结果直接存入临时表，全部保存完毕后触发一后台线程来处理，页面直接返回并且可以报告进度。后台线程每处理一条就打一个标记，处理完毕后页面提示用户成功。这样就不至于让用户一直处在等待中。

22 楼 kusix 2009-07-31  

Jwind 提出的先导入临时表再校验、处理的思路比较好

总之数据的处理和校验应当是花费最大的部分。

至于内存方面，为了不OOM,劝LZ还是放弃并发吧

21 楼 ebeach 2009-07-30  

Jwind 写道

2.单个Excel，6万多行的文件是完全可以大于10M的（很多列）

要处理这么大的Excel还有很多校验逻辑，为什么非得在一个动作里做完呢？
尤其像楼主说的还有可能同时有多个用户提交。多大多快的机器也处理不了吧！

20 楼 guooscar 2009-07-30  

<div class="quote_title">Jwind 写道</div>
<div class="quote_div">个人认为上面说的还是没解决问题。<br>最近也碰到这样的问题了，我把问题说得更清楚点：<br><br>1.针对用户，只能是Excel<br>2.单个Excel，6万多行的文件是完全可以大于10M的（很多列）<br>3.最应该考虑的是内存和效率<br>内存：Excel本身大小、程序读取内容存储在内存中的大小<br>效率：Excel读取的速度、校验速度<br><br>最近在做类似这样的东西发现，首先考虑的是内存，LZ说是采用POI提供最简单的例子读取，这种默认采用usermodel方式读取，Excel文件如果大了，很有可能就会out of memry；可以考虑使用eventusermodel方式进行读取。至于baoq说的sqlldr ，因为考虑到服务器的实际的操作系统、配置等等，暂时放弃这种方式。<br><br>至于效率，一是校验内容的合法性、二是读取Excel速度。在校验时尽量的只考虑一些简单的条件，如是否为空、字段是否过长等等，至于唯一性这些可以暂时不考虑。先把数据导入到一张‘临时表’，然后用存储过程做更复杂的校验，最后的就是把‘临时表’中的数据分配到其他的表中。在整个过程中，只要发现有不合法的数据就立即清空现场，然后返回提示。另外一个就是<span style="color: #ff0000;">Excel读取的速度</span>了，不知道有不有比POI更好东西，因为这里只是读取，所以一直在找更好的方式来读取。至于LZ说得多线程，我认为这个并不是核心问题，把上面两个问题解决了，在考虑这个。<br><br>上面说的也没有解决LZ的问题，只是最近碰到，一起说出来，看有不有更好的解决方式。</div>
<p>试试fastexcel</p>
<p> </p>

19 楼 Jwind 2009-07-30  

个人认为上面说的还是没解决问题。
最近也碰到这样的问题了，我把问题说得更清楚点：

1.针对用户，只能是Excel
2.单个Excel，6万多行的文件是完全可以大于10M的（很多列）
3.最应该考虑的是内存和效率
内存：Excel本身大小、程序读取内容存储在内存中的大小
效率：Excel读取的速度、校验速度

最近在做类似这样的东西发现，首先考虑的是内存，LZ说是采用POI提供最简单的例子读取，这种默认采用usermodel方式读取，Excel文件如果大了，很有可能就会out of memry；可以考虑使用eventusermodel方式进行读取。至于baoq说的sqlldr ，因为考虑到服务器的实际的操作系统、配置等等，暂时放弃这种方式。

至于效率，一是校验内容的合法性、二是读取Excel速度。在校验时尽量的只考虑一些简单的条件，如是否为空、字段是否过长等等，至于唯一性这些可以暂时不考虑。先把数据导入到一张‘临时表’，然后用存储过程做更复杂的校验，最后的就是把‘临时表’中的数据分配到其他的表中。在整个过程中，只要发现有不合法的数据就立即清空现场，然后返回提示。另外一个就是Excel读取的速度了，不知道有不有比POI更好东西，因为这里只是读取，所以一直在找更好的方式来读取。至于LZ说得多线程，我认为这个并不是核心问题，把上面两个问题解决了，在考虑这个。

上面说的也没有解决LZ的问题，只是最近碰到，一起说出来，看有不有更好的解决方式。

18 楼 eddysheng 2009-07-30  

谢谢kusix和ebeach的回复。
很不错，我会好好想想。

17 楼 ebeach 2009-07-30  

用异步任务调度完成，如果做的好点，可以用ajax查看用户提交任务的完成情况。

1 用户提交excel；上传完成后触发任务，服务器返回显示“任务已经提交，处理中。。。”；
2 用户可以进行其他操作，也可以等在这里，如果等在这里可以把自己提交的任务处理情况通过ajax告诉用户；
3 服务端根据资源使用情况决定串行或是并行的处理这些任务；
。。。
4 N久后，服务将用户提交的任务处理完成；
5 N久后，用户回来看到任务已经被处理完毕了，皆大欢喜！

16 楼 kusix 2009-07-30  

有多个文件需要导入的话，为了降低时间，提高用户体验，多线程处理是可以的，

只是web容器下这样比较危险，自己要管理好自己的线程。

同时要注意内存，就算1个excel占40M内存，10个并发也有400M了，

有点容易OOM

不如顺序处理，把处理的过程用进度条的方式展示给客户，这样用户体验应当也不错