当前位置：首页 > news >正文

easyexcel基于模板生成报表

news 2025/6/19 12:13:50

对比了一下 easyexcel，jxls, poi-tl 等工具，感觉各有千秋。

做个笔记，有需要的同学可以进行参考
github地址：https://github.com/alibaba/easyexcel

1、先定义模板

在这里插入图片描述

2、上代码

package com.luo.easyexcel.demos;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
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.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;public class BasicTest {public static void main(String[] args) {ExcelWriter excelWriter = EasyExcel.write("D:\\result.xlsx", User.class).withTemplate("D:\\template.xlsx").registerWriteHandler(new FillRowMergeStrategy()).autoCloseStream(false).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();Map<String, Object> params = new HashMap<>(8);params.put("spr", "张三");params.put("spsj", LocalDateTime.now());params.put("cjr", "李四");params.put("cjsj", LocalDateTime.now());excelWriter.fill(params, writeSheet);excelWriter.fill(getData(), fillConfig, writeSheet);// 完成写入excelWriter.finish();}private static class FillRowMergeStrategy implements CellWriteHandler {/** 需要进行单元格合并的列数组 **/private int mergeColumnIndex = 0;private int mergeRowIndex = 4;/*** 在每个单元格写入完成后执行，适合做样式、合并、校验等操作* @param writeSheetHolder 当前 sheet 的上下文信息（如当前行、sheet 对象等）* @param writeTableHolder 表格相关上下文（通常不常用）* @param cellDataList 当前单元格的数据集合（包含样式、值等）* @param cell* @param head 表头信息* @param relativeRowIndex 当前行在数据中的索引（从 0 开始）* @param isHead 是否是表头*/@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) {if (curColIndex == mergeColumnIndex) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);}}}/*** 当前单元格向上合并** @param writeSheetHolder* @param cell*            当前单元格* @param curRowIndex*            当前行* @param curColIndex*            当前列*/private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {Object curData = getCellValue(cell);Row preRow = cell.getSheet().getRow(curRowIndex - 1);if (preRow == null) {// 当获取不到上一行数据时，使用缓存sheet中数据preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);}Cell preCell = preRow.getCell(curColIndex);Object preData =  getCellValue(preCell);// 将当前单元格数据与上一个单元格数据比较boolean same = preData.equals(curData);if (same) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.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);}}}// 获取单元格值private Object getCellValue(Cell cell) {switch (cell.getCellType()) {case STRING:return cell.getStringCellValue();case NUMERIC:return cell.getNumericCellValue();case BOOLEAN:return cell.getBooleanCellValue();default:return null;}}}/*** 测试数据*/private static List<User> getData() {List<User> userList = new ArrayList<>();for (int i = 1; i <= 10; i++) {if (i > 5) {userList.add(new User("张三" + i,"男", "北京"+i, LocalDate.now(), "17745528756", "xxxxxxx", 18));}else if (i > 3){userList.add(new User("张三" + i,"男", "北京"+i, LocalDate.now(), "17745528756", "xxxxxxx", 19));}else {userList.add(new User("张三" + i,"男", "北京"+i, LocalDate.now(), "17745528756", "xxxxxxx", 20));}}return userList;}
}