前言
使用spring boot 对excel 进行操作在平时项目中要经常使用。常见通过jxl和poi 的方式进行操作。但他们都存在一个严重的问题就是非常的耗内存。这里介绍一种 Easy Excel 工具来对excel进行操作。
一、Easy Excel是什么？
EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。easyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
二、使用EasyExcel 实现读操作
从excel 中读取数据，常用的场景就是读取excel的数据，将相应的数据保存到数据库中。需要实现一定的逻辑处理。
1、导入依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version> </dependency>
2、创建读取数据封装类
@Data public class User { @ExcelProperty(index = 0) private Integer id; @ExcelProperty(index = 1) private String name; @ExcelProperty(index = 2) private Integer age; }
比如我们要读取两列的数据，就写两个属性。@ExcelProperty(index = 0)来设置要读取的列,index=0表示读取第一列。
3、创建读取excel的监听类
监听器继承 AnalysisEventListener 类
@Slf4j public class UserExcelListener extends AnalysisEventListener<User> { /** * 解析excel文档的每一行 * @param user 参数user即是每行读取数据转换的User对象 * @param analysisContext */ @Override public void invoke(User user, AnalysisContext analysisContext){ log.info("excel数据行：{}",user.toString()); } /** * 整个文档解析完执行 * @param analysisContext */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { log.info("文档解析完毕"); } }
当解析每一条数据时都会调用invoke方法，当所有数据都解析完毕时最后会调用doAfterAllAnalysed方法。可以在监听类内的方法中将每次读取到的数据进行保存或者其他操作处理。
4、接口使用easyExcel读取excel文件调用监听器
/** * 上传excel文件并读取其中内容 * * @param file * @return */ @PostMapping("/upload") public String uploadExcel(MultipartFile file) { log.info("easyExcel上传文件：{}", file); try { InputStream inputStream = file.getInputStream(); EasyExcel.read(inputStream, User.class, new UserExcelListener()) .sheet() .doRead(); } catch (Exception e) { } return "表格文件上传成功"; }
三、使用EasyExcel 实现写操作
写操作有两种写法，一种是不创建对象的写入，另一种是根据对象写入。这里主要介绍创建对象写入
创建对象写入
1、创建excel对象类
@Data public class User { @ExcelProperty(index = 0) private Integer id; @ExcelProperty(index = 1) private String name; @ExcelProperty(index = 2) private Integer age; }
注意@ExcelProperty(“用户编号”) 会生成相应的列名为 用户编号，如果不设置，则会直接将字段名设置为excel的列名。
2、接口使用测试数据导出（常规导出不合并单元格）
/** * 输出导出excel */ @PostMapping("/export") public void export() { ArrayList<User> users = new ArrayList<>(); for (int i = 0; i < 10; i++) { User user = new User(); user.setId(i); user.setName("测试用户-" + i); user.setAge(20 + i); users.add(user); } log.info("导出数据结果集：{}", users); String fileName = "C:\\Users\\pytho\\Desktop\\fsdownload\\用户信息表.xlsx"; EasyExcel.write(fileName, User.class) .autoCloseStream(true) .sheet("sheet名称") .doWrite(users); }
3、接口测试导出（单列合并单元格）
/** * 输出导出excel */ @PostMapping("/export1") public void export1() { ArrayList<User> users = new ArrayList<>(); for (int i = 0; i < 10; i++) { User user = new User(); user.setId(i); if (i == 3 || i == 4 || i == 5) { user.setName("测试用户-3"); } else { user.setName("测试用户-" + i); } user.setAge(20 + i); users.add(user); } log.info("导出数据结果集：{}", users); String fileName = "C:\\Users\\pytho\\Desktop\\fsdownload\\(单列相同内容合并单元格)用户信息表.xlsx"; EasyExcel.write(fileName, User.class) .registerWriteHandler(new SimpleExcelMergeUtil()) .autoCloseStream(true) .sheet("sheet名称") .doWrite(users); }
如果要对导出的excel进行处理，就需要自定义处理器类进行处理
自定义easyExcel处理器（单列合并：根据用户id相同的列进行合并单元格）：
/** * @version 1.0 * @Package: com.stech.bms.buss.utils * @ClassName: ExcelMergeUtil * @Author: sgq * @Date: 2023/7/28 13:29 * @Description: 仅处理单列数据相同合并单元格 */ public class SimpleExcelMergeUtil implements CellWriteHandler { public SimpleExcelMergeUtil() { } /** * 创建每个单元格之前执行 * * @param writeSheetHolder * @param writeTableHolder * @param row * @param head * @param columnIndex * @param relativeRowIndex * @param isHead */ @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } /** * 创建每个单元格之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } /** * 每个单元格数据内容渲染之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cellData * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } /** * 每个单元格完全创建完之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cellDataList * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 当前行 int curRowIndex = cell.getRowIndex(); // 当前列 int curColIndex = cell.getColumnIndex(); if (!isHead) { if (curRowIndex > 1 && curColIndex == 1) { // 从第二行数据行开始,获取当前行第二列数据 Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); // 获取上一行第二列数据 Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); if (curData.equals(preData)) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergedRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergedRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergedRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } } } }
4、接口测试导出（通用合并单元格）
/** * 输出导出excel */ @PostMapping("/export2") public void export2() { ArrayList<User> users = new ArrayList<>(); for (int i = 0; i < 10; i++) { User user = new User(); user.setId(i); if (i == 3 || i == 4 || i == 5) { user.setName("测试用户-3"); } else { user.setName("测试用户-" + i); } user.setAge(20 + i); users.add(user); } log.info("导出数据结果集：{}", users); // 从第几行开始合并 int mergeStartRowIndex = 5; // 需要合并哪些列 int[] mergeColumns = {1}; String fileName = "C:\\Users\\pytho\\Desktop\\fsdownload\\(单列相同内容合并单元格-通用版)用户信息表.xlsx"; EasyExcel.write(fileName, User.class) .registerWriteHandler(new SimpleCommonExcelMergeUtil(mergeStartRowIndex,mergeColumns)) .autoCloseStream(true) .sheet("sheet名称") .doWrite(users); }
excel处理器类：
/** * @version 1.0 * @Package: com.stech.bms.buss.utils * @ClassName: ExcelMergeUtil * @Author: sgq * @Date: 2023/7/28 13:29 * @Description: 仅处理单列数据相同合并单元格 */ public class SimpleCommonExcelMergeUtil implements CellWriteHandler { private int mergeStartRowIndex; private int[] mergeColumns; private List<Integer> mergeColumnList; public SimpleCommonExcelMergeUtil() { } public SimpleCommonExcelMergeUtil(int mergeStartRowIndex, int[] mergeColumns) { this.mergeStartRowIndex = mergeStartRowIndex; this.mergeColumns = mergeColumns; mergeColumnList = new ArrayList<>(); for (int i : mergeColumns) { mergeColumnList.add(i); } } /** * 创建每个单元格之前执行 * * @param writeSheetHolder * @param writeTableHolder * @param row * @param head * @param columnIndex * @param relativeRowIndex * @param isHead */ @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } /** * 创建每个单元格之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } /** * 每个单元格数据内容渲染之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cellData * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } /** * 每个单元格完全创建完之后执行 * * @param writeSheetHolder * @param writeTableHolder * @param cellDataList * @param cell * @param head * @param relativeRowIndex * @param isHead */ @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 当前行 int curRowIndex = cell.getRowIndex(); // 当前列 int curColIndex = cell.getColumnIndex(); if (!isHead) { if (curRowIndex > mergeStartRowIndex && mergeColumnList.contains(curColIndex)) { // 从第二行数据行开始,获取当前行第二列数据 Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); // 获取上一行第二列数据 Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); if (curData.equals(preData)) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergedRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergedRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergedRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } } } }
这只是简单的合并单元格例子，抛砖引玉的作用。工作中可能会遇到很多情况：合并单元格后第一列序列号也需要根据其他列进行合并单元格且序列号还必须保持连续，根据部分列合并单元格，隔行合并单元格等等情况，这就需要开发者对easyExcel的处理器类里面的api比较了解才能完成。遇到的问题也可以留言，看到也会尝试一起处理解决。
