Apache POI Excel入门（从POI提供的API倒推Excel功能）
之前只有简单用过Excel，对Excel的功能不甚了解。而在使用POI时，上面就看到密密麻麻的API,看得眼花缭乱。 本文就从一名Excel小白，通过代码API的角度，去发掘和认识Excel所提供的各项属性与功能。
一、Excel整体概念
| 名称 | 英文 | 解释 |
|---|---|---|
| 工作簿 | WorkBook | 一个Excel文件对应一个WorkBook，可以认为WorkBook就是这整个文档文件。 |
| 工作表 | Sheet | 一个WorkBook下有很多Sheet |
| 单元格 | Cell | 每一个Sheet都由若干Cell组成 |
名称
- 名称注意范围可以是WorkBook或者具体的一个Sheet就好了！
- 名称可指定引用位置来自哪一个单元格，也可以应用于一个单元格！
Sheet工作表
打开Excel工具，底下显示的就是若干个Sheet。
-
每次打开Excel文件，会回到上一次打开的Sheet，这个是Excel文件本身存储的信息。（即使换一个Excel工具打开也一样） 而且，每个Sheet还会存储上一次打开这个Sheet停留的单元格（Excel工具中不一定有把这个信息显示出来） 也就是，只是读一个Excel文件，哪怕没有对它进行修改操作，文件本身也会有一些状态属性在发生改变。
-
Sheet工作表可以设置隐藏、保护等属性。
打印区域
- 每一个Sheet工作表可以单独设置打印区域。
方案管理器
- sheet方案：数据>模拟分析>方案管理器 方案提供了一种动态改变单元格数据的手段。（每个方案最多允许32个单元格)
条件格式
选一个范围，然后设置：条件格式，效果如下:
二、Excel测试
在编写代码前，先进行以下操作。
注意：这部分添加的内容会与第三部分的代码结果一一对应！
添加名称
名称设置后如下：
添加图片
添加批注
添加分页符
添加超链接
添加数据提示与约束
三、Excel代码
依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
</dependencies>
初始化
通过new XSSFWorkbook(文件or文件流)获取到Workbook，如下：
@Slf4j
public class ExcelTest {
public static void main(String[] args) throws Exception {
//读取文件
String userDir = System.getProperty("user.dir");
String excelPath = userDir + "/live-test/src/main/resources/test.xlsx";
File file = new File(excelPath);
log.info("文件路径：{} , 存在:{},大小:{}", excelPath, file.exists(), file.length());
//流
FileInputStream inputStream = new FileInputStream(file);
Workbook workbook = new XSSFWorkbook(inputStream);
}
WorkBook
通过WorkBook可以获取的信息
| 集合属性相关的方法 | 获取到的内容 | 关联的方法 |
|---|---|---|
| getNumberOfSheets() | sheet的数量 | getSheet(String)/getSheetAt(int) |
| getNumberOfNames() | 范围为WorkBook级别的名称数量（不清楚名称是什么？详细见上） | getAllNames() |
| getNumCellStyles() | 单元格样式数量 | getCellStypeAt(int) |
| getNumberOfFonts() | 字体样式数量 | getFontAt(int) |
| getAllNames() | 名称集合（范围为WorkBook级别的） | |
| getAllPictures() | 图片集合,与Names不同，所有插入图片都归WorkBook管 |
| 基础属性相关的方法 | 获取到的内容 |
|---|---|
| getSpreadsheetVersion() | excel文件的版本 |
| getActiveSheetIndex() | 最后打开sheet的索引(可以切换一下Sheet,然后跑程序，看看是不是发生了变化) |
| getMissingCellPolicy() | 找不到Cell单元格的策略，默认：RETURN_NULL_AND_BLANK |
| getForceFormulaRecalculation() | 公式是否重新计算，或者沿用之前运算的结果 |
| getFirstVisibleTab() | 第一个可见Sheet的索引（每个Sheet是有一个隐藏属性的） |
| getPrintArea(int) | 获取Sheet索引对应的Sheet的打印区域 |
字体信息
int numberOfFonts = workbook.getNumberOfFonts();
for (int i = 0; i < numberOfFonts; i++) {
Font fontAt = workbook.getFontAt(i);
log.info("""
索引:{}
字体信息:{},颜色:{},字符集:{},类型偏移:{},
是否加粗:{},是否斜体:{},是否下划线:{},是否删除线:{},
字体高度:{},字体高度(磅):{}
""",
fontAt.getIndex(),
fontAt.getFontName(), fontAt.getColor(), fontAt.getCharSet(), fontAt.getTypeOffset(),
fontAt.getBold(), fontAt.getItalic(), fontAt.getUnderline(), fontAt.getStrikeout(),
fontAt.getFontHeight(), fontAt.getFontHeightInPoints());
}
名字信息
workbook.getAllNames().forEach((name -> {
log.info("name:{}", name);
log.info("""
名字:{},备注:{},
所在sheet索引:{},所在sheet名称:{},
引用公式:{}
""", name.getNameName(), name.getComment(), name.getSheetIndex(), name.getSheetName(), name.getRefersToFormula());
}));
单元格风格信息
int styleLength = workbook.getNumCellStyles();
for (int i = 0; i < styleLength; i++) {
CellStyle cellStyle = workbook.getCellStyleAt(i);
log.info("""
索引:{},字体索引:{},
对齐方式:{},垂直对齐方式:{},
填充模式:{},前景色:{},背景色:{},
边框左:{},右边:{},上边:{},下边:{},
边框颜色左:{},右边:{},上边:{},下边:{},
痕入:{},旋转:{},
是否换行:{},是否收缩:{},是否锁定:{},是否隐藏:{},是否引用前缀:{},
日期格式:{},日期格式字符串:{}
""",
cellStyle.getIndex(), cellStyle.getFontIndex(),
cellStyle.getAlignment(), cellStyle.getVerticalAlignment(),
cellStyle.getFillPattern(), cellStyle.getFillForegroundColor(), cellStyle.getFillBackgroundColor(),
cellStyle.getBorderLeft(), cellStyle.getBorderRight(), cellStyle.getBorderTop(), cellStyle.getBorderBottom(),
cellStyle.getLeftBorderColor(), cellStyle.getRightBorderColor(), cellStyle.getTopBorderColor(), cellStyle.getBottomBorderColor(),
cellStyle.getIndention(), cellStyle.getRotation(),
cellStyle.getWrapText(), cellStyle.getShrinkToFit(), cellStyle.getLocked(), cellStyle.getHidden(), cellStyle.getQuotePrefixed(),
cellStyle.getDataFormat(),cellStyle.getDataFormatString()
);
}
图片信息
workbook.getAllPictures().forEach((pictureData -> {
log.info("pictureData:{}", pictureData);
log.info("""
图片类型:{},Mime类型:{},图片扩展名:{},
图片数据:{}
""",
pictureData.getPictureType(),
pictureData.getMimeType(),
pictureData.suggestFileExtension(),
pictureData.getData()
);
}));
Sheet
我们可以使用workBook.getSheetAt(int)或者workBook.getSheet(String)方法通过索引或者名称获取到一个sheet，然后就可以获取到以下属性和方法。
| 基础属性 | 内容 |
|---|---|
| LeftCol,TopRow | 值默认为0，表示可视区域列、行的索引从0开始 |
| PhysicalNumberOfRows | 实际拥有数据的行数 |
| DefaultColumnWidth,DefaultRowHeight | 默认列宽,行高 |
| NumMergedRegions | 合并单元格的数量 |
| ActiveCell | 激活的单元,Excel文件除了记录上一次打开的sheet索引外，还会记录每一个sheet最后停留的cell |
| ForceFormulaRecalculation | 是否重新记录本sheet中涉及的公式重新运算(workbook的这项属性范围更大） |
| Protect | 是否对工作表进行保护 |
| ScenarioProtect | 是否对本工作表的方案进行保护，不清楚什么是方案，见上文描述！ |
| Header、Footer | 页眉、页脚 |
基本属性省略了很多不影响对sheet本身工作机制理解的属性，如水平居中属性等样式以及是否展示水平线之类的布尔值等。 上面仅记录一些比较重要或者需要理解的属性。
| 集合属性相关的方法 | 内容 |
|---|---|
| getSheetConditionalFormatting() | 条件格式集合，不清楚的话，见上文描述! |
| getCellComments() | 单元格批注集合 |
| getHyperLinkList() | 超链接集合 |
| getDataValidations() | 数据验证 |
| getDrawingPatriarch() | 图形绘制集合，与workbook的pictures集合相关 |
| getMergedRegions() | 单元格合并区域集合 |
| getColumnBreaks(),getRowBreaks() | 设置分页的行/列信息集合 |
| getRepeatingRows(),getRepeatingColumns() | 用于打印的行/列集合，多页的每一页数据打印都包含此行/列 |
基本属性
log.info("""
TopRow:{},LeftCol:{},
physicalNumberOfRows:{},numMergedRegions:{},
defaultColumnWidth:{},defaultRowHeight:{},defaultRowHeightInPoints:{},
firstRowNum:{},lastRowNum:{},
rowSumsBelow:{},rowSumsRight:{},
verticallyCenter:{},horizontallyCenter:{},fitToPage:{},
autobreaks:{},displayGuts:{},
forceFormulaRecalculation:{},
protect:{},scenarioProtect:{},
isDisplayZeros:{},isDisplayFormulas:{},isDisplayGridlines:{},
isPrintGridlines:{},isPrintRowAndColumnHeadings:{},
isSelected:{},isDisplayRowColHeadings:{},
isRightToLeft:{}
""",
sheet.getTopRow(), sheet.getLeftCol(),
sheet.getPhysicalNumberOfRows(), sheet.getNumMergedRegions(),
sheet.getDefaultColumnWidth(), sheet.getDefaultRowHeight(), sheet.getDefaultRowHeightInPoints(),
sheet.getFirstRowNum(), sheet.getLastRowNum(),
sheet.getRowSumsBelow(), sheet.getRowSumsRight(),
sheet.getVerticallyCenter(), sheet.getHorizontallyCenter(), sheet.getFitToPage(),
sheet.getAutobreaks(), sheet.getDisplayGuts(),
sheet.getForceFormulaRecalculation(),
sheet.getProtect(), sheet.getScenarioProtect(),
sheet.isDisplayZeros(),sheet.isDisplayFormulas(),sheet.isDisplayGridlines(),
sheet.isPrintGridlines(),sheet.isPrintRowAndColumnHeadings(),
sheet.isSelected(),sheet.isDisplayRowColHeadings(),
sheet.isRightToLeft()
);
列分页符ColumnBreaks
for (int i = 0; i < sheet.getColumnBreaks().length; i++) {
int columnBreak = sheet.getColumnBreaks()[i];
log.info("columnBreak:{}", columnBreak);
}
单元格评论CellComments
sheet.getCellComments().forEach(((cellAddress, comment) -> {
log.info("""
cellAddress:{},
批注内容:{},
批注作者:{},{}
""",
cellAddress,
comment.getString(),
comment.getAuthor(), comment.getClientAnchor()
);
}));
超链接HyperLinkList
sheet.getHyperlinkList().forEach((hyperlink -> {
log.info("hyperlink:{}", hyperlink);
log.info("""
地址:{}, 地址类型:{},
标签:{},
起始行:{},结束行:{},
起始列:{},结束列:{}
""",
hyperlink.getAddress(), hyperlink.getType(),
hyperlink.getLabel(),
hyperlink.getFirstRow(), hyperlink.getLastRow(),
hyperlink.getFirstColumn(), hyperlink.getLastColumn()
);
}));
数据验证DataValidations
sheet.getDataValidations().forEach((dataValidation -> {
log.info("dataValidation:{}", dataValidation);
log.info("""
promptBoxTitle:{},promptBoxText:{},showPromptBox:{},
errorBoxTitle:{},errorBoxText:{},showErrorBox:{},errorStyle:{},
suppressDropDownArrow:{},emptyCellAllowed:{}
""",
dataValidation.getPromptBoxTitle(), dataValidation.getPromptBoxText(), dataValidation.getShowPromptBox(),
dataValidation.getErrorBoxTitle(), dataValidation.getErrorBoxText(), dataValidation.getShowErrorBox(), dataValidation.getErrorStyle(),
dataValidation.getSuppressDropDownArrow(), dataValidation.getEmptyCellAllowed()
);
CellRangeAddressList regions = dataValidation.getRegions();
regions.getGenericChildren().forEach((region -> {
log.info(
"""
起始行:{},结束行:{},
起始列:{},结束列:{},
单元格数:{},格式文本:{}
""",
region.getFirstRow(), region.getLastRow(),
region.getFirstColumn(), region.getLastColumn(),
region.getNumberOfCells(), region.formatAsString()
);
}));
DataValidationConstraint constraint = dataValidation.getValidationConstraint();
log.info("""
operator:{},validationType:{},
formula1:{},formula2:{},
explicitListValues:{}
""",
constraint.getOperator(), constraint.getValidationType(),
constraint.getFormula1(),constraint.getFormula2(),
constraint.getExplicitListValues()
);
}));
图片绘制DrawingPatriarch
Drawing<?> drawingPatriarch = sheet.getDrawingPatriarch();
drawingPatriarch.forEach((shape -> {
log.info("shape:{}", shape);
log.info("""
父:{},
锚点:{},
文本:{}
""",
shape.getParent(),
shape.getAnchor(),
shape.getShapeName()
);
}));
PrintSetup打印设置
PrintSetup printSetup = sheet.getPrintSetup();
log.info("printSetup:{}", printSetup);
log.info("""
水平分辨率:{},缩放比例:{},
高度:{},宽度:{},
页脚边距:{},页眉边距:{},
打印份数:{},纸张大小:{},
起始页:{},使用页:{},草稿:{},
横向:{},备注:{},
有效设置:{},无颜色:{},无方向:{},
从左到右:{}
""",
printSetup.getHResolution(),printSetup.getScale(),
printSetup.getFitHeight(),printSetup.getFitWidth(),
printSetup.getFooterMargin(),printSetup.getHeaderMargin(),
printSetup.getCopies(),
printSetup.getPaperSize(),printSetup.getPageStart(),
printSetup.getUsePage(),printSetup.getDraft(),printSetup.getLandscape(),printSetup.getNotes(),
printSetup.getValidSettings(),printSetup.getNoColor(), printSetup.getNoOrientation(),printSetup.getLeftToRight()
);
Cell
获取到一个Cell单元格：sheet.getRow(int)->row.getCell(int)
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for (int i = firstRowNum; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
log.info("第{}行===================", i);
int rowNum = row.getRowNum();
for (int j = 0; j <= rowNum; j++) {
Cell cell = row.getCell(j);
log.info("cell[{}]:{}", j, cell);
}
}
属性
- 确定是什么类型的值时就获取什么类型的值，不然就会报错！
log.info("""
类型:{},地址:{},批注:{},
""",
// 缓存类型:{},
// 公式:{},数组公式{}:
// 超链接:{},
// 字符串:{},富文本:{},
// 数字:{},布尔:{},错误:{},
// 日期:{},日期时间:{}
// """,
cell.getCellType(), cell.getAddress(),
cell.getCellComment()
// ,cell.getCachedFormulaResultType(),
// cell.getCellFormula(),
// cell.getArrayFormulaRange(),
// cell.getHyperlink(),
// cell.getStringCellValue(), cell.getRichStringCellValue(),
// cell.getNumericCellValue(), cell.getBooleanCellValue(), cell.getErrorCellValue(),
// cell.getDateCellValue(), cell.getLocalDateTimeCellValue()
);
POI- EasyExcel的底层框架
认识了上面POI的API,我们接下来学EasyExcel也会更加轻松，也能够更加灵活地去使用和拓展EasyExcel。
最后分享一下EasyExcel比较好的文章。
站内大佬@竹子爱熊猫 的个人主页 - 动态 - 掘金详细分析了EasyExcel的功能原理，一共有五篇文章，下面贴两篇。
(一)EasyExcel初相识：万字吃透EasyExcel核心API，从此报表处理不再喊难！POI是报表处理领域的知名框 - 掘金
(四)并发编程实战：多线程+EasyExcel，20ms内极速导入百万级大数据报表！什么？工作好些年了还不会用多线程？接 - 掘金
