男女做羞羞事的网站,证券网站开发,华为免费企业网站建设,查询证书网站建设在EasyExcel中自定义拦截器不仅可以帮助我们不止步于数据的填充#xff0c;而且可以对样式、单元格合并等带来便捷的功能。下面直接开始
我们定义一个MergeWriteHandler的类继承AbstractMergeStrategy实现CellWriteHandler
public class MergeLastWriteHandler extends Abst…在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 {ListUser userList DataByExcel(); //获取数据的列表ListBudgetForm 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;ExcelIgnoreApiModelProperty(合计)private String total;}
