`
lzg3267373
  • 浏览: 30403 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

大量数据与Excel交互的解决方案

ExcelVBAJNISQLWindows 
阅读更多

一. 导入生成 Excel

原始的做法：

将数据加载到内存中，导入 Excel, 此方法的弊端：

1. 大量数据加载到内存中 , 使机器变得缓慢

2. 大量数据在导入 Excel 时形成大量 cell 对象产生 OOM

 

在使用 TOMCAT 的情况下， JVM 最高只能支持到 2G 内存，则会发生内存溢出的情况。此时的内存开销主要是两部分，一部分是大量数据加载到内存，另一部分是写入一个 EXCEL 时形成大量 cell 对象的开销。由于 JVM 的 GC 机制是不能强制回收的 .

 

采用的方案是：将大量要导出的数据放入临时表分批次形成多个 Excel 文件后合并.说明：

1. 大量要导出的数据放入临时表

大量数据有可能是在海量数据表里通过条件查询出来的，这样可以有效避免多次在海量 数据中查询，提高了查询效率

2. 分批次形成多个 Excel

比形成一个 Excel 的多个 sheet 好，因为多个 sheet 形式需要将整个 Excel 加载到内存中，那么 sheet 越多， Excel 越大 . 改多个 SHEET 页为多个 EXCEL 。通过多次生成，可以在后面 EXCEL 生成所需要的内存不      足时，有效回收前面生成 EXCEL 时占用的内存。

3. 将多个 Excel 合并

参考 :http://www.iteye.com/topic/240053.

 

二. Excel 导出入库

1.excel 文件上传至服务器

2. 采用 eventusermodel 方式读取 excel 数据 , 分块读取数据并批量入临时表

3. 利用存储过程或其他纯 sql 语句来验证临时表 (Temp1) 中的数据

4. 将验证失败数据放入存储至校验失败的临时表中 (Temp2)

5. 将临时表 (Temp1) 中的数据插入库表

 

在此感谢 JAVAEYE 上的提供参考资料的各位同行

分享到：
hibernate+proxool
评论
24 楼 老汉学编程 2011-03-08  
遇到50～60MB的excel的文件，有什么解决方案！
PS：我是想读
23 楼 y_ys79 2010-06-30  
guooscar 写道
wl95421 写道
如果在Windows平台上，安装MS Excel，然后用Jacob通过OLE调用，应该可以，速度应该是最快的，相当于JNI了。

excel速度很慢滴...你可以试试浏览器里面用js调调

人家说的是服务器端，跟js没什么关系。
我计算过，我用oledb插入excel的速度是47条/秒。
大数据量的我用xml方式的excel进行保存。读一条写一个row,速度不错，不会溢出。
22 楼 aixuebo 2010-06-25  
newbaby 写道
为什么不考虑文本流生成XML格式的word文档呢，几万条也没有问题。

几万条的数据，如果分成多个xls文件生成，估计用户边合并边就要骂娘了，用户感知基本倒退到原始社会了

当然，对不少开发人员而说，调现成的excel开发包，第一时间交差就行了




麻烦说的详细点。生成XML格式的word文档？怎么个概念。如何最终又生成出Excel那？
21 楼 kivil 2010-06-25  
上附件是什么意思！内容都一样，晕！你这是浪费资源啊！
20 楼 lzg3267373 2010-03-12  
newbaby 写道
为什么不考虑文本流生成XML格式的word文档呢，几万条也没有问题。

几万条的数据，如果分成多个xls文件生成，估计用户边合并边就要骂娘了，用户感知基本倒退到原始社会了

当然，对不少开发人员而说，调现成的excel开发包，第一时间交差就行了


哥们word有Excel的功能嘛,比强计算统计,分类.
一个excel最少可以放65536的数据的,几万条的数据还用合并xls文件嘛.
19 楼 newbaby 2010-03-12  
为什么不考虑文本流生成XML格式的word文档呢，几万条也没有问题。

几万条的数据，如果分成多个xls文件生成，估计用户边合并边就要骂娘了，用户感知基本倒退到原始社会了

当然，对不少开发人员而说，调现成的excel开发包，第一时间交差就行了
18 楼 lzg3267373 2010-03-11  
ray_linn 写道
找到个偷头像的贼。。。

再大数据有什么难的， excel支持vba,vba可以调用xmlhttprequest


我可不是偷你的头像。
朋友有没有vba的例子或给个链接，学习下。
17 楼 ray_linn 2010-03-11  
找到个偷头像的贼。。。

再大数据有什么难的， excel支持vba,vba可以调用xmlhttprequest
16 楼 mycybyb 2010-03-11  
研究一下POI源代码，稍微改改就可以了。
15 楼 kimmking 2010-03-11  
csv是个办法，不过是文本格式。

如果数据量超级大，还是建议不用jxl poi
自己看下biff8文件格式，实现简单的格式封装，自己一点点的往io写得了。
14 楼 banfry 2010-03-11  
附件应该整点代码，不要和帖子内容一摸一样！
13 楼 jspine 2010-03-11  
willvvv 写道
这个以前做过类似的导入到excel中，采用的是从数据库读出若干条数据生成二维数组，写入到excel，再取若干条生成二维数组，写入到excel。基本上不会内存溢出，而且cpu占用也不是很高。导出的excel在5M左右没有问题。

二维数组，怎么具体操作，有相关API？？？
12 楼 wl95421 2010-03-11  
Excel导入数据慢不慢？
你可以生成一个csv文件，然后打开试一下，不就知道了吗？
不要臆断它就慢。
11 楼 lzg3267373 2010-03-11  
<div class="quote_title">e3002 写道</div>
<div class="quote_div">
<div class="quote_title">lzg3267373 写道</div>
<div class="quote_div">
<p><span style="color: #ff0000; font-size: medium;"><span>一.</span> </span><span style="color: #ff0000; font-size: medium;"><span>导入生成</span> </span><span style="color: #ff0000; font-size: medium;">Excel</span> </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="font-size: small;"><span>采用的方案是：将大量要导出的数据放入临时表分批次形成多个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>文件后合并.说明：</span> </span></p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">1.</span> <span>大量要导出的数据放入临时表</span> </span></p>
<p class="MsoNormal" style="margin-left: 21pt;"><span style="font-size: small;"><span>大量数据有可能是在海量数据表里通过条件查询出来的，这样可以有效避免多次在海量</span> <span>数据中查询，提高了查询效率</span> </span></p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">2.</span> <span>分批次形成多个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> </span></p>
<p class="MsoNormal" style="margin-left: 21pt;"><span style="font-size: small;"><span>比形成一个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>的多个</span> <span style="font-size: 12pt;" lang="EN-US">sheet</span> <span>好，因为多个</span> <span style="font-size: 12pt;" lang="EN-US">sheet</span> <span>形式需要将整个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>加载到内存中，那么</span> <span style="font-size: 12pt;" lang="EN-US">sheet</span> <span>越多，</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>越大</span> <span style="font-size: 12pt;" lang="EN-US">.</span> <span>改多个</span> <span style="font-size: 12pt;" lang="EN-US">SHEET</span> <span>页为多个</span> <span style="font-size: 12pt;" lang="EN-US">EXCEL</span> <span>。通过多次生成，可以在后面</span> <span style="font-size: 12pt;" lang="EN-US">EXCEL</span> <span>生成所需要的内存不</span> <span style="font-size: 12pt;" lang="EN-US"><span>     </span></span><span>足时，有效回收前面生成</span> <span style="font-size: 12pt;" lang="EN-US">EXCEL</span> <span>时占用的内存。</span> </span></p>
<p class="MsoNormal"> </p>
</div>
<p>请问你说的临时表是个啥概念，导出时的数据本身就是从数据库中查询出的吧，难道还要在存入临时表不成?</p>
</div>
<p>我是说如果一张表里亿级数据量,通过条件查询出10W数据,那么我们只需要分批次处理这10W数据,而不需要每次从亿级数据量里再次查询.</p>
10 楼 曾经de迷茫 2010-03-11  
先生成CSV文件，再找软件另存为Excel
9 楼 lzg3267373 2010-03-11  
langhua9527 写道
eventusermodel 读取一点不方便的

eventusermodel 的确不好操作,但它只支持只读模式,读的时候是不允许操作的,效率会比usermodel高很多.
8 楼 e3002 2010-03-11  
<div class="quote_title">lzg3267373 写道</div>
<div class="quote_div">
<p><span style="font-size: medium; color: #ff0000;"><span>一.</span> </span><span style="font-size: medium; color: #ff0000;"><span>导入生成</span> </span><span style="font-size: medium; color: #ff0000;">Excel</span> </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="font-size: small;"><span>采用的方案是：将大量要导出的数据放入临时表分批次形成多个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>文件后合并.说明：</span> </span></p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">1.</span> <span>大量要导出的数据放入临时表</span> </span></p>
<p class="MsoNormal" style="margin-left: 21pt;"><span style="font-size: small;"><span>大量数据有可能是在海量数据表里通过条件查询出来的，这样可以有效避免多次在海量</span> <span>数据中查询，提高了查询效率</span> </span></p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">2.</span> <span>分批次形成多个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> </span></p>
<p class="MsoNormal" style="margin-left: 21pt;"><span style="font-size: small;"><span>比形成一个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>的多个</span> <span style="font-size: 12pt;" lang="EN-US">sheet</span> <span>好，因为多个</span> <span style="font-size: 12pt;" lang="EN-US">sheet</span> <span>形式需要将整个</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>加载到内存中，那么</span> <span style="font-size: 12pt;" lang="EN-US">sheet</span> <span>越多，</span> <span style="font-size: 12pt;" lang="EN-US">Excel</span> <span>越大</span> <span style="font-size: 12pt;" lang="EN-US">.</span> <span>改多个</span> <span style="font-size: 12pt;" lang="EN-US">SHEET</span> <span>页为多个</span> <span style="font-size: 12pt;" lang="EN-US">EXCEL</span> <span>。通过多次生成，可以在后面</span> <span style="font-size: 12pt;" lang="EN-US">EXCEL</span> <span>生成所需要的内存不</span> <span style="font-size: 12pt;" lang="EN-US"><span>     </span></span><span>足时，有效回收前面生成</span> <span style="font-size: 12pt;" lang="EN-US">EXCEL</span> <span>时占用的内存。</span> </span></p>
<p class="MsoNormal"><span style="font-size: small;"></span></p>
</div>
<p>请问你说的临时表是个啥概念，导出时的数据本身就是从数据库中查询出的吧，难道还要在存入临时表不成?</p>
7 楼 langhua9527 2010-03-10  
eventusermodel 读取一点不方便的
6 楼 坏孩子 2010-03-10  
<div class="quote_title">lzg3267373 写道</div>
<div class="quote_div">
<p><span style="color: #ff0000;"><span style="font-size: medium;"><br></span></span></p>
<p class="MsoNormal"><span style="font-size: small;"><br></span></p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">1.excel</span>
<span>文件上传至服务器</span>
</span>
</p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">2.</span>
<span>采用</span>
<span style="font-size: 12pt;" lang="EN-US">eventusermodel</span>
<span>方式读取</span>
<span style="font-size: 12pt;" lang="EN-US">excel</span>
<span>数据</span>
<span style="font-size: 12pt;" lang="EN-US">,</span>
<span>分块读取数据并批量入临时表</span>
</span>
</p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">3.</span>
<span>利用存储过程或其他纯</span>
<span style="font-size: 12pt;" lang="EN-US">sql</span>
<span>语句来验证临时表</span>
<span style="font-size: 12pt;" lang="EN-US">(Temp1)</span>
<span>中的数据</span>
</span>
</p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">4.</span>
<span>将验证失败数据放入存储至校验失败的临时表中</span>
<span style="font-size: 12pt;" lang="EN-US">(Temp2)</span>
</span>
</p>
<p class="MsoNormal"><span style="font-size: small;"><span style="font-size: 12pt;" lang="EN-US">5.</span>
<span>将临时表</span>
<span style="font-size: 12pt;" lang="EN-US">(Temp1)</span>
<span>中的数据插入库表</span>
</span>
</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span style="color: #ff0000;"><span style="font-size: medium;"><br></span></span></p>
</div>
<p> </p>
<p>这个我们也是用存储过程校验的，用一句sql完成，速度的确很快</p>
5 楼 willvvv 2010-03-10  
这个以前做过类似的导入到excel中，采用的是从数据库读出若干条数据生成二维数组，写入到excel，再取若干条生成二维数组，写入到excel。基本上不会内存溢出，而且cpu占用也不是很高。导出的excel在5M左右没有问题。
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    MATLAB与其他软件的数据交互 MATLAB与excel与数据库交互技术详解

    特别是在处理大量分散存储在不同来源（如 Excel 表格或关系型数据库）中的数据时，提供了一套完整的解决方案。此外，还能增强对大型数据集的理解能力和深度挖掘能力。 阅读建议：建议边学边练，多尝试书中提供的例子...

    基于Excel与XML交互的物流业数据处理方法.pdf

    总结来说，基于Excel与XML交互的物流业数据处理方法是一种结合了两种技术优势的解决方案，它不仅提高了物流业务的效率，也保证了数据处理的灵活性和准确性。通过这种方式，物流企业在处理大量数据时能够减少手动输入...

    VC读写Excel速度慢问题解决方案的源代码

    在开发过程中，尤其是在使用VC++（Visual C++）进行编程时，我们经常遇到与Excel交互的需求，例如数据导入导出。然而，使用默认的方式读写Excel文件时，可能会遇到性能瓶颈，导致整个程序运行速度变慢。这在处理大量...

    ASP.NET与EXCEL交互详解及技巧

    二是使用第三方库，如EPPlus，它提供了更高效且无需安装Excel的解决方案。 1. 使用Excel对象模型（COM互操作）：这是早期常用的方法，通过创建Excel Application对象，打开工作簿，访问工作表和单元格来读写数据。...

    MATLAB与Excel文件的数据交互.doc

    Exlink宏则是一种基于MATLAB和Excel之间的动态链接库(DLL)的解决方案，它允许用户在MATLAB中直接执行Excel宏命令，从而实现更灵活的数据交互。 #### 三、函数方式详解 ##### 3.1 获取文件信息——xlsfinfo函数 **...

    关于表单的导出EXCEL设计,有时需要选择性的导出表单上的某些数据的解决方案

    本篇将深入探讨如何实现选择性地导出表单上的特定数据到Excel的解决方案。 首先，我们需要理解表单的基本结构。表单（Form）是HTML中的一个重要元素，用于收集用户输入的信息。它由各种表单控件组成，如文本输入框...

    C#操作EXCEL EXCEL类库 Excel模板处理 将DataTable数据写入Excel文件 C#数据写入EXCEL

    然而，这种方式需要Excel安装在运行程序的机器上，且性能较低，不适合大量数据处理。下面是一个简单的示例，演示如何将DataTable写入Excel： ```csharp using Microsoft.Office.Interop.Excel; // 假设已有...

    百万级数据在Excel和Sql数据库之间相互导入、导出

    对于百万级的数据，SQL Server能提供稳定且高性能的解决方案。它可以快速执行复杂的查询，并通过索引优化进一步提升性能。 在Excel与SQL Server之间进行数据导入导出有多种方法： 1. **Excel数据连接**：利用Excel...

    C# Excel大量数据快速导入数据库源码

    总的来说，这个C#源码提供了从Excel到数据库的数据导入解决方案，对于处理大量数据的场景，其效率远高于逐条插入的方式，具有很高的实用价值。开发者可以根据自己的需求调整和扩展这个源码，以满足特定的业务场景。

    vb批量将excel数据导入access以及批量将access数据库中的数据导入excel源代码

    在VB（Visual Basic）编程环境中，常常需要处理数据导入导出的问题，特别是在处理大量数据时，Excel和Access数据库之间的交互显得尤为重要。标题提到的“vb批量将excel数据导入access以及批量将access数据库中的数据...

    C#语言 EXCEL和SQL数据交互集锦

    3. 使用NPOI库：NPOI是针对.NET平台的Java POI库的移植，支持.xls和.xlsx格式，适用于大量数据处理。 二、SQL数据的访问 1. ADO.NET：微软提供的标准数据库访问技术，包括SqlConnection、SqlCommand、...

    MSSQL excel数据导入

    在本项目中，ImportExcel.sln是解决方案文件，它包含了整个项目的配置和引用。而ImportExcel可能是项目主程序的可执行文件，它提供了一个图形用户界面（GUI），用户可以通过这个界面选择Excel文件，设置数据库连接...

    Excel与数据库数据交换类

    《Excel与数据库数据交换类》是针对这个需求提供的一种编程解决方案，它允许开发者通过编写代码来便捷地实现Excel与数据库之间的数据导入导出。 首先，我们要理解这个类的基本工作原理。数据交换通常涉及到读取...

    VB读取串口数据记录在excel，access的软件

    总之，这个VB软件提供了串口数据采集、存储和可视化的一站式解决方案，对于需要处理串口数据的用户来说，是一个非常实用的工具。其核心技术包括串口通信、数据库操作和图形化显示，这些是IT行业中常见的技能点，也是...

    PHP导出数据到excel

    当我们需要从数据库中导出大量数据并以用户友好的格式提供时，Excel是一种理想的选择。本文将深入探讨如何使用PHP将数据从MySQL数据库导出到Excel文件。 首先，要实现这个功能，你需要了解PHP的基本语法以及如何...

    我的boss的excel导出解决方案20170316

    - **性能优化**：对于大量数据的处理，可以考虑分批次上传或者采用异步处理机制，避免长时间阻塞用户界面。 - **异常处理**：添加异常处理逻辑，确保程序在遇到问题时能够妥善处理并给出反馈。 综上所述，本方案...

    处理大数据量excel

    这篇博文“处理大数据量excel”可能提供了针对这一问题的解决方案。博主分享了在处理大量数据时，如何利用编程工具和特定库来提升效率和性能。 首先，我们关注到标签中的“源码”和“工具”，这暗示了博主可能介绍...

    c# 存入数据到excel以及读取excel数据

    另一种无依赖的解决方案是使用第三方库，如EPPlus，它提供了一个更轻量级的API来处理Excel文件（.xlsx格式）。 1. **使用Microsoft.Office.Interop.Excel** 要使用此方法，首先需要添加对`Microsoft.Office....

    HW-TCP终端数据采集EXCEL存储程序LABWINDOWS

    【描述】中提到的"使用labwindows开发的数据采集程序，完全自动存储到EXCEL文档"，意味着开发人员利用LabWindows/CVI的强大功能，创建了一个自动化程度高的解决方案。LabWindows/CVI是美国国家仪器公司（NI）提供的...

    Microsoft Office SharePoint Server 2007管理与应用系列之九：利用Excel Service创建商业智能解决方案

    7. Excel Services与Business Connectivity Services（BCS，之前称为Business Data Catalog，BDC）的集成，使得直接从SharePoint访问企业系统如ERP和CRM成为可能，进一步增强了BI解决方案的功能。 8. PowerPivot...

Magicbox
Global site tag (gtag.js) - Google Analytics