background

Before, I took over a project to parse Excel using POI components in Java, but it was often analyzed in OOM. Later, I optimized it from the following aspects and solved 99% of the problems. Yes, you read that correctly, only 99% of the problems were solved.

The solution

  1. Adjust the HEAP memory of the JVM

We know that almost all the Java object instances are stored in the Java heap, OOM must be the heap memory is not enough, so we first switch to the heap memory.

The following command changes the initial and maximum memory size of the heap to 4g after JVM startup:

java -Xms4g -Xmx4g
Copy the code
  1. Limit Excel size

A lot of data takes up a lot of memory. After observing for a period of time, I found that some Excel has pictures or dozens of sheet pages, while the data that really needs to be analyzed may only be hundreds of lines. Therefore, I directly limited the size of Excel files when uploading.

  1. Modify POI source code

Although the frequency of OOM appears after the above two schemes is low, but there will still be OOM. Later, I found several Excel that reported OOM to track POI source code, and found that a lot of empty line POI have created objects, and it is good to directly modify the empty line without processing.

The POI version used in the project is 3.17, and the initRows() method of xssfsheet.java is modified as follows, with only three lines (including braces) added:

private void initRows(CTWorksheet worksheetParam) {
        _rows.clear();
        tables = new TreeMap<String, XSSFTable>();
        sharedFormulas = new HashMap<Integer, CTCellFormula>();
        arrayFormulas = new ArrayList<CellRangeAddress>();
        for(CTRow row: worksheetParam getSheetData () getRowArray ()) {/ / modify poi source beginif(row.getCArray().length<=0){
        		continue; } end XSSFRow r = new XSSFRow(row, this); // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory final Integer rownumI = new Integer(r.getRowNum()); // NOSONAR _rows.put(rownumI, r); }}Copy the code

If the above three solutions do not work, you can consider using easyExcel, Alibaba open source, annotated, readable, want to know more can refer to: github.com/alibaba/eas…

Recommended reading

1. Hand-by-hand guide you to complete the sub-database sub-table with database middleware Mycat+SpringBoot

2. 35 Java code optimization details

Interviewer ali: Tell me the realization principle behind wechat and Taobao scanning code login respectively.

4. A minute to introduce you to MyBatis dynamic SQL!

5. Spring Security in a minute!

If you think the article is good, I hope you can forward it or “I am reading” oh, thank you very much!

Follow the public account below and reply to “1024”, there are surprises!