java百万级别数据Excel表导入导出

一、问题概述

在Java中处理百万级别数据的Excel导入导出时，主要会遇到以下问题：

1. 内存溢出：

   • 使用select * from table直接从数据库获取大量数据可能会导致内存耗尽，尤其是当数据量达到数百万级别时。

2. 写入效率低下：

   • 将数据一行一行写入Excel文件的过程非常缓慢，尤其是在写入大量数据时，可能会显著影响性能。

3. 导入性能问题：

   • 导入数据时，如果采取逐条插入的方式，效率极低，并且可能耗尽数据库连接池的资源。

二、解决方案

1. 分页查询：

   • 将数据进行分页查询。例如，对于800万条数据，可以每次导出20万条，分20次执行，以避免一次性加载过多数据。

2. 批量写入Excel：

   • 在写入Excel时，可以将查询到的数据分批分块地写入，这样可以显著提高写入效率。

3. 批量插入数据库：

   • 在导入数据时，先将数据读取到一个List中，当List的大小达到一定阈值时，再执行批量插入，以提高导入效率。

三、代码案例

这里插入学生信息：id，no(学生编号)，name为例

Controller

@RestController
public class StudentController {
    @Resource
    private StudentService studentService;
 
    @Resource
    private StudentExcelExport studentExcelExport;
 
    @PostMapping("importBigData")
    public RespResult<Boolean> importData(@RequestParam("file") MultipartFile multipartFile, HttpServletRequest request){
        try{
            long start = System.currentTimeMillis();
            Integer sheetNo = Convert.toInt(request.getParameter("sheetNo"), 0);
            ExcelUtil.readExcel(multipartFile.getInputStream(), StudentDO.class,
                                new StudentExcelImport(studentService), sheetNo);
            return RespResult.success(true);
        } catch (Exception e){
            return RespResult.error(RespResultCode.IO_EXCEPTION);
        }
    }
    @GetMapping("exportBigData")
    public void exportData(HttpServletResponse response,
                           @RequestParam(required = false) String no,
                           @RequestParam(required = false) String name){
        StudentReqDTO reqDTO = new StudentReqDTO();
        reqDTO.setNo(no);
        reqDTO.setName(name);
        studentExcelExport.exportWithBigData(response, "学生列表", reqDTO);
    }
 
    @GetMapping("exportSmallData")
    public void exportSmallData(HttpServletResponse response,
                           @RequestParam(required = false) String no,
                           @RequestParam(required = false) String name){
        StudentReqDTO reqDTO = new StudentReqDTO();
        reqDTO.setNo(no);
        reqDTO.setName(name);
        studentExcelExport.exportWithSmallData(response, "学生列表", reqDTO);
    }
 
    /**
     * 循环设置要添加的数据，最终封装到list集合中
     */
    private static List<StudentDO> getData(){
        List<StudentDO> demoData = new ArrayList<>();
        for (int i = 0; i < 1000000; i++) {
            StudentDO data = new StudentDO();
            data.setNo("100"+i);
            data.setName("Eric" + i);
            demoData.add(data);
        }
        return demoData;
    }
 
    /*
    public static void main(String[] args) {
        //实现excel写的操作
        //1、设置写入文件夹地址和excel文件名称
        String fileName = "xxx.xlsx";
        //2、调用EasyExcel里面方法实现写的操作
        //write两个参数：参数1：文件路径名称   参数2：参数实体类class
        EasyExcel.write(fileName, StudentDO.class).sheet("学生列表").doWrite(getData());
    }
     */
}

service

@Service
@Transactional(rollbackFor = Exception.class)
public class StudentService extends ServiceImpl<StudentDao, StudentDO> {
 
    @Resource
    private StudentDao studentDao;
 
    public List<StudentDO> listPage(Map<String, Object> queryCondition, Integer pageNo, Integer pageSize){
        String no = Convert.toStr(queryCondition.get("no"));
        String name = Convert.toStr(queryCondition.get("name"));
        return studentDao.findByPage(no, name, (pageNo-1)*pageSize, pageSize);
    }
 
    public List<StudentDO> findList(Map<String, Object> queryCondition){
        LambdaQueryWrapper<StudentDO> lambdaQueryWrapper = Wrappers.lambdaQuery();
        String no = Convert.toStr(queryCondition.get("no"));
        if(StrUtil.isNotEmpty(no)){
            lambdaQueryWrapper.eq(StudentDO::getNo, no);
        }
        String name = Convert.toStr(queryCondition.get("name"));
        if(StrUtil.isNotEmpty(name)){
            lambdaQueryWrapper.eq(StudentDO::getName, name);
        }
        lambdaQueryWrapper.orderByDesc(StudentDO::getId);
        return studentDao.selectList(lambdaQueryWrapper);
    }
 
    public Integer getTotalCount(){
        return this.count();
    }
 
    /**
     * 功能：批量插入数据
     */
    public RespResult<Boolean> saveStuListWithBatch(List<StudentDO> studentList) {
        this.saveBatch(studentList);
        return RespResult.success(true);
    }
}

导入导出工具类util

package com.litian.dancechar.framework.excel.util;
 
import com.alibaba.excel.EasyExcel;
import com.litian.dancechar.framework.excel.core.AbstractExcelImport;
import lombok.extern.slf4j.Slf4j;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Collection;
import java.util.List;
 
 
@Slf4j
public class ExcelUtil {
 
    /**
     * 从Excel中指定sheet读取文件
     */
    public static <T> void readExcel(final InputStream inputStream, Class<?> clazz,
                                     AbstractExcelImport<T> listener, Integer sheetNo) {
        if(sheetNo != null){
            EasyExcel.read(inputStream, clazz, listener).sheet(sheetNo).doRead();
        }
    }
 
    /**
     * 从Excel中读取文件(第一个sheet)
     */
    public static <T> void readExcel(final InputStream inputStream, Class<?> clazz, AbstractExcelImport<T> listener) {
        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
    }
 
    /**
     * 写入到指定excel文件
     */
    public static <T> void write(String fileName, String sheetName, Class<T> head, Collection<T> datas){
        EasyExcel.write(fileName).head(head).sheet(sheetName).doWrite(datas);
    }
 
    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, String sheetName, Class<T> head, Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetName).doWrite(data);
    }
 
    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, Integer sheetNo, String sheetName, Class<T> head, Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetNo).sheetName(sheetName).doWrite(data);
    }
 
    /**
     * 写入到指定excel文件
     */
    public static <T> void write(String fileName, String sheetName, List<List<String>> head, Collection<T> datas){
        EasyExcel.write(fileName).head(head).sheet(sheetName).doWrite(datas);
    }
 
    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, String sheetName, List<List<String>> head, Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetName).doWrite(data);
    }
 
    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, Integer sheetNo, String sheetName, List<List<String>> head,
                                 Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetNo).sheetName(sheetName).doWrite(data);
    }
}