利用 EasyExcel 导出单元格合并、动态表头的Excel文件 |
您所在的位置：网站首页 › wps文件怎么合并单元格格式 › 利用 EasyExcel 导出单元格合并、动态表头的Excel文件 |
利用 EasyExcel 导出单元格合并、动态表头的Excel文件
|
EasyExcel 官方文档 一、 动态表头案例生成该种样式 excel的核心功能点有两个： 1. 如何保证表头动态可变解决该问题需要使用到EasyExcel不创建对象写的方式来实现 2. 如何定义单元格合并策略AbstractMergeStrategy 继承该类来实现自定义单元格合并策略 /** * 自定义单元格合并策略 */ public class CellMergeStrategy extends AbstractMergeStrategy { /** 唯一标识列(该列cell内容一定是全局唯一的,当cell的值相等时才能进行下一列合并) */ private Integer uniqueColumnIndex; /** 从哪一行开始合并 */ private Integer mergeRowIndex = 0; /** 合并列编号,从0开始 */ private List mergeColumnIndex = Lists.newArrayList(); private CellMergeStrategy() { } public CellMergeStrategy(Integer uniqueColumnIndex, Integer mergeRowIndex, Set mergeColumnIndex) { mergeColumnIndex.stream().forEach(item -> { this.mergeColumnIndex.add(item); }); this.mergeColumnIndex.stream().sorted(); if (null == uniqueColumnIndex) { this.uniqueColumnIndex = this.mergeColumnIndex.get(0); } else { this.uniqueColumnIndex = uniqueColumnIndex; } this.mergeRowIndex = mergeRowIndex; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { int curColIndex = cell.getColumnIndex(); int curRowIndex = cell.getRowIndex(); // 判断该列是否需要合并 if (!mergeColumnIndex.contains(curColIndex)) { return; } if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.size(); i++) { if (curColIndex == mergeColumnIndex.get(i)) { this.mergeRow(sheet, cell, curRowIndex, curColIndex, uniqueColumnIndex); break; } } } } /** * 向上合并单元格 * @param cell 当前单元格 * @param rowIndex 当前行 * @param colIndex 当前列 * @param uniqueColIndex 唯一标识列(该列cell内容一定是全局唯一的,当cell的值相等时才能进行下一列合并) */ private void mergeRow(Sheet sheet, Cell cell, int rowIndex, int colIndex, int uniqueColIndex) { Object curCellValue = getCellValue(cell); Object preCellValue = getCellValue(cell.getSheet().getRow(rowIndex - 1).getCell(colIndex)); boolean cellEqual = preCellValue.equals(curCellValue); boolean baseCellEqual = true; if (colIndex >= uniqueColIndex) { Object baseCellValue = getCellValue(cell.getRow().getCell(uniqueColIndex)); Object preBaseCellValue = getCellValue(cell.getSheet().getRow(rowIndex - 1).getCell(uniqueColIndex)); baseCellEqual = baseCellValue.equals(preBaseCellValue); } /** * 合并条件 * 1. 将当前单元格数据与上一个单元格数据比较,相同则执行合并逻辑 * 2. 唯一标识列内容相同，才能进行下一列合并 */ if (!(cellEqual && baseCellEqual)) { return; } List mergeRegionList = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegionList.size() && !isMerged; i++) { CellRangeAddress cellRange = mergeRegionList.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRange.isInRange(rowIndex - 1, colIndex)) { sheet.removeMergedRegion(i); cellRange.setLastRow(rowIndex); sheet.addMergedRegion(cellRange); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRange = new CellRangeAddress(rowIndex - 1, rowIndex, colIndex, colIndex); sheet.addMergedRegion(cellRange); } } private Object getCellValue(Cell baseCell) { return CellType.STRING.equals(baseCell.getCellType()) ? baseCell.getStringCellValue() : baseCell.getNumericCellValue(); } } 复制代码 3. 案例的代码实现 public class AttExportDataTemplateSecond { public static void main(String[] args) { String writeFileName = FilePathUtil.getPath() + "att-second-" + System.currentTimeMillis() + ".xlsx"; EasyExcel.write(writeFileName) .head(createDynamicHead()) .registerWriteHandler(new CellMergeStrategy(0, 0, Sets.newHashSet(0, 1, 2))) .excelType(ExcelTypeEnum.XLSX) .sheet("考勤报表") .doWrite(createDataByList()); } // 模拟填充数据 private static List createDataByList() { List list = ListUtils.newArrayList(); for (int i = 0; i < 2; i++) { List data = ListUtils.newArrayList(); data.add("RY044" + i); data.add("张三" + i); data.add("技术部"); data.add("出勤工时"); List dayKeyList = DateUtil.getBetweenDateKey(1643644800L, 1646063999L); for (Integer integer : dayKeyList) { data.add(""); } list.add(data); List data1 = ListUtils.newArrayList(); data1.add("RY044" + i); data1.add("张三" + i); data1.add("技术部"); data1.add("加班工时"); for (Integer integer : dayKeyList) { int hour = new Random().nextInt(5); if (0 == hour) { data1.add(""); } else { data1.add(new Random().nextInt(5)); } } list.add(data1); List data2 = ListUtils.newArrayList(); data2.add("RY044" + i); data2.add("张三" + i); data2.add("技术部"); data2.add("考勤补贴"); for (Integer integer : dayKeyList) { data2.add(""); } list.add(data2); List data3 = ListUtils.newArrayList(); data3.add("RY044" + i); data3.add("张三" + i); data3.add("技术部"); data3.add("请假旷工"); for (Integer integer : dayKeyList) { data3.add(""); } list.add(data3); } return list; } // 创建动态日期表头 private static List createDynamicHead() { List list = ListUtils.newArrayList(); List jobNo = ListUtils.newArrayList(); jobNo.add("202201月考勤报表"); jobNo.add("工号"); list.add(jobNo); List name = ListUtils.newArrayList(); name.add("202201月考勤报表"); name.add("姓名"); list.add(name); List dept = ListUtils.newArrayList(); dept.add("202201月考勤报表"); dept.add("部门"); list.add(dept); List attendanceItem = ListUtils.newArrayList(); attendanceItem.add("202201月考勤报表"); attendanceItem.add("考勤项"); list.add(attendanceItem); /**2022-02-01 到 2022-02-28*/ List dayKeyList = DateUtil.getBetweenDateKey(1643644800L, 1646063999L); for (Integer day : dayKeyList) { List oneDay = ListUtils.newArrayList(); oneDay.add("202201月考勤报表"); oneDay.add(day + ""); list.add(oneDay); } List total = ListUtils.newArrayList(); total.add("202201月考勤报表"); total.add("合计"); list.add(total); return list; } } 复制代码 二、 单元格合并案例
比如在实际开发中，我们导出的部门路径很长，在单个单元格中显示成如上图所示，该怎么办？ 让列宽可以根据单元格内容长度自适应 超过单元格能够支持最大长度时自动换行(单个单元格最大能支持 256 * 255的长度，如果设置超过这个长度导出时会报错) public void setColumnWidth(int columnIndex, int width) { if(width > 255*256) { throw new IllegalArgumentException("The maximum column width for an individual cell is 255 characters."); } columnHelper.setColWidth(columnIndex, (double)width/256); columnHelper.setCustomWidth(columnIndex, true); } 复制代码关于自适应列宽，EasyExcel中可以使用 LongestMatchColumnWidthStyleStrategy，它可以根据单元格内容自动适配列宽。如果还不满足你的需要你可以继承AbstractColumnWidthStyleStrategy，自定义适配规则。 /** * 自适应列宽 */ public class AdaptColumnWidth { private static final short FONT_HEIGHT_IN_POINTS = 11; public static void main(String[] args) { String writeFileName = FilePathUtil.getPath() + "employee-" + System.currentTimeMillis() + ".xlsx"; ExcelWriter writer = null; try { writer = EasyExcel.write(writeFileName, Employee.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) .registerWriteHandler(getHorizontalCellStyleStrategy()) .build(); WriteSheet writeSheet = EasyExcel.writerSheet("员工信息").build(); writer.write(createData(), writeSheet); } finally { if (writer != null) { writer.finish(); } } } // 设置表头样式、单元格内容自动换行 public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() { // excel 表头格式设置 WriteCellStyle headCellStyle = new WriteCellStyle(); headCellStyle.setFillForegroundColor(IndexedColors.TEAL.getIndex()); //蓝绿色 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints(FONT_HEIGHT_IN_POINTS); headWriteFont.setFontName("微软雅黑"); headWriteFont.setBold(Boolean.TRUE); //字体加粗 //设置字体 headCellStyle.setWriteFont(headWriteFont); //自动换行 headCellStyle.setWrapped(Boolean.TRUE); //垂直居中 headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //表头边框 headCellStyle.setBorderLeft(BorderStyle.THIN); headCellStyle.setBorderRight(BorderStyle.THIN); // excel表格内容样式设置 WriteCellStyle contentCellStyle = new WriteCellStyle(); WriteFont contentWriteFont = new WriteFont(); contentWriteFont.setFontHeightInPoints(FONT_HEIGHT_IN_POINTS); contentWriteFont.setFontName("微软雅黑"); contentWriteFont.setFontHeightInPoints(FONT_HEIGHT_IN_POINTS); contentCellStyle.setWriteFont(contentWriteFont); //自动换行 contentCellStyle.setWrapped(Boolean.TRUE); //垂直居中 contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); contentCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle); return horizontalCellStyleStrategy; } private static List createData() { List orderList = Lists.newArrayList(); Employee employee = new Employee(); employee.setName("张三"); employee.setDeptName("唐门科技有限公司/技术部/暗器冶炼部"); employee.setJobName("软件开发工程师"); orderList.add(employee); Employee employee1 = new Employee(); employee1.setName("李四"); employee1.setDeptName("唐门科技有限公司/总裁办公室/人力资源部/技术部/AAAAAAAAAAA/BBBBBBBBBBB/CCCCCCCCCCCC/DDDDDDDDDD/EEEEEEEEE/FFFFFFFF/GGGGGGGGGG/HHHHHHHHHHHH"); employee1.setJobName("CEO"); orderList.add(employee1); return orderList; } @Data private static class Employee { @ExcelProperty(value = "姓名", index = 0) private String name; @ExcelProperty(value = "部门", index = 1) private String deptName; @ExcelProperty(value = "职位", index = 2) private String jobName; } } 复制代码 最终导出的效果 |
【本文地址】
今日新闻 |
推荐新闻 |