POI导出excel性能问题

POI导出excel性能问题

认识Excel

Excel有2种大的版本，2003，2007（2010/2013总体架构于2007差不多）。Excel中的数据多少将会影响客户端打开数据的速度。
Excel中的数据是由行*列来决定的，一个10列5000行的excel打开是在用户可以接受的范围内的，但是对excel做相关操作，比如“筛选”，可能会爆掉。
另外2003和2007有个最大的不同，2003所能存储216=65,536，而2007所能存储220=1,048,576

认识POI

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。针对2003和2007有两套不同的实现。
结构：

  • HSSF － 提供读写Microsoft Excel格式档案的功能。2003
  • XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。 2007

POI实现Excel

我们用POI去实现Excel的导出功能，有两个性能问题：1. 导出的时间太长;2. 打开Excel的时间太长。你可以认为由于数据太大引起的性能问题，但是对于不可以避免的
数据量有可以解决的问题吗？就这两方面做一个浅浅的分析。

  1. 导出的时间

    a. 首先检查后台代码是否有可以优化的空间，过于复杂的SQL查询,多余的循环等等。如果你是一次性将所有结果放入List中，那么内存可能会逸出，可以考虑分页查询或者边查边写。

    b. 选择导出2003或2007，一般人可能会认为导出2007无疑是更好的选择，几乎没有行数的限制，版本高写入速度快。但是通过我的测试导出2003比2007快10倍,选择2003，行数的限制怎么办？

  2. 打开Excel的时间

    a. 当文件生成后，打开速度慢已经不是我们可以决定的了。之前说过，数据量影响打开的速度，去掉哪些可有可无的列

    b. 换台高配的电脑

如果想既不受行的限制，又能快速并查看导出，如何处理？

目前我能想到2种方案是：

  1. 以2003的方式导5000条记录为一个文件，最后打包成zip提供客户下载
  2. 因为Excel可以浏览CSV文件，可导到一个CSV文件中，注意英文环境下中文乱码的问题

英文操作系统，Excel打开csv乱码解决方案

首先需要了解CSV

逗号分隔值（Comma-Separated Values，CSV，有时也称为字符分隔值，因为分隔字符也可以不是逗号），其文件以纯文本形式存储表格数据（数字和文本）。纯文本意味着该文件是一个字符序列，不含必须像二进制数字那样被解读的数据。CSV文件由任意数目的记录组成，记录间以某种换行符分隔；每条记录由字段组成，字段间的分隔符是其它字符或字符串，最常见的是逗号或制表符。通常，所有记录都有完全相同的字段序列。

用记事本打开CSV，ANSI,UTF-8都不会出现乱码的，但是，CSV是用Excel打开utf-8 without BOM是会显示乱码的，这个尤其注意；用ANSI、UTF-8(有BOM)可以打开，为什么UTF-8(有BOM)却可以打开呢？知乎上有人是这么回答的,姑且就这么认为吧

UTF-8 不需要 BOM，尽管 Unicode 标准允许在 UTF-8 中使用 BOM。
所以不含 BOM 的 UTF-8 才是标准形式，在 UTF-8 文件中放置 BOM 主要是微软的习惯（顺便提一下：把带有 BOM 的小端序 UTF-16 称作「Unicode」而又不详细说明，这也是微软的习惯）

有上面可知，只要保证CSV文件是ANSI就可以了,在中文操作系统，文件格式指定GBK即可

在简体中文Windows操作系统中，ANSI 编码代表 GBK 编码；在繁体中文Windows操作系统中，ANSI编码代表Big5；在日文Windows操作系统中，ANSI 编码代表 Shift_JIS 编码

但是有一天客户跟我们说，他的英文操作系统打开CSV出现了乱码

这时候可以采用UTF-8(有BOM)存储CSV文件，在java中指定UTF-8都是有BOM，需要自己转换

public void utf2bom(File file) throws IOException {
        byte[] content = FileUtils.readFileToByteArray(file);

        byte[] bomContent = new byte[content.length + 3];
        bomContent[0] = (byte) 0xEF; 
        bomContent[1] = (byte) 0xBB; 
        bomContent[2] = (byte) 0xBF;

        System.arraycopy(content, 0, bomContent, 3, content.length);

        FileUtils.writeByteArrayToFile(file, bomContent);
}

客户端调用,这样你储存的文件就是UTF-8 with BOM，就不会受操作系统环境的影响了

File file = new File(excelDir + fullName);
OutputStreamWriter fwriter = new OutputStreamWriter(  
        new FileOutputStream(file), "UTF-8");
//转换utf-8 bom
utf2bom(file);

CSV文件中包数字，转义

  • CSV用逗号隔开数据，用换行符产生一行一行的数据， 逗号就是第一个特殊字符，如果数据内容中出现了逗号，就要用半角双引把数据内容包起来，所以比如数据是 xilang,yan， 就要改成 “xilang,yan”。第二个特殊字符就是引号:”，数据中如果有引号，就要换成两个引号，比如xilang”yan要转义为xilang””yan。
  • 如果数据是存数字，并且第一个是0， 在excel下不会显示出来，解决方法就是，先用引号把数据包起来，再在数据前加一等号，比如：0123456就变成=”0123456″。但是这种解决方法有限，如果自己内容很长的话，也不能正确显示，不过还好，正常情况下，数字应该不会很长