EasyExcel实现导入功能_easyexcel导入

我这篇导入文章包含1.下载导入模板 2.导入接口 3.如果导入过程中有数据未导入成功的话则把错误数据存到错误导入模板中然后调用下载错误返回模板查看数据导入失败的信息

1.首先导入依赖

<!-- xls格式excel依赖包 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<!--xlsx格式excel依赖包-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<!-- easyexcel依赖包 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beat1</version>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.首先下载导入模板，ManufacturerManagementVo导入模板对象，可以跟导出用同一个

//下载导入模板
@GetMapping("/download/template/{fileName}")
public void downloadTemplate(@PathVariable String fileName) throws Exception {
        String name = buildResponse(fileName.split("\\."));
   EasyExcel.write(response.getOutputStream(),ManufacturerManagementVo.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(name).doWrite(Collections.emptyList());

}
1
2
3
4
5
6
7

3.创建监听类

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
//监听器，传入导入的对象
public class ManufacturerManagementListener extends AnalysisEventListener<ManufacturerManagementVo> {
    private int sum = 0;
	//记录导入失败的数据信息
    private List<ManufacturerManagementErrorModel> errorDataList = new ArrayList<>(100);
	//转换类
    private ManufacturerManagementConverter converter;
	//构造函数的参数类型,function传入的对象
    private Function<ManufacturerManagement, ServiceResult<Boolean>> function;

//构造函数
    public ManufacturerManagementListener(ManufacturerManagementConverter converter, Function<ManufacturerManagement, ServiceResult<Boolean>> function) {
        this.converter = converter;
        this.function = function;
    }

    @Override
    public void invoke(ManufacturerManagementVo manufacturerManagementVo, AnalysisContext analysisContext) {
        try {
            //将导入模板跟新增类进行转换
            ManufacturerManagement archive = converter.modelToManufa(manufacturerManagementVo);
            function.apply(archive);
            sum++;
        } catch (Exception e) {
            //异常类新增错误信息，将导入对象转换为错误信息对象
            ManufacturerManagementErrorModel error = converter.modelToError(manufacturerManagementVo);
            error.setMessage(e.getMessage());
            errorDataList.add(error);
        }
    }

//读完之后的操作
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
    public int getSum() {
        return sum;
    }
    public List<ManufacturerManagementErrorModel> getErrorDataList() {
        return errorDataList;
    }
}
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

4.业务层调用

//导入数据
@PostMapping("/import")
@ResponseBody
public IResult<Object> importData(MultipartFile file) throws IOException {
    //addManufacturerManagement是业务层新增方法名
        ManufacturerManagementListener listener = new ManufacturerManagementListener(converter, service::addManufacturerManagement);
    //sheet为0表示导入第一页的数据
        EasyExcel.read(file.getInputStream(), ManufacturerManagementVo.class, listener).sheet(0).doRead();
    //判断是否有错误文件，有就导出来
        List<ManufacturerManagementErrorModel> errorModelList = listener.getErrorDataList();
        if (!CollectionUtils.isEmpty(errorModelList)) {
            return new CustomResult<>(0, "IMPORT_SUCCESS", listener.getSum(), initFaultFile(errorModelList));
        }
        return new ServiceResult<>().success(listener.getSum());
    }




---------------自定义的CostomResult结果类
//CustomResult返回类
class CustomResult<DATA> extends Result<DATA>{
    private String fileId;
    
    public CustomResult(int code,String msg,DATA data,String fileId){
        super(code,msg,data);
        this.fileId=fileId;
    }
    
    public String getFileId(){return fileId;}
    
    public CustomResult<DATA> setFileId(String fileId){
        this.fileId=fileId;
        return this;
    }
}


----------
//下载错误文件，如果有错误文件会给前端返回一个fileId，前端判断该参数是否存在，如果存在则下载错误模板
private ExpiryMap<String, List<ManufacturerManagementErrorModel>> expiryFileMap = new ExpiryMap<>(60 * 1500);

-----这个方法将错误信息保存下来
private String initFaultFile(List<ManufacturerManagementErrorModel> errorModelList) {
    String fileId = UUIDUtils.getUUID();
    expiryFileMap.put(fileId, errorModelList);
    return fileId;
}


------------下载错误模板文件
@GetMapping("/downloadErrorFile/{fileId}/{fileName}")
@ApiOperation(value = "根据文件ID 下载错误的数据文件")
public void downloadErrorFile(@PathVariable String fileId, @PathVariable String fileName) throws Exception {
//从存储错误信息的map中把数据取出来，然后再下载导出错误文件模板
List<ManufacturerManagementErrorModel> dataList = expiryFileMap.get(fileId);
if (!CollectionUtils.isEmpty(dataList)) {
    String name = buildResponse(fileName.split("\\."));
    EasyExcel.write(response.getOutputStream(), ManufacturerManagementErrorModel.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(name).doWrite(dataList);
} else {
    MessageUtils.throwMsg("FILE NOT EXISTS");
}

}


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

5.创建转换类对象

import org.mapstruct.Mapper

@Mapper(componentModel="spring")
public interface ManufacturerManagementConverter {
	//对象转userEx
	ManufacturerManagement modelToManufa(ManufacturerManagementVo vo);

	ManufacturerManagementErrorModel modelToError(ManufacturerManagementVo vo);
}
1
2
3
4
5
6
7
8
9

6.错误模板对象，以及导入模板对象