aspose file tools*
The moose likes Other Open Source Projects and the fly likes Creating Exel File, streamming 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 "Creating Exel File, streamming" Watch "Creating Exel File, streamming" New topic
Author

Creating Exel File, streamming

Jilesh Lakhani
Ranch Hand

Joined: Jul 26, 2006
Posts: 49

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 ?


-Jilesh
Universe and Knowledge has no bounderies
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42935
    
  68
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

Joined: Jul 26, 2006
Posts: 49

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
Marshal

Joined: Mar 22, 2005
Posts: 42935
    
  68
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

Joined: Jul 26, 2006
Posts: 49

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
Marshal

Joined: Mar 22, 2005
Posts: 42935
    
  68
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

Joined: Jul 26, 2006
Posts: 49

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
Marshal

Joined: Mar 22, 2005
Posts: 42935
    
  68
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

Joined: Jul 26, 2006
Posts: 49

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
Marshal

Joined: Mar 22, 2005
Posts: 42935
    
  68
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

Joined: Jul 26, 2006
Posts: 49

I'll take a close look at that and will let you know on what I decide upon.
m Shalaby
Greenhorn

Joined: Sep 09, 2009
Posts: 2
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 :

Martijn Verburg
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

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.


Cheers, Martijn - Blog,
Twitter, PCGen, Ikasan, My The Well-Grounded Java Developer book!,
My start-up.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Creating Exel File, streamming