apache poi copy from very large xls to new workbook sheet

Question

I have very large xls file , which contains two sheets. I want to combine these two sheets into one and copy to new workbook . But I get out of memory exception when I try to access this large xls as below :

FileInputStream fis = new FileInputStream(new File("input.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fis);

I tried using event api for xls : http://poi.apache.org/spreadsheet/how-to.html#event_api But using that we can only read the cell values . But here I need to copy to new excel sheet.

Have you increased heap size with -Xms and Xmx parameters? — Jim Garrison
Yes. I tried that and it will not work. I need some other solution through program. — Sowmya
Please provide some specifics. What are the sizes of the two workbooks, both in number of cells (H x V) and megabytes. — Jim Garrison
That is not a "large" file and it should fit comfortably on most modern systems. Did you try with -Xms1G -Xmx2G? Can you run this on a system with more memory? Are you using the 64-bit JVM? — Jim Garrison

Ankit Tripathi Ankit Tripathi · Accepted Answer · 2016-05-30T07:21:05

Apache POI provides a low-memory footprint SXSSF API to write data to xlsx. It does not load everything in memory at once so it's the solution while working with very large excel files. You may want to consider this.

apache poi copy from very large xls to new workbook sheet

1 Answers