Win a copy of OCP Oracle Certified Professional Java SE 11 Programmer I Study Guide: Exam 1Z0-815 this week in the Programmer Certification forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Paweł Baczyński
  • Piet Souris
  • Vijitha Kumara

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

 
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • 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: 43011
76
  • Mark post as helpful
  • send pies
  • 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
  • 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: 43011
76
  • Mark post as helpful
  • send pies
  • 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
  • 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: 43011
76
  • Mark post as helpful
  • send pies
  • 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
  • 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: 43011
76
  • Mark post as helpful
  • send pies
  • 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.
 
This looks like a job for .... legal tender! It says so right in this tiny ad:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!