• 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

Creating Exel File, streamming

 
Ranch Hand
Posts: 49
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I wish to create Excel file, such that it should be able to incrementally write data to the file system ... and not hold everything in memory... i.e unlike HSSF POI.

Any Suggestions ?
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
JileshL,

you've been around the Ranch long enough to know that we have a policy on screen names. Basically, it must consist of a first name, a space, and a last name, and not be obviously fictitious. Since yours does not conform with it, please take a moment to change it, which you can do right here.

As to your question, the XLS file format can't be read or written in parts; it's either all or nothing. If you can't work with POI for some reason (which ones?), have a look at jExcelApi.
[ February 25, 2008: Message edited by: Ulf Dittmer ]
 
Jilesh Lakhani
Ranch Hand
Posts: 49
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How can I use that.... I am using Java.. to create the Excel file, the
problem I am facing is .. I have large data which has to be stored
into memory and den it creates the Excel file...

I over come this memory issue I have to stream the data to the clients
end.....how should I go about it.??
 
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 do you mean by "how can I use that" - jExcelApi? If so, its web site has lots of examples that should get you going. I think it generally uses less memory than POI, so if that's an issue you might give it a try.
 
Jilesh Lakhani
Ranch Hand
Posts: 49
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
well, I checked with JExcelApi,.. and the faq section says
"http://jexcelapi.sourceforge.net/resources/faq/"

The JVM places an upper limit on the amount of memory available to the current process in order to prevent runaway processes gobbling system resources and making the machine grind to a halt. When reading or writing large spreadsheets, the JVM may require more memory than has been allocated to the JVM by default - this normally manifests itself as a java.lang.OutOfMemory exception. By default, each workbook allocates 5MB when created and allocates more memory in 5MB increments. These values can be changed by using class WorkbookSettings. Often the OutOfMemory exception can be removed by lowering these values (note that performance may suffer as a result). Alternatively, you can you can allocate more memory to the JVM using the -Xms and -Xmx options; e.g., to allocate an initial heap size of 10 MB, with 100 MB as the upper bound, you should start the JVM like this: java -Xms10m -Xmx100m MyClass. In order to allocate more memory in this manner to servlets/JSPs, consult the help documentation for the Web Application Server. Oftentimes, as is the case with Tomcat and WebLogic, a simple change to a startup script is all that's necessary.

So, JExcel doesn't seem to be solution I am looking forward too.. any other option you can think of ???
 
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

So, JExcel doesn't seem to be solution I am looking forward too.


Why not? That just says that JVM memory is limited, and what you can do if you run out of it. That's true for all Java code, Excel-related or not. Have you tried it, and tested whether this is really a concern for your use cases?
 
Jilesh Lakhani
Ranch Hand
Posts: 49
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, currently I am using the POI.. and I need to create Excel Sheets with huge data...... which is currently giving us the performance issue.....
yes agreed that.. JExcel would be the better option.. when it comes to the performance.. however I just don't want the temporary patch!!.. that's what is JExcel would give me .. a temporary patch.. as it still works on the same ideology of keep things in memory and then writing it into an Excel file....
 
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
It's not a temporary patch, it's the only way to generate XLS files, as I explained above. Have you actually tried whether it is a problem? Memory is so cheap these days that you can (and should) allocate a lot to the JVM.
 
Jilesh Lakhani
Ranch Hand
Posts: 49
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, I've been hunting all over the place for this... and well I am looking at two options

1) Excel using XML generation
2) Using OpenOffice.

Do you know If any using above approaches I can achieve the Goal of Steaming Data into the Excel file??
 
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
It's not a matter of using one tool or another to create the file. The problem is that the file can't be created until all its contents are known. The file format is substantially different in concept from text files or HTML files (which you can start generating without knowing their full contents).

So you'll need to find a way to create these files in a way that doesn't exhaust memory. If you've determined that neither POI nor jExcelApi can do it (have you?), then the http://faq.javaranch.com/java/AccessingFileFormats FAQ page points to a couple of other libraries for creating Excel files called java2excel and jxls. Whether these use less memory I have no idea, but they may be worth checking out.
 
Jilesh Lakhani
Ranch Hand
Posts: 49
Python Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'll take a close look at that and will let you know on what I decide upon.
 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello Jilesh & Ulf ,
I have the same problem, have you found a solution for you problem? have you try any of the mentioned APIs?

BTW, I tryed to create an excel file incrementally on FileOutputStream and It worked fine. I had to close the FileOutputStream and reopen it after every time. But I could not do it on ServletOutPutStream!!! Do you have any idea why i cant do this on ServletOutPutStream???
Here is the code for writing on FileOutputStream :

 
author
Posts: 3285
13
Mac OS X Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi m Shalaby and welcome to Javaranch!

Please not you've responded to an old thread. You may want to start a new thread with your specific problem.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic