java POI导出excel性能优化

最新推荐文章于 2024-08-21 11:26:46 发布
最新推荐文章于 2024-08-21 11:26:46 发布
阅读量3.1k 收藏 9
点赞数 4
分类专栏： 性能优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qxy15997826736/article/details/117404740
版权

大数据导出 SXSSFWorkbook 内存优化 Excel性能提升 样式复用
关键词由CSDN通过智能技术生成

项目场景：

导出40万以上的数据到xlsx

问题描述：

实施地出现导出40万数据到excel中时，CPU占不超过100%，时间超过半个小时，系统中其他功能模块卡顿 
	@EcpGetMapping("/exportResultDetail")
	public void exportResultDetail(final HttpServletRequest req, final HttpServletResponse resp, @RequestParam final String operParams) {
		byte[] exportBytes = null;
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        try {
        	JSONObject jsonObj = JSONObject.parseObject(operParams);
        	String fileName = String.valueOf(jsonObj.get("fileName"));
        	String fileType = String.valueOf(jsonObj.get("fileType"));
        	String compId = String.valueOf(jsonObj.get("compId"));
        	String modelId = String.valueOf(jsonObj.get("modelId"));
        	List<Map> colList = this.mainResultService
        								.findColListByModelId(compId, modelId);
        	String queryParamStr = String.valueOf(jsonObj.get("queryParam"));
        	Map queryParam = JSONUtil.fromJsonString(queryParamStr, Map.class);
//        	fileName = new String(fileName.getBytes("iso8859-1"),"utf8");
        	Map<String, Object> queryResult = this.tAmModelMainResultService
        									.queryDetailList(queryParam);
        	List<Map> list = (List<Map>) queryResult.get("data");
    		final Workbook exportWorkbook = AmExcelHelpUtil.createWorkbook(fileType);
    		exportWorkbook.createSheet(fileName);
    		final CellStyle headCellStyle = exportWorkbook.createCellStyle();
    		final CellStyle contentCellStyle = exportWorkbook.createCellStyle();
    		final CellStyle contentCenterCellStyle = exportWorkbook.createCellStyle();
    		final CellStyle contentRightCellStyle = exportWorkbook.createCellStyle();
    		final Font headCellFont = exportWorkbook.createFont();
    		final Font contentFont = exportWorkbook.createFont();
    		final Sheet manuSheetNew = exportWorkbook.getSheetAt(0);
    		manuSheetNew.setDefaultRowHeightInPoints((short) 25);
    		manuSheetNew.setDefaultColumnWidth(25);
    		final Row headRow = manuSheetNew.createRow(0); // 标题行
    		int colCount = colList.size();
    		for (int j = 0; j < colCount; j++) {
    			final Cell cell = headRow.createCell(j);
    			headCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    			headCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    			headCellStyle.setWrapText(true);
    			headCellFont.setFontHeightInPoints((short)10);
    			headCellFont.setFontName("微软雅黑");
    			headCellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    			headCellStyle.setFont(headCellFont);
    			headCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    			headCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    			headCellStyle.setBorderRight(CellStyle.BORDER_THIN);
    			headCellStyle.setBorderTop(CellStyle.BORDER_THIN);
    			headCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    			headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    			cell.setCellStyle(headCellStyle);
    			Map colMap = colList.get(j);
    			String colCnName = String.valueOf(colMap.get("colcnname"));
    			excelHelpUtil.setCellValue(fileType, manuSheetNew, 0, j, colCnName);
    		}
    		if (list != null && list.size() > 0) {
    			int indx = 1;
    			for (Map dataMap : list) {
    				if (dataMap == null) {
    					continue;
    				}
    				final Row row = manuSheetNew.createRow(indx);
        			row.setHeightInPoints((short) 25);
        			for (int j = 0; j < colCount; j++) {
        				final Cell cell = row.createCell(j);
        				Map colMap = colList.get(j);
        				String colEnName = String.valueOf(colMap.get("colenname")).toLowerCase();
        				String dataType = String.valueOf(colMap.get("coldatatype"));
        				if ("1,2".contains(dataType)) {
        					contentCenterCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        					contentCenterCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        					contentCenterCellStyle.setWrapText(true);
            				contentFont.setFontHeightInPoints((short) 10);
            				contentFont.setFontName("微软雅黑");
            				contentCenterCellStyle.setFont(contentFont);
            				contentCenterCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            				contentCenterCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
            				contentCenterCellStyle.setBorderRight(CellStyle.BORDER_THIN);
            				contentCenterCellStyle.setBorderTop(CellStyle.BORDER_THIN);
            				cell.setCellStyle(contentCenterCellStyle);
        				} else if ("3".equals(dataType)) {
        					contentRightCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        					contentRightCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        					contentRightCellStyle.setWrapText(true);
            				contentFont.setFontHeightInPoints((short) 10);
            				contentFont.setFontName("微软雅黑");
            				contentRightCellStyle.setFont(contentFont);
            				contentRightCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            				contentRightCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
            				contentRightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
            				contentRightCellStyle.setBorderTop(CellStyle.BORDER_THIN);
            				cell.setCellStyle(contentRightCellStyle);
        				} else {
        					contentCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
            				contentCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            				contentCellStyle.setWrapText(true);
            				contentFont.setFontHeightInPoints((short) 10);
            				contentFont.setFontName("微软雅黑");
            				contentCellStyle.setFont(contentFont);
            				contentCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            				contentCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
            				contentCellStyle.setBorderRight(CellStyle.BORDER_THIN);
            				contentCellStyle.setBorderTop(CellStyle.BORDER_THIN);
            				cell.setCellStyle(contentCellStyle);
        				}
        				excelHelpUtil.setCellValue(fileType, 
            					manuSheetNew, indx, j, String.valueOf(dataMap.get(colEnName)));
        			}
        			indx++;
    			}
    		}
    		exportWorkbook.write(byteArrayOut);
    		exportBytes = byteArrayOut.toByteArray();
        	resp.setStatus(200);
            resp.setContentType("application/x-download;charset=utf8");
            resp.addHeader("Content-Disposition", String.format("attachment; filename=\"%s\"",
            			URLEncoder.encode(fileName + "." + fileType, "UTF-8")));
			resp.getOutputStream().write(exportBytes);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

原因分析：

  1. 大数据量导出应该使用poi 3.8以上提供的类SXSSFWorkbook，2007版 XSSFWorkbook不适用于数据量较大的数据导出。
  2. 创建样式对象时，应重复使用，不要每次填充cell时都新建一个样式对象。

解决方案：

/**
	 * 
	 * @param req
	 * @param resp
	 * @param operParams
	 */
	@EcpGetMapping("/exportResultDetailForExcel")
	public void exportResultDetailForExcel(final HttpServletRequest req, final HttpServletResponse resp, @RequestParam final String operParams) {
		byte[] exportBytes = null;
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		SXSSFWorkbook exportWorkbook = null;
        try {
        	JSONObject jsonObj = JSONObject.parseObject(operParams);
        	String fileName = String.valueOf(jsonObj.get("fileName"));
        	String fileType = String.valueOf(jsonObj.get("fileType"));
        	String compId = String.valueOf(jsonObj.get("compId"));
        	String modelId = String.valueOf(jsonObj.get("modelId"));
        	List<Map> colList = this.mainResultService
        								.findColListByModelId(compId, modelId);
        	String queryParamStr = String.valueOf(jsonObj.get("queryParam"));
        	Map queryParam = JSONUtil.fromJsonString(queryParamStr, Map.class);
        	Map<String, Object> queryResult = this.tAmModelMainResultService
        									.queryDetailList(queryParam);
        	List<Map> list = (List<Map>) queryResult.get("data");
        	exportWorkbook = new SXSSFWorkbook(2000);
        	exportWorkbook.setCompressTempFiles(false);
    		exportWorkbook.createSheet(fileName);
    		final CellStyle headCellStyle = exportWorkbook.createCellStyle();
    		final CellStyle contentCenterCellStyle = exportWorkbook.createCellStyle();
    		final Font headCellFont = exportWorkbook.createFont();
			headCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
			headCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			headCellStyle.setWrapText(true);
			headCellFont.setFontHeightInPoints((short)10);
			headCellFont.setFontName("微软雅黑");
			headCellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			headCellStyle.setFont(headCellFont);
			headCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			headCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			headCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			headCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			headCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
			headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    		final Font contentFont = exportWorkbook.createFont();
    		//居中
    		contentFont.setFontHeightInPoints((short) 10);
			contentFont.setFontName("微软雅黑");
    		contentCenterCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
			contentCenterCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			contentCenterCellStyle.setWrapText(true);
			contentCenterCellStyle.setFont(contentFont);
			contentCenterCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			contentCenterCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			contentCenterCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			contentCenterCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			//居右
    		final CellStyle contentRightCellStyle = exportWorkbook.createCellStyle();
    		contentRightCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
			contentRightCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			contentRightCellStyle.setWrapText(true);
			contentRightCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			contentRightCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			contentRightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			contentRightCellStyle.setBorderTop(CellStyle.BORDER_THIN);
			contentFont.setFontHeightInPoints((short) 10);
			contentFont.setFontName("微软雅黑");
			contentRightCellStyle.setFont(contentFont);
			//居左
    		final CellStyle contentCellStyle = exportWorkbook.createCellStyle();
			contentCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
			contentCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			contentCellStyle.setWrapText(true);
			contentFont.setFontHeightInPoints((short) 10);
			contentFont.setFontName("微软雅黑");
			contentCellStyle.setFont(contentFont);
			contentCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
			contentCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
			contentCellStyle.setBorderRight(CellStyle.BORDER_THIN);
			contentCellStyle.setBorderTop(CellStyle.BORDER_THIN);
    		final Sheet manuSheetNew = exportWorkbook.getSheetAt(0);
    		manuSheetNew.setDefaultRowHeightInPoints((short) 25);
    		manuSheetNew.setDefaultColumnWidth(25);
    		final Row headRow = manuSheetNew.createRow(0); // 标题行
    		int colCount = colList.size();
    		for (int j = 0; j < colCount; j++) {
    			final Cell cell = headRow.createCell(j);   
    			cell.setCellStyle(headCellStyle);
    			Map colMap = colList.get(j);
    			String colCnName = String.valueOf(colMap.get("colcnname"));
    			excelHelpUtil.setSXSSFCellValue(fileType, manuSheetNew, 0, j, colCnName);
    		}
    		if (list != null && list.size() > 0) {
    			int indx = 1;
    			for (Map dataMap : list) {
    				if (dataMap == null) {
    					continue;
    				}
    				final Row row = manuSheetNew.createRow(indx);
        			row.setHeightInPoints((short) 25);
        			for (int j = 0; j < colCount; j++) {
        				final Cell cell = row.createCell(j);
        				Map colMap = colList.get(j);
        				String colEnName = String.valueOf(colMap.get("colenname")).toLowerCase();
        				String dataType = String.valueOf(colMap.get("coldatatype"));
        				if ("1,2".contains(dataType)) {			
            				cell.setCellStyle(contentCenterCellStyle);
        				} else if ("3".equals(dataType)) {              				
            				cell.setCellStyle(contentRightCellStyle);
        				} else {     					
            				cell.setCellStyle(contentCellStyle);
        				}
        				excelHelpUtil.setSXSSFCellValue(fileType, 
            					manuSheetNew, indx, j, String.valueOf(dataMap.get(colEnName)));
        			}
        			indx++;
    			}
    		}
    		exportWorkbook.write(byteArrayOut);
    		exportBytes = byteArrayOut.toByteArray();
        	resp.setStatus(200);
            resp.setContentType("application/x-download;charset=utf8");
            resp.addHeader("Content-Disposition", String.format("attachment; filename=\"%s\"",
            			URLEncoder.encode(fileName + "." + fileType, "UTF-8")));
			resp.getOutputStream().write(exportBytes);
		} catch (IOException e) {
			e.printStackTrace();
		} finally{
			if (exportWorkbook != null) {
				try {
					//删除磁盘上的文件
					deleteSXSSFTempFiles(exportWorkbook);
				} catch (NoSuchFieldException | IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * Returns a private attribute of a class
	 * @param containingClass The class that contains the private attribute to retrieve
	 * @param fieldToGet The name of the attribute to get
	 * @return The private attribute
	 * @throws NoSuchFieldException
	 * @throws IllegalAccessException 
	 */
	public static Object getPrivateAttribute(final Object containingClass, final String fieldToGet) throws NoSuchFieldException, IllegalAccessException {
	    //get the field of the containingClass instance
	    Field declaredField = containingClass.getClass().getDeclaredField(fieldToGet);
	    //set it as accessible
	    declaredField.setAccessible(true);
	    //access it
	    Object get = declaredField.get(containingClass);
	    //return it!
	    return get;
	}

	/**
	 * 删除缓存文件 Deletes all temporary files of the SXSSFWorkbook instance.
	 * @param workbook
	 * @throws NoSuchFieldException
	 * @throws IllegalAccessException 
	 */
	public static void deleteSXSSFTempFiles(final SXSSFWorkbook workbook) throws NoSuchFieldException, IllegalAccessException {
	    int numberOfSheets = workbook.getNumberOfSheets();
	    //iterate through all sheets (each sheet as a temp file)
	    for (int i = 0; i < numberOfSheets; i++) {
	        Sheet sheetAt = workbook.getSheetAt(i);
	        //delete only if the sheet is written by stream
	        if (sheetAt instanceof SXSSFSheet) {
	            SheetDataWriter sdw = (SheetDataWriter) getPrivateAttribute(sheetAt,"_writer");
	            //如果exportWorkbook.setCompressTempFiles(true);
	            //下一行代码就会报错
	            File f = (File) getPrivateAttribute(sdw,"_fd");
	            try {
	                f.delete();
	            } catch (Exception ex) {
	                //could not delete the file
	            }
	        }
	    }
	}

说明：由于poi 3.8 没有 dispose()方法，所以需要写清除硬盘上存的文件，poi 3.8以上就可以直接使用。
exportWorkbook = new SXSSFWorkbook(2000);
就是将超过2000条的数据存入到电脑磁盘上，2000以内的数据放入缓存中，这样就可以减少内存的占用。创建样式的话，不需要放在for循环中，这样就解决问题了，36万的数据只需要10几秒就导出完成，提高了导出的性能。

MicaQian
关注
专栏目录
POI-Excel表格生成速度优化
weixiang2039的博客
03-19 1828
从实际上看，笔者之前的项目用的Excel表格工具类，是一个单线程的工具类，执行效率比较慢。 在生成Sheet，1W行数据时，花费大概在10秒左右，多Sheet数据量叠加，时间开销也会线性叠加。 笔者项目用的框架是阿帕奇开源的POI，本篇文章的主体聚焦于优化的思路，如POI的使用、线程池细节、并发工具类的使用等，不在讨论范围。 在生产环境，通过以下优化之后，生成Excel的速度能快上好几倍(产品赶紧给我加鸡腿)。 分Sheet优化 Excel表格中，不同的sheet应该没有什么关联关系，起码对于笔者的
JAVA POI导入技术之性能优化（三）
samile的专栏
06-03 5640
     之前的几篇文章中，提到了EXCEL导入的方法和相关代码的优化。但是在正式系统上线之后，由于老系统和新系统数据要进行同步复用，所以很多之前的老数据借助EXCEL批量导入的功能，导入到相应的表中。但是在导入的过程中发现，EXCEL解析的速度较快，但是点击导入功能，时间真是太久了，3000条数据可能需要30分钟甚至更长的时间。这迫使代码性能优化迫在眉睫。     观察了下EXCEL导入的流程，...
Java中优化大量数据导出到Excel的内存消耗（三）：边读边写
最新发布
NAMELZX的博客
08-21 502
数据导出进行边读边写excel方式导出。
java poi 导出excel 特别慢
weixin_40434747的博客
08-16 79
我整理的一些关于【Java】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/bLN8S1使用Java POI导出Excel文件的性能优化技巧 在进行Excel文件导出时，Java POI库是一个非常流行的选择。然而，如果数据量较大，处理速度可能会变得非常缓慢。本篇文章将为你详...
excel导入导出性能优化
04-03
由于excel导出属于可预估的内存消耗型功能，其对资源的消耗根据任务并发数量可能会影响该功能所在的JVM进程健康，则忧进行优化
POI -- XSSFWorkbook性能优化
qq_42530556的博客
06-20 433
背景：财务管理系统凭证导出Excel 文件请求超时。问题2、对合并的单元格添加边框速度很慢。问题1、合并单元格速度很慢。
导出百万数据优化 分批写入_POI如何高效导出百万级Excel数据？
weixin_39630999的博客
01-24 812
在一个具有统计功能的系统中，导出excel功能几乎是一定的，如何导出excel？导出的数据有多少？如何高效的导出？Excel简介什么是excel就不用介绍了，这里主要说明不同版本下每个sheet下的行列限制。版本区间行数列数文件后缀Excel 2003及以下65535256xlsExcel 2007及以上104857616384xlsx由上面可知 Excel 2003及以下是无法实现单s...
java多线程导出excel（千万级别）优化
12-05
轻松解决普通poi形式导出Excel的中出现的栈溢出问题，此资源可实现千万级数据分批导出csv文件，csv大数据量导出(千万级别，不会内存溢出)，多线程导出 ，生产环境已经很稳定的使用着
java poi 导出excel 速度慢_java POI技术之导出数据优化（15万条数据1分多钟）
weixin_39969611的博客
02-13 2551
专针对导出excel2007 ，用到poi3.9的jarpackage com.cares.ynt.util;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.text.ParseException;import...
java使用POI导出 Excel工具类
08-14
java使用POI导出 Excel+图片工具类 ，里面含有poi jar包，只调用接口即可直接保存Excel。使用的时候需先把数据封装，具体包装需根据实际导出数据进行处理。文件demo中只提供包装格式。
java poi导出excel
04-09
以上就是使用Java POI导出Excel的基本步骤。你可以根据实际需求调整代码，例如添加数据遍历、样式设置、图表生成等功能。确保正确管理资源，避免内存泄漏，特别是在服务器端处理大量数据时。记得在完成后关闭工作簿...
POI百万级大数据量EXCEL导出 - 请叫我猿叔叔的博客 - CSDN博客.htm
12-10
POI百万级大数据量EXCEL导出 - 请叫我猿叔叔的博客 - CSDN博客.htm
poi操作excel导入导出，实际项目中应用并且优化
05-03
该文件是我在实际项目中应用操作excel，符合企业中95%的业务，不同业务自己在根据具体需求新增饥渴
JAVA poi 做EXCEL导出(包含图片) 完整版
12-17
本教程将详细介绍如何使用JAVA POI库来创建一个包含图片的完整Excel导出功能。 首先，你需要在项目中引入Apache POI依赖。如果你使用的是Maven，可以在pom.xml文件中添加以下依赖： ```xml <groupId>org.apache....
java poi导出图片到excel示例代码
08-26
Java POI导出图片到Excel示例代码详解 Java POI是Java开发中常用的开源库，用于读写Microsoft Office文件格式，包括Excel、Word、PowerPoint等。今天，我们将介绍如何使用Java POI将图片导出到Excel中。 标题解释 ...
POI(excel)导出优化
Rain
09-08 5594
寄语：第一次接触百万级别导出，不知道极限，尝试过百度，经历过绝望，内存溢出，等了一万年，不出来文件…. 1.要明白 极限： excel 不同版本的行极限: excel 2007 及以上：1048576 2003 : 65536 行 不考虑列：因为最少256列够用 2：什么会导致内存溢出： 1.循环量过大 2.创建重复实体过多 3.map 添加多只 4...
POI导出excel表格优化
yjt520557的博客
10-15 778
package com.ywj.excel; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.LinkedHashMap; import java.util.List; impor...
POI框架EXCEL解析性能优化
weixin_34167043的博客
07-23 1794
背景 在做商品EXCEL的时候，线上发现了Full GC,排查得知是商家搞了一个巨大的excel，单商品发布接口平均耗时400ms(调用sell耗时200ms左右,系统自身处理商品同步耗时150ms左右)，对于3000个商品的发布，耗时在20min左右，这20min内该excel的内存一直未能释放。 第一时间想到的是POI真坑，真吃内存。 事情发生了就...
java实现Excel 快速、非常实用导入导出操作
ma2399268689的博客
06-08 1070
java实现Excel 快速、非常实用导入导出操作
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值