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<CellData> 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<Integer> mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelFillCellMergeStrategy() {
    }

    public List<Integer>  getMergeColumnIndex() {
        return mergeColumnIndex;
    }

    public void setMergeColumnIndex(List<Integer>  mergeColumnIndex) {
        this.mergeColumnIndex = mergeColumnIndex;
    }

    public int getMergeRowIndex() {
        return mergeRowIndex;
    }

    public void setMergeRowIndex(int mergeRowIndex) {
        this.mergeRowIndex = mergeRowIndex;
    }

    public ExcelFillCellMergeStrategy(int mergeRowIndex, List<Integer>  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<CellData> 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<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);
            }
        }
    }
}

4、使用

//获取数据源，数据源实体类不能包含List、Map等
 List<FirProfessionalByDeptVo> list = (List<FirProfessionalByDeptVo>) stringListMap.get("resultList");
       //需要合并的列
       List<Integer> mergeColumnIndex = Arrays.asList(new Integer[] {0, 1, 2, 3});
       //需要从第一行开始，列头第一行
       int mergeRowIndex = 1;
       Set<String> excludeColumnFiledNames = new HashSet<String>();
       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<WriteHandler> 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<Row>  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<List<String>> head(String headTitle){
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head1 = new ArrayList<String>();
        head1.add(headTitle);
        head1.add("模块");
        List<String> head2 = new ArrayList<String>();
        head2.add(headTitle);
        head2.add("一级指标");
        List<String> head3 = new ArrayList<String>();
        head3.add(headTitle);
        head3.add("二级指标");
        List<String> head4 = new ArrayList<String>();
        head4.add(headTitle);
        head4.add("评分内容");
        List<String> head5 = new ArrayList<String>();
        head5.add(headTitle);
        head5.add("今年评分");
        List<String> head6 = new ArrayList<String>();
        head6.add(headTitle);
        head6.add("去年评分");
        List<String> head7 = new ArrayList<String>();
        head7.add(headTitle);
        head7.add("今年备注");
        List<String> head8 = new ArrayList<String>();
        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;
    } 

效果展示