优化

MrShi

2025-08-19 30e858fa504b268b9b436afca0a1259cf6e8c488

 server/src/main/java/com/doumee/core/annotation/excel/ExcelImporter.java

@@ -1,212 +1,394 @@
package com.doumee.core.annotation.excel;

import com.doumee.core.constants.ResponseStatus;
import com.doumee.core.exception.BusinessException;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.annotation.AnnotationConfigurationException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.*;

/**
 * Excel导入实现
 * @author  dm
 * @since 2025/03/31 16:44
 * 导入Excel文件（支持“XLS”和“XLSX”格式）
 */
public class ExcelImporter<T> {
public class ExcelImporter {

    private Class<T> modelClass;

    private ExcelImporter () {}
    private static Logger log = LoggerFactory.getLogger(ExcelImporter.class);

    /**
     * 构造ExcelImporter对象
     *
     * @param modelClass 实体Class对象
     * @return ExcelImporter实例
     * 工作薄对象
     */
    public static <T> ExcelImporter<T> build(Class<T> modelClass) {
        ExcelImporter<T> excelImporter = new ExcelImporter<>();
        excelImporter.modelClass = modelClass;
        return excelImporter;
    private Workbook wb;

    /**
     * 工作表对象
     */
    private Sheet sheet;

    /**
     * 标题行号
     */
    private int headerNum;
    /**
     * 标题行号
     */
    private CellType changeType;

    /**
     * 构造函数
     * @param--path 导入文件，读取第一个工作表
     * @param headerNum 标题行号，数据行号=标题行号+1
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ExcelImporter(String fileName, int headerNum)
        throws InvalidFormatException, IOException {
        this(new File(fileName), headerNum);
    }

    /**
     * 导入数据
     *
     * @param is 输入流
     * @param callback 回调
     * @param sync 是否同步已存在数据
     * @return 导入成功数
     * 构造函数
     * @param--path 导入文件对象，读取第一个工作表
     * @param headerNum 标题行号，数据行号=标题行号+1   (eg: 1)
     * @throws InvalidFormatException
     * @throws IOException
     */
    public int importData (InputStream is, ExcelImportCallback<T> callback, boolean sync) {
        return this.importData(is, 0, callback, sync);
    public ExcelImporter(File file, int headerNum)
        throws InvalidFormatException, IOException {
        this(file, headerNum, 0);
    }

    /**
     * 导入数据
     *
     * @param is 输入流
     * @param sheetIndex sheet坐标
     * @param callback 回调
     * @param sync 是否同步已存在数据
     * @return 导入成功数
     * 构造函数
     * @param--path 导入文件
     * @param headerNum 标题行号，数据行号=标题行号+1
     * @param sheetIndex 工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public int importData (InputStream is, int sheetIndex, ExcelImportCallback<T> callback, boolean sync) {
        try {
            Workbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            // 获取列信息
            List<ColumnInfo> columns = this.getColumns();
            List<T> data = new ArrayList<>();
            // 循环获取excel行记录
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                // 构造数据实例对象
                T instance = modelClass.newInstance();
                Row row = sheet.getRow(i);
                // 循环获取单元格信息
                for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
                    Cell cell = row.getCell(columnIndex);
                    if (cell == null) {
                        continue;
                    }
                    if (StringUtils.isBlank(cell.toString())) {
                        continue;
                    }
                    ColumnInfo columnInfo = columns.get(columnIndex);
                    if (columnInfo == null) {
                        break;
                    }
                    // 写入对象属性
                    columnInfo.getField().setAccessible(Boolean.TRUE);
                    columnInfo.getField().set(instance, this.getCellValue(cell, columnInfo));
                    columnInfo.getField().setAccessible(Boolean.FALSE);
                }
                // 如果是空行则结束行读取
                if (this.isEmptyRow(instance)) {
                    break;
                }
                data.add(instance);
            }
            // 执行回调函数
            return callback.callback(data, sync);
        } catch (Exception e) {
            throw new BusinessException(ResponseStatus.IMPORT_EXCEL_ERROR, e);
        }  finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
    public ExcelImporter(String fileName, int headerNum, int sheetIndex)
        throws InvalidFormatException, IOException {
        this(new File(fileName), headerNum, sheetIndex);
    }

    /**
     * 构造函数
     * @param--path 导入文件对象
     * @param headerNum 标题行号，数据行号=标题行号+1
     * @param sheetIndex 工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ExcelImporter(File file, int headerNum, int sheetIndex)
        throws InvalidFormatException, IOException {
        this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
    }

    /**
     * 构造函数 (重要)
     * @param--file 导入文件对象
     * @param headerNum 标题行号，数据行号=标题行号+1
     * @param sheetIndex 工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ExcelImporter(MultipartFile multipartFile, int headerNum, int sheetIndex)
        throws InvalidFormatException, IOException {
        this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
    }
    /**
     * 构造函数 (重要)
     * @param--file 导入文件对象
     * @param headerNum 标题行号，数据行号=标题行号+1
     * @param sheetIndex 工作表编号
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ExcelImporter(MultipartFile multipartFile, int headerNum, int sheetIndex, CellType cellType)
        throws InvalidFormatException, IOException {
        this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex,cellType);
    }

    /**
     * 构造函数 （）
     * @param--path 导入文件对象
     * @param headerNum 标题行号，数据行号=标题行号+1
     * @param sheetIndex 工作表编号 (eg: 0)
     * @throws InvalidFormatException
     * @throws IOException
     */
    public ExcelImporter(String fileName, InputStream in, int headerNum, int sheetIndex)
        throws InvalidFormatException, IOException {
        if (StringUtils.isBlank(fileName)){
            throw new RuntimeException("导入文档为空!");
        }else if(fileName.toLowerCase().endsWith("xls")){
            this.wb = new HSSFWorkbook(in);
        }else if(fileName.toLowerCase().endsWith("xlsx")){
            this.wb = new XSSFWorkbook(in);
        }else{
            throw new RuntimeException("文档格式不正确!");
        }
        if (this.wb.getNumberOfSheets()<sheetIndex){
            throw new RuntimeException("文档中没有工作表!");
        }
        this.sheet = this.wb.getSheetAt(sheetIndex);
        this.headerNum = headerNum;
        log.debug("Initialize success.");
    }
    public ExcelImporter(String fileName, InputStream in, int headerNum, int sheetIndex,CellType cellType)
        throws InvalidFormatException, IOException {
        if (StringUtils.isBlank(fileName)){
            throw new RuntimeException("导入文档为空!");
        }else if(fileName.toLowerCase().endsWith("xls")){
            this.wb = new HSSFWorkbook(in);
        }else if(fileName.toLowerCase().endsWith("xlsx")){
            this.wb = new XSSFWorkbook(in);
        }else{
            throw new RuntimeException("文档格式不正确!");
        }
        if (this.wb.getNumberOfSheets()<sheetIndex){
            throw new RuntimeException("文档中没有工作表!");
        }
        this.sheet = this.wb.getSheetAt(sheetIndex);
        this.headerNum = headerNum;
        this.changeType = cellType;
        log.debug("Initialize success.");
    }



    /**
     * 获取行对象
     * @param rownum
     * @return
     */
    public Row getRow(int rownum){
        return this.sheet.getRow(rownum);
    }

    /**
     * 获取Cell值
     *
     * @param cell 单元格对象
     * @param columnInfo 列配置
     * 获取数据行号
     * @return
     */
    public int getDataRowNum(){
        return headerNum+1;
    }

    /**
     * 获取最后一个数据行号
     * @return
     */
    public int getLastDataRowNum(){
        return this.sheet.getLastRowNum()+headerNum;
    }

    /**
     * 获取最后一个列号
     * @return
     */
    public int getLastCellNum(){
        return this.getRow(headerNum).getLastCellNum();
    }

    /**
     * 获取单元格值
     * @param row 获取的行
     * @param column 获取单元格列号
     * @return 单元格值
     */
    private Object getCellValue (Cell cell, ColumnInfo columnInfo) {
        CellType cellType = cell.getCellType();
        Type fieldType = columnInfo.getField().getGenericType();
        Object value = null;
        if (fieldType.getTypeName().equals("java.util.Date")) {
            value = cell.getDateCellValue();
        } else if (cellType.equals(CellType.NUMERIC)) {
            value = cell.getNumericCellValue();
        } else if (cellType.equals(CellType.STRING)) {
            value = cell.getStringCellValue();
        } else if (cellType.equals(CellType.BOOLEAN)) {
            value = cell.getBooleanCellValue();
        } else if (cellType.equals(CellType.BLANK)) {
            value = "";
        } else if (cellType.equals(CellType.ERROR)) {
            value = cell.getErrorCellValue();
        }
        // 调用转换器
        if (!columnInfo.columnConfig.converter().equals(ExcelDataConverterAdapter.class)) {
            try {
                Object instance = columnInfo.columnConfig.converter().newInstance();
                Method convertMethod = columnInfo.columnConfig.converter().getMethod("convert", Object[].class);
                List<Object> args = new ArrayList<>();
                args.add(value);
                for (String arg : columnInfo.columnConfig.args()) {
                    args.add(arg);
    public Object getCellValue(Row row, int column){
        Object val = "";
        try{
            Cell cell = row.getCell(column);
            if (cell != null){
                if (cell.getCellType() == CellType.NUMERIC){
                    cell.setCellType(CellType.STRING); // 确保单元格类型为字符串
                    val = cell.getStringCellValue();
                }else if (cell.getCellType() == CellType.STRING){
                    val = cell.getStringCellValue();
                }else if (cell.getCellType() == CellType.FORMULA){
                    val = cell.getCellFormula();
                }else if (cell.getCellType() == CellType.BOOLEAN){
                    val = cell.getBooleanCellValue();
                }else if (cell.getCellType() == CellType.ERROR){
                    val = cell.getErrorCellValue();
                }
                value = convertMethod.invoke(instance, new Object[]{args.toArray()});
            } catch (Exception e) {
                throw new IllegalStateException("EVA: can not convert data by " + columnInfo.columnConfig.converter(), e);
            }
        }catch (Exception e) {
            return val;
        }
        return value;
        return val;
    }

    /**
     * 获取列集合
     * 获取导入数据列表
     * @param cls 导入对象类型
     * @param groups 导入分组  可为空
     */
    private List<ColumnInfo> getColumns () {
        Map<Integer, ColumnInfo> sortedFields = new TreeMap<>();
        Field[] fields = modelClass.getDeclaredFields();
        int index = 0;
        for (Field field : fields) {
            ExcelImportColumn excelColumn = field.getAnnotation(ExcelImportColumn.class);
            if (excelColumn == null) {
    public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
        List<Object[]> annotationList = new ArrayList<>();
        // Get annotation field
        Field[] fs = cls.getDeclaredFields();
        for (Field f : fs){
            ExcelColumn ef = f.getAnnotation(ExcelColumn.class);
            if (ef != null && (ef.type()==0 || ef.type()==2)){
                if (groups!=null && groups.length>0){
                    boolean inGroup = false;
                    for (int g : groups){
                        if (inGroup){
                            break;
                        }
                        for (int efg : ef.groups()){
                            if (g == efg){
                                inGroup = true;
                                annotationList.add(new Object[]{ef, f});
                                break;
                            }
                        }
                    }
                }else{
                    annotationList.add(new Object[]{ef, f});
                }
            }
        }
        // Get annotation method
        Method[] ms = cls.getDeclaredMethods();
        for (Method m : ms){
            ExcelColumn ef = m.getAnnotation(ExcelColumn.class);
            if (ef != null && (ef.type()==0 || ef.type()==2)){
                if (groups!=null && groups.length>0){
                    boolean inGroup = false;
                    for (int g : groups){
                        if (inGroup){
                            break;
                        }
                        for (int efg : ef.groups()){
                            if (g == efg){
                                inGroup = true;
                                annotationList.add(new Object[]{ef, m});
                                break;
                            }
                        }
                    }
                }else{
                    annotationList.add(new Object[]{ef, m});
                }
            }
        }
        // Field sorting
        Collections.sort(annotationList, (o1, o2) -> {
            int index1 =((ExcelColumn)o1[0]).index(), index2=((ExcelColumn)o2[0]).index();
            if(index1 == -1){
                index1  = annotationList.size()+99999;
            }
            if(index2 == -1){
                index2  = annotationList.size()+99999;
            }
            return new Integer(index1).compareTo(new Integer(index2));
        });
        //log.debug("Import column count:"+annotationList.size());
        // Get excel data
        List<E> dataList = new ArrayList<>();
        System.out.println("起始数据行:"+getDataRowNum());
        System.out.println("结束数据行:"+getLastDataRowNum());
        for (int i = this.getDataRowNum(); i <= this.getLastDataRowNum(); i++) {
            E e = (E)cls.newInstance();
            int column = 0;
            Row row = this.getRow(i);
            if (Objects.isNull(row)){
                continue;
            }
            if (sortedFields.get(excelColumn.index()) != null) {
                throw new AnnotationConfigurationException("EVA: Excel column contains the same index.");
            StringBuilder sb = new StringBuilder();
            for (Object[] os : annotationList){
                Object val = this.getCellValue(row, column++);
                if (val != null){
                    ExcelColumn ef = (ExcelColumn)os[0];
                    // Get param type and type cast
                    Class<?> valType = Class.class;
                    if (os[1] instanceof Field){
                        valType = ((Field)os[1]).getType();
                    }else if(os[1] instanceof Method){
                        Method method = ((Method)os[1]);
                        if ("get".equals(method.getName().substring(0, 3))){
                            valType = method.getReturnType();
                        }else if("set".equals(method.getName().substring(0, 3))){
                            valType = ((Method)os[1]).getParameterTypes()[0];
                        }
                    }
                    //log.debug("Import value type: ["+i+","+column+"] " + valType);
                    try {
                        if (valType == String.class){
                            String s = String.valueOf(val.toString());
                            if(StringUtils.endsWith(s, ".0")){
                                val = StringUtils.substringBefore(s, ".0");
                            }else{
                                val = String.valueOf(val.toString()).trim();
                            }
                        }
                        else if (valType == Integer.class){
                            val = Double.valueOf(val.toString()).intValue();
                        }else if (valType == Long.class){
                            val = Double.valueOf(val.toString()).longValue();
                        }else if (valType == Double.class){
                            val = Double.valueOf(val.toString());
                        }else if (valType == Float.class){
                            val = Float.valueOf(val.toString());
                        }else if (valType == Date.class){
                            val = DateUtil.getJavaDate((Double)val);
                        }else{
                            if (ef.fieldType() != Class.class){
                                val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
                            }else{
                                val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                    "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
                            }
                        }
                    } catch (Exception ex) {
                        log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
                        val = null;
                    }
                    // set entity value
                    if (os[1] instanceof Field){
                        Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
                    }else if (os[1] instanceof Method){
                        String mthodName = ((Method)os[1]).getName();
                        if ("get".equals(mthodName.substring(0, 3))){
                            mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
                        }
                        Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
                    }
                }
                sb.append(val+", ");
            }
            sortedFields.put(excelColumn.index() == -1 ? index : excelColumn.index(), new ColumnInfo(excelColumn, field));
            index++;
            dataList.add(e);
            log.debug("Read success: ["+i+"] "+sb.toString());
        }
        return new ArrayList<>(sortedFields.values());
        return dataList;
    }

    /**
     * 判断是否为空行
     *
     * @param row 行对象
     * @return Boolean
     *  关闭流 清理临时文件
     */
    private boolean isEmptyRow(Object row) throws IllegalAccessException{
        Field[] fields = row.getClass().getDeclaredFields();
        for (Field field : fields) {
            field.setAccessible(Boolean.TRUE);
            if (field.get(row) != null) {
                field.setAccessible(Boolean.FALSE);
                return Boolean.FALSE;
    public void dispose(){
        try {
            if(wb!=null){
                wb.close();
            }
            field.setAccessible(Boolean.FALSE);
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        return Boolean.TRUE;

    }

    /**
     * 列信息
     */
    @Data
    @AllArgsConstructor
    private static class ColumnInfo {

        // 列配置
        private ExcelImportColumn columnConfig;

        // 字段信息
        private Field field;
    }
}
}