easyexcel内容追加与单元格合并

文章列表

easyexcel内容追加与单元格合并

这里的需求是，如果表格不存在，则新建表格，并填入数据，如果表格存在，那么就追加内容，并且支持单元格合并。

内容追加，需要分两种方式插入，第一种就是没有表格，需要生成表头，并且插入内容，第二种就是已经有了表格，这个表格作为模板并，不用设置表头，直接追加内容，这里需要生成一个新的表格，如果有必要，最后还要删除老的表格。

关于单元格合并，网上有这样的示例，使用easyexcel来实现，我们就是需要定义一个CellWriteHandler的接口实现，在实现类中，覆盖afterCellDispose()方法，本例中就是CustomCellWriteHandler类。

这里为了验证我们的结果，我们利用springboot构建两个不同的请求，一个是创建excel，一个是追加excel。

主要代码如下：

依赖文件：

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId>
</dependency>
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel-core</artifactId><version>3.2.1</version>
</dependency>

控制器类：

import com.example.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;@RestController
@RequestMapping("/excel")
public class ExcelController {@Autowiredprivate ExcelService excelService;@GetMapping("/create")public ResponseEntity create() {return ResponseEntity.ok(excelService.create());}@GetMapping("/append")public ResponseEntity append() {return ResponseEntity.ok(excelService.append());}
}

服务类：

package com.example.service;import com.alibaba.excel.EasyExcel;
import com.example.config.CustomCellWriteHandler;
import com.example.dao.ExcelDao;
import com.example.model.ExcelData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.io.File;@Service
public class ExcelService {@Autowiredprivate ExcelDao excelDao;private String fileName = "e:\\\\test\\\\student.xlsx";private String tempName = "e:\\\\test\\\\temp.xlsx";private int mergeRowIndex = 0;private int[] mergeColumnIndexes = {0, 1, 2};public boolean create() {File out = new File(fileName);EasyExcel.write(out, ExcelData.class).sheet("student").registerWriteHandler(new CustomCellWriteHandler(mergeRowIndex,mergeColumnIndexes)).doWrite(excelDao.createData());return true;}public boolean append() {File out = new File(fileName);File temp = new File(tempName);if (out.exists()) {// 采用模板方式写入EasyExcel.write(out).needHead(false).withTemplate(out).file(temp).sheet("student").registerWriteHandler(new CustomCellWriteHandler(mergeRowIndex,mergeColumnIndexes)).doWrite(excelDao.appendData());// out.delete();// temp.renameTo(out);}return true;}}

数据接口模拟类：

package com.example.dao;import com.example.model.ExcelData;
import org.springframework.stereotype.Component;import java.util.ArrayList;
import java.util.List;@Component
public class ExcelDao {public List<ExcelData> createData() {List<ExcelData> list = new ArrayList<>();list.add(new ExcelData("罗芳",18, "female","语文",100));list.add(new ExcelData("罗芳",18, "female","数学",99));list.add(new ExcelData("王芳",17, "female","语文",80));list.add(new ExcelData("王芳",17, "female","数学",60));return list;}public List<ExcelData> appendData() {List<ExcelData> list = new ArrayList<>();list.add(new ExcelData("张雪琴",18, "female","语文",88));list.add(new ExcelData("张雪琴",18, "female","数学",75));list.add(new ExcelData("王勇",18, "male","语文",80));list.add(new ExcelData("王勇",18, "male","数学",92));return list;}
}

表格实体类：

package com.example.model;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;@Data
@AllArgsConstructor
public class ExcelData {@ExcelProperty("学生姓名")private String name;@ExcelProperty("年龄")private int age;@ExcelProperty("性别")private String gender;@ExcelProperty({"课程", "课程名称"})private String courseName;@ExcelProperty({"课程", "分数"})private double score;
}

表格单元格合并策略类：

package com.example.config;import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;public class CustomCellWriteHandler implements CellWriteHandler {private int mergeRowIndex;private int[] mergeColumnIndexes;public CustomCellWriteHandler(int mergeRowIndex, int[] mergeColumnIndexes) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndexes = mergeColumnIndexes;}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {int curRowIndex = cell.getRowIndex();int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndexes.length; i++) {if (curColIndex == mergeColumnIndexes[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();Row prevRow = cell.getSheet().getRow(curRowIndex - 1);if (prevRow != null) {Cell prevCell = prevRow.getCell(curColIndex);Object prevData = prevCell.getCellType() == CellType.STRING ? prevCell.getStringCellValue() : prevCell.getNumericCellValue();boolean isDataSame = curData.equals(prevData);if (isDataSame) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size(); i++) {CellRangeAddress cellRangeAddress = mergeRegions.get(i);if (cellRangeAddress.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddress.setLastRow(curRowIndex);sheet.addMergedRegionUnsafe(cellRangeAddress);isMerged = true;}}if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}}}

运行示例，分别访问 http://localhost:8080/excel/create 和 http://localhost:8080/excel/append 。

最后会生成两个文件，分别是e:\\test\\student.xlsx e:\\test\\temp.xlsx，内容如下所示：

内容是符合预期的，第一个只有两个学生信息，第二个表格因为是追加的，所以有四个学生信息。但是单元格合并，我们指定的是0，1，2三列，也就是姓名，年龄，性别需要合并，实际中他们确实合并了，但是似乎合并的超出了范围。我们希望合并的是同样学生的年龄和性别，而不是所有数值一样的年龄、性别。

解决办法就是在合并策略里面增加一个判断，如果合并行的列值（当前属性列age,gender）相同，而且第一列值（唯一属性name）也相同那么合并，而不是列的内容相同就合并。

变更过的合并策略主体逻辑：

private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();Row prevRow = cell.getSheet().getRow(curRowIndex - 1);Row curRow = cell.getSheet().getRow(curRowIndex);if (prevRow == null) {prevRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);}Cell prevFirstColCell = prevRow.getCell(0);Cell curFirstColCell = curRow.getCell(0);Object prevFirstColData = prevFirstColCell.getCellType() == CellType.STRING ? prevFirstColCell.getStringCellValue() : prevFirstColCell.getNumericCellValue();Object curFirstColData = curFirstColCell.getCellType() == CellType.STRING ? curFirstColCell.getStringCellValue() : curFirstColCell.getNumericCellValue();Cell prevCell = prevRow.getCell(curColIndex);Object prevData = prevCell.getCellType() == CellType.STRING ? prevCell.getStringCellValue() : prevCell.getNumericCellValue();boolean isDataSame = curData.equals(prevData) && curFirstColData.equals(prevFirstColData);if (isDataSame) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size(); i++) {CellRangeAddress cellRangeAddress = mergeRegions.get(i);if (cellRangeAddress.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddress.setLastRow(curRowIndex);sheet.addMergedRegionUnsafe(cellRangeAddress);isMerged = true;}}if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}

生成的表格：

单元格合并范围超出问题似乎解决了，完美。

有的代码里面，在获取上一行记录某列的时候，代码是这样的：

Cell prevCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);

在实际中，这里可能会报空指针异常，其实数据在缓存里面，我们需要这样来获取：

Row prevRow = cell.getSheet().getRow(curRowIndex - 1);
if (prevRow == null) {prevRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
}

或者，在后面的逻辑里面加上判断 if (prevRow != null) 也是可以的。

easyexcel内容追加与单元格合并

相关问题

公告