POI高效百万级别Excel导出-1min轻松解决问题

2023-11-29 19:08
文章标签 excel 高效 导出 轻松 级别 解决问题 poi 百万 1min

本文主要是介绍POI高效百万级别Excel导出-1min轻松解决问题，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

 

思路：在数据量达到百万级别的情况下，如果一次性读取并写入Excel会比较慢，如果将数据写入同一个Excel的多个Sheet，并分别通过多个线程分别写Sheet，效率将会提高很多。

经测试：100w行，每行16列的数据导出大概60s到70s之间。

使用工具：

        // 总记录数Integer totalRowCount =ipccDao.getSearchSize("customer.selectExportCustNum", params);// 导出EXCEL文件名称String filaName = "客户信息";// 标题String[] titles = {"客户名称", "客户名称", "联系人", "联系方式", "负责人", "分配时间", "客户意向等级", "最后跟进时间", "通话状态","跟进阶段", "通话轮次","最近外呼任务","客户来源","创建时间","客户关注点","通话时长"};// 开始导入PoiUtil.exportExcelToWebsite(StrutsUtils.getResponse(), totalRowCount, filaName, titles,new WriteExcelDataDelegated() {@Overridepublic void writeExcelData(SXSSFSheet eachSheet, Integer startRowCount, Integer endRowCount,Integer currentPage, Integer pageSize) throws Exception {int startRow = (currentPage - 1) * pageSize; // 分页开始行号params.put("limit", " LIMIT " + startRow + ", " + pageSize);//List<Map<String, Object>> exportCust = customerService.exportCust(params);List<Map<String, Object>> exportCust = ipccDao.getSearchList("customer.selectCustomer", params);if (!CollectionUtils.isEmpty(exportCust)) {for (int i = startRowCount; i <= endRowCount; i++) {SXSSFRow eachDataRow = eachSheet.createRow(i);if ((i - startRowCount) < exportCust.size()) {Map<String, Object> customer = exportCust.get(i - startRowCount);eachDataRow.createCell(0).setCellValue(customer.get("name") != null ? customer.get("name") + "" : "");eachDataRow.createCell(1).setCellValue(customer.get("customer_nbr") != null? customer.get("customer_nbr") + "": "");eachDataRow.createCell(2).setCellValue(customer.get("contacts_name") != null? customer.get("contacts_name") + "": "");eachDataRow.createCell(3).setCellValue(customer.get("contacts_nbr")!=null?customer.get("contacts_nbr")+ "":"");eachDataRow.createCell(4).setCellValue(customer.get("leader")!=null?customer.get("leader") + "":"");eachDataRow.createCell(5).setCellValue(customer.get("distribution_date")!=null?customer.get("distribution_date") + "":"");eachDataRow.createCell(6).setCellValue(customer.get("cust_type")!=null?customer.get("cust_type") + "":"");eachDataRow.createCell(7).setCellValue(customer.get("last_contact_date")!=null?customer.get("last_contact_date") + "":"");changeCallStatusToMsg(customer);eachDataRow.createCell(8).setCellValue(customer.get("status") + "");changeContactGradation(customer);eachDataRow.createCell(9).setCellValue(customer.get("contact_gradation") + "");eachDataRow.createCell(10).setCellValue(customer.get("talk_times")!=null?customer.get("talk_times") + "":"");eachDataRow.createCell(11).setCellValue(customer.get("task_name")!=null?customer.get("task_name") + "":"");eachDataRow.createCell(12).setCellValue(customer.get("finder") !=null?customer.get("finder") + "":"");eachDataRow.createCell(13).setCellValue(customer.get("create_date")!=null?customer.get("create_date") + "":"");eachDataRow.createCell(14).setCellValue(customer.get("result_label")!=null?customer.get("result_label") + "":"");eachDataRow.createCell(15).setCellValue(customer.get("tel_time")!=null?customer.get("tel_time") + "":"");}}}}});

POIUtil.java工具类

package com.admin.util;import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;import javax.servlet.http.HttpServletResponse;import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import com.admin.task.ExportExcelCallable;/*** @author qjwyss* @date 2018/9/18* @description POI导出工具类*/
public class PoiUtil {/*** 每个sheet存储的记录数10W*/public static Integer PER_SHEET_ROW_COUNT;/*** 每次向EXCEL写入的记录数(查询每页数据大小) 5W*/public static Integer PER_WRITE_ROW_COUNT;static{PER_SHEET_ROW_COUNT = SysConfig.getInstance().getPropertyInt("PER_SHEET_ROW_COUNT",200000);PER_WRITE_ROW_COUNT = SysConfig.getInstance().getPropertyInt("PER_WRITE_ROW_COUNT",100000);}/*** 每个sheet的写入次数 2*/public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;private final static Logger logger = LoggerFactory.getLogger(PoiUtil.class);/*** 初始化EXCEL(sheet个数和标题)** @param totalRowCount 总记录数* @param titles        标题集合* @return XSSFWorkbook对象*/public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {logger.info("PER_SHEET_ROW_COUNT：{}，PER_WRITE_ROW_COUNT：{}",PER_SHEET_ROW_COUNT,PER_WRITE_ROW_COUNT);// 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中SXSSFWorkbook wb = new SXSSFWorkbook(100);Integer sheetCount = ((totalRowCount % PER_SHEET_ROW_COUNT == 0) ?(totalRowCount / PER_SHEET_ROW_COUNT) : (totalRowCount / PER_SHEET_ROW_COUNT + 1));// 根据总记录数创建sheet并分配标题for (int i = 0; i < sheetCount; i++) {SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));sheet.setRandomAccessWindowSize(-1);SXSSFRow headRow = sheet.createRow(0);for (int j = 0; j < titles.length; j++) {SXSSFCell headRowCell = headRow.createCell(j);headRowCell.setCellValue(titles[j]);}}return wb;}/*** 下载EXCEL到本地指定的文件夹** @param wb         EXCEL对象SXSSFWorkbook* @param exportPath 导出路径*/public static void downLoadExcelToLocalPath(SXSSFWorkbook wb, String exportPath) {FileOutputStream fops = null;try {fops = new FileOutputStream(exportPath);wb.write(fops);} catch (Exception e) {e.printStackTrace();} finally {if (null != wb) {try {wb.dispose();} catch (Exception e) {e.printStackTrace();}}if (null != fops) {try {fops.close();} catch (Exception e) {e.printStackTrace();}}}}/*** 下载EXCEL到浏览器** @param wb       EXCEL对象XSSFWorkbook* @param response* @param fileName 文件名称* @throws IOException*/public static void downLoadExcelToWebsite(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws IOException {response.setHeader("Content-disposition", "attachment; filename="+ new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1"));//设置下载的文件名OutputStream outputStream = null;try {outputStream = response.getOutputStream();wb.write(outputStream);} catch (Exception e) {e.printStackTrace();} finally {if (null != wb) {try {wb.dispose();} catch (Exception e) {e.printStackTrace();}}if (null != outputStream) {try {outputStream.close();} catch (Exception e) {e.printStackTrace();}}}}/*** 导出Excel到本地指定路径** @param totalRowCount           总记录数* @param titles                  标题* @param exportPath              导出路径* @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现* @throws Exception*/public static final void exportExcelToLocalPath(Integer totalRowCount, String[] titles, String exportPath, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {logger.info("开始导出：" + formatDate(new Date(), YYYY_MM_DD_HH_MM_SS));// 初始化EXCELSXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);// 调用委托类分批写数据int sheetCount = wb.getNumberOfSheets();for (int i = 0; i < sheetCount; i++) {SXSSFSheet eachSheet = wb.getSheetAt(i);for (int j = 1; j <= PER_SHEET_WRITE_COUNT; j++) {int currentPage = i * PER_SHEET_WRITE_COUNT + j;int pageSize = PER_WRITE_ROW_COUNT;int startRowCount = (j - 1) * PER_WRITE_ROW_COUNT + 1;int endRowCount = startRowCount + pageSize - 1;writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);}}// 下载EXCELPoiUtil.downLoadExcelToLocalPath(wb, exportPath);logger.info("导出完成：" + formatDate(new Date(),"yyyy-MM-dd HH:mm:ss"));}/*** 将日期转换为字符串** @param date   DATE日期* @param format 转换格式* @return 字符串日期*/public static String formatDate(Date date, String format) {SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);return simpleDateFormat.format(date);}public static final String YYYY_MM_DD_HH_MM_SS = "yyyy-MM-dd HH:mm:ss";/*** 导出Excel到浏览器** @param response* @param totalRowCount           总记录数* @param fileName                文件名称* @param titles                  标题* @param writeExcelDataDelegated 向EXCEL写数据/处理格式的委托类 自行实现* @throws Exception*/public static final void exportExcelToWebsite(HttpServletResponse response, Integer totalRowCount, String fileName, String[] titles, WriteExcelDataDelegated writeExcelDataDelegated) throws Exception {logger.info("开始导出：" + formatDate(new Date(), YYYY_MM_DD_HH_MM_SS));long start = System.currentTimeMillis();// 初始化EXCELSXSSFWorkbook wb = PoiUtil.initExcel(totalRowCount, titles);// 调用委托类分批写数据int sheetCount = wb.getNumberOfSheets();ExecutorService exportSheetPool = Executors.newFixedThreadPool(sheetCount);//工头ArrayList<Future<String>> results = new ArrayList<Future<String>>();for (int i = 0; i < sheetCount; i++) {SXSSFSheet eachSheet = wb.getSheetAt(i);for (int j = 1; j <= PER_SHEET_WRITE_COUNT; j++) {int currentPage = i * PER_SHEET_WRITE_COUNT + j;int pageSize = PER_WRITE_ROW_COUNT;int startRowCount = (j - 1) * PER_WRITE_ROW_COUNT + 1;int endRowCount = startRowCount + pageSize - 1;ExportExcelCallable exportExcelCallable =  new ExportExcelCallable(writeExcelDataDelegated, eachSheet, startRowCount, endRowCount, currentPage, pageSize);results.add(exportSheetPool.submit(exportExcelCallable));//submit返回一个Future，代表了即将要返回的结果}}boolean noFinishflag = true;while(noFinishflag){for (Future<String> future : results) {//首先校验是否结束，没有结束直接跳出for循环进行下一次whileif(!future.isDone()){noFinishflag = true;break;}else{noFinishflag = false;}}}logger.info("下载前："+formatDate(new Date(), YYYY_MM_DD_HH_MM_SS));// 下载EXCELPoiUtil.downLoadExcelToWebsite(wb, response, fileName);logger.info("下载后："+formatDate(new Date(), YYYY_MM_DD_HH_MM_SS));long end = System.currentTimeMillis();logger.info("导出完成：" + formatDate(new Date(), YYYY_MM_DD_HH_MM_SS)+"总消耗："+(end-start));}}

package com.admin.task;import java.util.concurrent.Callable;import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import com.admin.constant.Consts;
import com.admin.util.WriteExcelDataDelegated;/*** * 导出Excel异步处理类* 根据多个sheet创建多个线程分别去写不同的sheet，平衡最大处理量和最快处理时间。** @author jeffSheng* 2019年3月18日*/
public class ExportExcelCallable implements Callable<String>{private static final Logger logger = LoggerFactory.getLogger(ExportExcelCallable.class);private WriteExcelDataDelegated writeExcelDataDelegated;private SXSSFSheet eachSheet;private Integer startRowCount;private Integer endRowCount;private Integer currentPage;private Integer pageSize;public ExportExcelCallable(WriteExcelDataDelegated writeExcelDataDelegated,SXSSFSheet eachSheet,Integer startRowCount,Integer endRowCount,Integer currentPage,Integer pageSize){this.writeExcelDataDelegated = writeExcelDataDelegated;this.eachSheet = eachSheet;this.startRowCount = startRowCount;this.endRowCount = endRowCount;this.currentPage=currentPage;this.pageSize = pageSize;}/***  eachSheet 指定SHEETstartRowCount 开始行endRowCount 结束行currentPage 分批查询开始页pageSize 分批查询数据量*/@Overridepublic String call() throws Exception {try {writeExcelDataDelegated.writeExcelData(eachSheet, startRowCount, endRowCount, currentPage, pageSize);return Consts.Success;} catch (Exception e) {e.printStackTrace();logger.error("导出异常："+e);}return Consts.Fail;}}

重点说明下一下方法：

 /*** 初始化EXCEL(sheet个数和标题)** @param totalRowCount 总记录数* @param titles        标题集合* @return XSSFWorkbook对象*/public static SXSSFWorkbook initExcel(Integer totalRowCount, String[] titles) {logger.info("PER_SHEET_ROW_COUNT：{}，PER_WRITE_ROW_COUNT：{}",PER_SHEET_ROW_COUNT,PER_WRITE_ROW_COUNT);// 在内存当中保持 100 行 , 超过的数据放到硬盘中在内存当中保持 100 行 , 超过的数据放到硬盘中SXSSFWorkbook wb = new SXSSFWorkbook(100);Integer sheetCount = ((totalRowCount % PER_SHEET_ROW_COUNT == 0) ?(totalRowCount / PER_SHEET_ROW_COUNT) : (totalRowCount / PER_SHEET_ROW_COUNT + 1));// 根据总记录数创建sheet并分配标题for (int i = 0; i < sheetCount; i++) {SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));sheet.setRandomAccessWindowSize(-1);SXSSFRow headRow = sheet.createRow(0);for (int j = 0; j < titles.length; j++) {SXSSFCell headRowCell = headRow.createCell(j);headRowCell.setCellValue(titles[j]);}}return wb;}

  sheet.setRandomAccessWindowSize(-1);//对sheet设置一下无限制访问

这一行如果不添加的话就会报错：

Attempting to write a row[0] in the range [0,0] that is already written to disk.

 

这篇关于POI高效百万级别Excel导出-1min轻松解决问题的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/433967。

相关文章

Python创建Excel的4种方式小结

Python创建Excel的4种方式小结

《Python创建Excel的4种方式小结》这篇文章主要为大家详细介绍了Python中创建Excel的4种常见方式,文中的示例代码简洁易懂,具有一定的参考价值,感兴趣的小伙伴可以学习一下... 目录库的安装代码1——pandas代码2——openpyxl代码3——xlsxwriterwww.cppcns.c
阅读更多...
轻松上手MYSQL之JSON函数实现高效数据查询与操作

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》：本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE
阅读更多...
Go Mongox轻松实现MongoDB的时间字段自动填充

Go Mongox轻松实现MongoDB的时间字段自动填充

《GoMongox轻松实现MongoDB的时间字段自动填充》这篇文章主要为大家详细介绍了Go语言如何使用mongox库,在插入和更新数据时自动填充时间字段,从而提升开发效率并减少重复代码,需要的可以... 目录前言时间字段填充规则Mongox 的安装使用 Mongox 进行插入操作使用 Mongox 进行更
阅读更多...
Python给Excel写入数据的四种方法小结

Python给Excel写入数据的四种方法小结

《Python给Excel写入数据的四种方法小结》本文主要介绍了Python给Excel写入数据的四种方法小结,包含openpyxl库、xlsxwriter库、pandas库和win32com库,具有... 目录1. 使用 openpyxl 库2. 使用 xlsxwriter 库3. 使用 pandas 库
阅读更多...
Tomcat高效部署与性能优化方式

Tomcat高效部署与性能优化方式

《Tomcat高效部署与性能优化方式》本文介绍了如何高效部署Tomcat并进行性能优化,以确保Web应用的稳定运行和高效响应,高效部署包括环境准备、安装Tomcat、配置Tomcat、部署应用和启动T... 目录Tomcat高效部署与性能优化一、引言二、Tomcat高效部署三、Tomcat性能优化总结Tom
阅读更多...
使用Python在Excel中创建和取消数据分组

使用Python在Excel中创建和取消数据分组

《使用Python在Excel中创建和取消数据分组》Excel中的分组是一种通过添加层级结构将相邻行或列组织在一起的功能,当分组完成后,用户可以通过折叠或展开数据组来简化数据视图,这篇博客将介绍如何使... 目录引言使用工具python在Excel中创建行和列分组Python在Excel中创建嵌套分组Pyt
阅读更多...
Java使用POI-TL和JFreeChart动态生成Word报告

Java使用POI-TL和JFreeChart动态生成Word报告

《Java使用POI-TL和JFreeChart动态生成Word报告》本文介绍了使用POI-TL和JFreeChart生成包含动态数据和图表的Word报告的方法,并分享了实际开发中的踩坑经验,通过代码... 目录前言一、需求背景二、方案分析三、 POI-TL + JFreeChart 实现3.1 Maven
阅读更多...
Python利用自带模块实现屏幕像素高效操作

Python利用自带模块实现屏幕像素高效操作

《Python利用自带模块实现屏幕像素高效操作》这篇文章主要为大家详细介绍了Python如何利用自带模块实现屏幕像素高效操作,文中的示例代码讲解详,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1、获取屏幕放缩比例2、获取屏幕指定坐标处像素颜色3、一个简单的使用案例4、总结1、获取屏幕放缩比例from
阅读更多...
使用Python在Excel中插入、修改、提取和删除超链接

使用Python在Excel中插入、修改、提取和删除超链接

《使用Python在Excel中插入、修改、提取和删除超链接》超链接是Excel中的常用功能,通过点击超链接可以快速跳转到外部网站、本地文件或工作表中的特定单元格,有效提升数据访问的效率和用户体验,这... 目录引言使用工具python在Excel中插入超链接Python修改Excel中的超链接Python
阅读更多...
如何利用Python实现给Excel表格截图

如何利用Python实现给Excel表格截图

《如何利用Python实现给Excel表格截图》这篇文章主要为大家详细介绍了如何利用Python实现给Excel表格截图功能,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 我搜索了网络上的方案，感觉把 Excel 表格转换为 html 再用 platwright 截图是比China编程较顺
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2