How to edit a large .xlsx(3 MB) file using apache poi ?

Hi experts,

I've a large .xlsx file with multiple sheets in it. I've to retrieve one perticular sheet from the workbook & put some values in some columns.

I've already visited these :

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java
-I found, we can only read by using it

http://stackoverflow.com/questions/7274076/writing-a-large-resultset-to-an-excel-file-using-poi
-I found, we can only write new sheets, can't modify existing sheets.

I want your suggestions regarding this........

Thanks in advance..........

The POI API can certainly be used to edit files. It is a memory hog, though, so you may have to crank the JVM memory settings all the way up.

What have you tried so far that has failed?

Hi Ulf Dittmer,

You are saying about increasing heap size, but if my file size'll increase to 600 MB, then the same problem'll occur.....

I want a solution which'll convert a perticular sheet to it's .xml file, modify it & again merge it to the workbook using POI.

Is it possible ???

What is "the same problem"? Have you tried 2GB? Also increase PermGen.

Hi Ulf Dittmer,

Is there any alternative for that, actually I can't change the environment configuration..........

My application is like this :

Some reports are coming to me by my mail everyday. I've to read them, parse them & put them in .xlsx file which lies somewhere in the server's file system.
The .xlsx file contains reporths of entire year, so the size becomes very large (say 3-5 MB).
When I'm trying to read it using POI jars :

XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("D:\\reports.xlsx"));

it thows OutOfMemoryError.

I also have tried the SAX based event model provided by POI for reading the .xlsx file, it's woking fine, but I can't modify the file by using this........
Again SXSSFWorkbook another class provided for creating new large .xlsx file, but we can't modify an existing file by using this.....

Is there any alternative for that, actually I can't change the environment configuration.

The first step should be for you to try whether it actually works. It it does, then getting a setting in the environment changed will be far quicker than looking for some other solution, which whoever is in charge of these decisions will appreciate. But yes, you would need to challenge whoever said so.

Hi Ulf Dittmer,

It's working fine if I'm configuring the runtime as,

java -Xms512m -Xmx1024m com.LoadDemo

Now, is there any alternative ?
Please suggest..........

Not using POI. But why would you look for an alternative? 1GB is not a lot of memory for a production JVM.