java操作excel文件——POI
简述
在开发者经常会涉及和excel的交互，如将数据库的数据导出到内存中，如将excel的数据导入到内存中。
常用的方式有两种——poi和java excel，其中常用的是poi
POI的全称是Poor Obfuscation Implementation，中文是可怜的模糊实现（笑），POI是用Java编写的免费开源的跨平台的 Java API，可以用java操作excel文件
demo
https://github.com/Layton-sy/POI_practice
依赖
maven
<!-- 基本依赖，仅操作 xls 格式只需引入此依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- 使用 xlsx 格式需要额外引入此依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
gradle
implementation 'org.apache.poi:poi:5.2.1' //基本依赖，仅操作 xls 格式只需引入此依赖 implementation 'org.apache.poi:poi-ooxml:5.2.2' //使用 xlsx 格式需要额外引入此依赖
POI开发中常用的类和方法
POI中对不同的文件类型对应不同的类，以下是不同文件对应的类前缀
| HSSF | Microsoft Excel XLS格式 |
| XSSF | Microsoft Excel OOXML XLSX格式 |
| HWPF | Microsoft Word DOC97格式 |
| XWPF | Microsoft Word DOC2003格式 |
| HSLF | Microsoft PowerPoint格式 |
| HDGF | Microsoft Visio格式 |
| HPBF | Microsoft Publisher格式 |
| HSMF | Microsoft Outlook格式 |
经常使用的是HSSF和XSSF，对应xls和xlsx格式，这里我们选XSSF举例
以下是我们常用的类，他们的关系是
一个Excel文件对应于一个workbook(XSSFWorkbook)，
一个workbook可以有多个sheet（XSSFSheet）组成，
一个sheet是由多个row（XSSFRow）组成，
一个row是由多个cell（XSSFCell）组成
XSSFWorkbook
XSSFWorkbook对应excel的工作簿，代表整个文档，常用方法：
| HSSFWorkbook() | 创建一个工作簿 |
| HSSFWorkbook(InputStream inputStream) | 创建一个关联输入流的工作簿，可以将一个excel文件封装成工作簿 |
| createSheet(String sheetname) | 创建一个新的Sheet |
| getSheet(String sheetName) | 通过名称获取Sheet |
| getSheetAt(int index) | 通过索引获取Sheet，索引从0开始 |
| createCellStyle() | 创建单元格样式 |
| getNumberOfSheets() | 获取sheet的个数 |
| setActiveSheet(int index) | 设置默认选中的工作表 |
| write() | 写入文件(通过文件导入) |
| write(File newFile) | 写入文件(新建工作簿) |
XSSFSheet
HSSFSheet对应的是工作表，常用方法：
| createRow(int rownum) | 创建新行，需要指定行号，行号从0开始 |
| getRow(int index) | 根据索引获取指定的行 |
| addMergedRegion(CellRangeAddress region) | 合并单元格 |
| CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) | 单元格范围, 用于合并单元格，需要指定要合并的首行、最后一行、首列、最后一列 |
| autoSizeColumn(int column) | 自动调整列的宽度来适应内容 |
| getLastRowNum() | 获取最后的行的索引，没有行或者只有一行的时候返回0 |
XSSFRow
XSSFRow对应的是表中的一行，常用方法：
| createCell(int column) | 创建新的单元格 |
| setCell(shot index) | 设置单元格 |
| getCell(shot index) | 根据索引拿到单元格 |
| getCell(CellReference.convertColStringToIndex(“A”)) | 根据列名英文字母获取 |
| setRowStyle(HSSFCellStyle style) | 设置行样式 |
| short getLastCellNum() | 获取最后的单元格号，如果单元格有第一个开始算，lastCellNum就是列的个数 |
| setHeightInPoints(float height) | 设置行的高度 |
XSSFCell
XSSFCell对应的是一个单元格，常用方法：
| setCellValue(String value) | 设置单元格的值 |
| setCellType() | 设置单元格类型，如 字符串、数字、布尔等 |
| setCellStyle() | 设置单元格样式 |
| getStringCellValue() | 获取单元格中的字符串值 |
| setCellStyle(HSSFCellStyle style) | 设置单元格样式，例如字体、加粗、格式化 |
| setCellFormula(String formula) | 设置计算公式，计算的结果作为单元格的值 |
XSSFCellStyle
XSSFCellStyle对应的是一个单元格的样式，常用方法：
| setFont(Font font) | 为单元格设置字体样式 |
| setAlignment(HorizontalAlignment align) | 水平对齐 |
| setVerticalAlignment(VerticalAlignment align) | 垂直对齐 |
| setFillBackgroundColor(short bg) | 设置背景色 |
计算excel公式的值
使用XSSFFormulaEvaluator.evaluateAllFormulaCells可以遍历每一个单元格，计算单元格公式的值
XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
例如现在C1的单元格是一个公式，值为A1+B1
此时我们向A1和B1写入新的值
FileInputStream fileInputStream = new FileInputStream("路径"); Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); Cell A1 = row.getCell(0); A1.setCellValue(20); Cell B1 = row.getCell(1); B1.setCellValue(30); workbook.write(new FileOutputStream("路径"));
我们可以发现A1和B1的值被改成功了，但是C1的公式没有自动刷新
此时我们在代码加上XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook)
FileInputStream fileInputStream = new FileInputStream("路径"); Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); Cell A1 = row.getCell(0); A1.setCellValue(20); Cell B1 = row.getCell(1); B1.setCellValue(30); XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);//计算单元格的值 workbook.write(new FileOutputStream("路径"));
可以发现在改动A1和B1后，C1单元格的值也被更新了
示例
写入Excel
最简单地在excel里第一个表的第一行第一列加上一个自定义的值
Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet("laytonSheet1"); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("cell value"); FileOutputStream fileOutputStream = new FileOutputStream("文件路径"); workbook.write(fileOutputStream); fileOutputStream.close();
可以看到输出的文件sheet名被指定成我们想要的值，第一行第一列单元格也有我们的值
读取Excel
通过FileInputStream可以将文档封装成Workbook，然后读表、行、单元格，即可拿到你想要的单元格的值
FileInputStream fileInputStream = new FileInputStream("文件路径"); Workbook workbook = new XSSFWorkbook(fileInputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); Cell cell = row.getCell(0); String value = cell.getStringCellValue(); System.out.println(value);
批量处理单元格
待续
References
https://juejin.cn/post/6844904080704290824
https://juejin.cn/post/7039626565369462792#heading-12
https://blog.csdn.net/vbirdbest/article/details/72870714
https://www.cnblogs.com/LiZhiW/p/4313789.html
