投稿
  1. 首页
  2. 随笔

Springboot导出大数据量excel

凯松 • 2022-12-17 • 随笔 • 阅读 31

Springboot导出大数据量excel,第1张

Springboot导出大数据量excel

目录

一、简介

二、性能测试

1环境

2测试

ExcelExportUtil.exportExcel

ExcelExportUtil.exportBigExcel

 三、源码

1maven依赖

2测试代码

一、简介

1查询数据量过大，导致内存溢出。 该问题可以通过分批查询来解决；

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

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

二、性能测试

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

导出20万行数据

api结果耗时ExcelExportUtil.exportExcel正常导出10.91sExcelExportUtil.exportBigExcel内存溢出- 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

 
            cn.afterturn
            easypoi-web
            4.4.0
        

        
            cn.afterturn
            easypoi-annotation
            4.4.0

监控


            cn.pomit
            spring-boot-monitor
            0.0.4
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 selectListForExcelExport(Object queryParams, int page) {
                log.info("当前查询第{}页数据", page);
                if (queryParams instanceof GetDataByDynamicInputDto) {
                    GetDataByDynamicInputDto params = (GetDataByDynamicInputDto) queryParams;
                    params.setPageNum(page++);
                    return (List) 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 list = (List) 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 list = new ArrayList();
        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());
        }
    }
}
 

					
										


					

						
欢迎分享，转载请注明来源：内存溢出
原文地址: http://outofmemory.cn/zaji/5704834.html

					

				
								

										
										
										
										
										
									

								

					
						
							
						 赞
						(0)
					
					

						
							 打赏
						
							
								微信扫一扫 微信扫一扫
							
							
								支付宝扫一扫 支付宝扫一扫
							
						
					

				

				

					

						

							
								凯松
								凯松
								一级用户组
							
						

						

							

								
									
										
									
									0
								
								
									
										
									
									0
								
							

							
							

								
									
										
									
								
							

						

					

				

			
			
            

            	            

            
			

								

					
					

         				
							
								
							 上一篇
						
						2022-12-17
					

				

												

					
					

         				
							下一篇 
           						
							
						
						2022-12-17
					

				

							


			
			

				

					

						发表评论
					

										

						
						

							

								登录后才能评论
							

							
						

					

									

								
 评论列表（0条）

				
    
									

				
											

		

	

	





	


	

		

			

			

				
				

					

						Copyright © 2022 内存溢出 版权所有
						
							湘ICP备2022025235号						
						Powered by
						
							outofmemory.cn
						
					
					

				

			

			

				

					
					
						
						
					
					
						
					
				

			

		

	




























    
    

    

        
            保存