Since the previous article: a common class will be able to install your Springboot, do you believe? Later, it happened that this time I found a problem, so with this article, I still want to use the way of “running account” in the previous article to find and solve the problem. This article is mainly because an OOM(OutOfMemoryError) exception occurred when importing a 20M Excel using POI. It is a shame to say that after so many years of work, I have never imported a file of this size, and there is an overflow of memory. There are two ways for POI to import Excel files. The first is user mode, which is very simple and intuitive, and can be compared to THE DOM parsing of XML. (Here only for Excel 2007, because 2003 itself has a maximum number of limits and is rarely used at present. ), the second is the Event mode, which is usually said on the Internet to solve the POI import large Excel “panacea” method, can be analogous to XML SAX parsing way. The JVM uses -xms512m -XMx512m. Don’t believe you just write an import interface, import the following the size of a 20 m execl see: link: pan.baidu.com/s/1DUrS8ctL… Extraction code: HD79.
First of all, since we want to import a large number of excel2007, so we should know a little bit about how this file store data, we can find on baidu, 2007 is actually a compressed package, you can directly modify the suffix into zip and unzip open the file to see, as follows
As you can see, the two largest files are sharedstrings.xml and sheet1.xml. Sheet2.xml can be deleted directly from Excel, but it doesn’t matter. This Excel file was also provided to me directly by the test. Because sheet2 is small, it has nothing to do with the memory overflow mentioned in this article, so please don’t be distracted.
Open the two large files above directly with a large text editing tool, and you can see that the contents of SharedString. XML are the string contents of every cell in Excel (except the number types). Understanding here basically understand the basic knowledge of this article said the problem, and then enter the main topic.
First import excel by using the event provided in Baidu. The code is as follows:
package com.example.utils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.File;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
/**
* 百度上直接copy过来的
* XSSF and SAX (Event API)
*/
public abstract class BigDataParseExcelUtil extends DefaultHandler {
private ReadOnlySharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int sheetIndex = -1;
private List<String> rowlist = new ArrayList<String>();
private int curRow = 0; // 当前行
private int curCol = 0; // 当前列索引
private int preCol = 0; // 上一列列索引
private int titleRow = 0; // 标题行，一般情况下为0
private int rowsize = 0; // 列数
private List excelList = new ArrayList(); //excel全部转换为list
// excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型
public abstract void optRows(int sheetIndex, int curRow,
List<String> rowlist, List excelList) throws SQLException, Exception;
// 只遍历一个sheet，其中sheetId为要遍历的sheet索引，从1开始，1-3
/**
* @param filename
* @param sheetId sheetId为要遍历的sheet索引，从1开始，1-3
* @throws Exception
*/
public void processOneSheet(String filename, int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
XMLReader parser = fetchSheetParser(strings);
// rId2 found by processing the Workbook
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheet2 = r.getSheet("rId" + sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
@Override
public void characters(char[] ch, int start, int length)
throws SAXException {
// 得到单元格内容的值
lastContents += new String(ch, start, length);
}
public void process(InputStream inputStream) throws Exception {
OPCPackage pkg = OPCPackage.open(inputStream);
XSSFReader r = new XSSFReader(pkg);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
XMLReader parser = fetchSheetParser(strings);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
/**
* 遍历 excel 文件
*/
public void process(File file) throws Exception {
OPCPackage pkg = OPCPackage.open(file);
XSSFReader r = new XSSFReader(pkg);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
XMLReader parser = fetchSheetParser(strings);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
public XMLReader fetchSheetParser(ReadOnlySharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader();
// .createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
@Override
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 单元格
if (name.equals("c")) {
// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
String cellType = attributes.getValue("t");
String rowStr = attributes.getValue("r");
curCol = this.getRowIndex(rowStr);
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 置空
lastContents = "";
}
@Override
public void endElement(String uri, String localName, String name)
throws SAXException {
// 根据SST的索引值的到单元格的真正要存储的字符串
// 这时characters()方法可能会被调用多次
if (nextIsString) {
try {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
.toString();
} catch (Exception e) {
}
}
// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
if (name.equals("v")) {
String value = lastContents.trim();
value = value.equals("") ? " " : value;
int cols = curCol - preCol;
if (cols > 1) {
for (int i = 0; i < cols - 1; i++) {
rowlist.add(preCol, "");
}
}
preCol = curCol;
rowlist.add(curCol - 1, value);
} else {
// 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
if (name.equals("row")) {
int tmpCols = rowlist.size();
if (curRow > this.titleRow && tmpCols < this.rowsize) {
for (int i = 0; i < this.rowsize - tmpCols; i++) {
rowlist.add(rowlist.size(), "");
}
}
try {
optRows(sheetIndex, curRow, rowlist, excelList);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (curRow == this.titleRow) {
this.rowsize = rowlist.size();
}
rowlist.clear();
curRow++;
curCol = 0;
preCol = 0;
}
}
}
// 得到列索引，每一列c元素的r属性构成为字母加数字的形式，字母组合为列索引，数字组合为行索引，
// 如AB45,表示为第（A-A+1）*26+（B-A+1）*26列，45行
public int getRowIndex(String rowStr) {
rowStr = rowStr.replaceAll("[^A-Z]", "");
byte[] rowAbc = rowStr.getBytes();
int len = rowAbc.length;
float num = 0;
for (int i = 0; i < len; i++) {
num += (rowAbc[i] - 'A' + 1) * Math.pow(26, len - i - 1);
}
return (int) num;
}
}
Copy the codepackage com.example.service; import com.example.utils.BigDataParseExcelUtil; import org.springframework.stereotype.Service; import java.io.InputStream; import java.sql.SQLException; import java.util.List; /** * @author: rongdi * @date: */ @Service public class ExcelService { public void import1(InputStream inputStream) throws Exception { BigDataParseExcelUtil xlx = new BigDataParseExcelUtil() { @Override public void optRows(int sheetIndex, int curRow, List<String> rowlist, List excelList) throws SQLException { System.out.println(rowlist); }}; xlx.process(inputStream); }}Copy the codepackage com.example.controller;
import com.example.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
/**
* @author: rongdi
* @date:
*/
@Controller
public class ExcelController {
@Autowired
private ExcelService excelService;
@RequestMapping("/excel/import1")
@ResponseBody
public String import1(@RequestParam("file") MultipartFile multipartFile) throws Exception {
excelService.import1(multipartFile.getInputStream());
return "ok";
}
}
Copy the codeXLSX = 20M; postman = 20M;
So let’s optimize and instead of using inputStream, we’ll just pass in a File
public void import2(File file) throws Exception { BigDataParseExcelUtil xlx = new BigDataParseExcelUtil() { @Override public void optRows(int sheetIndex, int curRow, List<String> rowlist, List excelList) throws SQLException { System.out.println(rowlist); }}; xlx.process(file); } @RequestMapping("/excel/import2") @ResponseBody public String import2(@RequestParam("file") MultipartFile MultipartFile) throws the Exception {/ / delay resolution ratio ZipSecureFile setMinInflateRatio (1.0 d); File tmp = Files.createTempFile("tmp-", ".xlsx").toFile(); Files.copy(multipartFile.getInputStream(), Paths.get(tmp.getPath()), StandardCopyOption.REPLACE_EXISTING); excelService.import2(tmp); return "ok"; }Copy the codeUploading a file using Postman runs as follows:
At this time, I found it very embarrassing. Is there a bug in the CODE of POI? We can use breakpoint debugging to confirm whether all the contents in sharedstrings.xml are placed in this strings.
It is proved above that the memory overflow is caused by too much strings. It can be seen from this that it is basically impossible to use event events to parse Excel on the Internet. Ah, I don’t understand why baidu has such answers, don’t they encounter large data import? Of course, I may have wronged them, because sharedstrings. XML stores the string content of each cell, which has been rearranged. If there are many cells in Excel, but most of them are integers or most of them are repeated, then you can skip this step. After all, SAX parsing XML does save a lot of memory.
From the above analysis, it can be seen that there are two ways to import POI: one is simple to write code in user mode, and it is good to count grids in order, but it is similar to DOM mode to parse XML, which consumes a lot of memory. The second, event-like way of parsing XML like SAX does save a lot of memory, but POI provides a class that puts a lot of parsed strings into the collection, causing an overflow of memory. So how do we solve this problem, it is very normal idea is whether the strings are used to do, how to use, if can keep the same strings under the premise of logic function, modify the logic of ReadOnlySharedStringsTable this class, can solve the memory leak. Then we can directly search ReadOnlySharedStringsTable class all use strings breakpoint method, especially the method of value from the strings, and then adjust the JVM memory to avoid memory overflow the breakpoint debugging is as follows
Can’t we just add strings and the method that gets strings instead of using strings as a set to store all strings? But since Excel is designed to use sharedstrings.xml to store common strings, rather than CSV format, which reads one row at a time, it’s a good idea. So the data in sharedstrings. XML has to be parsed out and stored somewhere. Otherwise, how do you get each row of data in sheet. You can’t parse sharedstrings.xml without saving it every time you need to get strings. If you look at the XML in this article, you have to parse this XM 25W times, which is extremely inefficient. Now the problem is that we need to put all the strings parsed by sharedstrings.xml in one place that we can easily parse, but we can’t put them in memory for fear of running out of memory. There are options, such as parsing strings into a database, file, external storage, or cache (limited memory size, extra writing to files) in the order they are added to the strings collection. Then use the time according to the index position IDX to take out one by one. This article first uses temporary files to put these data, because do not want to get so complicated, no matter how complex the import task in the system, the final execution will be a single node, in the single node to use the local resource is the most convenient resource. The following is a direct copy of the source code, and then modify the above two places.
package com.example.utils;
import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.util.Removal;
import org.apache.poi.xssf.model.SharedStrings;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.ParserConfigurationException;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.LineNumberReader;
import java.io.PushbackInputStream;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import static org.apache.poi.xssf.usermodel.XSSFRelation.NS_SPREADSHEETML;
public class ReadOnlySharedStringsTable extends DefaultHandler implements SharedStrings {
protected final boolean includePhoneticRuns;
/**
* An integer representing the total count of strings in the workbook. This count does not
* include any numbers, it counts only the total of text strings in the workbook.
*/
protected int count;
/**
* An integer representing the total count of unique strings in the Shared String Table.
* A string is unique even if it is a copy of another string, but has different formatting applied
* at the character level.
*/
protected int uniqueCount;
/**
* The shared strings table.
*/
private List<String> strings;
private File tmp = null;
FileOutputStream fos = null;
private int counts;
private Map<Integer,String> map = new LinkedHashMap<Integer,String>();
public ReadOnlySharedStringsTable(OPCPackage pkg)
throws IOException, SAXException {
this(pkg, true);
}
public ReadOnlySharedStringsTable(OPCPackage pkg, boolean includePhoneticRuns)
throws IOException, SAXException {
this.includePhoneticRuns = includePhoneticRuns;
ArrayList<PackagePart> parts =
pkg.getPartsByContentType(XSSFRelation.SHARED_STRINGS.getContentType());
// Some workbooks have no shared strings table.
if (parts.size() > 0) {
PackagePart sstPart = parts.get(0);
readFrom(sstPart.getInputStream());
}
}
/**
* Like POIXMLDocumentPart constructor
*
* Calls {@link #ReadOnlySharedStringsTable(PackagePart, boolean)}, with a
* value of <code>true</code> to include phonetic runs.
*
* @since POI 3.14-Beta1
*/
public ReadOnlySharedStringsTable(PackagePart part) throws IOException, SAXException {
this(part, true);
}
/**
* Like POIXMLDocumentPart constructor
*
* @since POI 3.14-Beta3
*/
public ReadOnlySharedStringsTable(PackagePart part, boolean includePhoneticRuns)
throws IOException, SAXException {
this.includePhoneticRuns = includePhoneticRuns;
readFrom(part.getInputStream());
}
/**
* Read this shared strings table from an XML file.
*
* @param is The input stream containing the XML document.
* @throws IOException if an error occurs while reading.
* @throws SAXException if parsing the XML data fails.
*/
public void readFrom(InputStream is) throws IOException, SAXException {
// test if the file is empty, otherwise parse it
PushbackInputStream pis = new PushbackInputStream(is, 1);
int emptyTest = pis.read();
if (emptyTest > -1) {
pis.unread(emptyTest);
InputSource sheetSource = new InputSource(pis);
try {
XMLReader sheetParser = SAXHelper.newXMLReader();
sheetParser.setContentHandler(this);
sheetParser.parse(sheetSource);
} catch(ParserConfigurationException e) {
throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
}
}
}
/**
* Return an integer representing the total count of strings in the workbook. This count does not
* include any numbers, it counts only the total of text strings in the workbook.
*
* @return the total count of strings in the workbook
*/
@Override
public int getCount() {
return this.count;
}
/**
* Returns an integer representing the total count of unique strings in the Shared String Table.
* A string is unique even if it is a copy of another string, but has different formatting applied
* at the character level.
*
* @return the total count of unique strings in the workbook
*/
@Override
public int getUniqueCount() {
return this.uniqueCount;
}
/**
* Return the string at a given index.
* Formatting is ignored.
*
* @param idx index of item to return.
* @return the item at the specified position in this Shared String table.
* @deprecated use <code>getItemAt</code> instead
*/
@Removal(version = "4.2")
@Deprecated
public String getEntryAt(int idx) {
/**
* 这里就是修改部分了，直接从按行存储的临时文件读取需要的字符串
*/
String value = map.get(idx + 1);
if(value == null) {
return readString(idx,1000,this.uniqueCount);
} else {
return value;
}
}
/**
* 从指定位置读取size个字符串，这里是使用局部性原理，每次读取size个字符串，
* 以免每次需要读取文件，性能极低
* @return
*/
private String readString(int idx,int size,int numbers) {
map.clear();
int currNumber = idx + 1;
if (currNumber < 0 || currNumber > numbers) {
return null;
}
try {
FileReader in = new FileReader(tmp);
LineNumberReader reader = new LineNumberReader(in);
try {
String line = "";
for(int i = 1;i <= numbers;i ++) {
line = reader.readLine();
if(i >= currNumber && i < currNumber + size) {
map.put(i, line);
}
}
} finally {
reader.close();
in.close();
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
return map.get(idx + 1);
}
/**
* Returns all the strings.
* Formatting is ignored.
*
* @return a list with all the strings
* @deprecated use <code>getItemAt</code> instead
*/
@Removal(version = "4.2")
@Deprecated
public List<String> getItems() {
return strings;
}
@Override
public RichTextString getItemAt(int idx) {
return new XSSFRichTextString(getEntryAt(idx));
}
//// ContentHandler methods ////
private StringBuilder characters;
private boolean tIsOpen;
private boolean inRPh;
@Override
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if (uri != null && ! uri.equals(NS_SPREADSHEETML)) {
return;
}
if ("sst".equals(localName)) {
String count = attributes.getValue("count");
if(count != null) this.count = Integer.parseInt(count);
String uniqueCount = attributes.getValue("uniqueCount");
if(uniqueCount != null) this.uniqueCount = Integer.parseInt(uniqueCount);
try {
tmp = Files.createTempFile("tmp-", ".xlsx").toFile();
} catch (IOException e) {
e.printStackTrace();
}
// this.strings = new ArrayList<>(this.uniqueCount);
characters = new StringBuilder(64);
try {
fos = new FileOutputStream(tmp,true);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
} else if ("si".equals(localName)) {
characters.setLength(0);
} else if ("t".equals(localName)) {
tIsOpen = true;
} else if ("rPh".equals(localName)) {
inRPh = true;
//append space...this assumes that rPh always comes after regular <t>
if (includePhoneticRuns && characters.length() > 0) {
characters.append(" ");
}
}
}
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
if (uri != null && ! uri.equals(NS_SPREADSHEETML)) {
return;
}
if ("si".equals(localName)) {
// strings.add(characters.toString().intern());
try {
/**
* 这里就是修改的一部分，这里直接把字符串按行存入临时文件
*/
counts ++;
fos.write((characters.toString() + "\n").getBytes());
if(counts == this.uniqueCount) {
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
} else if ("t".equals(localName)) {
tIsOpen = false;
} else if ("rPh".equals(localName)) {
inRPh = false;
}
}
/**
* Captures characters only if a t(ext) element is open.
*/
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
if (tIsOpen) {
if (inRPh && includePhoneticRuns) {
characters.append(ch, start, length);
} else if (! inRPh){
characters.append(ch, start, length);
}
}
}
}
Copy the codeThen change the package to your own package in your own code, replace the package of that class in the POI, and run the JVM heap as follows without any pressure
The memory overflow problem is solved! The problems encountered in importing large Excel using POI are summarized as follows:
1) No matter the user mode or event mode, the online schemes often cannot support the import of large Excel
2) Excel is essentially a bundle of Excel compressions (consider 2007 and ignore 2003) renamed XLSX
Opcpackage.open (file). If you pass in the input stream directly, the logic will store all the contents of the input stream into the ByteArrayOutputStream. This output stream is actually a byte stream in memory. So it can also cause memory overflow.
The event schema parses sharedString. XML into a List. You can’t get around it by any means, because the strings for each cell are in the XML. And the most common way to parse this XML is to store it in memory in a container that uses a list and a map and I don’t think anyone would want to just parse an XML and put it back in a file, Here is not open around basic ReadOnlySharedStringsTable or SharedStringsTable, even if you get around, want to parse, may still repeat the tragedy of these two classes, the root cause of this is another kind of memory.
As a review of the above implementation, it is a poor implementation to save the contents of sharedstrings. XML to a file and then retrieve it from the file. The following is a direct reference to the ReadCache used in easyExcel source code to save sharedstrings.xml content
package com.example.advanceevent; import com.example.utils.FileUtils; import org.ehcache.Cache; import org.ehcache.CacheManager; import org.ehcache.config.CacheConfiguration; import org.ehcache.config.builders.CacheConfigurationBuilder; import org.ehcache.config.builders.CacheManagerBuilder; import org.ehcache.config.builders.ResourcePoolsBuilder; import org.ehcache.config.units.MemoryUnit; import org.ehcache.core.Ehcache; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.File; import java.util.HashMap; import java.util.UUID; /** * @author: rongdi * @date: */ public class ReadCache { private static final Logger LOGGER = LoggerFactory.getLogger(Ehcache.class); private int index = 0; private HashMap<Integer, String> dataMap = new HashMap(1334); private static CacheManager fileCacheManager; private static CacheConfiguration<Integer, HashMap> fileCacheConfiguration; private static CacheManager activeCacheManager; private CacheConfiguration<Integer, HashMap> activeCacheConfiguration; private Cache<Integer, HashMap> fileCache; private Cache<Integer, HashMap> activeCache; private String cacheAlias; private int cacheMiss = 0; public ReadCache(int maxCacheActivateSize) { this.activeCacheConfiguration = CacheConfigurationBuilder.newCacheConfigurationBuilder(Integer.class, HashMap.class, ResourcePoolsBuilder.newResourcePoolsBuilder().heap((long)maxCacheActivateSize, MemoryUnit.MB)).withSizeOfMaxObjectGraph(1000000L).withSizeOfMaxObjectSize((long)maxCacheActivateSize, MemoryUnit.MB).build(); init(); } private void init() { this.cacheAlias = UUID.randomUUID().toString(); this.fileCache = fileCacheManager.createCache(this.cacheAlias, fileCacheConfiguration); this.activeCache = activeCacheManager.createCache(this.cacheAlias, this.activeCacheConfiguration); } public void put(String value) { this.dataMap.put(this.index, value); if ((this.index + 1) % 1000 == 0) { this.fileCache.put(this.index / 1000, this.dataMap); this.dataMap = new HashMap(1334); } ++this.index; if (LOGGER.isDebugEnabled() && this.index % 1000000 == 0) { LOGGER.debug("Already put :{}", this.index); } } public String get(Integer key) { if (key ! = null && key >= 0) { int route = key / 1000; HashMap<Integer, String> dataMap = (HashMap)this.activeCache.get(route); if (dataMap == null) { dataMap = (HashMap)this.fileCache.get(route); this.activeCache.put(route, dataMap); if (LOGGER.isDebugEnabled() && this.cacheMiss++ % 1000 == 0) { LOGGER.debug("Cache misses count:{}", this.cacheMiss); } } return (String)dataMap.get(key); } else { return null; } } public void putFinished() { if (this.dataMap ! = null) { this.fileCache.put(this.index / 1000, this.dataMap); } } public void destroy() { fileCacheManager.removeCache(this.cacheAlias); activeCacheManager.removeCache(this.cacheAlias); } static { File cacheFile = FileUtils.createCacheTmpFile(); fileCacheManager = CacheManagerBuilder.newCacheManagerBuilder().with(CacheManagerBuilder.persistence(cacheFile)).build(true); activeCacheManager = CacheManagerBuilder.newCacheManagerBuilder().build(true); fileCacheConfiguration = CacheConfigurationBuilder.newCacheConfigurationBuilder(Integer.class, HashMap.class, ResourcePoolsBuilder.newResourcePoolsBuilder().disk(10L, MemoryUnit.GB)).withSizeOfMaxObjectGraph(1000000L).withSizeOfMaxObjectSize(10L, MemoryUnit.GB).build(); }}Copy the codepackage com.example.advanceevent;
import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.util.Removal;
import org.apache.poi.xssf.model.SharedStrings;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import javax.xml.parsers.ParserConfigurationException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.util.ArrayList;
import java.util.List;
import static org.apache.poi.xssf.usermodel.XSSFRelation.NS_SPREADSHEETML;
public class ReadOnlySharedStringsTable extends DefaultHandler implements SharedStrings {
protected final boolean includePhoneticRuns;
/**
* An integer representing the total count of strings in the workbook. This count does not
* include any numbers, it counts only the total of text strings in the workbook.
*/
protected int count;
/**
* An integer representing the total count of unique strings in the Shared String Table.
* A string is unique even if it is a copy of another string, but has different formatting applied
* at the character level.
*/
protected int uniqueCount;
/**
* 缓存
*/
ReadCache readCache = new ReadCache(100);
private int counts;
public ReadOnlySharedStringsTable(OPCPackage pkg)
throws IOException, SAXException {
this(pkg, true);
}
public ReadOnlySharedStringsTable(OPCPackage pkg, boolean includePhoneticRuns)
throws IOException, SAXException {
this.includePhoneticRuns = includePhoneticRuns;
ArrayList<PackagePart> parts =
pkg.getPartsByContentType(XSSFRelation.SHARED_STRINGS.getContentType());
// Some workbooks have no shared strings table.
if (parts.size() > 0) {
PackagePart sstPart = parts.get(0);
readFrom(sstPart.getInputStream());
}
}
/**
* Like POIXMLDocumentPart constructor
*
* Calls {@link #ReadOnlySharedStringsTable(PackagePart, boolean)}, with a
* value of <code>true</code> to include phonetic runs.
*
* @since POI 3.14-Beta1
*/
public ReadOnlySharedStringsTable(PackagePart part) throws IOException, SAXException {
this(part, true);
}
/**
* Like POIXMLDocumentPart constructor
*
* @since POI 3.14-Beta3
*/
public ReadOnlySharedStringsTable(PackagePart part, boolean includePhoneticRuns)
throws IOException, SAXException {
this.includePhoneticRuns = includePhoneticRuns;
readFrom(part.getInputStream());
}
/**
* Read this shared strings table from an XML file.
*
* @param is The input stream containing the XML document.
* @throws IOException if an error occurs while reading.
* @throws SAXException if parsing the XML data fails.
*/
public void readFrom(InputStream is) throws IOException, SAXException {
// test if the file is empty, otherwise parse it
PushbackInputStream pis = new PushbackInputStream(is, 1);
int emptyTest = pis.read();
if (emptyTest > -1) {
pis.unread(emptyTest);
InputSource sheetSource = new InputSource(pis);
try {
XMLReader sheetParser = SAXHelper.newXMLReader();
sheetParser.setContentHandler(this);
sheetParser.parse(sheetSource);
} catch(ParserConfigurationException e) {
throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
}
}
}
/**
* Return an integer representing the total count of strings in the workbook. This count does not
* include any numbers, it counts only the total of text strings in the workbook.
*
* @return the total count of strings in the workbook
*/
@Override
public int getCount() {
return this.count;
}
/**
* Returns an integer representing the total count of unique strings in the Shared String Table.
* A string is unique even if it is a copy of another string, but has different formatting applied
* at the character level.
*
* @return the total count of unique strings in the workbook
*/
@Override
public int getUniqueCount() {
return this.uniqueCount;
}
/**
* Return the string at a given index.
* Formatting is ignored.
*
* @param idx index of item to return.
* @return the item at the specified position in this Shared String table.
* @deprecated use <code>getItemAt</code> instead
*/
@Removal(version = "4.2")
@Deprecated
public String getEntryAt(int idx) {
/**
* 这里就是修改部分了，直接从按行存储的临时文件读取需要的字符串
*/
return readCache.get(idx);
}
/**
* Returns all the strings.
* Formatting is ignored.
*
* @return a list with all the strings
* @deprecated use <code>getItemAt</code> instead
*/
@Removal(version = "4.2")
@Deprecated
public List<String> getItems() {
return null;
}
@Override
public RichTextString getItemAt(int idx) {
return new XSSFRichTextString(getEntryAt(idx));
}
//// ContentHandler methods ////
private StringBuilder characters;
private boolean tIsOpen;
private boolean inRPh;
@Override
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if (uri != null && ! uri.equals(NS_SPREADSHEETML)) {
return;
}
if ("sst".equals(localName)) {
String count = attributes.getValue("count");
if(count != null) this.count = Integer.parseInt(count);
String uniqueCount = attributes.getValue("uniqueCount");
if(uniqueCount != null) this.uniqueCount = Integer.parseInt(uniqueCount);
// this.strings = new ArrayList<>(this.uniqueCount);
characters = new StringBuilder(64);
} else if ("si".equals(localName)) {
characters.setLength(0);
} else if ("t".equals(localName)) {
tIsOpen = true;
} else if ("rPh".equals(localName)) {
inRPh = true;
//append space...this assumes that rPh always comes after regular <t>
if (includePhoneticRuns && characters.length() > 0) {
characters.append(" ");
}
}
}
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
if (uri != null && ! uri.equals(NS_SPREADSHEETML)) {
return;
}
if ("si".equals(localName)) {
// strings.add(characters.toString().intern());
readCache.put(characters.toString());
/**
* 这里就是修改的一部分，这里直接把字符串按行存入临时文件
*/
counts ++;
if(counts == this.uniqueCount) {
readCache.putFinished();
}
} else if ("t".equals(localName)) {
tIsOpen = false;
} else if ("rPh".equals(localName)) {
inRPh = false;
}
}
/**
* Captures characters only if a t(ext) element is open.
*/
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
if (tIsOpen) {
if (inRPh && includePhoneticRuns) {
characters.append(ch, start, length);
} else if (! inRPh){
characters.append(ch, start, length);
}
}
}
}
Copy the codeAt this point, the code efficiency has improved considerably, and the memory overflow problem has been resolved. Detailed test code: github.com/rongdi/poi-…