【开发指南】Spring Cloud集成POI完成Excel读写操作
POI简介
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft office格式档案读和写的功能，其中包括Excel，Word，PowerPoint等。
官方网站：poi.apache.org/
POI前置知识
1. 坐标
Excel中每一个单元格都是有一个坐标的，起始坐标为（0，0），也就是说起始行为第0行，起始列为第0列。
Excel中每一个Sheet也是有下标的，从0开始。
2. 数据类型
POI中定义了6种单元格数据类型，每一个数据类型使用一个数字常量来表示：
- CELL_TYPE_NUMERIC：值是0，数字类型和日期类型（int，float，Date...）
- CELL_TYPE_STRING：值是1，字符串类型（String）
- CELL_TYPE_FORMULA：值是2，计算公式类型
- CELL_TYPE_BLANK：值是3，空类型，表示单元格里什么都没有（null）
- CELL_TYPE_BOOLEAN：值是4，布尔类型（boolean）
- CELL_TYPE_ERROR：值是5，类型错误
存储在Excel中最常见的莫过于字符数据和数值数据了：
-
字符数据类似于"abc"、"ab123"等，POI默认以CELL_TYPE_STRING类型存储，通过POI转成Java中的类型是String。
-
数值和日期数据类似于"123"、"2021/12/8"（将日期使用特定算法计算成数字）等，POI默认以CELL_TYPE_NUMERIC类型存储，通过POI转成Java中的类型是double。
如果Excel需要存储类似于手机号这样的数据，默认是CELL_TYPE_NUMERIC类型，但是可以自行修改类型为CELL_TYPE_STRING。
-
布尔数据类似于"TRUE"、"true"，POI默认以CELL_TYPE_BOOLEAN类型存储，通过POI转成Java中的类型是boolean。
3. 文件类型
Excel经历了两次大变革，就是在Excel 2003和Excel 2007。
在Excel 2003之前，只支持后缀为 .xls 的文件，而Excel 2007之后支持了后缀为 .xls 和 .xlsx 两种后缀的文件。
因此POI针对 .xls 和 .xlsx 两种文件分别提供了两套独立的读写接口，分别是HSSF和XSSF。
4. 前置工作
在xml文件中导入依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
复制代码Excel基础读写
1. HSSF
1.1 写操作
@Test
public void testHSSFWrite() throws IOException {
// 创建文件
Workbook workbook = new HSSFWorkbook();
// 创建Sheet
Sheet sheet = workbook.createSheet("sheet1");
// 创建第0行
Row row = sheet.createRow(0);
// 在第0行上创建第0个单元格
Cell cell = row.createCell(0);
// 在（0，0）处写入内容
cell.setCellValue("Hello POI");
// 写入
FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\hssf-write.xls");
workbook.write(outputStream);
// 关闭输出流
outputStream.close();
}
复制代码1.2 读操作
@Test
public void testHSSFRead() throws IOException {
FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\hssf-read.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
// 读出（0，0）处的字符内容
String content = cell.getStringCellValue();
System.out.println(content);
inputStream.close();
}
复制代码2. XSSF
2.1 写操作
@Test
public void testXSSFWrite() throws IOException {
// 创建工作簿
Workbook workbook = new XSSFWorkbook();
// 创建Sheet
Sheet sheet = workbook.createSheet("sheet1");
// 创建第0行
Row row = sheet.createRow(0);
// 在第0行上创建第0个单元格
Cell cell = row.createCell(0);
// 在（0，0）处写入内容
cell.setCellValue("Hello POI");
// 写入
FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\xssf-write.xlsx");
workbook.write(outputStream);
// 关闭输出流
outputStream.close();
}
复制代码2.2 读操作
@Test
public void testXSSFRead() throws IOException {
FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\xssf-read.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
// 读出（0，0）处的字符内容
String content = cell.getStringCellValue();
System.out.println(content);
inputStream.close();
}
复制代码Excel大数据读写
使用HSSF一次最多只能读写65535行记录（列不限），如果超过65535行，则无法读写。
使用XSSF一次最多能读写1048576行和16384列记录，理论上可以读写大数据，但是速度会非常慢，时间不可控，还可能会造成内存溢出。原因是只要没有读写结束，数据就会一直存储在内存中，无论该数据是否已经持久化，这样以来如果是大数据场景，就会造成内存溢出。
因此POI针对大数据提供了一套专门的读写接口，SXSSF，SXSSF只支持后缀为 .xlsx 文件。
1. 读操作
@Test
public void testHSSFWrite() throws IOException {
// 创建工作簿
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
// 创建Sheet
Sheet sheet = sxssfWorkbook.createSheet("sheet1");
// 创建第0行
Row row = sheet.createRow(0);
// 在第0行上创建第0个单元格
Cell cell = row.createCell(0);
// 在（0，0）处写入内容
cell.setCellValue("Hello POI");
// 写入
FileOutputStream outputStream = new FileOutputStream("G:\\file\\poi\\sxssf-write.xlsx");
sxssfWorkbook.write(outputStream);
// 清除内存中临时文件
sxssfWorkbook.dispose();
// 关闭输出流
outputStream.close();
}
复制代码2. 写操作
@Test
public void testSXSSFRead() throws IOException {
FileInputStream inputStream = new FileInputStream("G:\\file\\poi\\sxssf-read.xlsx");
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
// 内存窗口大小为1000，表示能从Sheet窗口最多看到1000跳新创建的数据
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, 1000);
Sheet sheet = xssfWorkbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
// 读出（0，0）处的字符内容
String content = cell.getStringCellValue();
System.out.println(content);
inputStream.close();
}
复制代码POI实战
将文件名为student-grade.xlsx的Excel表中的数据全部读入到程序中。
-
xml文件中引入POI依赖
<!-- POI --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency> 复制代码 -
自定义单元类型异常CellTypeException
-
在ResultCode中建立单元类型异常枚举对象
-
将单元类型异常配置进全局异常
-
创建存储读入数据的对象
@Data public class Student { private String university; private String studentId; private String name; private Integer score; private Integer credit; private Date gmtCreate; } 复制代码 -
创建POIUtils
/** * POI工具类 * @author admin */ public class POIUtils { /** * 获取单元格的值 * @param cell 单元格 * @return 单元格的值 */ public static Object getCellValue(XSSFCell cell) { Object value = null; if (cell != null) { // 获取cell类型 int type = cell.getCellType(); // Numeric类型 if (type == XSSFCell.CELL_TYPE_NUMERIC) { // 判断是日期类型还是数值类型 if (HSSFDateUtil.isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } } // String类型 else if (type == XSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } // Boolean类型 else if (type == XSSFCell.CELL_TYPE_BOOLEAN) { value = cell.getBooleanCellValue(); } // Error类型 else if (type == XSSFCell.CELL_TYPE_ERROR) { throw new CellTypeException(ResultCode.CELL_TYPE_ERROR); } } return value; } /** * CELL_TYPE_STRING ——> String * @param cell 单元 * @return 数据 */ public static String getStringValue(XSSFCell cell) { return (String) getCellValue(cell); } /** * CELL_TYPE_NUMERIC（数字，科学计数法） ——> String（正常） * @param cell 单元 * @return 数据 */ public static String getDouble2StringValue(XSSFCell cell) { // 将科学计数法表示的double数据四舍五入成整形数据 BigDecimal bigDecimal = BigDecimal.valueOf((Double) getCellValue(cell)).setScale(0, BigDecimal.ROUND_DOWN); return bigDecimal.toString(); } /** * CELL_TYPE_NUMERIC（数字） ——> Integer * @param cell 单元 * @return 数据 */ public static Integer getDouble2IntegerValue(XSSFCell cell) { // 将double数据四舍五入成整形数据 return (int) Math.round((double) getCellValue(cell)); } /** * CELL_TYPE_NUMERIC（日期） ——> Date * @param cell 单元 * @return 数据 */ public static Date getDateValue(XSSFCell cell) { return (Date) getCellValue(cell); } /** * 填充Student属性 * @param columnIndex 单元列下标 * @param cell 单元 * @param student Student对象 */ public static void setStudentProperty(int columnIndex, XSSFCell cell, Student student) { if (columnIndex == 0) { student.setUniversity(getStringValue(cell)); } else if (columnIndex == 1) { student.setStudentId(getDouble2StringValue(cell)); } else if (columnIndex == 2) { student.setName(getStringValue(cell)); } else if (columnIndex == 3) { student.setScore(getDouble2IntegerValue(cell)); } else if (columnIndex == 4) { student.setCredit(getDouble2IntegerValue(cell)); } else { student.setGmtCreate(getDateValue(cell)); } } } 复制代码工具类中除了第一个方法固定不变外，其他方法都需要视具体要导入的数据而定。
比如现在需要导入
student-grade.xlsx中的数据，在其每一行数据中，学校、姓名字段数据使用getStringValue方法接收，学号字段数据使用getDouble2StringValue方法接收，成绩、学分字段数据使用getDouble2IntegerValue方法接收，上传日期使用getDateValue方法接收，接收到数据之后需要使用setStudentProperty方法装配到PO中来持久化到数据库。 -
创建读操作方法
@Test public void read() throws IOException { FileInputStream inputStream = new FileInputStream("G:\\file\\student-grade.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = workbook.getSheetAt(0); // 获取行数 int rowCount = sheet.getPhysicalNumberOfRows(); for (int i = 1; i < rowCount; i ++) { XSSFRow row = sheet.getRow(i); Student student = new Student(); // 获取列数 int cellCount = row.getPhysicalNumberOfCells(); for (int j = 0; j < cellCount; j ++) { XSSFCell cell = row.getCell(j); // 装载数据 POIUtils.setStudentProperty(j, cell, student); } // 将student持久化到数据库 } inputStream.close(); }
查看9道真题和解析
投递字节跳动等公司10个岗位