easyExcel导出自定义表头以及自定义合并单元格

easyExcel导出自定义表头以及自定义合并单元格

easyExcel导出自定义表头

1、自定义-，写入头部样式

public class CustomCellWriteHandler extends AbstractCellStyleStrategy implements CellWriteHandler { Workbook workbook; @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { super.beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex, relativeRowIndex, isHead); } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead); } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { this.initCellStyle(writeSheetHolder.getSheet().getWorkbook()); this.setHeadCellStyle(cell,head,relativeRowIndex); } @Override protected void initCellStyle(Workbook workbook) { this.workbook = workbook; } @Override protected void setHeadCellStyle(Cell cell, Head head, Integer integer) { if (cell.getRowIndex() == 0) { cell.setCellStyle(EasyExcelUtils.getColumnTopStyle(workbook, 30)); } else if (cell.getRowIndex() == 1) { cell.setCellStyle(EasyExcelUtils.getColumnSecondLineStyle(workbook,10)); } if(cell.getRowIndex() > 1){ cell.setCellStyle(EasyExcelUtils.getColumnStyle(workbook)); } } @Override protected void setContentCellStyle(Cell cell, Head head, Integer integer) { }}

2、样式

public class EasyExcelUtils { /** * 首行单元格 * @param workbook * @param fontSize * @return */ public static CellStyle getColumnTopStyle(Workbook workbook, int fontSize) { if (fontSize == 0) { fontSize = 10; } // 设置字体 Font font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short) fontSize); //字体加粗 font.setBold(true); //设置字体名字 font.setFontName("宋体"); //设置样式; CellStyle style = workbook.createCellStyle(); //左右居中 style.setAlignment(HorizontalAlignment.CENTER); //垂直居中 style.setVerticalAlignment(VerticalAlignment.CENTER); //设置边框 style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); return style; } /** * * @param workbook * @param fontSize * @return */ public static CellStyle getColumnSecondLineStyle(Workbook workbook, int fontSize) { if (fontSize == 0) { fontSize = 10; } Font font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short) fontSize); //设置字体名字 font.setFontName("Arial"); //设置样式; CellStyle style = workbook.createCellStyle(); //左右居中 style.setAlignment(HorizontalAlignment.CENTER); //垂直居中 style.setVerticalAlignment(VerticalAlignment.CENTER); //设置边框 style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); return style; } /* * 字段样式 */ public static CellStyle getColumnStyle(Workbook workbook) { // 设置字体 Font font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short) 10); //设置字体名字 font.setFontName("Arial"); //设置样式; CellStyle style = workbook.createCellStyle(); //左右居中 style.setAlignment(HorizontalAlignment.CENTER); //垂直居中 style.setVerticalAlignment(VerticalAlignment.CENTER); //设置边框 style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(true); return style; }}

3、合并单元格utils

public class ExcelFillCellMergeStrategy implements CellWriteHandler { private List mergeColumnIndex; private int mergeRowIndex; public ExcelFillCellMergeStrategy() { } public List getMergeColumnIndex() { return mergeColumnIndex; } public void setMergeColumnIndex(List mergeColumnIndex) { this.mergeColumnIndex = mergeColumnIndex; } public int getMergeRowIndex() { return mergeRowIndex; } public void setMergeRowIndex(int mergeRowIndex) { this.mergeRowIndex = mergeRowIndex; } public ExcelFillCellMergeStrategy(int mergeRowIndex, List mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex && mergeColumnIndex.contains(curColIndex) ) { mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); } } /** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */ private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并 Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行 if (curData.equals(preData)) { Sheet sheet = writeSheetHolder.getSheet(); List 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); } } }}

4、使用

//获取数据源，数据源实体类不能包含List、Map等 List list = (List) stringListMap.get("resultList"); //需要合并的列 List mergeColumnIndex = Arrays.asList(new Integer[] {0, 1, 2, 3}); //需要从第一行开始，列头第一行 int mergeRowIndex = 1; Set excludeColumnFiledNames = new HashSet(); excludeColumnFiledNames.add("params"); excludeColumnFiledNames.add("ratingNum"); excludeColumnFiledNames.add("remark"); excludeColumnFiledNames.add("rating"); ExcelWriter excelWriter = null; try{ WriteWorkbook writeWorkbook = new WriteWorkbook(); writeWorkbook.setOutputStream(outputStream); writeWorkbook.setClazz(FirProfessionalByDeptVo.class); writeWorkbook.setHead(head(ratingYear+"年"+material.getDeptName()+"评价总体情况分析表")); //07的excel版本,节省内存 writeWorkbook.setExcelType(ExcelTypeEnum.XLS); //忽略字段 writeWorkbook.setExcludeColumnFiledNames(excludeColumnFiledNames); //是否自动关闭输入流 writeWorkbook.setAutoCloseStream(Boolean.TRUE); //自定义-，设置头部样式 List customWriteHandlerList = new ArrayList<>(); customWriteHandlerList.add(new CustomCellWriteHandler()); customWriteHandlerList.add(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex)); writeWorkbook.setCustomWriteHandlerList(customWriteHandlerList); excelWriter = new ExcelWriter(writeWorkbook); WriteSheet writeSheet = new WriteSheet(); writeSheet.setSheetName("XXXXXXX表"); excelWriter.write(list, writeSheet); //一般到到这里就结束了 /**---------------------这里是我自己特殊处理，合并处理----------------------------- */ WriteContext writeContext = excelWriter.writeContext(); Sheet sheet = writeContext.getCurrentSheet(); Iterator rowIterator= sheet.rowIterator(); while(rowIterator.hasNext()){ Row row = rowIterator.next(); int rowNum = row.getRowNum(); if(rowNum > mergeRowIndex && list.size() > rowNum - mergeRowIndex -1){ FirProfessionalByDeptVo vo = list.get(rowNum - mergeRowIndex - 1 ); Cell cell = row.getCell(4); cell.setCellValue(vo.getNowYearDeduction()); cell = row.getCell(5); cell.setCellValue(vo.getLastYearDeduction()); cell = row.getCell(6); cell.setCellValue(vo.getNowYearRemark()); cell = row.getCell(7); cell.setCellValue(vo.getLastYearRemark());/**----------------------------------------------------- */ } } }catch (Exception e){ logger.error("生成评分卡文件失败",e); }finally { // 千万别忘记finish 会帮忙关闭流 if (excelWriter != null) { excelWriter.finish(); } } /** * 自定义头 * @param headTitle 统一头 * * @return 返回整个头list。 头部相同连续的单元格会自动合并。 */ private static List> head(String headTitle){ List> list = new ArrayList>(); List head1 = new ArrayList(); head1.add(headTitle); head1.add("模块"); List head2 = new ArrayList(); head2.add(headTitle); head2.add("一级指标"); List head3 = new ArrayList(); head3.add(headTitle); head3.add("二级指标"); List head4 = new ArrayList(); head4.add(headTitle); head4.add("评分内容"); List head5 = new ArrayList(); head5.add(headTitle); head5.add("今年评分"); List head6 = new ArrayList(); head6.add(headTitle); head6.add("去年评分"); List head7 = new ArrayList(); head7.add(headTitle); head7.add("今年备注"); List head8 = new ArrayList(); head8.add(headTitle); head8.add("去年备注"); list.add(head1); list.add(head2); list.add(head3); list.add(head4); list.add(head5); list.add(head6); list.add(head7); list.add(head8); return list; }

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。