easyexcel导入excel数据的通用办法_easyexcel从第几行读取数据

一、问题引入

easyexcel官网提供的导入比较麻烦，每个实体都需要注册监听器等等。我这里写了一个通用的处理类。可以简单快速的导入excel数据到数据库中。
getFilesSigle方法传入文件路径，read方法中List list = e.getList(fileToMultipartFile(file), CountEntitySoes.class,1,4); 1代表读取第二个sheet，4代表从第5行开始读取数据。
使用时将insert的mapper注入进来，替换对应的实体类。调用t01Read方法就行。我这里是为了方便，用的junit的test。
其他关于导入时的各种问题，详见官网https://alibaba-easyexcel.github.io/

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
@SpringBootTest
public class SoesTest {

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

    @Resource
    CountEntitySoesMapper countEntitySoesMapper;
    @Test
    public void t01Read() {
        System.out.println("sss");
       ArrayList<ExcelMidBean> excelMidBeans = new ArrayList<>();
            getFilesSigle("C:\\Users\\Administrator\\Desktop\\gangdong-client",excelMidBeans);
        System.out.println(excelMidBeans.size()+"ssss");
        for (ExcelMidBean excelMidBean : excelMidBeans) {
            read(excelMidBean.getFile());
        }

    }


    private void read(File file) {
        ExcelAnalysisHelper e = new ExcelAnalysisHelper();
        //List list = e.getList(FileUtil.fileToMultipartFile(file), CountPatenteeTypeAuthorizationMessage.class);

       List<CountEntitySoes> list = e.getList(fileToMultipartFile(file), CountEntitySoes.class,1,4);
       //setEntityOtherValue(list);
        list.forEach(System.out::println);
      insert(list);

    }
    private void insert(List<CountEntitySoes> ss) {
        ss.forEach(s->{
            countEntitySoesMapper.insert(s);
        });
       // countEntitySoesService.saveBatch(ss,500);
    }
    public static void getFilesSigle(String path, ArrayList<ExcelMidBean> excelMidBeans) {

        File file = new File(path);
        // 如果这个路径是文件夹
        if (file.isDirectory()) {
            // 获取路径下的所有文件
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                // 如果还是文件夹 递归获取里面的文件 文件夹
                if (files[i].isDirectory()) {
                    getFilesSigle(files[i].getPath(),excelMidBeans);
                } else {
                    ExcelMidBean midBean = new ExcelMidBean();
                    midBean.setFile(files[i]);
                    excelMidBeans.add(midBean);
                }
            }

        } else {

            System.out.println("文件：" + file.getPath());

        }
    }
    public static MultipartFile fileToMultipartFile(File file) {
        FileItem fileItem = createFileItem(file);
        MultipartFile multipartFile = new CommonsMultipartFile(fileItem);
        return multipartFile;
    }
    private static FileItem createFileItem(File file) {
        FileItemFactory factory = new DiskFileItemFactory(16, null);
        FileItem item = factory.createItem("textField", "text/plain", true, file.getName());
        int bytesRead = 0;
        byte[] buffer = new byte[8192];
        try {
            FileInputStream fis = new FileInputStream(file);
            OutputStream os = item.getOutputStream();
            while ((bytesRead = fis.read(buffer, 0, 8192)) != -1) {
                os.write(buffer, 0, bytesRead);
            }
            os.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return item;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.util.List;

/**
 * @program: demo-excel
 * @description:
 * @author: chen
 * @create: 2021-06-01 15:26
 **/
public class ExcelAnalysisHelper<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelAnalysisHelper.class);

    public List<T> getList(MultipartFile file, Class<T> clazz) {
        return getList(file, clazz, 0, 1);
    }

    public List<T> getList(MultipartFile file, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        UploadDataListener<T> listener = new UploadDataListener<>(headRowNumber);
        try {
            EasyExcel.read(file.getInputStream(), clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        } catch (IOException e) {
            LOGGER.error(e.getMessage());
        }
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        List<T> data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
        return data;
    }

    /**
     * 处理合并单元格
     *
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @param headRowNumber      起始行
     * @return 填充好的解析数据
     */
    private List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
//        循环所有合并单元格信息
        extraMergeInfoList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
//            获取初始值
            Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
//            设置值
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                    setInitValueToList(initValue, i, j, data);
                }
            }
        });
        return data;
    }

    /**
     * 设置合并单元格的值
     *
     * @param filedValue  值
     * @param rowIndex    行
     * @param columnIndex 列
     * @param data        解析数据
     */
    public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
        T object = data.get(rowIndex);

        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == columnIndex) {
                    try {
                        field.set(object, filedValue);
                        break;
                    } catch (IllegalAccessException e) {
                        //throw new BizException(ResultCode.FAILURE, "解析数据时发生异常!");
                        System.out.println("解析数据时发生异常!");
                    }
                }
            }
        }
    }


    /**
     * 获取合并单元格的初始值
     * rowIndex对应list的索引
     * columnIndex对应实体内的字段
     *
     * @param firstRowIndex    起始行
     * @param firstColumnIndex 起始列
     * @param data             列数据
     * @return 初始值
     */
    private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
        Object filedValue = null;
        T object = data.get(firstRowIndex);
        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == firstColumnIndex) {
                    try {
                        filedValue = field.get(object);
                        break;
                    } catch (IllegalAccessException e) {
                        //throw new BizException(ResultCode.FAILURE, "解析数据时发生异常!");
                        System.out.println("解析数据时发生异常!");
                    }
                }
            }
        }
        return filedValue;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130

import java.io.File;
import java.io.Serializable;

/**
 * @author xchen
 * @create 2021-06-17-15:15
 */
public class ExcelMidBean<T> implements Serializable {


    private File file;

    private Class<T> aClass;

    private T t;

    private String type;

    private String unit;

    private String month;

    private Integer sort;

    private Integer isCity;

    private Integer isArea;

    public ExcelMidBean() {
    }

    public ExcelMidBean(File file, String type, String unit, String month, Integer sort, Integer isCity, Integer isArea,Class<T> c) {
        this.file = file;
        this.type = type;
        this.unit = unit;
        this.month = month;
        this.sort = sort;
        this.isCity = isCity;
        this.isArea = isArea;
        this.aClass=c;
    }

    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getUnit() {
        return unit;
    }

    public void setUnit(String unit) {
        this.unit = unit;
    }

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }

    public Integer getIsCity() {
        return isCity;
    }

    public void setIsCity(Integer isCity) {
        this.isCity = isCity;
    }

    public Integer getIsArea() {
        return isArea;
    }

    public void setIsArea(Integer isArea) {
        this.isArea = isArea;
    }

    public Class<T> getaClass() {
        return aClass;
    }

    public void setaClass(Class<T> aClass) {
        this.aClass = aClass;
    }

    public T getT() {
        try {
            t=aClass.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return t;
    }

    public void setT(T t) {
        this.t = t;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122