门户建设网站沈阳线上教学

在EasyExcel中自定义拦截器不仅可以帮助我们不止步于数据的填充，而且可以对样式、单元格合并等带来便捷的功能。下面直接开始

我们定义一个MergeWriteHandler的类继承AbstractMergeStrategy实现CellWriteHandler

public class MergeLastWriteHandler extends AbstractMergeStrategy implements CellWriteHandler 

当中我们重写merge方法

@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {}

我们可以在重写的方法中得到形参中的Cel，那我们可以通过调用cell.getStringCellValue()得到当前单元格的内容，判断当前单元格的内容是否是目标单元格，例如下面代码

if (cell.getStringCellValue().equals("说明")) {cell.setCellValue("说明：这是一条说明");//获取表格最后一行int lastRowNum = sheet.getLastRowNum();CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);sheet.addMergedRegionUnsafe(region);}

并且这里我们通过sheet中的 getLastRowNum()获取最后一行，最终通过CellRangeAddress来进行单元格合并，从下面源码我们可以了解到合并的规则是什么（通过int firstRow, int lastRow, int firstCol, int lastCol）

/*** Creates new cell range. Indexes are zero-based.** @param firstRow Index of first row* @param lastRow Index of last row (inclusive), must be equal to or larger than {@code firstRow}* @param firstCol Index of first column* @param lastCol Index of last column (inclusive), must be equal to or larger than {@code firstCol}*/public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {super(firstRow, lastRow, firstCol, lastCol);if (lastRow < firstRow || lastCol < firstCol) {throw new IllegalArgumentException("Invalid cell range, having lastRow < firstRow || lastCol < firstCol, " +"had rows " + lastRow + " >= " + firstRow + " or cells " + lastCol + " >= " + firstCol);}}

这样就可以实现合并单元格，不过这里可能会出现一个问题

java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cellat org.apache.poi.xssf.streaming.SXSSFCell.typeMismatch(SXSSFCell.java:943)at org.apache.poi.xssf.streaming.SXSSFCell.getStringCellValue(SXSSFCell.java:460)

因为会访问所有的单元格，有可能会出现是不是字符串类型的单元格，所以我们最好在开始的时候对其进行处理一次

if (cell.getCellType().equals(CellType.NUMERIC)){double numericCellValue = cell.getNumericCellValue();String s = Double.toString(numericCellValue);String substring = s.substring(0, s.indexOf("."));cell.setCellValue(substring);}

但这里可能我们还需要一个操作，例如如果我们全局配置了表框线条，但是不想当前的单元格有线条，如何处理呢，定义CustomCellWriteHandler拦截器继承AbstractCellWriteHandler

public class CustomCellWriteHandler extends AbstractCellWriteHandler{}

重写当中的afterCellDispose方法，得到

 @Overridepublic void afterCellDispose(CellWriteHandlerContext context) {super.afterCellDispose(context);}

现在我们对其进行操作

  Cell cell = context.getCell();if(BooleanUtils.isNotTrue(context.getHead())){if(cell.getStringCellValue().contains("说明"))){Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setAlignment(HorizontalAlignment.LEFT);cell.setCellStyle(cellStyle);context.getFirstCellData().setWriteCellStyle(null); //关键代码，不设置不生效}
}

最后只需要在写入的时候，把拦截器放进去就可以了，看完整代码

public class CustomCellWriteHandler extends AbstractCellWriteHandler {@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {short height = 600;row.setHeight(height);}@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {Cell cell = context.getCell();if(BooleanUtils.isNotTrue(context.getHead())){if(cell.getStringCellValue().contains("说明"))){Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setAlignment(HorizontalAlignment.LEFT);cell.setCellStyle(cellStyle);context.getFirstCellData().setWriteCellStyle(null);}}super.afterCellDispose(context);}
}

合并单元格的拦截器

public class MergeLastWriteHandler extends AbstractMergeStrategy implements CellWriteHandler {public static HorizontalCellStyleStrategy getStyleStrategy() {// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 设置对齐//headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);// 背景色, 设置为绿色，也是默认颜色headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());// 字体//WriteFont headWriteFont = new WriteFont();//headWriteFont.setFontHeightInPoints((short) 12);//headWriteCellStyle.setWriteFont(headWriteFont);// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 字体策略WriteFont contentWriteFont = new WriteFont();//contentWriteFont.setFontHeightInPoints((short) 12);contentWriteCellStyle.setWriteFont(contentWriteFont);//设置 自动换行contentWriteCellStyle.setWrapped(true);//设置 垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置边框样式contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);return horizontalCellStyleStrategy;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (cell.getCellType() == CellType.NUMERIC) {double numericCellValue = cell.getNumericCellValue();String s = Double.toString(numericCellValue);String substring = s.substring(0, s.indexOf("."));cell.setCellValue(substring);}if (cell.getStringCellValue().equals("说明")) {cell.setCellValue("说明：这是一条说明");//获取表格最后一行int lastRowNum = sheet.getLastRowNum();CellRangeAddress region = new CellRangeAddress(lastRowNum, lastRowNum, 0, 5);sheet.addMergedRegionUnsafe(region);}}
}

看一下Controller层

@GetMapping("/excelWrapper")public void excelWrapper(HttpServletResponse response) throws IOException {try {List<User> userList =  DataByExcel();  //获取数据的列表List<BudgetForm> budgetForm = BeanUtil.copyToList(userList,BudgetForm.class);String fileName = one.getProjectName() + ".xlsx";response.setContentType("application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName );// 创建ExcelWriter对象WriteSheet writeSheet = EasyExcel.writerSheet("表格sheet").registerWriteHandler(new MergeLastWriteHandler()).registerWriteHandler(new CustomCellWriteHandler()).build();// 向Excel中写入数据ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), BudgetForm.class).build();excelWriter.write(userList , writeSheet);// 关闭流excelWriter.finish();} catch (Exception e) {e.printStackTrace();}}

对表头设置（自己对应表格设置对应字段）

@Data
@ContentRowHeight(47) //内容行高
@HeadRowHeight(35)//表头行高
public class BudgetForm implements Serializable  {@ColumnWidth(6)@ExcelProperty(value ={"表格","序号"} ,index = 0)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)private Integer serialNumber;@ColumnWidth(15)@ExcelProperty(value ={"表格","名称"} ,index = 1)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)private String name;@ColumnWidth(26)@ExcelProperty(value = {"表格","备注"},index = 2)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)private String remark;@ColumnWidth(26)@ExcelProperty(value = {"表格","其他"},index = 3)@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)private String budgetary;@ExcelIgnore@ApiModelProperty("合计")private String total;}