devbox
繁依Fanyi0
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Excel大文件时读取内存溢出的解决方案，测试50M的Excel通过。_xssfreader.getsharedstringtable内存溢出

作者：繁依Fanyi0 | 2024-02-16 00:37:10

赞

踩

xssfreader.getsharedstringtable内存溢出

1.大文件xlsx的文件解析，本案例仅仅只是读取Excel文件的内容为例。具体需求可按自己的需求改。

  1. package com.sundy.parse.util;
  2.  
  3. import java.io.File;
  4. import java.io.IOException;
  5. import java.io.InputStream;
  6. import java.util.ArrayList;
  7.  
  8. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  9. import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
  10. import org.apache.poi.openxml4j.opc.OPCPackage;
  11. import org.apache.poi.openxml4j.opc.PackageAccess;
  12. import org.apache.poi.xssf.eventusermodel.XSSFReader;
  13. import org.apache.poi.xssf.model.SharedStringsTable;
  14. import org.apache.poi.xssf.usermodel.XSSFRichTextString;
  15. import org.xml.sax.Attributes;
  16. import org.xml.sax.InputSource;
  17. import org.xml.sax.SAXException;
  18. import org.xml.sax.XMLReader;
  19. import org.xml.sax.helpers.DefaultHandler;
  20. import org.xml.sax.helpers.XMLReaderFactory;
  21.  
  22. public class ParseXlsxExcel extends DefaultHandler {
  23. 	private OPCPackage opcPackage = null;
  24. 	private XSSFReader xssfReader = null;
  25. 	private boolean nextIsStr;
  26. 	private String cellContent; //单元格的内容
  27. 	private ArrayList<String> sheetList; // sheet的所有内容
  28. 	private ArrayList<String> excelList; // Excel的所有内容
  29. 	private SharedStringsTable sst;
  30. 	private int currentColumn = 0;
  31. 	private XMLReader reader;
  32. 	
  33. 	/**
  34. 	 * @param path .xlxs后缀的Excel文件
  35. 	 * @param sheetId 
  36. 	 * @throws IOException
  37. 	 * @throws OpenXML4JException
  38. 	 * @throws SAXException
  39. 	 */
  40. 	public ParseXlsxExcel(String path,int sheetId) throws IOException, OpenXML4JException, SAXException{
  41. 		init(new File(path));
  42. 		parseSheet(sheetId);
  43. 	}
  44. 	
  45. 	public ParseXlsxExcel(File file,int sheetId) throws IOException, OpenXML4JException, SAXException{
  46. 		init(file);
  47. 		parseSheet(sheetId);
  48. 	}
  49. 	
  50. 	/**
  51. 	 * @param file	.xlxs 后缀的Excel文件
  52. 	 * @param flag	true 表示查询所有 ，false 表示只获取sheetId为1的内容
  53. 	 * @throws IOException
  54. 	 * @throws OpenXML4JException
  55. 	 * @throws SAXException
  56. 	 */
  57. 	public ParseXlsxExcel(File file,boolean flag) throws IOException, OpenXML4JException, SAXException{
  58. 		initAll(file, flag);
  59. 	}
  60. 	
  61. 	public ParseXlsxExcel(String path,boolean flag) throws IOException, OpenXML4JException, SAXException{
  62. 		initAll(new File(path), flag);
  63. 	}
  64.  
  65. 	private void initAll(File file, boolean flag)throws InvalidFormatException, IOException, OpenXML4JException,SAXException {
  66. 		init(file);
  67. 		if(flag){
  68. 			parseAllSheet();
  69. 		}else {
  70. 			parseSheet(1);
  71. 		}
  72. 	}
  73. 	
  74. 	private void parseAllSheet() {
  75. 		int x = 0;
  76. 		int y = 0;
  77. 		while(x==y){
  78. 			try {
  79. 				parseSheet(++x);
  80. 				excelList.addAll(sheetList);
  81. 				y=x;
  82. 				currentColumn=0;
  83. 			} catch (Exception e) {
  84. 				// e.printStackTrace();
  85. 			}
  86. 		}
  87. 	}
  88.  
  89. 	private void init(File file) throws IOException, OpenXML4JException, SAXException{
  90. 		opcPackage = OPCPackage.open((file), PackageAccess.READ);
  91. 		sheetList = new ArrayList<String>();
  92. 		excelList = new ArrayList<String>();
  93. 		xssfReader = new XSSFReader(opcPackage);
  94. 		sst = xssfReader.getSharedStringsTable();
  95. 		reader = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
  96. 		reader.setContentHandler(this);
  97. 	}
  98. 	
  99. 	public void close() throws IOException{
  100. 		if(opcPackage!=null) opcPackage.close();
  101. 	}
  102. 	
  103. 	private void parseSheet(int sheetId) throws IOException, OpenXML4JException, SAXException{
  104. 		sheetList.clear();
  105. 		InputStream inStream = xssfReader.getSheet("rId"+sheetId);
  106. 		InputSource inputSource = new InputSource(inStream);
  107. 		reader.parse(inputSource);
  108. 	}
  109.  
  110. 	@Override
  111. 	public void startElement(String uri, String localName, String qName,Attributes attributes) throws SAXException {
  112. 		if(qName.equals("c")){ // c为单元格，v为值，row为行。
  113. 			String type = attributes.getValue("t");
  114. 			if(type!=null && type.equals("s")){
  115. 				nextIsStr = true;
  116. 			}else {
  117. 				nextIsStr = false;
  118. 			} 
  119. 		}
  120. 	}
  121. 	
  122. 	@Override
  123. 	public void characters(char[] ch, int start, int length) throws SAXException {
  124. 		cellContent = new String(ch, start, length);
  125. 	}
  126. 	
  127. 	@Override
  128. 	public void endElement(String uri, String localName, String qName) throws SAXException{
  129. 		if(nextIsStr&&cellContent!=""&&cellContent!=null){
  130. 			try {
  131. 				int idx = Integer.parseInt(cellContent);
  132. 				cellContent = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
  133. 			} catch (Exception e) {
  134. //				 e.printStackTrace();
  135. 			}
  136. 		}
  137. 		if(qName.equals("v")){
  138. 			sheetList.add(currentColumn++, cellContent);
  139. 			cellContent="";
  140. 		}
  141. 		
  142. 	}
  143. 	
  144. 	/** sheet的所有内容 */
  145. 	public ArrayList<String> getSheetList() {
  146. 		return sheetList;
  147. 	}
  148. 	
  149. 	/** Excel的所有内容 */
  150. 	public ArrayList<String> getExcelList() {
  151. 		return excelList;
  152. 	}
  153. 	
  154. 	public static void main(String[] args) throws Exception{
  155. 		long start = System.currentTimeMillis();
  156. 		ParseXlsxExcel excel = new ParseXlsxExcel("C:/Users/admin/Desktop/大师傅家的感觉.xlsx",true);
  157. 		excel.close();
  158. 		ArrayList<String> list = excel.getExcelList();
  159. 		long end = System.currentTimeMillis();
  160. 		
  161. 		System.out.println(list.size());//19677984
  162. 		System.out.println(end-start); //16389ms
  163. 	}
  164. }

2.xls大文件的解析，参考如下(http://gaosheng08.iteye.com/blog/624604);

  1. package com.sundy.parse.util;
  2.  
  3. import java.io.FileInputStream;
  4. import java.io.IOException;
  5. import java.util.ArrayList;
  6. import java.util.List;
  7. import java.util.regex.Pattern;
  8.  
  9. import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
  10. import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
  11. import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
  12. import org.apache.poi.hssf.eventusermodel.HSSFListener;
  13. import org.apache.poi.hssf.eventusermodel.HSSFRequest;
  14. import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
  15. import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
  16. import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
  17. import org.apache.poi.hssf.model.HSSFFormulaParser;
  18. import org.apache.poi.hssf.record.BOFRecord;
  19. import org.apache.poi.hssf.record.BlankRecord;
  20. import org.apache.poi.hssf.record.BoolErrRecord;
  21. import org.apache.poi.hssf.record.BoundSheetRecord;
  22. import org.apache.poi.hssf.record.FormulaRecord;
  23. import org.apache.poi.hssf.record.LabelRecord;
  24. import org.apache.poi.hssf.record.LabelSSTRecord;
  25. import org.apache.poi.hssf.record.NoteRecord;
  26. import org.apache.poi.hssf.record.NumberRecord;
  27. import org.apache.poi.hssf.record.RKRecord;
  28. import org.apache.poi.hssf.record.Record;
  29. import org.apache.poi.hssf.record.SSTRecord;
  30. import org.apache.poi.hssf.record.StringRecord;
  31. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  32. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  33.  
  34. /**
  35.  * 用于解析大于5M的后缀为.xls的Excel文件，防止内存溢出
  36.  * @author sundy
  37.  * 参考：http://gaosheng08.iteye.com/blog/624604
  38.  */
  39. public class ParseXlsExcel implements HSSFListener{
  40.     private int minColumns;  
  41.     private POIFSFileSystem fs;  
  42.   
  43.     private int lastRowNumber;  
  44.     private int lastColumnNumber;  
  45.   
  46.     /** Should we output the formula, or the value it has? */  
  47.     private boolean outputFormulaValues = true;  
  48.   
  49.     /** For parsing Formulas */  
  50.     private SheetRecordCollectingListener workbookBuildingListener;  
  51.     private HSSFWorkbook stubWorkbook;  
  52.   
  53.     // Records we pick up as we process  
  54.     private SSTRecord sstRecord;  
  55.     private FormatTrackingHSSFListener formatListener;  
  56.   
  57.     /** So we known which sheet we're on */  
  58.     private int sheetIndex = -1;  
  59.     private BoundSheetRecord[] orderedBSRs;  
  60.     @SuppressWarnings("unchecked")  
  61.     private ArrayList boundSheetRecords = new ArrayList();  
  62.   
  63.     // For handling formulas with string results  
  64.     private int nextRow;  
  65.     private int nextColumn;  
  66.     private boolean outputNextStringRecord;  
  67.   
  68.     private int curRow;  
  69.     private List<String> rowlist;  
  70.     @SuppressWarnings( "unused")  
  71.     private String sheetName;  
  72.     
  73. 	private  List<String> contents = new ArrayList<String>();
  74.     public ParseXlsExcel(POIFSFileSystem fs) throws Exception {  
  75.         this.fs = fs;  
  76.         this.minColumns = -1;  
  77.         this.curRow = 0;  
  78.         this.rowlist = new ArrayList<String>();  
  79.     }  
  80.   
  81.     public ParseXlsExcel(String filename) throws Exception {  
  82.         this(new POIFSFileSystem(new FileInputStream(filename)));  
  83.     }  
  84.     
  85.     //excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型  
  86.     public void optRows(int sheetIndex,int curRow, List<String> rowlist){
  87.      	StringBuffer sb = new StringBuffer();
  88.         for (int i = 0 ;i< rowlist.size();i++){  
  89.             String str = rowlist.get(i);
  90.            if(null!=str&&!"".equals(str)) sb.append(str.trim()+" ");
  91.         }  
  92.         contents.add(sb.toString());
  93.     }
  94.     /**读取到的Excel文档,返回文档的content*/  
  95.     public List<String> getList() {
  96. 		return contents;
  97. 	}
  98.     
  99.     /** 
  100.      * 遍历 excel 文件 
  101.      */  
  102.     public void process() throws IOException {  
  103.         MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);  
  104.         formatListener = new FormatTrackingHSSFListener(listener);  
  105.         HSSFEventFactory factory = new HSSFEventFactory();  
  106.         HSSFRequest request = new HSSFRequest();  
  107.         if (outputFormulaValues) {  
  108.             request.addListenerForAllRecords(formatListener);  
  109.         } else {  
  110.             workbookBuildingListener = new SheetRecordCollectingListener(formatListener);  
  111.             request.addListenerForAllRecords(workbookBuildingListener);  
  112.         }  
  113.         factory.processWorkbookEvents(request, fs);  
  114.     }  
  115.       
  116.     /** 
  117.      * HSSFListener 监听方法，处理 Record 
  118.      */  
  119.     @SuppressWarnings("unchecked")  
  120.     public void processRecord(Record record) {  
  121.         int thisRow = -1;  
  122.         int thisColumn = -1;  
  123.         String thisStr = null;  
  124.         String value = null;  
  125.           
  126.         switch (record.getSid()) {  
  127.         case BoundSheetRecord.sid:  
  128.             boundSheetRecords.add(record);  
  129.             break;  
  130.         case BOFRecord.sid:  
  131.             BOFRecord br = (BOFRecord) record;  
  132.             if (br.getType() == BOFRecord.TYPE_WORKSHEET) {  
  133.                 // Create sub workbook if required  
  134.                 if (workbookBuildingListener != null && stubWorkbook == null) {  
  135.                     stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();  
  136.                 }  
  137.                 sheetIndex++;  
  138.                 if (orderedBSRs == null) {  
  139.                     orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);  
  140.                 }  
  141.                 sheetName = orderedBSRs[sheetIndex].getSheetname();  
  142.             }  
  143.             break;  
  144.   
  145.         case SSTRecord.sid:  
  146.             sstRecord = (SSTRecord) record;  
  147.             break;  
  148.   
  149.         case BlankRecord.sid:  
  150.             BlankRecord brec = (BlankRecord) record;  
  151.   
  152.             thisRow = brec.getRow();  
  153.             thisColumn = brec.getColumn();  
  154.             thisStr = "";  
  155.             break;  
  156.         case BoolErrRecord.sid:  
  157.             BoolErrRecord berec = (BoolErrRecord) record;  
  158.   
  159.             thisRow = berec.getRow();  
  160.             thisColumn = berec.getColumn();  
  161.             thisStr = "";  
  162.             break;  
  163.   
  164.         case FormulaRecord.sid:  
  165.             FormulaRecord frec = (FormulaRecord) record;  
  166.   
  167.             thisRow = frec.getRow();  
  168.             thisColumn = frec.getColumn();  
  169.   
  170.             if (outputFormulaValues) {  
  171.                 if (Double.isNaN(frec.getValue())) {  
  172.                     outputNextStringRecord = true;  
  173.                     nextRow = frec.getRow();  
  174.                     nextColumn = frec.getColumn();  
  175.                 } else {  
  176.                     thisStr = formatListener.formatNumberDateCell(frec);  
  177.                 }  
  178.             } else {  
  179.                 thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';  
  180.             }  
  181.             break;  
  182.         case StringRecord.sid:  
  183.             if (outputNextStringRecord) {  
  184.                 // String for formula  
  185.                 StringRecord srec = (StringRecord) record;  
  186.                 thisStr = srec.getString();  
  187.                 thisRow = nextRow;  
  188.                 thisColumn = nextColumn;  
  189.                 outputNextStringRecord = false;  
  190.             }  
  191.             break;  
  192.   
  193.         case LabelRecord.sid:  
  194.             LabelRecord lrec = (LabelRecord) record;  
  195.   
  196.             curRow = thisRow = lrec.getRow();  
  197.             thisColumn = lrec.getColumn();  
  198.             value = lrec.getValue().trim();  
  199.             value = value.equals("")?"":value;  
  200. //            this.rowlist.add(thisColumn, value);  
  201.             rowlist.add(value);
  202.             break;  
  203.         case LabelSSTRecord.sid:  
  204.             LabelSSTRecord lsrec = (LabelSSTRecord) record;  
  205.   
  206.             curRow = thisRow = lsrec.getRow();  
  207.             thisColumn = lsrec.getColumn();  
  208.             if (sstRecord != null){  
  209.                 value =  sstRecord  
  210.                 .getString(lsrec.getSSTIndex()).toString().trim();  
  211.                 value = value.equals("")?"":value;  
  212.                 rowlist.add(value);
  213.             }  
  214.             break;  
  215.         case NoteRecord.sid:  
  216.             NoteRecord nrec = (NoteRecord) record;  
  217.   
  218.             thisRow = nrec.getRow();  
  219.             thisColumn = nrec.getColumn();  
  220.             thisStr = '"' + "(TODO)" + '"';  
  221.             break;  
  222.         case NumberRecord.sid:  
  223.             NumberRecord numrec = (NumberRecord) record;  
  224.   
  225.             curRow = thisRow = numrec.getRow();  
  226.             thisColumn = numrec.getColumn();  
  227.             value = formatListener.formatNumberDateCell(numrec).trim();  
  228.             value = value.equals("")?"":value;  
  229.             rowlist.add(value);
  230.             break;  
  231.         case RKRecord.sid:  
  232.             RKRecord rkrec = (RKRecord) record;  
  233.   
  234.             thisRow = rkrec.getRow();  
  235.             thisColumn = rkrec.getColumn();  
  236.             thisStr = '"' + "(TODO)" + '"';  
  237.             break;  
  238.         default:  
  239.             break;  
  240.         }  
  241.   
  242.         // 遇到新行的操作  
  243.         if (thisRow != -1 && thisRow != lastRowNumber) {  
  244.             lastColumnNumber = -1;  
  245.         }  
  246.   
  247.         // 空值的操作  
  248.         if (record instanceof MissingCellDummyRecord) {  
  249.             MissingCellDummyRecord mc = (MissingCellDummyRecord) record;  
  250.             curRow = thisRow = mc.getRow();  
  251.             thisColumn = mc.getColumn();  
  252. //            rowlist.add(thisColumn," ");  
  253.         }  
  254.   
  255.         // 更新行和列的值  
  256.         if (thisRow > -1)  
  257.             lastRowNumber = thisRow;  
  258.         if (thisColumn > -1)  
  259.             lastColumnNumber = thisColumn;  
  260.   
  261.         // 行结束时的操作  
  262.         if (record instanceof LastCellOfRowDummyRecord) {  
  263.             if (minColumns > 0) {  
  264.                 // 列值重新置空  
  265.                 if (lastColumnNumber == -1) {  
  266.                     lastColumnNumber = 0;  
  267.                 }  
  268.             }  
  269.             // 行结束时， 调用 optRows() 方法  
  270.             lastColumnNumber = -1;  
  271.             try {  
  272.                 optRows(sheetIndex,curRow, rowlist);  
  273.             } catch (Exception e) {  
  274.                 e.printStackTrace();  
  275.             }  
  276.             rowlist.clear();  
  277.         }  
  278.     }  
  279.     public static void main(String[] args) throws Exception {
  280.     	
  281. 		ParseXlsExcel excel = new ParseXlsExcel("F:\\srv\\tiptop\\crawl\\www.anxiang.gov.cn\\Excel\\3fbc7e659d1f42311ee89c6d47fac414.xls");
  282. 		excel.process();  
  283.         int size = excel.getList().size();
  284.         List<String> list2 = excel.getList();
  285.         String reg = "楚源收储点";
  286.         Pattern pattern = Pattern.compile(reg);
  287.         int x = 0;
  288.         for (int i = 0; i < size; i++) {
  289.         	String replace = list2.get(i).trim().replace("","");
  290.         	if(null!=replace&&!"".equals(replace)){
  291.         		if(pattern.matcher(replace).find()) x+=1;	
  292.         			System.out.println(replace);
  293.         	} 
  294. 		}
  295.         System.err.println(" 存在关键字【"+reg+"】的个数： "+x);
  296.     }
  297. }


声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/88890
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号