springboot+easyexcel实现导入_springboot引入easyexcel

1.所需架包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
        <version>2.1.4</version>
        </dependency>

2、实体类

package com.yunhe.zzedu.file;
 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import lombok.Data;
 
/**
 * @description:
 * @author: bgq
 * @create: 2021/8/25 11:45
 */
@Data
@ContentRowHeight(10)
public class Apply {
 
    @ColumnWidth(20)
    @ExcelProperty(value = "姓名",index = 1)
    private String xm;
    @ColumnWidth(20)
    @ExcelProperty(value = "身份证号",index = 2)
    private String sfzh;
    @ColumnWidth(20)
    @ExcelProperty(value = "性别",index = 3)
    private String xb;
    @ColumnWidth(20)
    @ExcelProperty(value = "出生日期",index = 4)
    private String csrq;
    
}

 3、监听

package com.yunhe.zzedu.web.controller.common;
 
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.yunhe.zzedu.common.tool.IDTool;
import com.yunhe.zzedu.file.Apply;
import com.yunhe.zzedu.service.base.ApplyService;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
 
import java.util.*;
 
/**
 * @description:
 * @author: bgq
 * @create: 2021/8/25 11:11
 */
@Component
@Scope("prototype")
public class ApplyExcelListener extends AnalysisEventListener<Apply> {
 
    private ApplyService applyService;
    List<Apply> datas=new ArrayList<>();
    String batchId = null;
    private static final int BATCH_COUNT = 3000;
    @Override
    public void invoke(Apply chapter, AnalysisContext analysisContext) {
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(chapter);
        //达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(datas.size() >= BATCH_COUNT){
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }
    public void setBatchId(String batchId){
        this.batchId = batchId;
    }
    public ApplyExcelListener(ApplyService applyService){
        this.applyService = applyService;
    }
    //数据处理
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
    }
    private void saveData() {
        List<Map<String,Object>> mapList =new ArrayList<>();
        if (datas.size() >= 1) {
            for (int i = 0; i < datas.size(); i++) {
                Map<String,Object> map = new HashMap<>();
                Apply apply = datas.get(i);
                map.put("id", IDTool.getUUID32());
                map.put("xm",apply.getXm());
                map.put("sfzh",apply.getSfzh());
                map.put("xb",apply.getXb());
                map.put("csrq",apply.getCsrq());
                mapList.add(map);
            }
            applyService.saveBatch(mapList);
        }
    }
 
}

4、接口

@RequestMapping(value = "/importExcel",method = RequestMethod.POST)
    @ResponseBody
    public Result importExcel(MultipartRequest request){
        //获得工作簿
        ExcelReaderBuilder read = null;
        
        try(InputStream inputStream = request.getFile("file").getInputStream()) {
 
            String batchId = ((StandardMultipartHttpServletRequest) request).getParameterMap().get("batchId")[0];
            //实例化实现了AnalysisEventListener接口的类
            ApplyExcelListener listener = new ApplyExcelListener(applyService);
            listener.setBatchId(batchId);
            //获得工作簿
            read = EasyExcel.read(inputStream, Apply.class, listener);
            //获得工作表  又两种形形式可以通过下标也可以通过名字2003Excel不支持名字
            ExcelReaderSheetBuilder sheet = read.sheet();
            //进行读操作
            sheet.doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
 
 
        return ResultUtil.success();
    }