easyExcel导出自定义表头

1、自定义拦截器，写入头部样式

publicclassCustomCellWriteHandlerextendsAbstractCellStyleStrategyimplementsCellWriteHandler{

    Workbook workbook;@OverridepublicvoidbeforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead){super.beforeCellCreate(writeSheetHolder, writeTableHolder, row, head, columnIndex, relativeRowIndex, isHead);}@OverridepublicvoidafterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead){super.afterCellDispose(writeSheetHolder, writeTableHolder, cellDataList, cell, head, relativeRowIndex, isHead);}@OverridepublicvoidafterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead){this.initCellStyle(writeSheetHolder.getSheet().getWorkbook());this.setHeadCellStyle(cell,head,relativeRowIndex);}@OverrideprotectedvoidinitCellStyle(Workbook workbook){this.workbook= workbook;}@OverrideprotectedvoidsetHeadCellStyle(Cell cell, Head head, Integer integer){if(cell.getRowIndex()==0){
            cell.setCellStyle(EasyExcelUtils.getColumnTopStyle(workbook,30));}elseif(cell.getRowIndex()==1){
            cell.setCellStyle(EasyExcelUtils.getColumnSecondLineStyle(workbook,10));}if(cell.getRowIndex()>1){
            cell.setCellStyle(EasyExcelUtils.getColumnStyle(workbook));}}@OverrideprotectedvoidsetContentCellStyle(Cell cell, Head head, Integer integer){}}

2、样式

publicclassEasyExcelUtils{/**
     * 首行单元格
     * @param workbook
     * @param fontSize
     * @return
     */publicstatic CellStylegetColumnTopStyle(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
     */publicstatic CellStylegetColumnSecondLineStyle(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;}/*
     * 字段样式
     */publicstatic CellStylegetColumnStyle(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

publicclassExcelFillCellMergeStrategyimplementsCellWriteHandler{private List<Integer> mergeColumnIndex;privateint mergeRowIndex;publicExcelFillCellMergeStrategy(){}public List<Integer>getMergeColumnIndex(){return mergeColumnIndex;}publicvoidsetMergeColumnIndex(List<Integer>  mergeColumnIndex){this.mergeColumnIndex= mergeColumnIndex;}publicintgetMergeRowIndex(){return mergeRowIndex;}publicvoidsetMergeRowIndex(int mergeRowIndex){this.mergeRowIndex= mergeRowIndex;}publicExcelFillCellMergeStrategy(int mergeRowIndex, List<Integer>  mergeColumnIndex){this.mergeRowIndex= mergeRowIndex;this.mergeColumnIndex= mergeColumnIndex;}@OverridepublicvoidbeforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead){}@OverridepublicvoidafterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean){}@OverridepublicvoidafterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean){}@OverridepublicvoidafterCellDispose(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      当前列
     */privatevoidmergeWithPrevRow(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=newCellRangeAddress(curRowIndex-1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);}}}}

4、使用

//获取数据源，数据源实体类不能包含List、Map等
 List<FirProfessionalByDeptVo> list=(List<FirProfessionalByDeptVo>) stringListMap.get("resultList");//需要合并的列
       List<Integer> mergeColumnIndex= Arrays.asList(newInteger[]{0,1,2,3});//需要从第一行开始，列头第一行int mergeRowIndex=1;
       Set<String> excludeColumnFiledNames=newHashSet<String>();
       excludeColumnFiledNames.add("params");
       excludeColumnFiledNames.add("ratingNum");
       excludeColumnFiledNames.add("remark");
       excludeColumnFiledNames.add("rating");
       ExcelWriter excelWriter= null;try{
           WriteWorkbook writeWorkbook=newWriteWorkbook();
           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=newArrayList<>();
           customWriteHandlerList.add(newCustomCellWriteHandler());
           customWriteHandlerList.add(newExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex));
           writeWorkbook.setCustomWriteHandlerList(customWriteHandlerList);
           excelWriter=newExcelWriter(writeWorkbook);
           WriteSheet writeSheet=newWriteSheet();
           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。 头部相同连续的单元格会自动合并。
     */privatestatic List<List<String>>head(String headTitle){
        List<List<String>> list=newArrayList<List<String>>();
        List<String> head1=newArrayList<String>();
        head1.add(headTitle);
        head1.add("模块");
        List<String> head2=newArrayList<String>();
        head2.add(headTitle);
        head2.add("一级指标");
        List<String> head3=newArrayList<String>();
        head3.add(headTitle);
        head3.add("二级指标");
        List<String> head4=newArrayList<String>();
        head4.add(headTitle);
        head4.add("评分内容");
        List<String> head5=newArrayList<String>