File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Other Open Source Projects and the fly likes How to edit a large .xlsx(3 MB) file using apache poi ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "How to edit a large .xlsx(3 MB) file using apache poi ?" Watch "How to edit a large .xlsx(3 MB) file using apache poi ?" New topic
Author

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

Sidhartha Ray
Greenhorn

Joined: Jul 04, 2012
Posts: 29
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..........
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42047
    
  64
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?


Ping & DNS - my free Android networking tools app
Sidhartha Ray
Greenhorn

Joined: Jul 04, 2012
Posts: 29
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 ???
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42047
    
  64
What is "the same problem"? Have you tried 2GB? Also increase PermGen.
Sidhartha Ray
Greenhorn

Joined: Jul 04, 2012
Posts: 29
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.....
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42047
    
  64
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.
Sidhartha Ray
Greenhorn

Joined: Jul 04, 2012
Posts: 29
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..........
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42047
    
  64
Not using POI. But why would you look for an alternative? 1GB is not a lot of memory for a production JVM.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to edit a large .xlsx(3 MB) file using apache poi ?