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