导出Excel文件的通用类

本文记录一种通用的导出Excel文件的通用类。

---

1.在pom.xml中添加POI相关依赖

        <!-- POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>

2.Excel字段注解类

package com.example.study.annotation;

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

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {
    /**
     * 排序，该字段值越小，排序越前
     */
    int ordinal() default 0;

    /**
     * 列宽，取值范围0-255，默认16，当列宽<0或>255时，使用默认列宽
     */
    int columnWidth() default 16;

    /**
     * 字段名
     */
    String columnName();

    /**
     * 时间类型的格式化，目前支持Date, LocalDate, LocalTime, LocalDateTime四种时间类型
     */
    String format() default "yyyy-MM-dd HH:mm:ss";
}

3.Excel导出类

package com.example.study.util;

import com.example.study.annotation.ExcelField;
import lombok.Builder;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAccessor;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Optional;

/**
 * 导出Excel
 */
@Slf4j
public class ExcelUtil {
    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection) {
        return write(collection, null, null);
    }

    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param clazz      数据实体类的class
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection, Class<?> clazz) {
        return write(collection, clazz, null);
    }

    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param sheetName  工作表表名
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection, String sheetName) {
        return write(collection, null, sheetName);
    }

    /**
     * 导出excel文件
     *
     * @param collection 数据集合
     * @param clazz      数据实体类的class
     * @param sheetName  工作表表名
     * @param <T>        数据实体类
     * @return workbook对象
     */
    public static <T> XSSFWorkbook write(Collection<T> collection, Class<?> clazz, String sheetName) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(StringUtils.hasText(sheetName) ? sheetName : "Sheet0");
        XSSFCellStyle titleStyle = createCellStyle(workbook);
        List<FieldProperties> properties = null;
        if (clazz != null) {
            properties = getFieldProperties(clazz);
            writeTitle(sheet, properties, titleStyle);
        }
        if (collection == null || collection.isEmpty()) {
            return workbook;
        }
        if (properties == null) {
            Optional<T> any = collection.stream().filter(o -> o != null).findAny();
            if (!any.isPresent()) {
                return workbook;
            }
            properties = getFieldProperties(any.get().getClass());
            writeTitle(sheet, properties, titleStyle);
        }
        try {
            writeData(sheet, properties, collection);
        } catch (IllegalAccessException exception) {
            log.error("throw IllegalAccessException when writeData:{}", exception.getMessage());
        }
        return workbook;
    }

    /**
     * 返回表头的单元格格式
     *
     * @param workbook workbook对象
     * @return 绿色背景的单元格格式
     */
    private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return titleStyle;
    }

    /**
     * 写入数据
     *
     * @param sheet      工作表
     * @param properties 表头
     * @param collection 数据
     * @param <T>        数据实体类
     * @throws IllegalAccessException 访问数据字段值时无访问权限
     */
    private static <T> void writeData(XSSFSheet sheet, List<FieldProperties> properties, Collection<T> collection) throws IllegalAccessException {
        int rowIndex = 1;
        XSSFRow row;
        for (T element : collection) {
            if (element == null) {
                continue;
            }
            row = sheet.createRow(rowIndex++);
            for (int index = 0; index < properties.size(); index++) {
                FieldProperties property = properties.get(index);
                Object value = property.getField().get(element);
                if (value == null) {
                    continue;
                }
                String type = property.getField().getType().getSimpleName();
                switch (type) {
                    case "Date":
                        SimpleDateFormat format = new SimpleDateFormat(property.getExcelField().format());
                        row.createCell(index).setCellValue(format.format(value));
                        break;
                    case "LocalDate":
                    case "LocalTime":
                    case "LocalDateTime":
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern(property.getExcelField().format());
                        row.createCell(index).setCellValue(formatter.format((TemporalAccessor) value));
                        break;
                    default:
                        row.createCell(index).setCellValue(value.toString());
                }
            }
        }
    }

    /**
     * 写入表头
     *
     * @param sheet      工作表
     * @param properties 表头
     * @param titleStyle 表头格式
     */
    private static void writeTitle(XSSFSheet sheet, List<FieldProperties> properties, XSSFCellStyle titleStyle) {
        XSSFRow row = sheet.createRow(0);
        for (int index = 0; index < properties.size(); index++) {
            ExcelField excelField = properties.get(index).getExcelField();
            String columnName = excelField.columnName();
            XSSFCell cell = row.createCell(index);
            cell.setCellValue(columnName);
            cell.setCellStyle(titleStyle);
            // 设置列宽
            int columnWidth = excelField.columnWidth();
            columnWidth = columnWidth < 0 || columnWidth > 255 ? 16 : columnWidth;
            sheet.setColumnWidth(index, columnWidth * 256);
        }
    }

    /**
     * 获取表头字段
     *
     * @param clazz 数据实体类的class
     * @return 排好序的表头字段
     */
    private static List<FieldProperties> getFieldProperties(Class<?> clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        List<FieldProperties> properties = new ArrayList<>();
        for (Field declaredField : declaredFields) {
            ExcelField excelField = declaredField.getAnnotation(ExcelField.class);
            if (excelField == null) {
                continue;
            }
            declaredField.setAccessible(true);
            FieldProperties property = FieldProperties.builder().excelField(excelField).field(declaredField).build();
            properties.add(property);
        }
        properties.sort((o1, o2) -> o1.getExcelField().ordinal() - o2.getExcelField().ordinal());
        return properties;
    }

    @Getter
    @Setter
    @Builder
    private static class FieldProperties {
        private ExcelField excelField;
        private Field field;
    }
}