poi解析excel的问题讨论

内存占用问题

关于OOXML

Office Open Xml的简称，基于xml的office文档标准，2006年由微软开发并被接受为ECMA-376标准，2008年出第二版，2011年出第三版，到了第四版，就被ISO/IEC接受为ISO/IEC 29500标准。

excel2007的格式

excel2007就是OOXML格式，本质上它是一个zip包，如果我们在excel文件名后加.zip后缀，是可以用winrar打开的，打开后我们看到zip包内部就是一些文件夹和xml文件，每个sheet页的数据都在这些xml里。

这是解压后的顶层目录：

_rels\
docProps\
xl\

xl目录下是sheet页的信息，如下：

worksheets\
sharedStrings.xml
styles.xml
workbook.xml

这里面我们要关注的是workbook.xml，sharedStrings.xml，styles.xml和worksheets目录。workbook.xml描述了sheet页清单；sharedStrings.xml可看作一个字符串池，其中的每个字符串通过一个数字索引被sheet页引用；styles.xml记录了用到的样式信息；worksheets下每个xml就是sheet页的具体内容。

读取excel2007的内容

java下用poi读取excel内容，常用的是用户模式，就是使用XSSFWorkbook类，这种方式用起来简单，却有一个很要命的问题：内存占用很高，容易OOM。我曾遇到过1.5M的文件，用XSSFWorkbook读取后，内存瞬间暴涨到120M！究其原因，用户模式在实现上使用了dom方式来读取excel中所有的xml文件，因此其内存占用应该是：M*C*original_size，M是xml文件的内存占用比，一般是10，C是压缩比（前面说过，excel2007就是一个zip包），8倍的压缩比也很常见，original_size是excel文件磁盘大小。因此80倍的内存涨幅对excel来说是完全可能的，这种程度的消耗远比dom方式解析xml要恐怖！

因此，我们只能考虑用sax方式来解析excel，excel里比较大的xml文件有：sharedStrings.xml、worksheets里各sheet页所在的xml，因此对这两类xml我们使用sax解析，应该就可以极大减少内存的消耗。poi里已经提供了eventusermodel API来帮我们做好了底层工作，包括zip结构的读取，甚至对sharedStrings.xml也提供了一个ReadOnlySharedStringsTable类来做sax解析，所以我们只需自行实现sheet xml的sax解析即可，当然这需要参考ECMA-376标准的相关章节先获得sheet xml的格式定义。
我在实际项目中做了一个Parser+Handler回调模式的excel解析框架，就是上述思路，能完美解决excel解析内存占用大的问题。

excel写入

写入规格

以前总听说excel的sheet页最多能写入65535行记录，这个其实不对的。
微软的官方规格是一个sheet页支持1048576 rows 和 16384 columns，一个workbook支持255个sheet页。所以，如果要写的数据超过百万行，实现上可以再起若干sheet页接着写入。

支持表格下拉框

poi里提供了相关的辅助类DataValidation，用法如下：

// row and column both start from 0 and is end-inclusive
List<String> dropdownList = ...
// 下拉框的excel表格范围
CellRangeAddressList addressList = new CellRangeAddressList(startRow,
                    endRow, startColumn, endColumn);
DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(
    dropdownList.toArray(new String[0]));
DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);