• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..........
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Sidhartha Ray
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is "the same problem"? Have you tried 2GB? Also increase PermGen.
 
Sidhartha Ray
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 29
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Not using POI. But why would you look for an alternative? 1GB is not a lot of memory for a production JVM.
 
Gravity is a harsh mistress. But this tiny ad is pretty easy to deal with:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic