超大数据量的xlsx格式的excel文件的读取和解析，解决了POI方式的内存溢出和性能问题

最新推荐文章于 2023-12-29 16:42:48 发布
最新推荐文章于 2023-12-29 16:42:48 发布
阅读量1.1w 收藏 36
点赞数 5
分类专栏： java 文章标签： java excel POI 内存溢出 解析成xml
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gaoweijiegwj/article/details/77977425
版权
在之前的博文《 POI读取并解析xlsx格式的excel文件》中，在小数据量的情况下是可以轻松愉快的处理的，但是当excel文件的数据量达到百万级的时候，
  1. InputStream is = files[i].getInputStream();  
  2. XSSFWorkbook xssFWorkbook = new XSSFWorkbook(is); 

在02处直接就会内存溢出了。无论怎么抢救都没用，看来得要换一条路走走了。

在不停的Google查资料折腾了两天之后，在POI官网成功的找到了解决方案。此处请允许我稍微吐槽一下POI，有瓶颈问题的解决方案却隐藏的很深，只有一个不起眼的小链接，难道是怕大家都知道有点丢脸？

总结一下该方案的主要原理：超大数据量的excel文件通过页面上传后（nginx的默认最大文件字节要调大一些：client_max_body_size  xxxm），后台接收到该文件的对象CommonsMultipartFile。首先获取该文件对象的inputStream，然后用OPCPackage来打开该文件流，将excel在内存中转化为xml格式却不会出现内存溢出的问题，根据该xml的标签就可以识别是格式，标题还是内容。然后在内存中通过POI框架中的XSSFSheetXMLHandler类中的sheetContentsHandler接口来根据上述的标签来解析内容。可以将解析到的内容存入list或者map容器中便于后续的业务处理（纯内容数据，几百万的数据量轻松胜任，亲测不会内存溢出）。当然根据业务需要，需要复写sheetContentsHandler接口的startRow，endRow，cell，headerFooter四大方法。

当然了，笔者也亲测了另一种方案：就是用OPCPackage来打开该文件流并且将excel在内存中转化为xml格式之后，一股脑儿的用缓冲流分批的将所有原excel内容数据写入到本地的txt文件，再去分批的readLine该文件中的数据，进行业务处理。该方案的好处是交易导入的文件可以物理的落地，作为后期查验的依据和凭证。当然了，缺点是多了一次物理转储的过程，耗时会延长。如何选择看个人的业务需求咯。

本文中重点讲述第一种方案吧，话不多说，贴代码：

/**
	 * @return
	 * @throws Exception
	 * @author jason.gao
	 * 功能描述：交易导入
	 */
	@RequestMapping(value = "/transDetail/upload", method = {RequestMethod.POST, RequestMethod.GET})
	@RequestGuard(perm = "transaction.import.upload")
	public ResponseEntity<ResponseEnvelope<RestApiResp>> uploadFile(@RequestParam("file") CommonsMultipartFile[] files, HttpServletRequest req, HttpServletResponse resp) throws IOException {
        logger.info("uploadFile == >upload button start; fileName:[{}], CommonsMultipartFile[]:[{}]", files[0].getFileItem().getName(), files);
	    long start = System.currentTimeMillis();
		String result = "完成交易文件的导入！";
		if (null == files || files.length != 1) {
			return RestApiResp.getSuccResponseEntity("必须上传一个文件", null);
		}
		//重置buffer，在可能会超时的地方输出resp的字节，避免前端ajax请求断开！
		resp.setBufferSize(1);
		ServletOutputStream out = resp.getOutputStream();
		XlsxProcessAbstract xlsxProcess = new XlsxProcessAbstract();
		long getFileAndDataTime;
		ProcessTransDetailDataDto data;
		try {
			//获取明细行累积的支付/退款的总金额/总笔数等数据的DTO
			data = xlsxProcess.processAllSheet(files[0]);
			logger.info("汇总行的数据：[{}]", data.dtoToString());
			//获取汇总行和明细行数据（包含标题）
			List<String> contentList = data.contentList;
			logger.info("明细行的数据条数为：[{}]", JSON.toJSONString(contentList.size() - 3));
			getFileAndDataTime = System.currentTimeMillis();
			logger.info("获取文件并得到数据完成。耗时：[{}]秒", (getFileAndDataTime - start)/1000);
			//校验汇总行数据正确性
			checkDetailSummary(contentList, data, out);
			logger.info("汇总行数据正确性的校验已通过！");
			//分批调用OSP插入过程
			String handleResult =  doOspHandle(contentList, data, out);
			if (!handleResult.equals(TransImportJobStatus.Success.getValue())) {
				result = TransImportJobStatus.getDescByKey(handleResult);
				logger.error(result);
			}
		} catch (CellDataException e) {
			logger.error("CellDataException: Error:[{}]", e);
			return RestApiResp.getSuccResponseEntity(e.getMessage(), null);
		} catch (OspException e) {
			logger.error("OspException：[{}]", e);
			return RestApiResp.getSuccResponseEntity(e.getMessage(), null);
		} catch (IOException e) {
			logger.error("IOException：[{}]", e);
			return RestApiResp.getSuccResponseEntity(e.getMessage(), null);
		} catch (Exception e) {
			logger.error("未知异常：[{}]", e);
			return RestApiResp.getSuccResponseEntity("未知异常，请排查日志：" + e.getMessage(), null);
		}
		long finishCheckAndInsertTime = System.currentTimeMillis();
		logger.info("完成数据校验和数据分批插入。耗时：[{}]秒", (finishCheckAndInsertTime - getFileAndDataTime)/1000);
		logger.info("[{}]，整个后台处理过程共耗时：[{}]秒", result, (finishCheckAndInsertTime - start)/1000);
		return RestApiResp.getSuccResponseEntity(result, HttpStatus.OK);
	}

上面代码块是整个后台的主流程，注意的是要充分的捕捉异常，将异常信息呈献给前端页面和日志系统，便于生产故障时排查问题。

接下来的四个代码块是对excel中字段的业务处理，属于业务部分，不关心业务的可以忽略这些代码片段。

public String doOspHandle (List<String> contentList, ProcessTransDetailDataDto data, ServletOutputStream out) throws CellDataException, OspException, IOException{
		// 获取当前工作薄的明细行
		int start = 3;
		int size = 1000;
		String importStatus = "";

		//分批调用OSP接口执行插入
        while(start < contentList.size()) {
            importStatus = handleTransImport(contentList, start, size, data);
            if (!importStatus.equals(TransImportJobStatus.Success.getValue())) {
                logger.error("从第[{}]到[{}]行的数据，分批调用OSP接口失败", start + 1, start + size + 1);
                return importStatus;
            }
            start += size;
			out.write(new String(" ").getBytes());
			out.flush();
        }
		//最终状态：交易全部成功
		if (importStatus.equals(TransImportJobStatus.Success.getValue())){
			logger.info("调用“交易明细导入”的OSP接口成功！");
			TransDetailResp confirmResp;
			OspTransDetailServiceHelper.OspTransDetailServiceClient ospTransDetailServiceClient = new OspTransDetailServiceHelper.OspTransDetailServiceClient();
			logger.info("调用“确认交易明细成功”的OSP接口的请求参数：商户号=[{}]，结算单号=[{}]，总条数=[{}]", data.getMerchantId(), data.getSettleOrderNo(), contentList.size()-3);
			try{
				confirmResp = ospTransDetailServiceClient.transDetailConfirm(data.getMerchantId(), data.getSettleOrderNo(), contentList.size()-3);
			} catch (OspException e) {
				logger.error("调用“确认交易明细成功”的OSP接口的抛出异常！[{}]", e);
				throw e;
			} finally {
				out.write(new String("").getBytes());
				out.flush();
			}
			logger.info("调用“确认交易明细成功”的OSP接口的返回参数为：{}", JSON.toJSONString(confirmResp));
			if (!confirmResp.getResponseCode().equals(MessageEnum.SUCCESS.getValue())) {
				throw new OspException(TransImpFileExceptEnums.OspTransDetailConfirm.getValue(), TransImpFileExceptEnums.OspTransDetailConfirm.getDesc());
			}
		}
		return importStatus;
	}


/**
	 * 调用osp接口：执行交易明细的导入
	 * 返回OSP操作完成的状态
	 */
	public String handleTransImport(List<String> contentList, int start, int size, ProcessTransDetailDataDto data) throws CellDataException, OspException{
        //分批的调用osp接口：执行交易明细的导入
		OspTransDetailServiceHelper.OspTransDetailServiceClient ospTransDetailServiceClient = new OspTransDetailServiceHelper.OspTransDetailServiceClient();
		TransDetailResp transDetailResp;
        List<TransDetailImport> transDetailImportList = new ArrayList<>();
        //组织好一个list数据：读取从start -> start+size行的数据
        for (int i = start; i < start + size && i < contentList.size(); i++) {
			TransDetailImport transDetailImport = new TransDetailImport();
			String[] detailRow = contentList.get(i).split("\\|@\\|");
			if (detailRow != null || !detailRow.equals("")) {
				try {
					transDetailImport.setMerchantId(data.getMerchantId());
					transDetailImport.setMerchantName(data.getMerchantName());
					transDetailImport.setMerchantBatchNo(data.getSettleOrderNo());    //商户批次号
					transDetailImport.setMerchantBatchSerialNo(XssfCellValueCheckHelper.getStringNotEmpty(detailRow[0], i, 0));    //商户批次序号<来源:页面导入模板中明细行的序号>模板必填
					transDetailImport.setMerchantOrderNo(XssfCellValueCheckHelper.getStringNotEmpty(detailRow[1], i, 1));    //商户订单号：模板必填
					transDetailImport.setPlatformOrderNo(XssfCellValueCheckHelper.getRealOrDefaultValue(detailRow[2], detailRow[1]));        //平台订单号(支付退款订单号)：如果不送默认商户订单号
					transDetailImport.setMerchantTransDate(detailRow[4].equals("") ? new Date() : new Date(detailRow[4]));        //商户交易日期：如果不送默认上送日期
					transDetailImport.setTransType(XssfCellValueCheckHelper.getRealOrDefaultValue(detailRow[5], TransTypeEnums.Payment.getValue()));    //交易类型：如果不送默认支付
					transDetailImport.setOriginOrderNo(XssfCellValueCheckHelper.checkAndGetOriginOrderNo(detailRow[3], transDetailImport.getTransType(), i));    //原支付订单号
					transDetailImport.setCurrency(XssfCellValueCheckHelper.getRealOrDefaultValue(detailRow[6], "CNY"));  //币种：三位货币代码，如果不送默认CNY:人民币
					transDetailImport.setAmount(XssfCellValueCheckHelper.getAmount(detailRow[7], i));   //交易金额：外部交易上传金额，内部商户订单金额
					transDetailImport.setCustomerName(XssfCellValueCheckHelper.getStringNotEmpty(detailRow[9], i, 9));        //客户名称：模板必填
最低0.47元/天 解锁文章
月下吴钩
关注
  • 5
    点赞
  • 踩
  • 36
    收藏
    觉得还不错? 一键收藏
  • 21
    评论
专栏目录
内存溢出xssfworkbook
04-28
File test = new File("D:\\test.xlsx"); try { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(test)); SXSSFWorkbook workbook = new SXSSFWorkbook(wb); Sheet sheet = workbook.getSheetAt(0); for (int i = 0; i < 5; i++) { Row row = sheet.getRow(i); if(row==null){ System.out.println(i+":null"); } } } catch (Exception e) { e.printStackTrace(); }
Excel文件解析--超大Excel文件读写
最新发布
weixin_64975807的博客
04-22 995
当我们想在Excel文件中写入100w条数据时，我们用普通的XSSFWorkbook对象写入时会发现，只有在将100w条数据全部加载入内存后才会用write()方法统一写入，这样效率很低，所以我们引入了SXSSFWorkbook进行超大Excel文件的读写。SXSSFWorkbook可以通过构造参数来控制：当数据写入内存量达到参数值时，就把这些数据flush到Excel文件中。
Excel百万数据导入内存溢出(OOM)解决方案，以及HSSFworkbook，XSSFworkbook，SXSSFworkbook失败分析
secretdaixin的博客
02-15 1万+
Excel百万数据导入，出现内存溢出，HSSFworkbook数据上限太低，不可行；XSSFworkbook会发生OOM，不可行；SXSSFworkbook读取数据做导入还是会发生OOM，不可行。使用StreamingReader可以解决大文件读取造成的OOM，它可以根据配置将一个大文件分成多个临时文件，再读取一部分Row到内存中，全部读取完成后将临时文件删掉。但是它仅支持XLSX文件，需要java8及以上运行环境。
Excel数据量太大，使用poi的XSSFWorkbook时内存栈溢出，解决方案
mymic的博客
12-29 2731
五、所以可以先为此excel文件创建一个实体类或者直接使用JSONObject，先把excel里的数据读取到List内存中，再对整个List进行操作就能实现随机访问。一、使用XSSFWorkbook 直接读取大excel时，直接报java.lang.OutOfMemoryError: Java heap space。四、此时的sheet无法随机访问某一行，调用如下getRow()方法会报错。只能通过for来遍历全部。二、因此引进包xlsx-streamer来使用流读取（poi相关包需要在4.0以上）。
poi读取超大excel内存溢出问题
letterss的博客
02-01 4133
读取超大数据量excel会导致内存溢出，可使用流式处理组件xlsx-streamer解决内存溢出问题。
解决POI的XSSFWorkbook导入大excel的内存消耗过大问题
热门推荐
y41992910的博客
11-28 1万+
方式1:使用SXSSFWorkbook ,经过测试,这个情况无效,因为本质上SXSSFWorkbook 也是通过XSSFWorkbook来的,他可以解决写出excel的场景,但是解决不了我们这种用户上传且读取excel中的内容的场景 XSSFWorkbook XSSFWorkbook = new XSSFWorkbook(fileInputStream); System.gc(); SXSSFWorkbook SXSSFWorkbook = new SXSS
完美解决java读取excel内存溢出问题.rar
06-06
完美解决java读取excel内存溢出问题，希望可以帮到大家
解决Java导入excel大量数据出现内存溢出的问题
08-27
今天小编就为大家分享一篇解决Java导入excel大量数据出现内存溢出的问题，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
JAVA使用POI(XSSFWORKBOOK)读取EXCEL文件过程解析
08-25
需要注意的是，POI库提供了多种方式来读取EXCEL文件，包括使用HSSFWorkbook和XSSFWorkbook两种方式，对于不同的EXCEL文件格式，需要选择合适的实现类。 使用POI库读取EXCEL文件是非常方便的，通过这篇文章的介绍，...
SpringBoot整合poi实现Excel文件的导入和导出.pdf
10-08
下面我们将详细介绍如何在SpringBoot中整合POI来实现Excel的导入和导出，以及如何利用ExcelFormatUtil工具类进行单元格数据格式的判断。 首先，我们需要在项目的pom.xml文件中引入Apache POI的相关依赖，具体如下：...
java解析Excel文件并把数据存入数据库和导出数据为excel文件SpringBoot代码示例
05-08
1、java解析读取excel文件中的数据，并写入数据库。 2、java读取数据库数据，并导出为excel文件。 3、README.md中有详细的操作步骤示例。 使用说明： 1. 先使用postman导入：other/excel相关.postman_collection....
XSSFWorkbook在生成大批量数据内存溢出问题解决方案
qq_39337319的博客
09-08 8468
应用SXSSFWorkbook可解决所有数据均放在内存中导致内存溢出问题 XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); SXSSFWorkbook writeWB = new SXSSFWorkbook(wb,100); SXSSFSheet sheet = (SXSSFSheet) writeWB.createSheet("工作簿"); SXSSFRow row = (SXSSFRow) sheet.createRow
记录一次使用poi的HSSFWorkbook内存溢出问题
leigelg的博客
02-20 2280
记录一次使用poi的HSSFWorkbook内存溢出问题
解决POI读取Excel内存溢出的问题
pengli
03-27 2751
POI读取Excel有两种模式，一种是用户模式，一种是SAX模式，将xlsx格式的文档转换成CVS格式后再进行处理用户模式相信大家都很清楚，也是POI常用的方式，用户模式API接口丰富，我们可以很容易的使用POI的API读取Excel，但用户模式消耗的内存很大，当遇到很多sheet、大数据网格、假空行、公式等问题时，很容易导致内存溢出。POI官方推荐解决内存溢出的方式使用CVS格式解析， &lt...
解决java poi海量数据导出内存溢出问题
u010486495的专栏
08-21 2428
摘要： 解决java poi海量数据导出导致内存溢出的问题 找了很多天的解决方法，一直被分页查询绕进去了，其实数据都能查出来的，真正卡的地方是ExcelExportUtil类下调错了方法。     最开始调用的方法是（标记的地方）：  workbook = new HSSFWorkbook();和 workbook = new XSSFWorkbook(); 这两个方法就是导出Excel...
Excel大批量导入内存溢出，多线程SAX读取方式解决，及页面返回数据响应超时解决办法
xiangchengzhangdecai的博客
03-01 4865
在POI中提供的XSSFWorkBook及HSSFWorkBook在Excel表格大批量读取以及写入操作时，就是数据量过大会产生内存溢出；在网上众多能解决的方案中，我会提供一个实际运用并能成功解决的方案；采用SAX及多线程读取的方式，POI其实存在两种读取的方式，用户模式和驱动模式在这里就不解释两者的区别；说说SAX读取方式和XSSFWorkBook读取方式的区别，XSSFWorkBook采用的方...
poi0020,大文件读取，有效解决内存溢出。
登峰小蚁
11-04 4227
资源贴：https://www.cnblogs.com/swordfall/p/8298386.html 解决思路，一次加载少量文件进内存，处理完成后，再加载下一批，不断循环。难点：传统的用户模式读取方式没法完成，用户模式是一次性将文件内容全部加载到内存中；所以需要引入另外一种事件模式，事件模式是一次读取一条进入文件。 代码实现： 1、建立maven工程，导入项目依赖 &lt;depen...
后端node-xlsx读取excel文件后时间格式的转换 - 利用xlsx自带方法
新欧粒子炮的博客
03-27 5004
在node端中使用node-xlsx读取带有日期数据的excel文件文件时，发现日期数据全部变成了数字，手动验证发现是距离1900年1月1日的天数，网上很多解决方案为进行天数转换，但是要额外增加一个函数，麻烦。查看官方文档在解析文件时也就一个路径参数，如下 import xlsx from 'node-xlsx'; // Or var xlsx = require('node-xlsx').d...
Java解析Excel文件（.xlsx和.xls格式均适用）
07-28
你可以使用Apache POI库来解析Excel文件。下面是一个使用Java解析Excel文件的示例代码： ```java import java.io.File; import java.io.FileInputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ExcelParser { public static void main(String[] args) { String filePath = "path/to/your/excel/file.xlsx"; // 替换为你的Excel文件路径 try { FileInputStream fis = new FileInputStream(new File(filePath)); Workbook workbook; if (filePath.endsWith(".xlsx")) { workbook = new XSSFWorkbook(fis); // 处理.xlsx文件 } else if (filePath.endsWith(".xls")) { workbook = new HSSFWorkbook(fis); // 处理.xls文件 } else { throw new IllegalArgumentException("The specified file is not Excel file"); } Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表 for (Row row : sheet) { for (Cell cell : row) { CellType cellType = cell.getCellType(); if (cellType == CellType.STRING) { System.out.print(cell.getStringCellValue() + " "); } else if (cellType == CellType.NUMERIC) { System.out.print(cell.getNumericCellValue() + " "); } else if (cellType == CellType.BOOLEAN) { System.out.print(cell.getBooleanCellValue() + " "); } } System.out.println(); } workbook.close(); fis.close(); } catch (IOException e) { e.printStackTrace(); } } } ``` 请将代码中的`"path/to/your/excel/file.xlsx"`替换为你实际的Excel文件路径。该代码会打开Excel文件并输出每个单元格的值。你可以根据需要对解析的内容进行进一步处理。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 21
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值