easyExcel导入

controller

    @PostMapping("/import")
    public AjaxResult import(MultipartFile file) {
   
        PlantExcelImporter plantExcelImporter = new PlantExcelImporter(ptPlanService);
        EasyExcelUtil.save(file, plantExcelImporter, PtPlanExcel.class);
        return AjaxResult.success("导入成功");
    }
1
2
3
4
5
6
7

PlantExcelImporter

import com.bjsasc.transferasset.adjust.domain.PtPlanAdjustExcel;
import com.bjsasc.transferasset.adjust.service.IPtPlanAdjustService;
import com.bjsasc.transferasset.utils.ExcelImporter;

import java.util.List;

/**
 * 年度计划调整导入
 * @author 张程
 * @date 2022/9/20 8:59
 */
public class PlantExcelImporter implements ExcelImporter<PtPlanAdjustExcel> {
   

    private IPtPlanAdjustService ptPlanAdjustService;

    public PlantAdjustExcelImporter(IPtPlanAdjustService ptPlanAdjustService) {
   
        this.ptPlanAdjustService = ptPlanAdjustService;
    }

    @Override
    public void save(List<PtPlanAdjustExcel> datas) {
   
        ptPlanAdjustService.savePlanAdjustExcel(datas);
    }
}

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

IPtPlanAdjustService 实现类

 @Transactional(noRollbackFor = EasyExcelException.class, rollbackFor = Exception.class)
    public void savePlanAdjustExcel(List<PtPlanExcel> ptPlanExcels) {
   

       msgMap.put("countMsg", "导入总计：" + ptPlanAdjustExcels.size() + "条。");
        msgMap.put("successMsg", "成功：" + PtPlanAdjustSet.size() + "条。");
        msgMap.put("errorCount", "失败：" + errorNum + "条。");
        msgMap.put("errorMsg", sb.toString());
        msgMap.put("warningMsg", sbb.toString());

        // 批量保存信息
        ptPlanAdjustMapper.batchSave(PtPlanAdjustSet);
        throw new EasyExcelException(JSON.toJSONString(msgMap));

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

ExcelDataListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;

import java.util.List;

/**
 * excel监听器 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
 *
 * @author 张程
 * @date 2022/9/16 10:12
 */
public class ExcelDataListener<T> extends AnalysisEventListener<T> {
   

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100000;

    /**
     * 缓存的数据
     */
    private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private final ExcelImporter<T> importer;

    public ExcelDataListener(ExcelImporter<T> importer) {
   
        this.importer = importer;
    }


    /**
     * 每一行数据解析都需要执行
     * @param data
     * @param analysisContext
     */
    @Override
    public void invoke(T data, AnalysisContext analysisContext) {
   
        cachedDataList.add(data);

        // 达到BATCH_COUNT，则调用importer方法入库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
   

            // 调用importer方法
            importer.save(cachedDataList);

            // 存储完成清理list
            cachedDataList.clear();
        }
    }

    /**
     * 解析完成，都需要执行
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
   

        // 最后的保存，防止数量少于100。
        importer.save(cachedDataList);

        // 存储完成清理list
        cachedDataList.clear();
    }
}

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

EasyExcelUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.commons.codec.Charsets;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel工具类
 *
 * @author zhangcheng
 *
 */
public class EasyExcelUtil {
   


    /**
     * 读取并导入数据
     *
     * @param excel    excel文件
     * @param importer 导入逻辑类
     * @param       泛型
     */
    public static <T> void save(MultipartFile excel, ExcelImporter<T> importer, Class<T> clazz) {
   
        ExcelDataListener<T> importListener = new ExcelDataListener<>(importer);
        ExcelReaderBuilder builder = getReaderBuilder(excel, importListener, clazz);
        if (builder != null) {
   
            builder.doReadAll();
        }
    }

    /**
     * 读取excel的所有sheet数据
     *
     * @param excel excel文件
     * @return List