Hi Team,
We use below 3 POI jars in in our organization for Excel processing .
· poi-3.8
· poi-ooxml-3.8-20120326.jar
· poi-ooxml-schemas-3.8-20120326.jar
Below excel formats are getting processed with the help of mentioned jar files.
· .xls
· .xlsx
· .xlsm
We have a new requirement where we need to load 500MB of .xlsm file and delete
the particular sheet from that workbook and save the updated excel in to the
server location.
Tried something like this but, while loading opc package into xssf work book
gave us out of memory error .
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
OPCPackage pkg =
OPCPackage.open("C:\\PROJECTS\\Test.xlsm");
XSSFWorkbook wb = new XSSFWorkbook(pkg);
SXSSFWorkbook wb1 = new SXSSFWorkbook(wb, 100);
Out Of Memory Error log:
JVMDUMP032I JVM requested System dump using 'C:\PROJECTS\
Workspace\webcontent\core.20190409.141414.14460.0001.dmp' in response to an
event
JVMDUMP010I System dump written to 'C:\PROJECTS\ Workspace\webcontent
\core.20190409.141414.14460.0001.dmp
We have another logic running fine where currently we are parsing same file and
reading some data .
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader( pkg );
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator)
r
.getSheetsData();
int index = 1;
while (iter.hasNext()) {
iter.next();
String sheetName = iter.getSheetName();
if(sheetName.equals("SHEET1")){
break;
}
index++;
}
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst); ==>
org.apache.xerces.parsers.SAXParser
InputStream sheet2 = r.getSheet("rId"+index);
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
pkg.close();
>From the stack overflow forum understood that one of the solution is to
>increase the JVM to support this .
But in my case already we have enabled maximum possible size for our JVM since
our application has more no of concurrent users .So further increasing it not
possible in my case.
Question: Is there any possible way available to delete particular sheet
without memory issue using Apache POI ?
Note: We are using JRE 1.8
Please provide your suggestions on this .
Thanks & Regards,
Dhanaraja.R
