ts-428 WordPressseo技术平台

概要

EasyExcel日常使用总结。

引入依赖

引入依赖

		<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.2</version></dependency>

常用操作方法

折叠或隐藏列

1. 新建对象用于存储隐藏列数据，后续直接根据此数据进行折叠隐藏。

@Data
public class ExcelHiddenCellVo {// 分组坐标起始private int startHidden;// 分组坐标结束private int endHidden;// 是否折叠private Boolean ifCollapse;// 是否隐藏private Boolean ifHidden;
}

2. 计算折叠隐藏列数据(逻辑可根据数据自定义，折叠隐藏的，可以是固定的也可以是动态的)

    private List<ExcelHiddenCellVo> calculateHiddenColumn() {List<ExcelHiddenCellVo> excelHiddenCellVos = new ArrayList<>();ExcelHiddenCellVo excelHiddenCellVo = new ExcelHiddenCellVo();excelHiddenCellVo.setStartHidden(monthValue + 1);excelHiddenCellVo.setEndHidden(12);excelHiddenCellVo.setIfHidden(false);excelHiddenCellVo.setIfCollapse(true);excelHiddenCellVos.add(excelHiddenCellVo);return excelHiddenCellVos;}

3. 定义RowWriteHandler类方法，在写入每一行数据之前或之后执行自定义操作

private RowWriteHandler rowWriteHandler(List<ExcelHiddenCellVo> excelHiddenCellVos) {return new RowWriteHandler() {@Overridepublic void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {// 列分组if (isHead && relativeRowIndex == 0) {excelHiddenCellVos.forEach(item -> {Sheet sheet = writeSheetHolder.getSheet();sheet.groupColumn(item.getStartHidden(), item.getEndHidden());sheet.setColumnGroupCollapsed(item.getStartHidden(), item.getIfCollapse());});}}};}

4. 使用的话直接注册一个写入处理器，把RowWriteHandler操作写入。

EasyExcel.write(outputStream).head(this.emailExcelHead(year, deptName)) // 设置表头.inMemory(true).registerWriteHandler(this.rowWriteHandler(this.calculateHiddenColumn())).doWrite(deptKpiStatisticsExportVos);

折叠或隐藏行

和列是类似的，只是使用的Hanlder不一样

1. 计算折叠隐藏行数据(逻辑可根据数据自定义，折叠隐藏的，可以是固定的也可以是动态的)

// 计算出需要折叠的行private List<ExcelHiddenCellVo> calculateHiddenRow(List<DeptKpiStatisticsExportVo> vos) {List<ExcelHiddenCellVo> excelHiddenCellVos = new ArrayList<>();......

2. 定义CellWriteHandler类方法，在单元格创建后触发行折叠

private CellWriteHandler cellStyleHandler(List<ExcelHiddenCellVo> excelHiddenCellVos) {return new CellWriteHandler() {@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {excelHiddenCellVos.forEach(item -> {Sheet sheet = writeSheetHolder.getSheet();sheet.groupRow(item.getStartHidden(), item.getEndHidden());sheet.setRowGroupCollapsed(item.getStartHidden(), true);});}}
}            

3. 使用的话直接注册一个写入处理器，把CellWriteHandler操作写入。

EasyExcel.write(outputStream).head(this.emailExcelHead(year, deptName)) // 设置表头.inMemory(true).registerWriteHandler(this.rowWriteHandler(this.calculateHiddenColumn())).registerWriteHandler(this.cellStyleHandler(excelHiddenCellVos)).doWrite(deptKpiStatisticsExportVos);

单元格样式

1. 我一般使用自定义HorizontalCellStyleStrategy类，作为整体的默认样式

// 整体默认的样式private HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {WriteCellStyle headWriteCellStyle = new WriteCellStyle();WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 9);headWriteFont.setBold(true);headWriteFont.setFontName("微软雅黑");headWriteCellStyle.setWriteFont(headWriteFont);// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 背景绿色contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short) 9);contentWriteFont.setFontName("微软雅黑");contentWriteCellStyle.setWriteFont(contentWriteFont);contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 设置水平居中contentWriteCellStyle.setBorderTop(BorderStyle.THIN); // 设置边框contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}

2. 需要自定义样式的，再写一个CellWriteHandler类。

// 创建自定义的单元格样式private CellWriteHandler cellStyleHandler(List<FinanceDeptKpiStatisticsExportVo> financeDeptKpiStatisticsExportVos, List<ExcelHiddenCellVo> excelHiddenCellVos) {return new CellWriteHandler() {@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {Cell cell = context.getCell();WriteCellData<?> cellData = context.getFirstCellData();WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();if (cell.getRowIndex() == 0 || cell.getRowIndex() == 1 || cell.getRowIndex() == 2) {WriteFont writeFont = new WriteFont();writeFont.setBold(true);writeFont.setFontName("微软雅黑");writeFont.setFontHeightInPoints((short) 14);writeCellStyle.setBorderTop(BorderStyle.NONE); // 设置边框writeCellStyle.setBorderBottom(BorderStyle.NONE);writeCellStyle.setBorderLeft(BorderStyle.NONE);writeCellStyle.setBorderRight(BorderStyle.NONE);writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);writeCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());writeCellStyle.setWriteFont(writeFont);}}      }
}

3. 使用同样的注册到registerWriteHandler里面。

单行表头设置

1. 单行表头可以定义一个类，使用@ExcelProperty注解标识每个字段。

@Data
public class ExportVo {@ExcelProperty(index = 0, value = "工号")private String headAccount;@ExcelProperty(index = 1, value = "姓名")private String headName;
}    

2. 使用

EasyExcel.write(outputStream).head(ExportVo.class) // 设置表头

多行表头设置

使用List<List>嵌套,List里面元素有多少个，则表示有多少级表头。以下为四级表头。

// 表头设置private List<List<String>> emailExcelHead(String year, String deptName) {String title = deptName + "明细表";String titleYear = "（" + year + "年度）";List<List<String>> list = new ArrayList<>();List<String> head1 = new ArrayList<>();head1.add(title);head1.add(titleYear);head1.add("部门:");head1.add("序号");List<String> head2 = new ArrayList<>();head2.add(title);head2.add(titleYear);head2.add(deptName);head2.add("科目");List<String> head3 = new ArrayList<>();head3.add(title);head3.add(titleYear);head3.add("");head3.add("费用类型");list.add(head1);list.add(head2);list.add(head3);return list;

多个sheet写入

ByteArrayOutputStream outputStream = new ByteArrayOutputStream();ExcelWriter excelWriter = EasyExcel.write(outputStream).build();WriteSheet writeSheet = EasyExcel.writerSheet("报表1").head(CompleteWarehousingManHourExportVo.class).registerWriteHandler(new ExcelWidthStyleStrategy(0)) // 自定义列宽策略.registerWriteHandler(WriteExcelUtils.getHorizontalCellStyleStrategy()) // 整体单元格的样式设置.registerWriteHandler(this.CompleteWarehousingStyleHandler()) // 自定义单元格样式.registerWriteHandler(this.rowWriteHandler(this.calculateHiddenColumn())) // 列分组.build();excelWriter.write(warehousingManHourExportVos, writeSheet);WriteSheet writeSheet2 = EasyExcel.writerSheet("报表2").head(CompleteMesManHourExportVo.class).registerWriteHandler(new ExcelWidthStyleStrategy(0)) // 自定义列宽策略.registerWriteHandler(WriteExcelUtils.getHorizontalCellStyleStrategy()) // 整体单元格的样式设置.build();excelWriter.write(completeMesManHourExportVos, writeSheet2);excelWriter.finish();outputStream.flush();outputStream.close();InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());

自动列宽

使用同样registerWriteHandler注册

public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {// 单元格的最大宽度private static final int MAX_COLUMN_WIDTH = 8;// 缓存（第一个Map的键是sheet的index, 第二个Map的键是列的index, 值是数据长度）private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);// 表头 0为第a行，1为第2行 依次类推Integer headRow = 0;public ExcelWidthStyleStrategy(Integer headRow){this.headRow = headRow;}// 重写设置列宽的方法@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder,List<WriteCellData<?>> cellDataList,Cell cell,Head head,Integer relativeRowIndex,Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);// 当时表头或者单元格数据列表有数据时才进行处理if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap =CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap(16);CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}// 获取数据长度Integer columnWidth = this.getLength(cellDataList, cell, cell.getRowIndex());if (columnWidth >= 0) {if (columnWidth > MAX_COLUMN_WIDTH) {columnWidth = MAX_COLUMN_WIDTH;}// 确保一个列的列宽以表头为主，如果表头已经设置了列宽，单元格将会跟随表头的列宽Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {// 控制以哪一行为表头，大于1则以第二行if (cell.getRowIndex() >= headRow)maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);// 如果使用EasyExcel默认表头，那么使用columnWidth * 512// 如果不使用EasyExcel默认表头，那么使用columnWidth * 256// 如果是自己定义的字体大小，可以再去测试这个参数常量writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 512);}}}}/*** 获取当前单元格的数据长度* @param cellDataList* @param cell* @param* @return*/private Integer getLength(List<WriteCellData<?>> cellDataList,Cell cell,Integer rowNum) {if (rowNum == headRow) {return cell.getStringCellValue().getBytes().length;} else {WriteCellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch(type) {case STRING:return cellData.getStringValue().getBytes().length;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}}