网站建设系统分析,深圳市福田区656号,wordpress付费问答,中国移动app免费下载文章目录 概要引入依赖常用操作方法折叠或隐藏列折叠或隐藏行单元格样式单行表头设置多行表头设置多个sheet写入自动列宽 概要
EasyExcel日常使用总结。
引入依赖
引入依赖 dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/a… 文章目录 概要引入依赖常用操作方法折叠或隐藏列折叠或隐藏行单元格样式单行表头设置多行表头设置多个sheet写入自动列宽 概要
EasyExcel日常使用总结。
引入依赖
引入依赖 dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.3.2/version/dependency常用操作方法
折叠或隐藏列
新建对象用于存储隐藏列数据后续直接根据此数据进行折叠隐藏。
Data
public class ExcelHiddenCellVo {// 分组坐标起始private int startHidden;// 分组坐标结束private int endHidden;// 是否折叠private Boolean ifCollapse;// 是否隐藏private Boolean ifHidden;
}计算折叠隐藏列数据(逻辑可根据数据自定义折叠隐藏的可以是固定的也可以是动态的) private ListExcelHiddenCellVo calculateHiddenColumn() {ListExcelHiddenCellVo 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;}定义RowWriteHandler类方法在写入每一行数据之前或之后执行自定义操作
private RowWriteHandler rowWriteHandler(ListExcelHiddenCellVo 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());});}}};}使用的话直接注册一个写入处理器把RowWriteHandler操作写入。
EasyExcel.write(outputStream).head(this.emailExcelHead(year, deptName)) // 设置表头.inMemory(true).registerWriteHandler(this.rowWriteHandler(this.calculateHiddenColumn())).doWrite(deptKpiStatisticsExportVos);折叠或隐藏行
和列是类似的只是使用的Hanlder不一样
计算折叠隐藏行数据(逻辑可根据数据自定义折叠隐藏的可以是固定的也可以是动态的)
// 计算出需要折叠的行private ListExcelHiddenCellVo calculateHiddenRow(ListDeptKpiStatisticsExportVo vos) {ListExcelHiddenCellVo excelHiddenCellVos new ArrayList();......定义CellWriteHandler类方法在单元格创建后触发行折叠
private CellWriteHandler cellStyleHandler(ListExcelHiddenCellVo 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);});}}
} 使用的话直接注册一个写入处理器把CellWriteHandler操作写入。
EasyExcel.write(outputStream).head(this.emailExcelHead(year, deptName)) // 设置表头.inMemory(true).registerWriteHandler(this.rowWriteHandler(this.calculateHiddenColumn())).registerWriteHandler(this.cellStyleHandler(excelHiddenCellVos)).doWrite(deptKpiStatisticsExportVos);单元格样式
我一般使用自定义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);}需要自定义样式的再写一个CellWriteHandler类。
// 创建自定义的单元格样式private CellWriteHandler cellStyleHandler(ListFinanceDeptKpiStatisticsExportVo financeDeptKpiStatisticsExportVos, ListExcelHiddenCellVo 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);}} }
}
使用同样的注册到registerWriteHandler里面。
单行表头设置
单行表头可以定义一个类使用ExcelProperty注解标识每个字段。
Data
public class ExportVo {ExcelProperty(index 0, value 工号)private String headAccount;ExcelProperty(index 1, value 姓名)private String headName;
} 使用
EasyExcel.write(outputStream).head(ExportVo.class) // 设置表头多行表头设置
使用ListList嵌套,List里面元素有多少个则表示有多少级表头。以下为四级表头。
// 表头设置private ListListString emailExcelHead(String year, String deptName) {String title deptName 明细表;String titleYear year 年度;ListListString list new ArrayList();ListString head1 new ArrayList();head1.add(title);head1.add(titleYear);head1.add(部门:);head1.add(序号);ListString head2 new ArrayList();head2.add(title);head2.add(titleYear);head2.add(deptName);head2.add(科目);ListString 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 MapInteger, MapInteger, Integer CACHE new HashMap(8);// 表头 0为第a行1为第2行 依次类推Integer headRow 0;public ExcelWidthStyleStrategy(Integer headRow){this.headRow headRow;}// 重写设置列宽的方法Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder,ListWriteCellData? cellDataList,Cell cell,Head head,Integer relativeRowIndex,Boolean isHead) {boolean needSetWidth isHead || !CollectionUtils.isEmpty(cellDataList);// 当时表头或者单元格数据列表有数据时才进行处理if (needSetWidth) {MapInteger, 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(ListWriteCellData? 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;}}}}}
