Springboot导出大数据量excel（一）-入门

阿瑟与非

已于 2022-03-27 08:46:56 修改

阅读量6.7k

点赞数 4

分类专栏： SpringBoot 文章标签： spring boot 后端 java poi

于 2022-01-16 22:17:53 首次发布

本文链接：https://blog.youkuaiyun.com/cs373616511/article/details/122529840

版权

SpringBoot 专栏收录该内容

24 篇文章

订阅专栏

ExcelExportUtil.exportExcel

ExcelExportUtil.exportBigExcel

一、简介

1查询数据量过大，导致内存溢出。该问题可以通过分批查询来解决；参考：Springboot导出大数据量excel（二）-分页查询数据List＜Map＞

2最后下载的时候大EXCEL转换的输出流内存溢出；该方式可以通过新版的SXSSFWorkbook来解决，可通过其构造函数执指定在内存中缓存的行数，剩余的会自动缓存在硬盘的临时目录上，同时，并不会存在页面卡顿的情况；

3.8版本的POI对excel的导出操作，一般只使用HSSFWorkbook以及SXSSFWorkbook，HSSFWorkbook用来处理较少的数据量，SXSSFWorkbook用来处理大数据量以及超大数据量的导出。

二、性能测试

使用easypoi框架实现大数量导出测试：

导出20万行数据

api	结果	耗时
ExcelExportUtil.exportBigExcel	正常导出	10.91s
ExcelExportUtil.exportExcel	内存溢出	-

1环境

jdk 1.8

easypoi 4.4.0

springboot 2.3.0

jvm -Xms128m -Xmx256m

2测试

ExcelExportUtil.exportExcel

http://localhost:8080/bigExcel/export2

结果：内存溢出

Exception in thread "http-nio-8080-ClientPoller" java.lang.OutOfMemoryError: Java heap space

ExcelExportUtil.exportBigExcel

http://localhost:8080/bigExcel/export

结果：正常导出

三、源码

1maven依赖

easypoi

 <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.4.0</version>
        </dependency>

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.4.0</version>
        </dependency>

监控

<dependency>
            <groupId>cn.pomit</groupId>
            <artifactId>spring-boot-monitor</artifactId>
            <version>0.0.4</version>
        </dependency>

2测试代码

代码都在一个controller

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import com.asyf.demo.pojo.GetDataByDynamicInputDto;
import com.asyf.demo.pojo.MsgClient;
import com.asyf.demo.pojo.MsgClientGroup;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@RestController
@Api(tags = "POI大数量导出")
@RequestMapping(value = "/bigExcel")
@Slf4j
public class BigExcelController {

    private ExecutorService executorService = Executors.newFixedThreadPool(5);

    //文档地址：http://doc.wupaas.com/docs/easypoi/easypoi-1c10lbsojh62f
    //官方demo：https://gitee.com/lemur/easypoi-test/commit/ac79b18d00874ef039a7ebf592f94c8eb29a69e7
    @RequestMapping(value = "export", method = RequestMethod.GET)
    @ApiOperation(value = "导出大量数据", notes = "官方demo")
    @ApiImplicitParams({})
    public void export(HttpServletResponse response, HttpServletRequest request) throws Exception {

        Workbook workbook = null;
        Date start = new Date();
        ExportParams params = new ExportParams("大数据测试", "测试");

        IExcelExportServer iExcelExportServer = new IExcelExportServer() {

            //这个方法会被循环调用 page每次+1
            @SneakyThrows
            @Override
            public List<Object> selectListForExcelExport(Object queryParams, int page) {
                log.info("当前查询第{}页数据", page);
                if (queryParams instanceof GetDataByDynamicInputDto) {
                    GetDataByDynamicInputDto params = (GetDataByDynamicInputDto) queryParams;
                    params.setPageNum(page++);
                    return (List<Object>) getPageData(params);
                }
                return null;
            }
        };
        GetDataByDynamicInputDto getDataByDynamicInputDto = new GetDataByDynamicInputDto();
        workbook = ExcelExportUtil.exportBigExcel(params, MsgClient.class, iExcelExportServer, getDataByDynamicInputDto);
        downLoadExcel("test" + System.currentTimeMillis() + ".xlsx", response, workbook);
        System.out.println(new Date().getTime() - start.getTime());
        workbook.close();
    }

    @RequestMapping(value = "export2", method = RequestMethod.GET)
    @ApiOperation(value = "导出大量数据(直接)", notes = "官方demo")
    @ApiImplicitParams({})
    public void export2(HttpServletResponse response, HttpServletRequest request) throws Exception {

        Workbook workbook = null;
        Date start = new Date();
        ExportParams params = new ExportParams("大数据测试", "测试");

        GetDataByDynamicInputDto getDataByDynamicInputDto = new GetDataByDynamicInputDto();
        getDataByDynamicInputDto.setPageSize(200000);
        getDataByDynamicInputDto.setPageNum(1);
        List<MsgClient> list = (List<MsgClient>) getPageData(getDataByDynamicInputDto);
        workbook = ExcelExportUtil.exportExcel(params, MsgClient.class, list);
        downLoadExcel("test" + System.currentTimeMillis() + ".xlsx", response, workbook);
        System.out.println(new Date().getTime() - start.getTime());
        workbook.close();
    }

    private Object getPageData(GetDataByDynamicInputDto params) {
        List<MsgClient> list = new ArrayList<MsgClient>();
        int pageNum = params.getPageNum();
        int pageSize = params.getPageSize();
        int offset = (pageNum - 1) * pageSize;
        //查询页数限制
        if (pageNum > 200) {//每页1000条
            return list;
        }
        for (int i = offset; i < offset + pageSize; i++) {
            MsgClient client = new MsgClient();
            client.setBirthday(new Date());
            client.setClientName("小明" + i);
            client.setClientPhone("18797" + i);
            client.setCreateBy("JueYue");
            client.setId("1" + i);
            client.setRemark("测试" + i);
            MsgClientGroup group = new MsgClientGroup();
            group.setGroupName("测试" + i);
            client.setGroup(group);
            list.add(client);
        }
        return list;
    }


    private void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try (OutputStream out = response.getOutputStream();) {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setHeader("content-Type", "application/vnd.ms-excel");
            workbook.write(out);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }
}

3源码地址

POI大数据量导出