数据导出至Excel文件总结

1.背景

最近工作中，常常遇到客户要求将系统内的数据导出到Excel文件中，供其进行核对或使用的需求。

• 用户要求的导出的数据量大，平均都在10W条以上
• 用户明确要求，导出数据格式必须是Excel文档
• 对数据导出时间也有一定的要求，不能无休止的等待；数据导出速度越快越好，Excel文件越小越好
• 数据导出由系统运维工作人员负责，通过离线文件、U盘的传输媒介将导出数据送至用户手中

结合用户提出的要求和在项目运维过程中遇到的问题对需求进行整理。需要完成一个供系统运维人员使用的数据导出至Excel文件工具的设计与开发，该工具具有如下特点：

• 使用人员：系统运维人员；用户不参与使用。
• 运维人员将SQL模板输入到工具后，工具自动替换参数；
• 工具自动执行SQL；
• 工具自动将SQL执行结果导出至Excel文件，支持XLS和XLSX两种格式。
• 效率。

2.方案选择

• 方案1：使用Java语言开发

优势：简单，有很多现成的库可以使用
缺点：JDBC效率是否能达标？使用JAVA写Excel文件是否会导致内存溢出？是否有解决方案？

• 方案2：使用VC开发

优势：速度响应应该比较快；使用OCI进行数据库访问；
缺点：开发量大，是否有现成的开源库支持写Excel文件，是否存在内存溢出问题？

• 方案3：主要集中在如何进行数据存储的问题上。使用TXT文件进行数据存储时写入很快，但不满足用户要求；使用CSV文件时，Excel工具可正常打开，但是如果是数字时显示不正确，数字会显示成科学计数法；如果数字前含有0时，0不会显示。如果在CSV文件中，假如特定的字符串的话，破坏了原始数据的真实性，同时也不利于用户使用。

• 方案4：方案4是基于方案3进行实现的，程序依然导出CSV/TXT文件，让用户在使用时，通过EXCEL的导入功能，将文件导入EXCEL中，同时特别告知用户在导入时需要注意选择列的数据类型为字符串或者是数字。此方案需要与客户沟通，同时与客户的要求是不一致的，如果实在不能解决的情况下考虑与客户沟通希望可以达成一致。

3.方案实现

• 首先确定技术方案：最终选择VC实现，涉及到的类库如下：
  EXCEL操作库：LIBXL（破解版），正版需要付199刀 http://www.libxl.com/
  OCI封装库：OCILib https://github.com/vrogier/ocilib
  两个库的介绍，执行打开网站即可。

• 通过调用OCI库完成数据查询，同时使用LIBXL库完成EXCEL文档写入。

使用OCI完成数据库连接时，要求客户端需要安装Oracle客户端和使用JDBC:thin的格式是有区别的。使用JDBC也可以使用OCI的方式连接，但未对效率进行测试。

• 使用LibXL操作Excel未出现内存溢出的情况。

• OCILib文档写的很全面，直接使用即可。

• LibXL写入Excel文件代码见官网，文档都很全面。

• 其他要点

清楚使用VS进行程序开发的相关知识，如添加include头文件，添加lib库以及使用DLL的知识。

特点

• 数据查询速度快
• 不需要使用PL/SQL工具
• 工具可以复用，针对任何SQL均可进行数据导出，同时支持动态添加Sheet页，并写入数据。
• 不需要安装Excel工具即可支持导出。但是需要安装Oracle客户端。

4.方案验证

在使用LibXL写入Excel文件的时候，大数据量的情况下效率比较低。使用拆分文件的方式，也没有很好解决。对于大文件的情况，测试的结果是当数据大于50万条列为100列的情况下，写入是失败的。在进行文件存盘时，没有报错，但是生成的文件是打不开的。（Excel提示文件已损坏）
过度方案，尽可能小的写文件，然后通过Excel自带的功能或者VBA编程将所有文件内容合并到一个文件上。

5.使用Java写Excel文件的方案实现情况