EasyExcel填充时合并单元格

黒い桜

已于 2022-02-17 15:20:46 修改

阅读量1w

点赞数 32

分类专栏： excel导出 easyexcel 文章标签： java

于 2021-01-20 17:03:41 首次发布

本文链接：https://blog.csdn.net/weixin_44682948/article/details/112897500

版权

excel导出同时被 2 个专栏收录

1 篇文章 1 订阅

订阅专栏

easyexcel

1 篇文章 0 订阅

订阅专栏

由于填充时第二行开始，easyexcel不会自动合并单元格，所以需要自定义handler根据上一行的合并信息自行合并

public class MyHandler extends AbstractMergeStrategy {
	

	@Override
	protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
		if(relativeRowIndex==null ||relativeRowIndex==0){			
			return;
		}		
		int rowIndex = cell.getRowIndex(); 
		int colIndex = cell.getColumnIndex();
		sheet=cell.getSheet();
		Row preRow = sheet.getRow(rowIndex - 1);
		Cell preCell = preRow.getCell(colIndex);//获取上一行的该格
		List<CellRangeAddress> list = sheet.getMergedRegions();
		CellStyle cs = cell.getCellStyle();
		cell.setCellStyle(cs);
		for (int i = 0; i < list.size(); i++) {
            CellRangeAddress cellRangeAddress = list.get(i);
            if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex())) {
            	int lastColIndex = cellRangeAddress.getLastColumn();
            	int firstColIndex = cellRangeAddress.getFirstColumn();
            	CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
                sheet.addMergedRegion(cra);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet); 
                RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);  
                RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet); 
                RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
                return;
            }
        }

	}

}

调用：

ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(Objects.requireNonNull(DeviceController.class.getClassLoader().getResourceAsStream("/excel/devmsg.xlsx"))).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
WriteSheet writeSheet1 = EasyExcel.writerSheet().registerWriteHandler(new MyHandler()).build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();      
excelWriter.fill(bm, writeSheet1);//写入设备基础信息
excelWriter.fill(new FillWrapper("data1", list), fillConfig, writeSheet1);
excelWriter.fill(new FillWrapper("data2", list1), fillConfig, writeSheet1);
excelWriter.fill(new FillWrapper("data3", list2), fillConfig, writeSheet1);
excelWriter.fill(new FillWrapper("data4", list3), fillConfig, writeSheet1);
excelWriter.finish();