说起Excel的读写，我们不免会想起POI这位德高望重的前辈。历经岁月变迁，虽然出现了很多如EasyExcel，EasyPOI等后起之秀，POI这位前辈依然在Excel江湖中留有一席之地，并始终与我们相向而行，且依然发光发热。平心而论，POI确实因为初始架构的制约等原因，在性能和内存资源消耗上做的不尽人如意，但是不可否认POI仍然是一个出色的Excel工具包，其丰富灵活的API也是EasyPOI等其他工具包所欠缺的。
POI对Excel的操作建立在一套细致如毛发的模型基础上，基本结构如下图1所示。然而成也萧何，败也萧何。这套过于细致的模型虽然提供了更多灵活便利，但是它也是一把双刃剑，同时制约了POI的性能，并增加了内存等资源的消耗。如果是大数据量的简单Excel报表的读写，选择EasyExcel，EasyPOI会是更好的选择。对于需要定制的格式和丰富的展示效果，那么POI也会是个不错的选择。
图1 POI的Excel模型示意
正如上图所示，POI通过四个关键模型对象及其相关API完成对Excel文件的操作. 他们分别是：
-
Workbook，别称工作簿，是包含整个Excel文件信息的容器，也是POI关于Excel一切操作的源头，包括获取Sheet，定义单元格样式，定义字体样式和文件的输入输出等。
-
Sheet，可以称为工作表或者表格，是包含多个行和列的表格容器。
-
Row, 行，是单行数据和单元格的集合容器。
-
Cell, 单元格，是单个数据的容器。
除了上述四个关键的模型对象之外，POI还有CellStyle表格样式，Font字体以及XSSFComment等其他模型对象。有些对象只能支持特定版本的Excel，例如XSSFComment则需要Excel 2007+。有了POI的Excel模型后，我们可以在下面对Excel的操作进行具体的展开。
基本操作
POI要对Excel进行细致的操作，那么必须要具备Workbook， Sheet， Row 和Cell这个四个基本要素。所以最基本操作就是对四个要素的获取和创建以及对单元格值的读写。
Workbook操作
如果我们需要读取一个Excel文件的数据，那么我们首先是通过文件输入流InputStream，构建一个Workbook实例对象。对于不同的Excel版本，我们需要构建不同类型的WorkBook。 例如对于Excel 2003，我们需要HSSFWorkbook类型的WorkBook。而更高的版本，我们可以使用XSSFWorkbook，下面我们都以XSSFWorkbook为例，代码如下：
Workbook book = new XSSFWorkbook(stream);
复制代码当我们需要写入的一个全新的Excel文件的时候，我们就可以直接实例一个Workbook对象实例，代码如下：
Workbook book = new XSSFWorkbook();
复制代码当然写入数据后，还有需要将Workbook的数据导出成文件或者目标输出流中。并且，需要在做完这些后关闭Workbook，不然有可能导致内存一直被占用，最终导致内存溢出。具体代码如下：
book.write(output);
book.close();
复制代码Sheet操作
当我们获取到Excel的Workbook实例后，我们就可以进一步对工作表进行操作。当我们需要读取特定工作表里的数据时候，我们就可以通过索引位置方式获取Sheet的实例对象：
book.getSheetAt(0)
复制代码当然我们也可以通过工作表名称的方式获取：
book.getSheet(name)
复制代码除此之外，还有可以通过遍历这种更加安全的方式去获取：
for (Sheet sheet : book) {
// to do more
……
}
复制代码读取数据的时候我们需要获取Sheet实例对象，写入全新的Excel文件的时候我们就需要创建Sheet实例对象。如果你对工作表有命名要求可以使用如下代码：
this.book.createSheet(WorkbookUtil.createSafeSheetName(data.getName(), ‘_'));
复制代码Excel对Sheet的命名是有规范要求的：1. 不能重名。2. 不能有特殊字符。对于第二条通过WorkbookUtil.createSafeSheetName这个小窍门可以帮助你有效规避这个问题。
Row操作
Row的操作相对其他元素的操作相对简单一点。读取数据的时候通过索引或者遍历获取Row实例对象：
int count = sheet.getLastRowNum();
for (int i = sheet.getFirstRowNum(); i <= count; i++) {
Row excelRow = sheet.getRow(i);
// to do more
……
}
复制代码写入的时候，通过索引序号创建Row实例对象：
this.sheet.createRow(row.getIndex());
复制代码Cell操作
对于我们来说，Cell操作最重要的不是获取或者创建Cell实例对象，而能够读取或者写入数据。当然我们获取到Cell对象实例并且读取其数值时候，我们不能简单就可以直接读取其中的数据。**在读取数据之前，很重要的一步就是判断单元格数据的类型，然后选择合适的接口读取。**代码如下：
Cell excelCell = row.getCell(i);
private Object getValue(Cell cell){
Object value = null;
CellType type = cell.getCellType();
switch(type){
case BOOLEAN:{
value = cell.getBooleanCellValue();
break;
}
case STRING:{
value = cell.getStringCellValue();
break;
}
case NUMERIC:{
CellStyle style= cell.getCellStyle();
if(!ObjectUtils.isEmpty(style) && !StringUtils.isEmpty(style.getDataFormatString())){
String dateFormat = style.getDataFormatString();
if(dateFormat.contains(":")){
value = LocalDateTimeUtils.from(DateUtil.getJavaDate(cell.getNumericCellValue()));
}
else if(dateFormat.toLowerCase().contains("y")){
value = LocalDateUtils.from(DateUtil.getJavaDate(cell.getNumericCellValue()));
}
else{
value = cell.getNumericCellValue();
}
}
else{
value = cell.getNumericCellValue();
}
break;
}
case FORMULA:
case ERROR:
case BLANK:
case _NONE:{
break;
}
default:break;
}
return value;
}
复制代码尤其是读取的时候遇到时间类型的数据，就需要格外当心。POI通常把Excel里的时间类型数据识别成NUMERIC类型，所以读取的时候很容易将它和数值混淆在一起。唯一的方法是通过它的数据格式部分弥补这个缺陷。另外在获取时间值的时候，可以DateUtil.getJavaDate()将Excel里的数值转化为Java的Date类型。如果你需要再转化为其他类型，你就需要自己写逻辑。
相对读取，写入单元格的数据就比较轻松一些：
this.cell = this.row.createCell(data.getIndex());
private void writeValue(Object value) {
if (ObjectUtils.isEmpty(value)) {
this.cell.setBlank();
} else if (value instanceof String) {
this.cell.setCellValue((String) value);
} else if (value instanceof Boolean) {
this.cell.setCellValue((boolean) value);
} else if (value instanceof Double || value instanceof Float) {
if (!Double.isNaN((double) value) && !Double.isInfinite((double) value)) {
this.cell.setCellValue(
BigDecimal.valueOf((double) value).setScale(2, RoundingMode.HALF_UP).doubleValue());
}
} else if (value instanceof Integer || value instanceof Short) {
this.cell.setCellValue((int) value);
} else if (value instanceof Long) {
this.cell.setCellValue((long) value);
} else if (value instanceof Date) {
this.cell.setCellValue((Date) value);
} else if (value instanceof LocalDate) {
this.cell.setCellValue(LocalDateUtils.to((LocalDate) value));
} else if (value instanceof LocalDateTime) {
this.cell.setCellValue(LocalDateTimeUtils.to((LocalDateTime) value));
}
}
复制代码如果需要定制时间格式，这技巧将在下面讲在这里先不做具体说明。
骚操作
在上面基本操作里，我们讲到了Workbook，Sheet，Row和Cell四个基本要素的常规操作。这些操作已经足够满足普通的日常读写需求。但是这些操作不能满足一些个性化的需求，比如合并单元格，添加背景色，甚至添加批注等。这里讲说明这些骚操作。
网格线
默认Excel的工作表的行列之间都会有一条条深灰色的分割线，这就是网格线。网格线让数据表更易阅读。但是同时网格线也影响了美观，所以很多个性化的Excel报表会隐藏网格线，如下图所示：
图2 Excel网格线和报表
那么如何隐藏网格线呢？其实很简单：
this.sheet.setDisplayGridlines(false);
复制代码冻结窗格
冻结窗格用于指定某些行和列不会随着滚动条滚动的那部分区域。当我们很多行和列的数据的时候，在滚动的时候我们通常很难记忆当前的数据属于哪一行哪一项。而冻结窗口可以锁定标题行和列，在滚动的时候，可以很容易知道数据属于哪一行哪一项。
图3 冻结窗口
那么POI又是如何实现冻结窗口的呢？也比较简单如下：
this.sheet.createFreezePane(data.getFreezen().x, data.getFreezen().y);
复制代码通过指定冻结窗口右下单元格就可以生成包含单元的上侧和左侧区域的冻结区域。
工作表隐藏
很多时候，工作表不一定需要显示出来。比如当我们使用自定义的Excel的模版文件时候，里面通常由一些用于存放配置项和报表原始数据的工作表。这些工作表仅仅提供数据支持，并不用于展示。这个时候我们通常会讲其隐藏起来。
图4 隐藏工作表
这也是比较简单的操作，但是需要Workbook和Sheet一起操作：
this.book.setSheetHidden(this.book.getSheetIndex(this.sheet), true);
复制代码自动公式生效
在单元格中有一类单元格比较特殊。它没有直接的值，而是通过一个公式引用其他单元格的数据计算获取值，并随着引用的单元格的数值变化而变化。理论上说，如果单元格配置好公式后就不需要我们去管理。但是现实非常骨感。**当我们通过POI写入生成Excel文件后，打开Excel，发现里面的配置的公式的单元格并没有随着写入的数据而变化。**是不是很难想象！但是现实就是如此。
图5 带公式的单元格
那么如何让公式能够生效呢？解决办法也是出乎预料的简单，POI已经提供了现成的接口。在填充完数据后调用下面的代码：
workBook.setForceFormulaRecalculation(true);
复制代码单元格样式
当我们需要做个性化报表定制的时候，总是难免需要用到单元格的样式，比如单元格的背景色，字体等。这个时候就需要用到CellStyle，Font等对象。让我们一点点来分解吧。另外补充一点，CellStyle，Font都是全局可复用对象，所以最好建立一个实例缓存池，将这些对象缓存起来，从而能够有更好的内存使用。
背景色
背景颜色的设置是个性化报表的基本要求。那么如何给单元格添加背景色呢？首先我们必须要有CellStyle实例对象，然后在这个实例对象中配置背景色，最后给Cell设置前面的样式即可，代码如下：
CellStyle style = this.book.createCellStyle();
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(data.getBackground());
this.cell.setCellStyle(style);
复制代码其中setFillForegroundColor设置的颜色值可以通过IndexedColors里很多Excel内置的颜色，例如蓝色可以通过IndexedColors.BLUE.getIndex()指定。
对齐方式
和背景色一样，对齐方式也是单元格的一种样式，同样也需要CellStyle实例对象。对齐方式分为垂直对齐和水平对齐。
图6 Excel的对齐方式
那么通过POI怎么实现呢，让我们看一下代码：
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
复制代码上面代码分别是水平居中和垂直居中，通过调整HorizontalAlignment 和VerticalAlignment参数同理可以实现左对齐和右对齐
边框
边框是单元格另外一个比较常用的个性化的设置。通常在定制化报表中，我们会对标题和表单头部添加一个粗边框，来突出显示某些内容区块。
图7 设置边框
POI对于边框的处理也是很有一套的，当然还是离不开CellStyle这个老伙计：
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
复制代码日期格式
在单元格数据写入的章节，我也说过对于日期格式，我们可以自定义日期格式。POI通过CellStyle来实现日期格式的自定义：
DataFormat format = this.book.createDataFormat();
style.setDataFormat(format.getFormat("yyyy-MM-dd"));
复制代码首先我们需要通过Workbook对象构建DataFormat，然后通过DataFormat构建一个模式化的日期格式。当然通过调整相应参数，我们也能构建其他的日期格式。
字体
单元格字体也是我们在定制报表中经常会魔改的一个设置，而且使用到的频率相对也挺高的。和边框一样，通常用于突出显示某部分内容，例如标题需要放大加粗。
图8 Excel里的各种字体
那么对于字体POI又是如何处理的呢？首先我们需要借助Workbook实例对象构建一个Font对象。Font实例对象基本包含了字体的所有设置例如颜色，大小，粗细等。然后需要将Font配置到CellStyle中，最后借助CellStyle应用到单元格上。具体代码如下：
Font font = this.book.createFont();
font.setColor(style.getColor()); //颜色
font.setFontHeight(style.getSize()); //字体大小
font.setBold(true); //粗体
font.setItalic(true); //斜体
font.setUnderline(Font.U_SINGLE); //下划线
style.setFont(font);
复制代码批注
批注是一个浮动在单元格之上的文本区域，通常用于标注，笔记或者备忘一些内容。例如对文章某一段落的校准批注等
图9 批注
批注的代码创建比较复杂，需要明确具体的Workbook类型，对于XSSFWorkbook类型，可以参考下面代码：
XSSFDrawing p = (XSSFDrawing) this.sheet.createDrawingPatriarch();
XSSFComment comment = p.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, firstCell, firstRow, lastCell, lastRow));
comment.setString(new XSSFRichTextString(data.getComments()));
this.cell.setCellComment(comment);
复制代码对于Excel来说，创建批注首先需要获取一块绘制区域即XSSFDrawing。 通过XSSFDrawing确定XSSFComment的锚点和大致相当于多少个单元格的显示范围。通过批注实例对象XSSFComment设置内容，最后绑定到对应的单元格上即可。
合并单元格
对于很多定制化的表头，我们通常需要对归类处理，这个时候我们就需要用到合并单元格，用于表示那些行或者那些列的数据是一组数据。
首先我们需要圈定需要合并的单元格范围，借助CellRangeAddress实现：
CellRangeAddress address = new CellRangeAddress(range.getFirstCell().y, range.getLastCell().y,
range.getFirstCell().x, range.getLastCell().x);
复制代码此外我们可以对需要合并的单元格设置边框等操作：
RegionUtil.setBorderLeft(BorderStyle.THIN, address, this.sheet);
RegionUtil.setLeftBorderColor(IndexedColors.BLACK.getIndex(), address, this.sheet);
复制代码最后让单元格合并生效：
this.sheet.addMergedRegion(address);
复制代码需要注意的是如果单元格少于等于1个，那么合并单元格不仅不会起作用，而且还会抛出异常，所有需要额外的检测措施。
图10 合并单元格
表格样式
Excel还有内建的表格样式，所以严格来说Sheet只是一张工作表，在sheet内部还有一个类似表格的模型定义。它就是XSSFTable。
图11 表格样式
表格样式的设置有点类似于批注的生成，具体的代码如下：
if (this.sheet instanceof XSSFSheet) {
XSSFSheet sheet = (XSSFSheet) this.sheet;
Row firstRow = this.sheet.getRow(tableSheet.getFirstRow());
Cell firstCell = firstRow.getCell(tableSheet.getFirstCell());
Row lastRow = this.sheet.getRow(tableSheet.getLastRow());
Cell lastCell = lastRow.getCell(tableSheet.getLastCell());
AreaReference reference = new AreaReference(new CellReference(firstCell), new CellReference(lastCell),
SpreadsheetVersion.EXCEL2007);
XSSFTable table = sheet.createTable(reference);
table.setDisplayName(tableSheet.getTableName());
table.setStyleName(XSSFBuiltinTableStyle.TableStyleMedium2.name());
}
复制代码模版导出
尽管通过POI可以实现很多骚操作，但是依然很多高级Excel功能是POI无能为力的，例如像柱状图等图表功能是POI无法实现的。但是并不是说没有办法做到。通过Excel模版文件就可以做到让POI也能生成非常高级的报表。
首先我们需要构建一个Excel模版文件，其中包含两类sheet。一类sheet包含原始的数据，提供数据支持。二另一类sheet就包含柱状图和相应的统计结构。第二类sheet的数据通常来自第一类的sheet。这样我们只需要通过向第一类sheet写入数据就可以自动在第二类sheet生成非常漂亮的报表。而代码也非常简单，只需像读取文件一样，给Workbook指定Excel模版文件输入流，然后正常写入即可：
Workbook book = new XSSFWorkbook(stream);
复制代码套壳操作
POI提供的操作和API考虑到普适性，做得比较细致和繁琐。然后在产品中，通常我们并不需要这么繁琐细致的操作，只需要一部分操作即可。所以对于POI的使用最好的方式在外面构建一层简化的壳。
模型映射
通过简化的模型映射是其中的一种方法。这个方法的核心就是将POI当作一个影子系统使用，然后定义Workbook， Sheet，Row和Cell四要素的简化数据模型以及相应的映射外壳API和POI进行交互。这样做可以在保留一定灵活性的情况下，简化POI的操作。
例如对于Row, 我们定义DataRow的映射数据模型：
public class DataRow {
protected int index;
protected List<DataCell> cells = new ArrayList<>();
/**获取行号
* @return
*/
public int getIndex() {
return index;
}
public void setIndex(int index) {
this.index = index;
}
/**
* 获取单元格数据
* @return
*/
public List<DataCell> getCells() {
return cells;
}
public void setCells(List<DataCell> cells) {
this.cells = cells;
}
/**
* 是否为空
* @return
*/
public boolean isEmpty(){
boolean result = ObjectUtils.isEmpty(this.cells);
if(!result){
for(DataCell cell: cells){
result = cell.isEmpty() && result;
}
}
return result;
}
}
复制代码然后通过壳API遥控POI操作：
class DefaultExcelRowWriter implements ExcelRowWriter {
private Sheet sheet;
private ExcelContext context;
private Row row;
public DefaultExcelRowWriter(Sheet sheet, ExcelContext context) {
this.sheet = sheet;
this.context = context;
}
/**
* 补全空格
*
* @param index
*/
private void addEmptyCell(int index) {
this.row.createCell(index, CellType.BLANK);
}
@Override
public void write(DataRow row) throws Exception {
this.row = this.sheet.createRow(row.getIndex());
int cellIndex = 0;
for (DataCell cell : row.getCells()) {
for (; cellIndex < cell.getIndex(); cellIndex++) {
this.addEmptyCell(cellIndex);
}
ExcelCellWriter cellWriter = new DefaultExcelCellWriter(this.sheet, this.row, this.context);
cellWriter.write(cell);
cellIndex = cell.getIndex() + 1;
}
}
}
复制代码注解
尽管模型映射和套壳API可以一定程度简化POI操作，但是依然无法避免大量内存的浪费以及操作上的繁琐。所以对于简单的Excel报表或者定制的报表，注解可能是目前的最优解。注解也借助了映射和套壳的原理。可以说注解是另辟蹊径的实现。但是确实可以更加简洁大方。对于注解这里不做具体展开，如果需要请参考EasyExcel和EasyPOI。这两个都是此道的优秀之作。
结论
确实随着时间的流逝，POI已经步入老年，略显老态龙钟。然后它依然是一个优秀的工具。POI其实还有很多骚操作，篇幅有限不再累述。希望它能够随着时代的进步，重新焕发青春。
近期评论