Troubleshoot POI read Excel memory overflow __java Basic Learning

Office software has always been a mixed blessing software, the vast majority of ordinary computer users with Office to meet day-to-day office needs, so produced a lot of production data and documents, and enterprise units of the dedicated Office system docking, and office parsing work has been a very headache programmers, Often incur the abuse of programmers, but also known as one of Microsoft's worst inventions. Poi's birth solved the Excel parsing problem (poi "Nasty spreadsheet", really annoying, I also hate Excel), but if you use a bad poi, it will cause some bugs in the program, such as memory overflow, false blank line, formula and so on. Here's a workaround for POI reading an Excel memory overflow problem.

POI read Excel has two modes, one is the user mode, one is the sax mode, the XLSX format of the document into a CVS format before processing user mode I believe that everyone is very clear, but also poi common way, user-mode API interface is rich, We can easily use the POI API to read Excel, but user mode consumes a lot of memory, when encountered many sheet, large data grids, fake blank lines, formulas and other problems, it is easy to cause memory overflow. POI official recommended solution to memory overflow using CVS format parsing, we can not manually convert the Excel file into CVS format and upload, it is too cumbersome to do, and then poi gave the xlsx conversion CVS, based on this example I did a bit of transformation, Resolves the user mode read Excel memory overflow problem. The following code is attached:

     [Java]   view plain  copy/* =============================================== =====================     Licensed to the Apache Software  foundation  (ASF)  under one or more     contributor license  agreements.  See the NOTICE file distributed with      this work for additional information regarding copyright ownership.      the asf licenses this file to you under the  Apache License, Version 2.0      (the  "License"); you  may not use this file except in compliance with      the license.  you may obtain a copy of the license  at           http://www.apache.org/licenses/license-2.0        unless required by applicable law or agreed to in writing,  software     distributed under the license is distributed on  an  "As is"  BASIS,     without warranties or conditions  OF ANY KIND, either express or implied.     See  the license for the specific language governing permissions and     limitations under the License.  ========================================= =========================== */      import java.io.ioexception;   Import  java.io.InputStream;   import java.io.printstream;   import  Java.text.SimpleDateFormat;   import java.util.arraylist;   import java.util.date;   Import  java.util.List;      import javax.xml.parsers.parserconfigurationexception;    import javax.xml.parsers.saxparser;   import javax.xml.parsers.saxparserfactory;       import org.apache.poi.hssf.usermodel.hssfdateutil;   import  org.apache.poi.openxml4j.exceptions.openxml4jexception;   import  org.apache.poi.openxml4j.opc.opcpackage;   import org.apache.poi.openxml4j.opc.packageaccess;    import org.apache.poi.ss.usermodel.builtinformats;   import  org.apache.poi.ss.usermodel.dataformatter;   import  org.apache.poi.xssf.eventusermodel.readonlysharedstringstable;   import  org.apache.poi.xssf.eventusermodel.xssfreader;   import org.apache.poi.xssf.model.stylestable;    IMPORT&Nbsp;org.apache.poi.xssf.usermodel.xssfcellstyle;   import  org.apache.poi.xssf.usermodel.xssfrichtextstring;   import org.xml.sax.attributes;   import org.xml.sax.inputsource;