Aspose.cell java文件导入

导入案列
文件下载：
链接：https://pan.baidu.com/s/1vzVxhuaiezoibmKxGtzRsA
提取码：YESH

参考视频
https://www.bilibili.com/video/BV1eL41137Ps/

数据测试

这是demo

 @Test
    public void testImportExcel() throws Exception {
        String[] name = {
                "corp",
                "depart",
                "accountNumber",
                "name",
                "position",
                "pohone",
                "email",
                "date",
        };
        Map<Integer, Set<Integer>> map = new HashMap<>();
        HashSet<Integer> hashSet = new HashSet<Integer>();
        hashSet.add(0);
        map.put(0, hashSet);
        map.put(1, null);
        map.put(2, null);

        List<UserImpTemp> build = new ExcelImportUtil()
                .setExcelFile("E:\\人员导入模板 (1).xls")
                .setClazz(UserImpTemp.class)
                .setAttributes(name)
                .setIgnoreData(map)
                .build();
        //no instance(s) of type variable(s) exist so that UserImpTemp conforms to ExcelData
        System.out.println(build);
    }

这是源码

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author ：yeguojin
 * @Date ：Created in 2021/10/25 16:02
 * @Description：Excel导入工具类 使用aspose.cell 插件
 */
public class ExcelImportUtil {


    private final static String STRING = "String";
    private final static String INT = "int";
    private final static String INTEGER = "Integer";
    private final static String LONG = "Long";
    private final static String DOUBLE = "Double";
    private final static String SHORT = "Short";
    private final static String FLOAT = "Float";
    private final static String BOOLEAN = "Boolean";
    private final static String DATE = "Date";
    private final static String BIGDECIMAL = "BigDecimal";

    /**
     * 全角空格
     */
    private final static String FULL_WIDTH_SPACE = "　";

    /**
     * 半角空格
     */
    private final static String HALF_WIDTH_SPACE = " ";

    /**
     * 年月日
     */
    private final static String YEAR_MONTH_DAY = "yyyy-MM-dd";

    /**
     * 空串
     */
    private final static String EMPTY_STRING = "";

    /**
     * 类型转换出错
     */
    private final static String TYPE_CONVERSION_ERROR = "字符串转换没有对应类型";

    /**
     * 英文逗号
     */
    private final static String COMMA = ",";

    /**
     * 传入 输入的excel、需要转换的数据class、忽略的数据、属性对应关系 名称
     */
    private Workbook workbook;

    private Class<? extends ExcelData> clazz;

    private Map<Integer, Set<Integer>> ignoreData;

    private String[] attributes;


    private Field[] fields;


    public ExcelImportUtil setExcelFile(String path) throws Exception {
        if (this.workbook == null) {
            this.workbook = new Workbook(path);
        }
        return this;
    }

    public ExcelImportUtil setExcelFile(Workbook workbook) throws Exception {
        if (this.workbook == null) {
            this.workbook = workbook;
        }
        return this;
    }

    public ExcelImportUtil setExcelFile(InputStream inputStream) throws Exception {
        if (this.workbook == null) {
            this.workbook = new Workbook(inputStream);
        }
        return this;
    }

    public ExcelImportUtil setClazz(Class<? extends ExcelData> t) {
        this.clazz = t;
        return this;
    }

    public ExcelImportUtil setIgnoreData(Map<Integer, Set<Integer>> ignoreData) {
        this.ignoreData = ignoreData;
        return this;
    }

    public ExcelImportUtil setAttributes(String[] attributes) {
        this.attributes = attributes;
        return this;
    }

    public <T extends ExcelData> List<T> build() throws Exception {
        if (this.workbook == null) {
            throw new RuntimeException("workbook is null!");
        }
        if (this.clazz == null) {
            throw new RuntimeException("clazz is null!");
        }
        if (this.ignoreData == null) {
            throw new RuntimeException("ignoreData is null!");
        }
        if (this.attributes == null) {
            throw new RuntimeException("attributes is null!");
        }
        //初始化 fields
        this.fields = new Field[attributes.length];
        for (int i = 0; i < attributes.length; i++) {
            this.fields[i] = clazz.getField(attributes[i]);
        }

        return excelToObjList(this.workbook, this.clazz, this.ignoreData, this.fields);
    }

    /**
     * @param workbook   工作薄
     * @param clazz      泛型
     * @param ignoreData 忽略数据
     * @param fields     属性
     * @param <T>        泛型
     * @return
     */
    private <T extends ExcelData> List<T> excelToObjList(Workbook workbook,
                                                         Class<? extends ExcelData> clazz,
                                                         Map<Integer, Set<Integer>> ignoreData,
                                                         Field[] fields) throws Exception {


        Set<Integer> sheetIgnore = ignoreData.keySet();
        //第一列数据对应 attributes 的第一个数据
        //遍历工作薄
        List<T> tList = new ArrayList<>();
        for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
            Set<Integer> rowsIgnore = ignoreData.get(Integer.valueOf(i));
            System.out.println(rowsIgnore);
            Cells cells = workbook.getWorksheets().get(i).getCells();
            //如果 sheetIgnore 包含 表示需要忽略 某些行 或者 整个sheet
            //如果 sheetIgnore 不包含 表示这个sheet 都不需要忽略 需要从第一行（row=0）开始 转换数据
            //忽略整个sheet
            if (sheetIgnore.contains(i) && rowsIgnore == null) {
                continue;
            }

            //如果工作薄不考虑录入数据
            boolean flag = rowsIgnore != null;
            Worksheet worksheet = workbook.getWorksheets().get(i);
            String sheetName = worksheet.getName();
            int rowCount = worksheet.getCells().getMaxDisplayRange().getRowCount();
            int columnCount = worksheet.getCells().getMaxDisplayRange().getColumnCount();

            //遍历一行
            for (int m = 0; m < rowCount; m++) {
                if (flag && rowsIgnore.contains(m)) {
                    continue;
                }
                if (cells.getRows().get(m).isBlank()) {
                    continue;
                }
                T t = (T) clazz.newInstance();

                //遍历该行整列
                for (int n = 0; n < columnCount; n++) {
                    Cell cell = cells.get(m, n);
                    fields[n].set(t, getValue(cell.getStringValue(), fields[n]));

                    clazz.getMethod("setSheetNum", int.class).invoke(t, i);
                    clazz.getMethod("setSheetName", String.class).invoke(t, sheetName);
                    clazz.getMethod("setColumn", int.class).invoke(t, m);
                    clazz.getMethod("setRow", int.class).invoke(t, n);
                }
                tList.add(t);
            }
        }
        return tList;
    }

    /**
     * 值的转化 将excel导入的字符串转换为int Long这一类型 并返回
     *
     * @param str   excel导入的字符串
     * @param field 反射获取的javabean的field
     * @return excel导入的字符串转换为对应javabean成员的类型
     * @throws Exception
     */
    private Object getValue(String str, Field field) throws Exception {
        if (str == null) {
            return null;
        }
        str = strFormat(str);
        if (EMPTY_STRING.equals(str)) {
            return null;
        }
        String typeName = field.getType().getSimpleName();
        if (STRING.equalsIgnoreCase(typeName)) {
            return str;
        }
        if (INT.equalsIgnoreCase(typeName) || INTEGER.equalsIgnoreCase(typeName)) {
            return Integer.valueOf(str);
        }
        if (LONG.equalsIgnoreCase(typeName)) {
            return Long.valueOf(str);
        }
        if (DOUBLE.equalsIgnoreCase(typeName)) {
            return Double.valueOf(str);
        }
        if (SHORT.equalsIgnoreCase(typeName)) {
            return Short.valueOf(str);
        }
        if (FLOAT.equalsIgnoreCase(typeName)) {
            return Float.valueOf(str);
        }
        if (BOOLEAN.equalsIgnoreCase(typeName)) {
            return Boolean.valueOf(str);
        }
        if (DATE.equalsIgnoreCase(typeName)) {
            return getStrToDate(str);
        }
        if (BIGDECIMAL.equalsIgnoreCase(typeName)) {
            return new BigDecimal(str);
        }
        throw new ParseException(TYPE_CONVERSION_ERROR, 0);
    }

    /**
     * 将str转换为date格式
     *
     * @param str 日期格式字符串
     * @return str--》date
     * @throws ParseException
     */
    private Date getStrToDate(String str) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat(YEAR_MONTH_DAY);
        return sdf.parse(str);
    }

    private String strFormat(String str) {
        /*去掉所有空格 （全角　和半角 空格），去掉所有逗号*/
        return str.replaceAll(HALF_WIDTH_SPACE, EMPTY_STRING).replaceAll(FULL_WIDTH_SPACE, EMPTY_STRING).replaceAll(COMMA, EMPTY_STRING);
    }

}

/**
 * @Author ：yeguojin
 * @Date ：Created in 2021/11/3 15:19
 * @Description：excel 简单格式数据对象
 * @Modified By：
 */
public class ExcelData {
    private int sheetNum;
    private String sheetName;
    private int column;
    private int row;

    public int getSheetNum() {
        return sheetNum;
    }

    public void setSheetNum(int sheetNum) {
        this.sheetNum = sheetNum;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public int getColumn() {
        return column;
    }

    public void setColumn(int column) {
        this.column = column;
    }

    public int getRow() {
        return row;
    }

    public void setRow(int row) {
        this.row = row;
    }

    @Override
    public String toString() {
        return "ExcelData{" +
                "sheetNum=" + sheetNum +
                ", sheetName='" + sheetName + '\'' +
                ", column=" + column +
                ", row=" + row +
                '}';
    }
}