Easyexcel 应用

pom.xml

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

Fill 填充

• 横向填充 WriteDirectionEnum.HORIZONTAL
• 垂直填充 WriteDirectionEnum.VERTICAL

// 设置填充配置
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();// 模板路径
String templatePath = physResultStatManager.localFileStorePath + bucketName + "template.xlsx";
// 写入新的文件本地路径
String saveLocalPath = physResultStatManager.localFileStorePath + bucketName + fileName + ".xlsx";ExcelWriter excelWriter = null;try{excelWriter = EasyExcel.write(saveLocalPath).withTemplate(templatePath).build();WriteSheet bmiSheet = EasyExcel.writerSheet(0, "BMI Data").automaticMergeHead(true).build();excelWriter.fill(new FillWrapper("data", List.of()), fillConfig, bmiSheet);excelWriter.fill(new FillWrapper("title", List.of()), fillConfig, bmiSheet);
} catch(Exception e) {} finally {if (excelWriter != null) {excelWriter.close();}
}

Sheet 多次写入同一模版

WriteSheet visionSheet = EasyExcel.writerSheet(2, "Vision Data").build();for (int i = 0 ; i < 3 ; i++) {// 每一行数据List<GradeVisionDTO> gradeVisions = visionDataMap.get(i);// 写入的表WriteTable writeTable = EasyExcel.writerTable(i)// 简单列宽样式策略.registerWriteHandler(new SimpleColumnWidthStyleStrategy(21))// 简单的行高风格策略.registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 86, (short) 30))// 构建表头.head(physResultStatManager.buildTemplateHead(phIdNameMap.get(phId))).needHead(Boolean.TRUE).build();excelWriter.write(gradeVisions, visionSheet, writeTable);
}

复杂表头构建参考

    public List<List<String>> buildTemplateHead(String title) {List<List<String>> headers = new ArrayList<>();List<String> column1 = new ArrayList<>();column1.add(title);column1.add("");column1.add("");headers.add(column1);headers.add(column1);List<String> column3 = new ArrayList<>();column3.add(title);column3.add("Color Vision");column3.add("Others");headers.add(column3);List<String> column4 = new ArrayList<>();column4.add(title);column4.add("Vision Acuity");column4.add("Uncorrected Vision Acuity（UCVA）and <0.8-");headers.add(column4);List<String> column5 = new ArrayList<>();column5.add(title);column5.add("Vision Acuity");column5.add("Corrected Vision and <0.8-");headers.add(column5);List<String> column6 = new ArrayList<>();column6.add(title);column6.add("Ametropia Suggestions");column6.add("Myopia");headers.add(column6);List<String> column7 = new ArrayList<>();column7.add(title);column7.add("Ametropia Suggestions");column7.add("Hyperopia has effected vision");headers.add(column7);List<String> column8 = new ArrayList<>();column8.add(title);column8.add("Ametropia Suggestions");column8.add("Astigmatism");headers.add(column8);List<String> column9 = new ArrayList<>();column9.add(title);column9.add("Ametropia Suggestions");column9.add("Anisometropia");headers.add(column9);List<String> column10 = new ArrayList<>();column10.add(title);column10.add("Ametropia Suggestions");column10.add("No need");headers.add(column10);List<String> column11 = new ArrayList<>();column11.add(title);column11.add("Slit-lamp Examination");column11.add("Normal");headers.add(column11);List<String> column12 = new ArrayList<>();column12.add(title);column12.add("Slit-lamp Examination");column12.add("Dry eye");headers.add(column12);List<String> column13 = new ArrayList<>();column13.add(title);column13.add("Slit-lamp Examination");column13.add("Conjunctivitis");headers.add(column13);List<String> column14 = new ArrayList<>();column14.add(title);column14.add("Slit-lamp Examination");column14.add("Strabismus");headers.add(column14);List<String> column15 = new ArrayList<>();column15.add(title);column15.add("Slit-lamp Examination");column15.add("Moderate-Severe Trichiasis");headers.add(column15);List<String> column16 = new ArrayList<>();column16.add(title);column16.add("Slit-lamp Examination");column16.add("Other Suggestion");headers.add(column16);List<String> column17 = new ArrayList<>();column17.add("Total");column17.add("Total");column17.add("Total");headers.add(column17);return headers;}

设置水平单元格样式

public HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 背景设置为白色headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//边框headWriteCellStyle.setBorderBottom(BorderStyle.THIN);headWriteCellStyle.setBorderLeft(BorderStyle.THIN);headWriteCellStyle.setBorderRight(BorderStyle.THIN);headWriteCellStyle.setBorderTop(BorderStyle.THIN);//自动换行headWriteCellStyle.setWrapped(true);WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 12);headWriteCellStyle.setWriteFont(headWriteFont);// 内容的策略WriteCellStyle contentWriteCellStyle = MergeFirstColumnHandler.getWriteCellStyle();return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}

单元格合并

• 合并第一列
• 并且按照规则设置颜色
• 合并最后一行的第一列和第二列
• 当写入多个tab时，支持合并

public class MergeFirstColumnHandler implements CellWriteHandler {/*** 需要合并列的下标，从0开始*/private final int[] mergeColumnIndex = new int[]{0};private final static XSSFColor DEFAULT_COLOR = new XSSFColor(new byte[]{(byte) 230, (byte) 240, (byte) 222}, null);public static WriteCellStyle getWriteCellStyle() {WriteCellStyle contentWriteCellStyle = new WriteCellStyle();contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//边框contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//自动换行contentWriteCellStyle.setWrapped(true);//文字WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short) 12);contentWriteCellStyle.setWriteFont(contentWriteFont);return contentWriteCellStyle;}@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {Cell cell = context.getCell();WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();// 当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();// 从第几行开始合并int mergeRowIndex = 3;if (curRowIndex > mergeRowIndex) {for (int columnIndex : mergeColumnIndex) {if (curColIndex == columnIndex) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}if (curRowIndex == 16) {if (curColIndex == 1) {// 最后一行 合并第一列和第二列mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, 0, 1);}}if ((curRowIndex - 16) % 17 == 0 || curColIndex == 16) {Workbook workbook = writeSheetHolder.getSheet().getWorkbook();CellStyle cellStyle = getCellStyle(workbook);cell.setCellStyle(cellStyle);context.getFirstCellData().setWriteCellStyle(null);}}public CellStyle getCellStyle(Workbook workbook) {XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();cellStyle.setFillForegroundColor(DEFAULT_COLOR);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);XSSFFont font = new XSSFFont();font.setFontHeightInPoints((short) 12);cellStyle.setFont(font);return cellStyle;}public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {Sheet sheet = writeSheetHolder.getSheet();CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);sheet.addMergedRegion(cellRangeAddress);}private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :cell.getNumericCellValue();Row preRow = cell.getSheet().getRow(curRowIndex - 1);if (preRow == null) {// 当获取不到上一行数据时，使用缓存sheet中数据preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);}Cell preCell = preRow.getCell(curColIndex);if (preCell == null) {return;}Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :preCell.getNumericCellValue();// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行if (curData.equals(preData)) {Sheet sheet = writeSheetHolder.getSheet();List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();boolean isMerged = false;for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {CellRangeAddress cellRangeAddr = mergeRegions.get(i);// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {sheet.removeMergedRegion(i);cellRangeAddr.setLastRow(curRowIndex);sheet.addMergedRegion(cellRangeAddr);isMerged = true;}}// 若上一个单元格未被合并，则新增合并单元if (!isMerged) {CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,curColIndex);sheet.addMergedRegion(cellRangeAddress);}}}
}