这里的需求是，如果表格不存在，则新建表格，并填入数据，如果表格存在，那么就追加内容，并且支持单元格合并。
内容追加，需要分两种方式插入，第一种就是没有表格，需要生成表头，并且插入内容，第二种就是已经有了表格，这个表格作为模板并，不用设置表头，直接追加内容，这里需要生成一个新的表格，如果有必要，最后还要删除老的表格。
关于单元格合并，网上有这样的示例，使用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 {
@Autowired
private 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 {
@Autowired
private 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;
}
@Override
public 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) 也是可以的。
