java大批量数据导出excel

大批量数据导出，如果一次性查询出数据，会导致内存溢出，
这里的思路就是 分页查询出数据 依次写入表格中。上代码吧

用的是阿里的easyExcel

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.5</version>
        </dependency>
        <!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

导出excel实体

package com.chao.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

/**
 * @author zhangcj
 * @date 2023/7/1 15:07
 */
@Data
@EqualsAndHashCode
public class User {

    @ExcelProperty("用户ID")
    private Long id;

    @ExcelProperty("名称")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

    @ExcelProperty("生日")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date birthday;

}

controller

package com.chao.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.chao.dao.UserDao;
import com.chao.entity.ExcelConstant;
import com.chao.entity.User;
import com.github.pagehelper.PageHelper;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

/**
 * @author zhangcj
 * @date 2023/7/6 17:23
 */
@RequestMapping("user")
public class ExportExcelControllerV2 {

    @Resource
    UserDao userDao;

    @RequestMapping(value = "exportUserExcelV2",
            produces = {MediaType.MULTIPART_FORM_DATA_VALUE})
    public void exportUserExcelV2(@RequestBody User user, HttpServletResponse response) throws Exception {
        ExcelWriter excelWriter = null;
        ServletOutputStream out = null;
        try {
            out = response.getOutputStream();
            String fileName =  "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
            excelWriter = EasyExcel.write(out, User.class).build();

            //  查询总数并封装相关变量(这块直接拷贝就行了不要改)
            Integer totalRowCount = this.userDao.getCount(user);
            Integer perSheetRowCount = ExcelConstant.PER_SHEET_ROW_COUNT;
            Integer pageSize = ExcelConstant.PER_WRITE_ROW_COUNT;
            Integer sheetCount = totalRowCount % perSheetRowCount == 0 ? (totalRowCount / perSheetRowCount) : (totalRowCount / perSheetRowCount + 1);
            Integer previousSheetWriteCount = perSheetRowCount / pageSize;
            Integer lastSheetWriteCount = totalRowCount % perSheetRowCount == 0 ?
                    previousSheetWriteCount :
                    (totalRowCount % perSheetRowCount % pageSize == 0 ? totalRowCount % perSheetRowCount / pageSize : (totalRowCount % perSheetRowCount / pageSize + 1));

            for (int i = 0; i < sheetCount; i++) {
                //  创建SHEET
                WriteSheet writeSheet = EasyExcel.writerSheet("sheet"+i).build();

                //  写数据  这个j的最大值判断直接拷贝就行了，不要改动
                for (int j = 0; j < (i != sheetCount - 1 ? previousSheetWriteCount : lastSheetWriteCount); j++) {
                    //  此处查询并封装数据即可  currentPage,  pageSize这俩个变量封装好的  不要改动
                    PageHelper.startPage(j + 1 + previousSheetWriteCount * i, pageSize);
                    List<User> userList = this.userDao.findList(user);
                    excelWriter.write(userList, writeSheet);
                }
            }

            //  下载EXCEL
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1"));
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");

            excelWriter.finish();


        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

    }
    
}

参数配置项

package com.chao.entity;

/**
 *
 * 优化加到配置文件中
 * @author zhangcj
 * @date 2023/7/1 15:58
 */
public class ExcelConstant {

    /**
     * 每个sheet存储的记录数  100W
     */
    public static final Integer PER_SHEET_ROW_COUNT = 1000000;

    /**
     * 每次向EXCEL写入的记录数(查询每页数据大小)  20W
     */
    public static final Integer PER_WRITE_ROW_COUNT = 50000;


}

可以调整 分页查询数量大小来调整最优的响应时间
我本地测试13万条数据 3s左右

如果有什么地方错了还麻烦各位帮忙指正