EasyExcel
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:/alibaba/easyexcel
官方文档：/easyexcel/doc/easyexcel
1. 依赖引入
<!-- easyexcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency><!-- lombok --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.22</version><scope>provided</scope></dependency><!-- hutool工具类 --><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.7.18</version></dependency><!-- knife4j --><dependency><groupId>com.github.xiaoymin</groupId><artifactId>knife4j-spring-boot-starter</artifactId><version>2.0.8</version></dependency>
官方单sheet填充示例
简单填充
/easyexcel/doc/fill#437f2d89
填充列表
/easyexcel/doc/fill#b68864a9
复杂填充
/easyexcel/doc/fill#0b212861
多列表组合填充
/easyexcel/doc/fill#EBYsX
存放Excle模板文件
我这里将excle模板文件放在resources/templates路径下
使用模板进行填充
多sheet填充
效果：
日期单元格合并
设置sheet注册监听
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();//设置强制计算公式：不然公式会以字符串的形式显示在excel中workbook.setForceFormulaRecalculation(true);// 这里writerSheet(3)说明设置的是第四个sheet工作表，注册监听器WriteSheet sumExportSheet = EasyExcel.writerSheet(3).registerWriteHandler(new CustomDateWriteHandler()).build();
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 lombok.extern.slf4j.Slf4j;import net.microcental.dcdrdatareported.constants.ReportConstant;import mons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Cell;import org.springframework.context.annotation.Configuration;import java.util.List;/*** easyExcel-日期单元格监听合并** @author yunnuo* @since -12-28*/@Slf4j@Configurationpublic class CustomDateWriteHandler implements CellWriteHandler {// 这里方法表示单元格填充完成后@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// 日期单元格合并final int dateColumnIndex = 0;if (Objects.equals(cell.getColumnIndex(), dateColumnIndex)) {CustomCellWriteHandler.mergeWithPrevRow(writeSheetHolder, cell, cell.getRowIndex(), cell.getColumnIndex());}CellWriteHandler.super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead);}}
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;import java.util.Optional;/*** easyExcel-单元格向上合并监听** @author yunnuo* @since -12-27*/@Slf4jpublic class CustomCellWriteHandler {/*** 当前单元格向上合并（合并日期）** @param writeSheetHolder sheet* @param cell 当前单元格* @param curRowIndex当前行* @param curColIndex当前列* @author yunnuo* @date -12-27 14:43*/public static void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {// 获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并Object curData = cell.getStringCellValue();Optional.ofNullable(cell.getSheet()).flatMap(sheetOne -> Optional.ofNullable(sheetOne.getRow(curRowIndex - 1))).ifPresent(row -> {Cell preCell = row.getCell(curColIndex);Object preData = preCell.getStringCellValue();// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行if (curData.equals(preData)) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAdder = mergeRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAdder.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAdder.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAdder);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);sheet.addMergedRegion(cellRangeAddress);}}});}}
设置选择不同sheet区域公式填充
效果
汇总公式填充代码：
/*** 获取排放汇总导出列表对象<p>* <p>* 汇总逻辑如下：<p>* 1. 遍历赋值日期 - sumDto.getAllDate() <p>* 2. 对排放汇总导出dto进行赋值 - new DischargeSumExportDto().setDate(date)<p>* 1.2. 判断报表数据中的日期和sumDto中的日期<p>* 1.2.1. 获取相同日期的报表数据的size，进行SUM函数取区域<p>* 2. 返回sumExportDto<p>** @param sumDto{@link DischargeSumDto} 排放汇总数据dto* @param workbook {@link Workbook} 工作簿* @param reportTime {@link List<DateTimeExportDto>} 导出报表日期时间列表数据* @return {@link List<DischargeSumExportDto>} 获取排放汇总导出列表对象* @author wxt.hexingtao* @date -12-28 16:31*/private List<DischargeSumExportDto> getDischargeSumExportDtoList(DischargeSumDto sumDto, Workbook workbook, List<DateTimeExportDto> reportTime) {AtomicInteger sumIndex = new AtomicInteger(4);// 遍历赋值日期return sumDto.getAllDate().stream().filter(Objects::nonNull).map(date -> {DischargeSumExportDto sumExportDto = new DischargeSumExportDto().setDate(date);// 判断是否有数据进行汇总计算Optional.of(reportTime.stream().filter(reportDate -> Objects.equals(reportDate.getDate(), date)).collect(Collectors.toList())).ifPresent(reportDateList -> {int size = reportDateList.size();if (size > 0) {// workbook.getSheetAt(2).getSheetName()获取第三个sheet表名String windSystemSum = String.format("SUM('%s'!AE%d:AE%d)", workbook.getSheetAt(2).getSheetName(), sumIndex.get(), sumIndex.get() + size - 1);String pumpSystemSum = String.format("SUM('%s'!X%d:X%d)", workbook.getSheetAt(3).getSheetName(), sumIndex.get(), sumIndex.get() + size - 1);String gasRecycleSum = String.format("SUM('%s'!X%d:X%d)", workbook.getSheetAt(4).getSheetName(), sumIndex.get(), sumIndex.get() + size - 1);String gasVentSum = String.format("SUM('%s'!Y%d:Y%d)", workbook.getSheetAt(4).getSheetName(), sumIndex.get(), sumIndex.get() + size - 1);String gasNetAmountSum = String.format("SUM('%s'!Z%d:Z%d)", workbook.getSheetAt(4).getSheetName(), sumIndex.get(), sumIndex.get() + size - 1);sumExportDto.setWindSystemSum(windSystemSum).setPumpSystemSum(pumpSystemSum).setGasRecycleSum(gasRecycleSum).setGasVentSum(gasVentSum).setGasNetAmountSum(gasNetAmountSum);sumIndex.addAndGet(size);}});return sumExportDto;}).collect(Collectors.toList());}
但是这个如果这样的填充的话Excel会直接将这个公式识别为字符串，所以还需要注册一个监听器来完成公式填充
监听器代码：
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 lombok.extern.slf4j.Slf4j;import net.microcental.dcdrdatareported.constants.ReportConstant;import mons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Cell;import org.springframework.context.annotation.Configuration;import java.util.List;/*** easyExcel-汇总表监听** @author yunnuo* @since -12-28*/@Slf4j@Configurationpublic class CustomSumWriteHandler implements CellWriteHandler {@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {// 定义最大求和公式列final int sumMaxColIndex = 6;if (cell.getColumnIndex() > 0 && StringUtils.isNotBlank(cell.getStringCellValue())) {log.info("汇总表-完成公式填写：{}:{},值：{}", cell.getRowIndex() + 1, cell.getColumnIndex() + 1, cell.getStringCellValue());// cell.setCellFormula(cell.getStringCellValue()); 设置该单元格内容为公式格式，注意cell.getStringCellValue()中不能有=号，不然会报错cell.setCellFormula(cell.getStringCellValue());}}// 这里对汇总表中的一行数据进行公式填充 可以不要if (cell.getColumnIndex() == sumMaxColIndex&& StringUtils.isBlank(cell.getStringCellValue())&& StringUtils.isNotBlank(cell.getSheet().getRow(cell.getRowIndex()).getCell(cell.getColumnIndex() - 1).getStringCellValue())) {int rowIndex = cell.getRowIndex() + 1;// "总排放量" = B26 + C26 - F26cell.setCellFormula(String.format("B%d+C%d-F%d", rowIndex, rowIndex, rowIndex));}CellWriteHandler.super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead);}}
然后就可以进行生成报表了。
EasyExcel：利用模板进行填充字段 生成公式处理 监听单元格填充后触发事件 相同日期单元格合并
