导出EXCEL工具类

基于注解设置excel的字段。

使用方法：

// 导出数据
List data = new ArrayList();
String excelName = "name";
ExcelUtil.export(response, data, excelName, true);

ExcelUtil.java

package com.yuwen.common.util;

import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 相柳
 * @date 2021/12/16
 */
public class ExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 导出excel
     *
     * @param response  response
     * @param data      data
     * @param excelName excelName
     * @param noFlag    添加序号的标志：true-添加，false-不添加
     */
    public static <T> void export(HttpServletResponse response, List<T> data, String excelName, boolean noFlag) {
        OutputStream out;
        try {
            // 1.创建excel和sheet
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet();

            if (data.size() != 0) {
                // 2.填充数据
                // 创建一个居中格式
                XSSFCellStyle style = wb.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);

                // 创建表头
                Class<?> dataClass = data.get(0).getClass();
                List<String> head = getHead(dataClass, noFlag);
                XSSFRow row = sheet.createRow(0);
                for (int i = 0; i < head.size(); i++) {
                    XSSFCell cell = row.createCell(i);
                    cell.setCellValue(head.get(i));
                    cell.setCellStyle(style);
                }

                // 2.设置数据
                for (int i = 1; i <= data.size(); i++) {
                    T tar = data.get(i - 1);
                    XSSFRow dataRow = sheet.createRow(i);

                    Field[] fields = dataClass.getDeclaredFields();

                    int limit = 0;
                    if (noFlag) {
                        XSSFCell cell = dataRow.createCell(0);
                        cell.setCellValue(i);
                        cell.setCellStyle(style);
                        limit = 1;
                    }
                    // 通过反射插入数据
                    for (int j = limit; j < fields.length + limit; j++) {
                        XSSFCell cell = dataRow.createCell(j);
                        Field field = fields[j - limit];
                        // 有ExcelCol注解的字段才会被导出
                        if (field.isAnnotationPresent(ExcelCol.class)) {
                            field.setAccessible(true);
                            // 对于时间字段，根据规则格式化字段值
                            if (field.isAnnotationPresent(ExcelDatePattern.class)) {
                                String pattern = field.getAnnotation(ExcelDatePattern.class).pattern();
                                Date obj = (Date) field.get(tar);
                                SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
                                String format = obj == null ? "" : dateFormat.format(obj);
                                cell.setCellValue(format);
                            } else {
                                // 把字段进行convert转换
                                ExcelCol excelCol = field.getAnnotation(ExcelCol.class);
                                ExcelDictConvert convert = excelCol.convert().newInstance();
                                Object key = field.get(tar);
                                cell.setCellValue(key == null ? "" : convert.convert(key.toString()));
                            }
                            // 设置cell的格式
                            cell.setCellStyle(style);
                        }
                    }
                }
            }

            // 3.导出excel
            out = response.getOutputStream();
            // 设置文件名
            String fileName = excelName + ".xlsx";
            // 导出
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
            wb.write(out);
        } catch (Exception e) {
            LOGGER.error("导出" + excelName + "失败", e);
            try {
                out = response.getOutputStream();
                LOGGER.error("导出" + excelName + "失败", e);
                String fileName = "error";
                response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
                out.write(new byte[]{});
            } catch (IOException ignored) {

            }
        }
    }

    /**
     * 获取表头
     *
     * @param cls    cls
     * @param noFlag noFlag
     * @return java.util.List<java.lang.String>
     */
    private static List<String> getHead(Class<?> cls, boolean noFlag) {
        List<String> retList = new ArrayList<>();
        Field[] fields = cls.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(ExcelCol.class)) {
                retList.add(field.getAnnotation(ExcelCol.class).name());
            }
        }
        if (noFlag) {
            retList.add(0, "序号");
        }
        return retList;
    }

}

ExcelDictConvert.java

package com.yuwen.common.util;

/**
 * 字段字段转换接口
 *
 * @author yuwen
 * @date 2022/6/6
 */
public interface ExcelDictConvert {

    /**
     * 把key，转为对应的值
     *
     * @param key key
     * @return java.lang.String
     */
    String convert(String key);
}

ExcelDatePattern.java

package com.yuwen.common.util;

import java.lang.annotation.*;

/**
 * 该注解作用于字段上，配合ExcelUtil使用
 *
 * @author yuwen
 * @date 2022/6/2
 */
@Target(value = ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelDatePattern {
    /**
     * 格式化时间的规则
     *
     * @return java.lang.String
     */
    String pattern();
}

ExcelCol.java

package com.yuwen.common.util;

import java.lang.annotation.*;

/**
 * 该注解作用于字段上，配合ExcelUtil使用
 *
 * @author yuwen
 * @date 2022/6/2
 */
@Target(value = ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCol {

    /**
     * 列名
     *
     * @return java.lang.String
     */
    String name();

    Class<? extends ExcelDictConvert> convert() default DefaultDictConvert.class;
}

DefaultDictConvert.java

package com.yuwen.common.util;

/**
 * @author yuwen
 * @date 2022/6/6
 */
public class DefaultDictConvert implements ExcelDictConvert {
    @Override
    public String convert(String key) {
        return key;
    }
}