自定义样式EXCEL导出工具

目前是简易版导出工具类，配合easyExcel

实现功能：可根据字段配置条件，根据是否满足条件对单元格或者行进行颜色填充，其他样式可直接扩展，在style创建时把其他样式配置渲染进去就可以，把配置这种最好定义到注解中，避免大量属性设置代码，但目前还未做

1.需要是用的依赖

 <!-- easy-poi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.1.0</version>
        </dependency>

代码实现

1.数据类型枚举

import lombok.AllArgsConstructor;
import lombok.Getter;

@Getter
@AllArgsConstructor
public enum DataTypeEnum {

    STRING("STRING", "字符串"),
    INTEGER("INTEGER", "数字"),
    BOOLEAN("BOOLEAN", "布尔"),
    DATE("DATE", "时间-年月日"),
    DATETIME("DATETIME", "时间-年月日时分秒"),
    ;

    private final String source;
    private final String desc;
}

判断类型枚举

@Getter
@AllArgsConstructor
public enum CompareEnum {

    THAN(">", "大于"),
    LESS("<", "小于"),
    THAN_OR_EQ(">=", "大于等于"),
    LESS_OR_EQ("<=", "小于等于"),
    EQ("=", "等于"),
    NOT_EQ("!=", "不等于"),
    ;

    private final String source;
    private final String desc;
}

填充样式类

@Builder
@Data
public class ExcelCellStyle {
    /**
     * 是否加粗HSSFFont.BOLDWEIGHT_BOLD
     */
    private Integer boldweight;
    /**
     * 字体大小
     */
    private Integer fontSize;
    /**
     * 背景颜色  如 IndexedColors.GREY_40_PERCENT.index
     */
    private Integer foregroundColor;
}

自定义配置类

@Data
public class ExportErrorDataCondition {
    /**
     * 字段类型
     */
    private DataTypeEnum type;

    private String field;

    private CompareEnum compare;
    /**
     * 比较null ="null"
     */
    private Object value;
    /**
     * true 填充行   false填充单元格  只能有一个行条件=true存在，否则样式会被覆盖
     */
    private Boolean fillRow = Boolean.FALSE;

    private ExcelCellStyle style;
}

导出参数类//为了在不需要渲染样式时方便直接使用easyExcel

@Getter
public class EasyExcelParams<T> {

    /**
     * sheet名称
     */
    private String sheetName;

    /**
     * 数据
     */
    private List<T> data;

    /**
     * 数据模型类型
     */
    private Class<T> dataModelClazz;

    /**
     * cell handler
     */
    private List<CellWriteHandler> strategies;

    public EasyExcelParams(String sheetName, List<T> data, Class<T> dataModelClazz) {
        this.sheetName = sheetName;
        this.data = data;
        this.dataModelClazz = dataModelClazz;
    }

    public EasyExcelParams(String sheetName, List<T> data, Class<T> dataModelClazz, CellWriteHandler... strategies) {
        this(sheetName, data, dataModelClazz);
        this.strategies = Arrays.asList(strategies);
    }

    public boolean validate() {
     return StringUtils.isNotBlank(sheetName)
                && Objects.nonNull(dataModelClazz)
                && Objects.nonNull(data);
    }

}

参数扩展类

@Getter
public class EasyExternalExcelParams extends EasyExcelParams {
    private List<ExportErrorDataCondition> conditions;
    private String headContent;

    public EasyExternalExcelParams(String sheetName, List data, Class dataModelClazz) {
        super(sheetName, data, dataModelClazz);
    }

    public EasyExternalExcelParams(String sheetName, List data, Class dataModelClazz, String headContent, ExportErrorDataCondition... conditions) {
        super(sheetName, data, dataModelClazz);
        this.conditions = Arrays.asList(conditions);
        this.headContent = headContent;
    }

    public EasyExternalExcelParams(String sheetName, List data, Class dataModelClazz, String headContent) {
        super(sheetName, data, dataModelClazz);
        this.headContent = headContent;
    }

    public EasyExternalExcelParams(String sheetName, List data, Class dataModelClazz, ExportErrorDataCondition... conditions) {
        super(sheetName, data, dataModelClazz);
        this.conditions = Arrays.asList(conditions);
    }
}

导出工具类

public class ExportUtil {
//默认普通样式
    public CellStyle ordinaryCellStyle;
//错误单元格
    public CellStyle errorCellStyle;
//字段样式
    public Map<String, CellStyle> fieldCellStyle = Maps.newHashMap();
//错误条件容器
    public Map<String, ExportErrorDataCondition> conditionMap = Maps.newHashMap();
//是否填充行
    public Boolean hasFillRow;
//是否填充
    public Boolean fillRow;

//导出工具
    public byte[] export(EasyExternalExcelParams params) throws IOException {
        initData(params);
        SXSSFWorkbook wb = new SXSSFWorkbook();
        createSheet(wb, params.getSheetName(), params.getHeadContent(), params.getData(), params.getDataModelClazz());
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            wb.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            bos.close();
        }
        return bos.toByteArray();
    }
//创建sheet
    public SXSSFSheet createSheet(SXSSFWorkbook workbook, String sheetName, String headContent, List<T> data, Class<T> dataModelClazz) {
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        Map<String, Integer> titleMap = Maps.newHashMap();
        Integer dataStartRow = createTileRow(workbook, sheet, dataModelClazz, titleMap, headContent);
        createDataRow(workbook, sheet, data, titleMap, dataStartRow);
        if (CollectionUtil.isEmpty(data)) {
            return sheet;
        }
        return sheet;
    }

//填充数据
    public void createDataRow(SXSSFWorkbook workbook, SXSSFSheet sheet, List<T> data, Map<String, Integer> titleMap, Integer dataStartRow) {
        if (CollectionUtil.isEmpty(data)) {
            return;
        }
        if (null == ordinaryCellStyle) {
            ordinaryCellStyle = workbook.createCellStyle();
            //颜色要两行
//            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//            style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
            ordinaryCellStyle.setBorderBottom(BorderStyle.THIN);
            ordinaryCellStyle.setBorderLeft(BorderStyle.THIN);
            ordinaryCellStyle.setBorderRight(BorderStyle.THIN);
            ordinaryCellStyle.setBorderTop(BorderStyle.THIN);
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 12);
            ordinaryCellStyle.setFont(font);
            DataFormat format = workbook.createDataFormat();
            ordinaryCellStyle.setDataFormat(format.getFormat("@"));
        }
        for (int i = 0; i < data.size(); i++) {
            createDataRow(workbook, sheet, data.get(i), titleMap, i + dataStartRow);
        }
    }

//填充行数据
    public void createDataRow(SXSSFWorkbook workbook, SXSSFSheet sheet, Object data, Map<String, Integer> titleMap, Integer index) {
        errorCellStyle = null;
        SXSSFRow titleRow = sheet.createRow(index);
        JSONObject dataJson = JSONConvert.fromString(JSONConvert.toString(data), JSONObject.class);
        for (Map.Entry<String, Integer> entry : titleMap.entrySet()) {
            SXSSFCell cell = titleRow.createCell(entry.getValue());
            cell.setCellValue(dataJson.getString(entry.getKey()));
            CellStyle cellStyle = fieldStyle(workbook, entry.getKey(), dataJson.getString(entry.getKey()));
            cell.setCellStyle(null != cellStyle ? cellStyle : ordinaryCellStyle);
        }
        if (null != errorCellStyle) {
            titleMap.values().stream().forEach(i -> {
                titleRow.getCell(i).setCellStyle(errorCellStyle);
            });
        }
    }

//创建标题行
    public Integer createTileRow(SXSSFWorkbook workbook, SXSSFSheet sheet, Class<T> dataModelClazz, Map<String, Integer> titleMap, String headContent) {
        Integer index = 0;
        if (StringUtils.isNotBlank(headContent)) {
            index = 1;
        }
        SXSSFRow titleRow = sheet.createRow(index);
        //设置标题
        Integer size = dataModelClazz.getDeclaredFields().length;
        for (int i = 0; i < size; i++) {
            Field field = dataModelClazz.getDeclaredFields()[i];
            if (null == field.getAnnotation(ExcelProperty.class) || null != field.getAnnotation(ExcelIgnore.class)) {
                size = size - 1;
                i = i - 1;
                continue;
            }
            sheet.setColumnWidth(i, (int) ((25 + 0.72) * 256));
            titleMap.put(field.getName(), i);
            String[] excelProperties = field.getAnnotation(ExcelProperty.class).value();
            SXSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(excelProperties[excelProperties.length - 1]);
            //setCellStyle(cell, workbook, IndexedColors.GREY_40_PERCENT.index);
            CellStyle style = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            style.setDataFormat(format.getFormat("@"));
            //颜色要两行
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.index);
            cell.setCellStyle(style);
            cell.getCellStyle().setBorderBottom(BorderStyle.THIN);
            cell.getCellStyle().setBorderLeft(BorderStyle.THIN);
            cell.getCellStyle().setBorderRight(BorderStyle.THIN);
            cell.getCellStyle().setBorderTop(BorderStyle.THIN);
            Font font = workbook.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            font.setFontHeightInPoints((short) 12);
            cell.getCellStyle().setFont(font);
        }
        if (StringUtils.isNotBlank(headContent)) {
            CellStyle cs = workbook.createCellStyle();
            cs.setWrapText(true);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, size - 1);
            sheet.addMergedRegion(region);
            SXSSFRow headRow = sheet.createRow(0);
            headRow.setHeight((short) ((12 + 0.72) * 256));
            headRow.createCell(0).setCellValue(headContent);
            headRow.setRowStyle(cs);
        }
        return index + 1;

    }

//设置单元格样式
    //IndexedColors
    public CellStyle setCellStyle(SXSSFCell cell, SXSSFWorkbook workbook, short colorIndex, Boolean boldweight) {
        CellStyle style = null != cell.getCellStyle() ? cell.getCellStyle() : workbook.createCellStyle();
        //颜色要两行
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(colorIndex);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 13);
        if (boldweight) {
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        }
        return style;
    }

//字段样式获取
    public CellStyle fieldStyle(SXSSFWorkbook workbook, String field, String value) {
        if (null != errorCellStyle) {
            return errorCellStyle;
        }
        if (estimateCondition(field, value)) {
            if (null != fieldCellStyle.get(field)) {
                if (conditionMap.get(field).getFillRow()) {
                    errorCellStyle = fieldCellStyle.get(field);
                }
                return fieldCellStyle.get(field);
            }
        }

        return fieldConditionStyle(workbook, field, value);
    }

//字段样式条件表达式判断
    public CellStyle fieldConditionStyle(SXSSFWorkbook workbook, String field, String value) {
        CellStyle style = null;
        ExportErrorDataCondition condition = null;
        if (estimateCondition(field, value)) {
            condition = conditionMap.get(field);
        } else {
            return style;
        }
        if (null == condition) {
            return style;
        }
        if (null == style) {
            style = workbook.createCellStyle();
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            DataFormat format = workbook.createDataFormat();
            style.setDataFormat(format.getFormat("@"));

        }
        if (null != style && null != condition.getStyle().getBoldweight() || null != condition.getStyle().getFontSize()) {
            Font font = workbook.createFont();
            font.setBoldweight(null != condition.getStyle().getBoldweight() ? Short.parseShort(condition.getStyle().getBoldweight().toString()) : null);
            font.setFontHeightInPoints(null != condition.getStyle().getFontSize() ? Short.parseShort(condition.getStyle().getFontSize().toString()) : null);
            style.setFont(font);
        }
        if (null != condition.getStyle().getForegroundColor()) {
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setFillForegroundColor(Short.parseShort(condition.getStyle().getForegroundColor().toString()));
        }
        if (condition.getFillRow()) {
            errorCellStyle = style;
            fieldCellStyle.put(field, style);
        }
        return style;
    }


//条件判断
    public Boolean estimateCondition(String field, String value) {
        if (CollectionUtil.isEmpty(conditionMap)) {
            return Boolean.FALSE;
        }
        //单个字段判断
        if ((null == fillRow || !fillRow) && null != conditionMap.get(field)) {
            return estimateDataValue(field, value);
        }
        return Boolean.FALSE;
    }

    /**
     * 比较数据值
     *
     * @param field
     * @param value
     * @return
     */
    public Boolean estimateDataValue(String field, String value) {
        ExportErrorDataCondition condition = conditionMap.get(field);
        switch (condition.getType()) {
            case STRING:
                return estimateString(condition.getCompare(), value, condition.getValue());
            case INTEGER:
                return estimateInteger(condition.getCompare(), value, condition.getValue());
            default:
                return Boolean.FALSE;
        }
    }

    /**
     * 字符串类型判断
     *
     * @param compare
     * @param value1
     * @param value2
     * @return
     */
    public Boolean estimateString(CompareEnum compare, String value1, Object value2) {
        switch (compare) {
            case EQ:
                if (null == value2 || StringUtils.isBlank(value2.toString())) {
                    return StringUtils.isBlank(value1);
                }
                return null != value1 ? value1.equals(value2) : Boolean.FALSE;
            case NOT_EQ:
                if (null == value2 || StringUtils.isBlank(value2.toString())) {
                    return StringUtils.isNotBlank(value1);
                }
                return null != value1 ? !value1.equals(value2) : Boolean.FALSE;
            default:
                return Boolean.FALSE;
        }
    }

    /**
     * 数字类型判断
     *
     * @param compare
     * @param value1
     * @param value2
     * @return
     */
    public Boolean estimateInteger(CompareEnum compare, String value1, Object value2) {
        if (compare != CompareEnum.EQ && null == value2) {
            return Boolean.FALSE;
        }
        switch (compare) {
            case EQ:
                if (null == value2) {
                    return StringUtils.isBlank(value1);
                }
                return null != value1 ? new BigDecimal(value1).compareTo(new BigDecimal(value2.toString())) == 0 : Boolean.FALSE;
            case NOT_EQ:
                return null != value1 ? new BigDecimal(value1).compareTo(new BigDecimal(value2.toString())) != 0 : Boolean.FALSE;
            case THAN:
                return null != value1 ? new BigDecimal(value1).compareTo(new BigDecimal(value2.toString())) > 0 : Boolean.FALSE;
            case THAN_OR_EQ:
                return null != value1 ? new BigDecimal(value1).compareTo(new BigDecimal(value2.toString())) >= 0 : Boolean.FALSE;
            case LESS:
                return null != value1 ? new BigDecimal(value1).compareTo(new BigDecimal(value2.toString())) < 0 : Boolean.FALSE;
            case LESS_OR_EQ:
                return null != value1 ? new BigDecimal(value1).compareTo(new BigDecimal(value2.toString())) <= 0 : Boolean.FALSE;
            default:
                return Boolean.FALSE;
        }
    }


    public void initData(EasyExternalExcelParams params) {
        if (CollectionUtil.isNotEmpty(params.getConditions())) {
            conditionMap = params.getConditions().stream().filter(k -> StringUtils.isNotBlank(k.getField()))
                    .collect(Collectors.toMap(ExportErrorDataCondition::getField, k -> k));
        }
        if (CollectionUtil.isNotEmpty(params.getConditions())) {
            params.getConditions().stream().forEach(d -> {
                if (d.getFillRow()) {
                    hasFillRow = d.getFillRow();
                }
            });
        }
    }

工具类调用

                ExportErrorDataCondition condition = new ExportErrorDataCondition();
                condition.setCompare(CompareEnum.NOT_EQ);
                condition.setType(DataTypeEnum.STRING);
                condition.setField("failReason");
                condition.setFillRow(Boolean.TRUE);
                condition.setStyle(ExcelCellStyle.builder().foregroundColor(Short.toUnsignedInt(IndexedColors.CORAL.index)).build());
                //导出
                RespResult<StructureVO> orgVo = structureClient.findById(LoginContext.getBaseInfo().getOrgId());
                String name = Objects.nonNull(orgVo.getData()) ? orgVo.getData().getName() + "订单数据" : "订单数据";
                EasyExternalExcelParams excelParams = new EasyExternalExcelParams(name, importErrorVOList,
                        OrderImportErrorVO.class, Constant.ORDER_IMPORT_HEAD, condition);
                String url = remoteFileClient.fileUpload(DefaultMultipartFile.build("订单批导错误行", new ExportUtil().export(excelParams), FileTypeEnum.XLSX)).getData();