SpringBoot自定义注解导出Excel

先定义一个注解

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;

@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelHander {
    String value() default "";
    String linkFiled() default "";
    CellType cellType() default CellType.STRING;
}

导出工具类

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.lazydong.pgsql.ExcelHander;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.BiFunction;
import java.util.function.Function;

@SuppressWarnings({"unchecked", "rawtypes"})
public class ExcelUtil {
    private XSSFWorkbook xssfWorkbook;
    private String excelName;
    private XSSFCellStyle defHanderCellStyle;
    private XSSFCellStyle defContentCellStyle;
    private final List<ExcelBuilder> excelBuilderList = new ArrayList<>();

    public static ExcelUtil create() {
        ExcelUtil excelUtil = new ExcelUtil();
        excelUtil.xssfWorkbook = new XSSFWorkbook();
        return excelUtil;
    }

    public ExcelUtil setExcelName(String excelName) {
        this.excelName = excelName;
        return this;
    }

    public void write(String path) {
        System.out.println("正在生成" + excelName);
        verifyExcelHander();
        BufferedOutputStream outputStream = FileUtil.getOutputStream(path);
        try {
            defCellStyle(xssfWorkbook);
            for (ExcelBuilder excelBuilder : excelBuilderList) {
                writeSheet(excelBuilder, xssfWorkbook);
            }
            xssfWorkbook.write(outputStream);
        } catch (IOException exception) {
            exception.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException exception) {
                    exception.printStackTrace();
                }
            }
            if (xssfWorkbook != null) {
                try {
                    xssfWorkbook.close();
                } catch (IOException exception) {
                    exception.printStackTrace();
                }
            }
        }
    }

    private void defCellStyle(XSSFWorkbook xssfWorkbook) {
        defHanderCellStyle = xssfWorkbook.createCellStyle();
        defHanderCellStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex());
        defHanderCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        defContentCellStyle = xssfWorkbook.createCellStyle();
    }

    private void verifyExcelHander() {
        for (ExcelBuilder excelBuilder : excelBuilderList) {
            Class aClass = excelBuilder.getDataClass();
            Map<String, Field> otherFieldMap = excelBuilder.getOtherFieldMap();
            List<ExcelHander> columnExcelHander = excelBuilder.getColumnExcelHander();
            List<Field> columnField = excelBuilder.getColumnField();
            Map<String, Field> allFieldMap = ReflectUtil.getFieldMap(aClass);
            boolean hasExcelHander = false;
            Field[] fields = ReflectUtil.getFields(aClass);
            for (Field field : fields) {
                if (!field.isAnnotationPresent(ExcelHander.class)) {
                    continue;
                }
                if (!hasExcelHander) {
                    hasExcelHander = true;
                }
                ExcelHander annotation = field.getAnnotation(ExcelHander.class);
                String linkFiledKey = annotation.linkFiled();
                if (StrUtil.isNotBlank(linkFiledKey)) {
                    if (!allFieldMap.containsKey(linkFiledKey)) {
                        throw new RuntimeException("指定的链接字段有误");
                    }
                    Field linkFiled = allFieldMap.get(linkFiledKey);
                    if (linkFiled.isAnnotationPresent(ExcelHander.class)) {
                        throw new RuntimeException("指定的链接字段上不允许有ExcelHander注解");
                    }
                    otherFieldMap.put(linkFiled.getName(), linkFiled);
                }
                columnExcelHander.add(annotation);
                columnField.add(field);
            }
            if (!hasExcelHander) {
                throw new RuntimeException("没有找到ExcelHander注解");
            }
        }
    }

    private void writeSheet(ExcelBuilder excelBuilder, XSSFWorkbook xssfWorkbook) {
        String sheetName = excelBuilder.getSheetName();
        XSSFSheet xssfSheet = StrUtil.isNotBlank(sheetName)
                ? xssfWorkbook.createSheet(sheetName) : xssfWorkbook.createSheet();
        XSSFRow xssfHanderRow = xssfSheet.createRow(0);
        List<ExcelHander> columnExcelHander = excelBuilder.getColumnExcelHander();
        // 写表头
        for (int columnIndex = 0; columnIndex < columnExcelHander.size(); columnIndex++) {
            XSSFCell xssfRowCell = xssfHanderRow.createCell(columnIndex);
            ExcelHander excelHander = columnExcelHander.get(columnIndex);
            String value = excelHander.value();
            xssfRowCell.setCellStyle(defHanderCellStyle);
            xssfRowCell.setCellType(CellType.STRING);
            xssfRowCell.setCellValue(value);
        }
        // 写内容
        Map<String, Field> otherFieldMap = excelBuilder.getOtherFieldMap();
        List<Field> columnField = excelBuilder.getColumnField();
        List<Object> exportDataList = excelBuilder.getExportDataList();
        // 先判断是否有自定义样式
        Map<String, XSSFCellStyle> fieldCellStyleMap = excelBuilder.getFieldCellStyleMap();
        Map<String, XSSFCellStyle> customCellStyleMap = excelBuilder.getCustomCellStyleMap();
        Map<String, BiFunction<String, Map<String, XSSFCellStyle>, XSSFCellStyle>> customConditionalMap = excelBuilder.getCustomConditionalMap();
        int rowIndex = 1;
        for (Object data : exportDataList) {
            XSSFRow xssfRow = xssfSheet.createRow(rowIndex);
            rowIndex++;
            for (int columnIndex = 0; columnIndex < columnExcelHander.size(); columnIndex++) {
                XSSFCell xssfRowCell = xssfRow.createCell(columnIndex);
                // 单元格判断其他类型
                ExcelHander excelHander = columnExcelHander.get(columnIndex);
                CellType cellType = excelHander.cellType();
                if (Objects.equals(CellType.BLANK, cellType)) {
                    xssfRowCell.setCellType(CellType.BLANK);
                    String linkFiledKey = excelHander.linkFiled();
                    Field linkFiled = otherFieldMap.get(linkFiledKey);
                    Object linkValue = ReflectUtil.getFieldValue(data, linkFiled);
                    XSSFHyperlink hyperlink = xssfWorkbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
                    hyperlink.setAddress(String.valueOf(linkValue));
                    xssfRowCell.setHyperlink(hyperlink);
                } else {
                    xssfRowCell.setCellType(CellType.STRING);
                }
                // 单元格赋值
                Field field = columnField.get(columnIndex);
                String fieldValue = String.valueOf(ReflectUtil.getFieldValue(data, field));
                xssfRowCell.setCellValue(fieldValue);
                // 单元格样式  优先级为，字段统一样式 > 根据字段的值来判断样式
                String fieldName = field.getName();
                if (fieldCellStyleMap.containsKey(fieldName)) {
                    // 这个字段存在 字段统一样式
                    XSSFCellStyle xssfCellStyle = fieldCellStyleMap.get(fieldName);
                    xssfRowCell.setCellStyle(xssfCellStyle);
                    continue;
                }

                if (customCellStyleMap.isEmpty()) {
                    // 根据字段的值来判断样式 设置为默认样式
                    xssfRowCell.setCellStyle(defContentCellStyle);
                    continue;
                }
                // 存在根据字段的值来判断样式
                if (customConditionalMap.containsKey(fieldName)) {
                    BiFunction<String, Map<String, XSSFCellStyle>, XSSFCellStyle> customConditionalBiFunction = customConditionalMap.get(fieldName);
                    XSSFCellStyle cellStyle = customConditionalBiFunction.apply(fieldValue, customCellStyleMap);
                    if (cellStyle != null) {
                        xssfRowCell.setCellStyle(cellStyle);
                        continue;
                    }
                    xssfRowCell.setCellStyle(defContentCellStyle);
                } else {
                    xssfRowCell.setCellStyle(defContentCellStyle);
                }
            }
        }
    }


    public <T> ExcelBuilder<T> sheetBuilder(Class<T> tClass) {
        ExcelBuilder<T> excelBudiler = new ExcelBuilder<>();
        excelBudiler.setExcelUtil(this);
        excelBudiler.setXSSFWorkbook(this.xssfWorkbook);
        excelBudiler.setDataClass(tClass);
        return excelBudiler;
    }

    public static class ExcelBuilder<T> {
        private ExcelUtil excelUtil;
        private XSSFWorkbook xssfWorkbook;
        private Class<T> dataClass;
        private List<T> exportDataList = new ArrayList<>();
        private String sheetName;
        private final List<ExcelHander> columnExcelHander = new ArrayList<>();
        private final List<Field> columnField = new ArrayList<>();
        private final Map<String, Field> otherFieldMap = new HashMap<>();
        // 自定义样式Map
        private final Map<String, XSSFCellStyle> fieldCellStyleMap = new HashMap<>();
        // 自定义样式Map
        private final Map<String, XSSFCellStyle> customCellStyleMap = new HashMap<>();

        private final Map<String, BiFunction<String, Map<String, XSSFCellStyle>, XSSFCellStyle>> customConditionalMap = new HashMap<>();


        private void setExcelUtil(ExcelUtil excelUtil) {
            this.excelUtil = excelUtil;
        }

        private void setXSSFWorkbook(XSSFWorkbook xssfWorkbook) {
            this.xssfWorkbook = xssfWorkbook;
        }

        public void setDataClass(Class<T> dataClass) {
            this.dataClass = dataClass;
        }

        public ExcelUtil end() {
            excelUtil.excelBuilderList.add(this);
            boolean assignableFrom = ExcelExportModel.class.isAssignableFrom(dataClass);
            if (assignableFrom) {
                // 是接口的实现类，把实现方法也加进来
                ExcelExportModel exportModel = null;
                try {
                    exportModel = (ExcelExportModel) dataClass.newInstance();
                } catch (InstantiationException | IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (exportModel != null) {
                    exportModel.addFieldCellStyle(this.xssfWorkbook, this.fieldCellStyleMap);
                    exportModel.addCustomConditionalCellStyle(this.xssfWorkbook, this.customCellStyleMap);
                    exportModel.addCustomConditionalByFiled(this.customConditionalMap);
                }
            }
            return excelUtil;
        }

        public ExcelBuilder<T> setSheetName(String sheetName) {
            this.sheetName = sheetName;
            return this;
        }

        /**
         * 添加字段样式
         *
         * @param fieldCellStyleFunction 字段样式
         * @return this
         */
        public ExcelBuilder<T> setFieldCellStyleMap(Function<XSSFWorkbook, Map<String, XSSFCellStyle>> fieldCellStyleFunction) {
            Map<String, XSSFCellStyle> apply = fieldCellStyleFunction.apply(this.xssfWorkbook);
            if (apply != null && !apply.isEmpty()) {
                this.fieldCellStyleMap.putAll(apply);
            }
            return this;
        }

        /**
         * 添加自定义条件
         *
         * @param filedName         字段名
         * @param customConditional 自定义条件
         * @return this
         */
        public ExcelBuilder<T> addCustomConditionalByFiled(String filedName, BiFunction<String, Map<String, XSSFCellStyle>,
                XSSFCellStyle> customConditional) {
            this.customConditionalMap.put(filedName, customConditional);
            return this;
        }

        /**
         * 添加自定义样式
         *
         * @param styleKey 自定义样式Key值
         * @param style    自定义样式
         * @return this
         */
        public ExcelBuilder<T> addCustomCellStyle(String styleKey, XSSFCellStyle style) {
            this.customCellStyleMap.put(styleKey, style);
            return this;
        }

        /**
         * 添加自定义样式
         *
         * @param customCellStyleFunction 自定义样式
         * @return this
         */
        public ExcelBuilder<T> setCustomCellStyle(Function<XSSFWorkbook, Map<String, XSSFCellStyle>> customCellStyleFunction) {
            Map<String, XSSFCellStyle> apply = customCellStyleFunction.apply(this.xssfWorkbook);
            if (apply != null && !apply.isEmpty()) {
                this.customCellStyleMap.putAll(apply);
            }
            return this;
        }

        public ExcelBuilder<T> setExportDataList(List<T> exportDataList) {
            this.exportDataList = exportDataList;
            return this;
        }
        
//        -------------------   get方法 ---------------------


        private Class<T> getDataClass() {
            return dataClass;
        }

        private List<T> getExportDataList() {
            return exportDataList;
        }

        private String getSheetName() {
            return sheetName;
        }

        private List<ExcelHander> getColumnExcelHander() {
            return columnExcelHander;
        }

        private List<Field> getColumnField() {
            return columnField;
        }

        private Map<String, Field> getOtherFieldMap() {
            return otherFieldMap;
        }

        private Map<String, XSSFCellStyle> getFieldCellStyleMap() {
            return fieldCellStyleMap;
        }

        private Map<String, XSSFCellStyle> getCustomCellStyleMap() {
            return customCellStyleMap;
        }

        private Map<String, BiFunction<String, Map<String, XSSFCellStyle>, XSSFCellStyle>> getCustomConditionalMap() {
            return customConditionalMap;
        }
    }
}

拓展接口

import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.Map;
import java.util.function.BiFunction;

public interface ExcelExportModel {

    /**
     * 设置字段统一样式
     *
     * @param xssfWorkbook xssfWorkbook
     * @param fieldCellStyle String 字段，  XSSFCellStyle 样式
     */
    default void addFieldCellStyle(XSSFWorkbook xssfWorkbook, Map<String, XSSFCellStyle> fieldCellStyle) {
    }

    /**
     * 设置自定义条件样式
     *
     * @param xssfWorkbook xssfWorkbook
     * @param customCellStyle  String样式Key值，XSSFCellStyle 样式
     */
    default void addCustomConditionalCellStyle(XSSFWorkbook xssfWorkbook, Map<String, XSSFCellStyle> customCellStyle) {
    }

    /**
     * 自定义条件
     * 优先级 高于 getCustomConditionalCellStyle
     * 需要基于 addCustomConditionalCellStyle 方法实现
     *
     * @param filed2conditionalMap String字段， BiFunction自定义条件
     */
    default void addCustomConditionalByFiled(Map<String, BiFunction<String, Map<String, XSSFCellStyle>, XSSFCellStyle>> filed2conditionalMap) {
    }

    /**
     * 获取自定义条件样式
     * 需要基于 addCustomConditionalCellStyle 方法实现
     *
     * @param filedName 字段名
     * @param value 字段值
     * @param cellStyleMap 自定义样式Map
     * @return cellStyle
     */
    default XSSFCellStyle getCustomConditionalCellStyle(String filedName, String value, Map<String, XSSFCellStyle> cellStyleMap) {
        return null;
    }
}