easyexcel导出中自定义合并单元格,通过重写AbstractRowWriteHandler
官方给出的合并单元格 只给出固定规律的单元格合并,当然官方也指出可以自定义合并单元格的策略,我们跟进LoopMergeStrategy 这个合并策略的实例类,发现里面继承了AbstractRowWriteHandler,官方示例代码如下
/*** 合并单元格* <p>* 1. 创建excel对应的实体对象 参照{@link DemoData} {@link DemoMergeData}* <p>* 2. 创建一个merge策略 并注册* <p>* 3. 直接写即可** @since 2.2.0-beta1*/@Testpublic void mergeWrite() {// 方法1 注解String fileName = TestFileUtil.getPath() + "mergeWrite" + System.currentTimeMillis() + ".xlsx";// 在DemoStyleData里面加上ContentLoopMerge注解// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭EasyExcel.write(fileName, DemoMergeData.class).sheet("模板").doWrite(data());// 方法2 自定义合并单元格策略fileName = TestFileUtil.getPath() + "mergeWrite" + System.currentTimeMillis() + ".xlsx";// 每隔2行会合并 把eachColumn 设置成 3 也就是我们数据的长度，所以就第一列会合并。//当然其他合并策略也可以自己写LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭EasyExcel.write(fileName, DemoData.class).registerWriteHandler(loopMergeStrategy).sheet("模板").doWrite(data());}LoopMergeStrategy 的具体实现
/*** The regions of the loop merge** @author Jiaju Zhuang*/
public class LoopMergeStrategy extends AbstractRowWriteHandler {/*** Each row*/private int eachRow;/*** Extend column*/private int columnExtend;/*** The number of the current column*/private int columnIndex;public LoopMergeStrategy(int eachRow, int columnIndex) {this(eachRow, 1, columnIndex);}public LoopMergeStrategy(int eachRow, int columnExtend, int columnIndex) {if (eachRow < 1) {throw new IllegalArgumentException("EachRows must be greater than 1");}if (columnExtend < 1) {throw new IllegalArgumentException("ColumnExtend must be greater than 1");}if (columnExtend == 1 && eachRow == 1) {throw new IllegalArgumentException("ColumnExtend or eachRows must be greater than 1");}if (columnIndex < 0) {throw new IllegalArgumentException("ColumnIndex must be greater than 0");}this.eachRow = eachRow;this.columnExtend = columnExtend;this.columnIndex = columnIndex;}public LoopMergeStrategy(LoopMergeProperty loopMergeProperty, Integer columnIndex) {this(loopMergeProperty.getEachRow(), loopMergeProperty.getColumnExtend(), columnIndex);}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,Integer relativeRowIndex, Boolean isHead) {if (isHead) {return;}if (relativeRowIndex % eachRow == 0) {CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum(), row.getRowNum() + eachRow - 1,columnIndex, columnIndex + columnExtend - 1);writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);}}}我们根据官方给出的AbstractRowWriteHandler行处理类 重写:
1.引入jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
2.声明需要合并单元格的定位实例
/*** easyExcel 单元格范围设定*/
@Data
@AllArgsConstructor
public class ExcelCellRange{/*** 起始行*/private int firstRow;/*** 终止行*/private int lastRow;/*** 起始列*/private int firstCol;/*** 终止列*/private int lastCol;}
3.重写自定义合并策略类
/*** @Description:EasyExcel自定义合并单元格策略* @Date: 2023/4/14 16:11*/
public class EasyExcelCustomMergeStrategy extends AbstractRowWriteHandler {/*** 单元格范围*/private List<ExcelCellRange> cellRanges;public EasyExcelCustomMergeStrategy(List<ExcelCellRange> cellRanges) {this.cellRanges = cellRanges;}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {if (!isHead) {for(ExcelCellRange cellRange :cellRanges){if (relativeRowIndex % cellRange.getFirstRow() == 0) {CellRangeAddress cellRangeAddress = new CellRangeAddress(cellRange.getFirstRow(), cellRange.getLastRow(), cellRange.getFirstCol(), cellRange.getLastCol());writeSheetHolder.getSheet().addMergedRegionUnsafe(cellRangeAddress);}}}}}
以上封装的实例 ,仅供参考,大家可以自行完善参数校验等逻辑
4.使用案例
// 定位合并数据 int rowIndex = 1; // 从第二行开始，因为第一行是表头 //存放合并单元格位置 List<ExcelCellRange> cellRanges = Lists.newArrayListWithCapacity(5);cellRanges.add(new ExcelCellRange(0, 1, 0, 0));cellRanges.add(new ExcelCellRange(0, 1, 1, 1));cellRanges.add(new ExcelCellRange(0, 1, 2, 2));//合并策略 EasyExcelCustomMergeStrategy mergeStrategy = new EasyExcelCustomMergeStrategy(cellRanges); // 创建工作簿并指定写入的Excel文件 ExcelWriter excelWriter = EasyExcel.write(os).head(TtRoomCheckInStateRes.class).registerWriteHandler(mergeStrategy).build(); // 创建一个只包含一行的Excel表格，用于合并单元格及为表格添加表头 WriteSheet writeSheet = EasyExcel.writerSheet(0, "房间入住情况").build(); //信息写入 excelWriter.write(roomRecordList, writeSheet); //关闭流 excelWriter.finish();
5.导出效果图
