easyExcel百万级读取导入

内容占用极低，不会导致OOM。 模拟请求:post请求

pom依赖，必须是对应版本：

<!-- 引入Easy Excel用于excel文件的读写 -->
   		<dependency>
		    <groupId>com.alibaba</groupId>
		    <artifactId>easyexcel</artifactId>
		    <version>2.0.1</version>
		</dependency>

springboot文件大小限制配置： springboot2.2.2.RELEASE版本，每个版本配置的key不一样

# set file size: max Size is 100Mb
spring.servlet.multipart.max-file-size=100MB
spring.servlet.multipart.max-request-size=100MB

controller层代码

@ApiOperation("xxxx")
	@PostMapping(value = "/importData",  consumes = "multipart/form-data;charset=utf-8",
                 produces = "application/json;charset=utf-8")
	public ResponseEntity<CommonResult> importData(@RequestParam(value = "file", required = false) MultipartFile file, @RequestParam(value = "Id",required = true) Long Id)
	{
          
   	
		return new ResponseEntity<>(service.importData(file, Id), HttpStatus.OK);
	}

service层代码

@AutoWired
	private UserDaoMapper daoMapper;
	public CommonResult importData(MultipartFile file, Long id)
	{
          
   
		long startReadExcelTime = System.currentTimeMillis();
		CompletableFuture.runAsync(()->{
          
   				
			try {
          
   
			InputStream ins = file.getInputStream();
			EasyExcel.read(ins, new CustomExcelListener(daoMapper, id)).sheet().doRead();
			log.info("ReadExcel cost :" + (System.currentTimeMillis() - startReadExcelTime));
			} 
        	catch (IOException e) {
          
   				
        		log.error("fail to imporData, the detail is: " + e);
			}
		});			
        return ReturnResult.buildSuccessResult(null);
	}

CustomExcelListener类： invoke是回调函数，每一行调用一次，从第二行开始。 invokeHeadMap只调用一次，读取第一行。 doAfterAllAnalysed所有数据读取完，即最后一次invoke后的操作。 AnalysisEventListener<Map<Integer, String>> 的<Map<Integer, String>>和invoke(Map<Integer, String> data, AnalysisContext context)的data可以使用自定义类

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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

public class CustomExcelListener extends AnalysisEventListener<Map<Integer, String>> 
{
          
   
    public CustomExcelListener() {
          
   

    }
    /**
                * 每读取1000条做一次处理，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
 
	List<String> columns = new ArrayList<>();

    List<String> content = new ArrayList<>();
     
    //业务相关。。。
	private UserDaoMapper daoMapper;

	private Long id;
	
    public CustomExcelListener(DaoMapper daoMapper, Long id) 
    {
          
   
	    this.daoMapper = daoMapper;
	    this.id = id;
    }
        
    /**
     *DO： 读数据，是从第二行开始读的
     *
     */
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) 
    {
          
     
    	try {
          
   			
			//map内容为：下标-内容
			content.add(data.get(i).toString());
			
	        if (content.size() >= BATCH_COUNT) 
	        {
          
   
	        	save();
	        }		
		} catch (ParseException e) {
          
   	
		}
    }

    /**
     * DO：读表头(列名)
     * */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) 
    {
          
   
		for (int i = 0; i < headMap.size(); i++) 
		{
          
   
			columns.add(headMap.get(i));
		}
    }
    
    /**
                * 所有数据解析完成了来调用
     *
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) 
    {
          
     		
		save();
    }

	private void save() {
          
   
	daoMapper.save(content, id);
	content.clear();
	}

}