EasyExcel 自定义头

应项目需求需要定制化 excel 表格的导出功能样式如下

导出的表格样式
EasyExcel 默认导出样式如下：
默认导出影视

思路将EasyExcel 的头部导出功能舍弃自定义头部布局只保留导出内容的功能

共有样式


import com.alibaba.excel.event.NotRepeatExecutor;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import static cn.ehai.reportingsystem.component.config.FontsStyleName.*;


/**
 * @author 苗小鹏 <a href="xiaopeng.miao@1hai.cn">Contact me.</a>
 * @version 1.0
 * @since 2020/04/03 18:24
 * desc : 在sheet 创建的时候进行初始化
 */
public  class CommonEasyExcelStyle implements SheetWriteHandler, NotRepeatExecutor {

    private HSSFPalette customPalette ;
    /**
     * 共有样式
     */
    private HSSFCellStyle defaultCellStyle;
    /**
     * 公用样式
     */
    public   Map<String, HSSFCellStyle> commonStyle = new HashMap<String, HSSFCellStyle>();
    /**
     * 公用字体
     */
    public   Map<String, HSSFFont> commFont = new HashMap<String, HSSFFont>();

    private Class[] ignore = new Class[]{WriteHandler.class,CellWriteHandler.class};

    /**
     * 这里定义好多有的样式
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (commonStyle.size()==0) {
        WriteSheetHolder writeSheetHolder1 = writeWorkbookHolder.getHasBeenInitializedSheet().get(0);
         Map<Class<? extends WriteHandler>, List<WriteHandler>> writeHandlerMap = writeSheetHolder1.getWriteHandlerMap();
         for (Class item : ignore){
              List<WriteHandler> writeHandlers = writeHandlerMap.get(item);
             final Iterator<WriteHandler> iterator = writeHandlers.iterator();
             while(iterator.hasNext()){
                  WriteHandler next = iterator.next();
                  //排除EasyExcel 的默认样式策略
                 if(next.getClass()== HorizontalCellStyleStrategy.class){
                     iterator.remove();
                 }
             }
         }
        //调整颜色为了多文件下载时默认进行调色
        HSSFWorkbook workbook = (HSSFWorkbook) writeWorkbookHolder.getWorkbook();
        //调整颜色
        changeColor(workbook);
        //初始化字体
        initCommFonts(workbook);
        //初始化common 样式
        defaultCellStyle(workbook);
        //初始化公用样式
        initCommonStyle(workbook);
        }

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
    /**
     * 创建公用字体
     * @param workbook
     */
    public  void initCommFonts(HSSFWorkbook workbook){
        FontsStyleName[] values = values();
        for (FontsStyleName value : values) {
            HSSFFont font = workbook.createFont();
            font.setColor(value.colors);
            font.setBold(value.blod);
            font.setFontName(value.fontName);
            font.setFontHeightInPoints(value.size);
            commFont.put(value.name(),font);
        }
    }
    /**
     * 初始化样式
     * @param workbook
     */
    public void initCommonStyle(HSSFWorkbook workbook){
        CellStyleName[] values = CellStyleName.values();
        for (CellStyleName value : values){
            HSSFCellStyle style = cloneDefaultStyle(workbook,value.center);
            style.setFont(commFont.get(value.fontName));
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(value.backGroundColor);
            commonStyle.put(value.name(),style);
        }
    }

    public void changeColor( HSSFWorkbook workbook){
        //调色板
        ChangeColor[] values = ChangeColor.values();
        customPalette = workbook.getCustomPalette();
        for (ChangeColor value:values){
            changeColor(value.index,value.r,value.b,value.g);
        }
    }
    public  void changeColor(short original, byte red, byte green, byte blue){
        customPalette.setColorAtIndex(original,red , green, blue);
    }
    enum ChangeColor{
        /**
         *
         */
        BLUE(IndexedColors.BLUE.index,(byte)0,(byte)127,(byte)214),
        GREY_25_PERCENT(IndexedColors.GREY_25_PERCENT.index,(byte)200,(byte)200,(byte)200),
        GREY_50_PERCENT(IndexedColors.GREY_50_PERCENT.index,(byte)210,(byte)210,(byte)210),
        GREY_80_PERCENT(IndexedColors.GREY_80_PERCENT.index,(byte)230,(byte)230,(byte)230);
        public short index;
        private byte r;
        private byte b;
        private byte g;

        ChangeColor(short index, byte r, byte b, byte g) {
            this.index = index;
            this.r = r;
            this.b = b;
            this.g = g;
        }
    }
    public void defaultCellStyle(HSSFWorkbook workbook){
        defaultCellStyle = workbook.createCellStyle();
        defaultCellStyle.setDataFormat(workbook.createDataFormat().getFormat("@"));
        defaultCellStyle.setBorderTop(BorderStyle.THIN);
        defaultCellStyle.setBorderBottom(BorderStyle.THIN);
        defaultCellStyle.setBorderLeft(BorderStyle.THIN);
        defaultCellStyle.setBorderRight(BorderStyle.THIN);
        defaultCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    }
    public  HSSFCellStyle cloneDefaultStyle(HSSFWorkbook workbook){
        return cloneDefaultStyle(workbook,true);
    }
    public  HSSFCellStyle cloneDefaultStyle(HSSFWorkbook workbook,boolean level){
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.cloneStyleFrom(defaultCellStyle);
        if (level){
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
        }
        return cellStyle;
    }
    public void setBroder(CellRangeAddress cra, Sheet reportingSheet){
        // 下边框
        RegionUtil.setBorderBottom(BorderStyle.THIN, cra, reportingSheet);
        // 左边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cra, reportingSheet);
        // 有边框
        RegionUtil.setBorderRight(BorderStyle.THIN, cra, reportingSheet);
        // 上边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cra, reportingSheet);
    }

    @Override
    public String uniqueValue() {
        return "comm-style";
    }
}

共有头部


import com.alibaba.excel.event.NotRepeatExecutor;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author 苗小鹏 <a href="xiaopeng.miao@1hai.cn">Contact me.</a>
 * @version 1.0
 * @since 2020/04/07 11:22
 * desc : 通用头和筛选条件的行处理
 */
public abstract class CommonHeadWrite implements CellWriteHandler, NotRepeatExecutor {
    protected   CommonEasyExcelStyle commonEasyExcelStyle;
    private boolean excute1 = false;
    //报表名称
    private String titelName ;

    private Map<String,String> headMap;
    /**
     *   横向合并的行数
     */
    private int meger ;
    protected  CommonHeadWrite(String titelName, Map<String,String> headMap,int meger,CommonEasyExcelStyle commonEasyExcelStyle){
        this.headMap=headMap;
        this.meger=meger;
        this.titelName=titelName;
        this.commonEasyExcelStyle = commonEasyExcelStyle;
    }

    @Override
    public String uniqueValue() {
        return "common-head-write";
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, int relativeRowIndex, boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, int relativeRowIndex, boolean isHead) {
        //基本的头内容
        if(!excute1){
            cell.setCellStyle(commonEasyExcelStyle.commonStyle.get(CellStyleName.SIXTEEN_Y_WHITE_BLUE.name()));
            cell.setCellValue(titelName);
            cell.getRow().setHeightInPoints(37);
            Sheet sheet = writeSheetHolder.getSheet();
            sheet.setDefaultColumnWidth(14);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,meger));
            Set<Map.Entry<String, String>> entries = headMap.entrySet();
            relativeRowIndex++;
            for (Map.Entry<String, String> target: entries) {
                Row createRow = sheet.createRow(relativeRowIndex);
                createRow.setHeightInPoints(17);
                Cell createCell =createRow.createCell(0);
                createCell.setCellValue(target.getKey()+"   "+target.getValue());
                createCell.setCellStyle(commonEasyExcelStyle.commonStyle.get(CellStyleName.TEN_Y_BLACK_WHITE_N_CENTER.name()));
                sheet.addMergedRegion(new CellRangeAddress(relativeRowIndex,relativeRowIndex,0,meger));
                commonEasyExcelStyle.setBroder(new CellRangeAddress(relativeRowIndex,relativeRowIndex,0,meger),sheet);
                relativeRowIndex++;
            }
            //创建空白 //添加行空白行
            sheet.createRow(relativeRowIndex).setHeightInPoints(8.5f);
            relativeRowIndex++;
            //头部内容
            head(sheet,relativeRowIndex);
            excute1=true;
        }
        content(cell,isHead);
    }

    protected   int rangAddress(Sheet sheet, List<String> list, int start, int i){
        return  rangAddress(sheet,list,start,0,0,17,i);
    }
    protected  int rangAddress(Sheet sheet,List<String> list,int start,int mergeColumn,int i){
        return rangAddress(sheet,list,start,mergeColumn,0,17,i);

    }
    /**start开始列 mergeColumn合并多少行 mergeRow合并多少列 rowHight行高*/
    private  int rangAddress(Sheet sheet,List<String> list,int start,int mergeColumn,int mergeRow,float rowHight,int i){
        Row row = sheet.createRow(i);

        for (String item : list) {
            row.setHeightInPoints(rowHight);
            Cell cell = row.createCell(start);
            cell.setCellValue(item);
            cell.setCellStyle(commonEasyExcelStyle.commonStyle.get(CellStyleName.TEN_Y_BLACK_GERY_25_PERCENT.name()));
            if(mergeColumn!=0||mergeRow!=0) {
                sheet.addMergedRegion(new CellRangeAddress(i, i + mergeRow, start, start + mergeColumn));
                commonEasyExcelStyle.setBroder(new CellRangeAddress(i, i + mergeRow, start, start + mergeColumn), sheet);
            }
            start+=mergeColumn+1;
        }
        if(mergeRow==0){
            i++;
        }else{
            i+=mergeRow+1;
        }
        list.clear();
        return i;
    }

    /**
     * 具体的头部内容在子类中实现
     * @param sheet
     * @param relativeRowIndex
     */
    protected abstract void head(Sheet sheet,int relativeRowIndex);
    protected abstract void content(Cell cell,boolean isHead);
}

枚举样式


import org.apache.poi.ss.usermodel.IndexedColors;

/**
 * @author 苗小鹏 <a href="xiaopeng.miao@1hai.cn">Contact me.</a>
 * @version 1.0
 * @since 2020/04/07 9:36
 * desc : 字体大小_是否粗体_字体颜色 如果么有就是 false
 */
public enum FontsStyleName {
    /**
     *
     */
    TEN_N_BLACK((short)10,false, IndexedColors.BLACK.index,"宋体"),

    TEN_Y_BLACK((short)10,true, IndexedColors.BLACK.index,"宋体"),

    TEN_N_RED((short)10,false, IndexedColors.RED.index,"宋体"),

    TEN_Y_RED((short)10,true, IndexedColors.RED.index,"宋体"),

    SIXTEEN_Y_WHITE((short)16,true, IndexedColors.WHITE.index,"宋体");

    public Short size;
    public boolean blod;
    public Short colors;
    public String fontName;

    FontsStyleName(Short size, boolean blod, Short colors,String fontName) {
        this.size = size;
        this.blod = blod;
        this.colors = colors;
        this.fontName = fontName;
    }

}

枚举字体


import org.apache.poi.ss.usermodel.IndexedColors;

/**
 * @author 苗小鹏 <a href="xiaopeng.miao@1hai.cn">Contact me.</a>
 * @version 1.0
 * @since 2020/04/07 9:36
 * desc : 字体大小_是否粗体_字体颜色_背景颜色  如果么有就是 false
 */
public enum CellStyleName {
    /**
     * 非粗体 白色背景
     */
    TEN_N_BLACK_WHITE(IndexedColors.WHITE.index,FontsStyleName.TEN_N_BLACK.name(),true),
    /**
     * 粗体 白色
     */
    TEN_Y_BLACK_WHITE(IndexedColors.WHITE.index,FontsStyleName.TEN_Y_BLACK.name(),true),

    /**
     *  红色 白色背景
     */
    TEN_N_RED_WHITE(IndexedColors.WHITE.index,FontsStyleName.TEN_N_RED.name(),true),
    /**
     * 粗体 25 灰度
     */
    TEN_Y_BLACK_GERY_25_PERCENT(IndexedColors.GREY_25_PERCENT.index,FontsStyleName.TEN_Y_BLACK.name(),true),
    /**
     * 红色 粗体 25 灰度
     */
    TEN_Y_RED_GERY_25_PERCENT(IndexedColors.GREY_25_PERCENT.index,FontsStyleName.TEN_Y_RED.name(),true),
    /**
     * 红色 粗体 50 灰度
     */
    TEN_Y_RED_GERY_50_PERCENT(IndexedColors.GREY_50_PERCENT.index,FontsStyleName.TEN_Y_BLACK.name(),true),
    /**
     * 16 号 粗体 蓝色
     */
    SIXTEEN_Y_WHITE_BLUE(IndexedColors.BLUE.index,FontsStyleName.SIXTEEN_Y_WHITE.name(),true),
    /**
     * 10 号 粗体 白色 非居中
     */
    TEN_Y_BLACK_WHITE_N_CENTER(IndexedColors.WHITE.index,FontsStyleName.TEN_Y_BLACK.name(),false);
    public Short backGroundColor;
    public String fontName;
    public boolean center;
    //由于样式需求 需要定义一些样式为非居中的
    CellStyleName(Short backGroundColor, String fontName, boolean center) {
        this.backGroundColor = backGroundColor;
        this.fontName = fontName;
        this.center= center;
    }
}

具体表格的类

import cn.ehai.reportingsystem.component.config.CellStyleName;
import cn.ehai.reportingsystem.component.config.CommonEasyExcelStyle;
import cn.ehai.reportingsystem.component.config.CommonHeadWrite;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 35716 苗小鹏 <a href="xiaopeng.miao@1hai.cn">Contact me.</a>
 * @version 1.0
 * @since 2020/04/07 15:06
 * desc :
 */
public class TenancyManagementHead extends CommonHeadWrite {
    private boolean  language=false;
    private static String  titleName = "表头";
    public TenancyManagementHead(String time, Map<String, String> headMap, int meger, boolean language, CommonEasyExcelStyle commonEasyExcelStyle) {
        super(titleName+time, headMap, meger,commonEasyExcelStyle);
        this.language=language;
    }

    @Override
    protected void head(Sheet sheet, int relativeRowIndex) {
      //自己实现头部的样式
    }

    @Override
    protected void content(Cell cell, boolean isHead) {
    //自己实现内容的样式
        if(!isHead){
            String stringValue = cell.getStringCellValue();
            if (stringValue.contains("-")){
                cell.setCellStyle(commonEasyExcelStyle.commonStyle.get(CellStyleName.TEN_N_RED_WHITE.name()));
            }else{
                cell.setCellStyle(commonEasyExcelStyle.commonStyle.get(CellStyleName.TEN_N_BLACK_WHITE.name()));
            }
        }
    }


}

使用方式

 PoiReadUtils.outEasyExcel(res,"测试导出",ExcelTypeEnum.XLS);
 //共有样式
        CommonEasyExcelStyle commonEasyExcelStyle = new CommonEasyExcelStyle();
        //具体表格的头和内容的策略
        TenancyManagementHead tenancyManagementHead = new TenancyManagementHead(“2019-01-01”, paramName.toMap(),17,false,commonEasyExcelStyle);
        EasyExcel.write(res.getOutputStream(),TenancyManagementInfo.class).excelType(ExcelTypeEnum.XLS)
                .registerWriteHandler(commonEasyExcelStyle).registerWriteHandler(tenancyManagementHead)
                .sheet(0,"测试内容").doWrite(tenancyManagementInfos);