java-classic-code/java-excel/ExcelImportFactory.java

package cn.exlive.exbooter.excel;

import cn.hutool.core.util.ReflectUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <pre>
 *
 * Created by zhenqin.
 * User: zhenqin
 * Date: 2025/2/13
 * Time: 09:17
 * Vendor: yiidata.com
 *
 * </pre>
 *
 * @author zhenqin
 */
@Slf4j
public abstract class ExcelImportFactory<T> {

    /**
     * 返回表头
     * @return
     */
    public abstract Map<Integer, String> getHeaderMap();

    /**
     * 返回数据
     *
     * @param processCallback 进度回调. 返回 0-100 之间的进度条
     * @return
     */
    public abstract List<T> getData(ProcessCallback processCallback);


    /**
     * 获取 实例
     * @param excelFile excel or csv 文件
     * @param headerFieldMapping 文件头对应的映射
     * @return
     */
    public static <T> ExcelImportFactory<T> newInstance(File excelFile,
                                                        Map<String, String> headerFieldMapping,
                                                        Class<T> clazz) {
        try (InputStream in = new FileInputStream(excelFile);) {
            if (excelFile.getName().endsWith(".csv")) {
                return new CsvFactory(in, headerFieldMapping);
            }
            return newInstance(in, headerFieldMapping, clazz);
        } catch (IOException e) {
            throw new IllegalStateException("文件无法读取", e);
        }
    }

    /**
     * 获取 实例
     * @param in excel 文件
     * @param headerFieldMapping 文件头对应的映射
     * @return
     */
    public static <T> ExcelImportFactory<T> newInstance(InputStream in,
                                                        Map<String, String> headerFieldMapping,
                                                        Class<T> clazz) {
        ExcelFactory factory = new ExcelFactory(in, headerFieldMapping, clazz);
        return factory;
    }

    /**
     * Excel 数据导入
     */
    static class ExcelFactory<T> extends ExcelImportFactory<T> {

        /**
         * 转换的目标 类型
         */
        final Class<T> clazz;

        /**
         * 表头
         */
        final Map<String, String> headerAndFieldMapping;

        /**
         * 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
         */
        final OnceReadListener readListener;

        public ExcelFactory(InputStream in, Map<String, String> headerFieldMapping, Class<T> clazz) {
            this.clazz = clazz;
            this.readListener = new OnceReadListener(headerFieldMapping);
            // 这里直接赋值，OnceReadListener 中可能会修改字段
            this.headerAndFieldMapping = headerFieldMapping;
            final ExcelReaderSheetBuilder sheet = EasyExcel.read(in, readListener).sheet();
            sheet.doRead();
        }

        /**
         * 返回表头
         * @return
         */
        @Override
        public Map<Integer, String> getHeaderMap() {
            return readListener.getHeadMap();
        }

        @Override
        public List<T> getData(ProcessCallback processCallback) {
            final Map<Integer, String> headMap = readListener.getHeadMap();
            if(headMap == null) {
                throw new IllegalStateException("无法读取 Excel 表头，非法的 Excel 表格。");
            }
            final Map<Integer, String> headFieldMap = new HashMap<>();
            for (Map.Entry<Integer, String> entry : headMap.entrySet()) {
                final String value = headerAndFieldMapping.get(StringUtils.trimToEmpty(entry.getValue()));
                if(StringUtils.isBlank(value)) {
                    continue;
                }
                headFieldMap.put(entry.getKey(), value);
            }
            log.info("header: {}", headMap);

            // 通过反射，获取 Bean 的字段和字段类型的映射，方便日期类型转换
            Map<String, Field> objClassFields = new HashMap<>();
            final Field[] fields = ReflectUtil.getFields(clazz);
            for (Field field : fields) {
                objClassFields.put(field.getName(), field);
            }

            ObjectMapper mapper = new ObjectMapper();
            final List<Object> list = readListener.getList();
            final List<T> fxaPeople = new ArrayList<>(list.size());
            final int total = list.size();
            int index = 0;
            for (Object o : list) {
                index++;
                // 每 10 条数据，更新一次进度
                if (index % 10 == 0) {
                    processCallback.process((int) (index * 100.0f / total));
                }
                Map<Integer, Object> mapData = (Map) o;
                JSONObject json = new JSONObject();

                // mapData，key 是第 N 列，value 是值
                for (Map.Entry<Integer, Object> entry : mapData.entrySet()) {
                    final String fieldName = headFieldMap.get(entry.getKey());
                    if (StringUtils.isBlank(fieldName)) {
                        // 没有字段名称，需要舍弃的
                        continue;
                    }

                    // 字段和值，放入，值没有转换，如：日期，字典码
                    final Field field = objClassFields.get(fieldName);
                    if(field == null || entry.getValue() == null) {
                        // 如果字段为空，或者值为空，则写入空
                        json.put(fieldName, entry.getValue());
                    } else if(field.getType() == Date.class || field.getType() == java.sql.Date.class ) {
                        // 日期类型
                        final JsonFormat annotation = field.getAnnotation(JsonFormat.class);
                        // System.out.println(fieldName + "    " + annotation + "  " + entry.getValue());
                        if(entry.getValue() instanceof String) {
                            try {
                                String v = (String) entry.getValue();
                                if (v.contains("/")) {
                                    json.put(fieldName, FastDateFormat.getInstance("yyyy/MM/dd").parse(v));
                                } else if (annotation != null) {
                                    final String pattern = annotation.pattern();
                                    json.put(fieldName, FastDateFormat.getInstance(pattern).parse(v));
                                }
                            } catch (ParseException e) {
                                throw new IllegalStateException("第 " + index + " 行，错误的日期格式：" + entry.getValue());
                            }
                        } else if(entry.getValue() instanceof Number) {
                            json.put(fieldName, new Date(((Number)entry.getValue()).longValue()));
                        }
                    } else {
                        json.put(fieldName, entry.getValue());
                    }
                }
                try {
                    T obj = mapper.readValue(json.toJSONString(), clazz);
                    fxaPeople.add(obj);
                } catch (JsonProcessingException e) {
                    throw new IllegalStateException("解析实体异常。", e);
                }
            }
            // 入库量
            log.info("excel data size: {}", fxaPeople.size());
            return fxaPeople;
        }
    }

    /**
     * CSV 数据导入
     *
     * @author zhenqin
     */
    static class CsvFactory extends ExcelImportFactory {

        /**
         * 前端传入的表头
         */
        final Map<String, String> headerAndFieldMapping;

        final InputStream in;

        public CsvFactory(InputStream in, Map<String, String> headerFieldMapping) {
            this.in = in;
            this.headerAndFieldMapping = headerFieldMapping;
        }

        /**
         * 返回表头
         * @return
         */
        @Override
        public Map<Integer, String> getHeaderMap() {
            return null;
        }

        /**
         * 返回数据
         *
         * @param processCallback 进度回调. 返回 0-100 之间的进度条
         * @return
         */
        @Override
        public List<JSONObject> getData(ProcessCallback processCallback) {
            try (CSVReader csvReader = new CSVReaderBuilder(
                    new BufferedReader(
                            new InputStreamReader(in, StandardCharsets.UTF_8))).build();) {
                String[] headTitle = csvReader.readNext();
                List<String> headList = Arrays.asList(headTitle);
                Map<Integer, String> headFieldMap = new HashMap<>();
                for (int i = 0; i < headList.size(); i++) {
                    String titleName = headList.get(i);
                    String value = headerAndFieldMapping.get(StringUtils.trimToEmpty(titleName));
                    if (StringUtils.isBlank(value)) {
                        continue;
                    }
                    headFieldMap.put(i, value);
                }
                log.info("header: {}", headTitle);

                List<String[]> dataList = csvReader.readAll();
                List<JSONObject> fxaPeople = new ArrayList<>(dataList.size());
                final int total = dataList.size();
                int index = 0;
                for (String[] data : dataList) {
                    JSONObject json = new JSONObject();
                    index++;
                    // 每 10 条数据，更新一次进度
                    if (index % 10 == 0) {
                        processCallback.process((int) (index * 100.0f / total));
                    }
                    for (int i = 0; i < data.length; i++) {
                        String fieldName = headFieldMap.get(i);
                        String dataValue = data[i];
                        if (StringUtils.isBlank(fieldName)) {
                            // 没有字段名称，需要舍弃的
                            continue;
                        }

                        json.put(fieldName, dataValue);
                    }

                    fxaPeople.add(json);
                }

                // 入库量
                log.info("csv data size: {}", fxaPeople.size());
                return fxaPeople;
            } catch (Exception e) {
                e.printStackTrace();
                return new ArrayList<JSONObject>();
            }
        }
    }
}