• Post Reply Bookmark Topic Watch Topic
  • New Topic

Save POI Excel file in JSF  RSS feed

 
Maksim Ustinov
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm new in POI and fairly new to JSF.
I spend almost whole day searching everywhere for my question but could not find any useful answer. There were topics to convert DataTable to excel but that's not what I need because I need to generate complicated excel spreadsheet.

Here is the scenario that I want to use in my program: Within simple JSF page I have a button that calls function in backing bean. That function generates HSSF Workbook with all goodies (sheets, rows, cells) using data from database after it finishes creating a file user should get a popup window to Open/Save/Cancel file. This file should not be stored on the server at any time, only user should store it.

I use RichFaces in my JSF but I don't think it should make any problems.

So, is it possible to have this scenario? if yes, then can you give me some example or clues how to do that.

Thanks in advance.
 
Rob Spoor
Sheriff
Posts: 21052
85
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is an evil trick to this, and it is so very easy too.

First of all, you set the content type to "application/vnd.ms-excel".
Next, you write just one simple thing: a table.

A little code I wrote in ASP once, as a test:

As you can see, the table cells contain i + j. Next I write one additional column with a function call; it basically is SUM(A1:T1), SUM(A2:T2) etc. You can use headers to set the file name as well I believe.

Of course this does require that the client has Excel installed, otherwise the user can only download it. If you make sure it has extension "xls" it will still open in Excel though.
[ September 16, 2008: Message edited by: Rob Prime ]
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JSF (being based on JSP) would have problems streaming binary content (like an XLS file) back to the browser. Can you make the code that creates the file a servlet instead of a JSF or JSP page? Then it would be trivial to stream it to the client.
 
Rob Spoor
Sheriff
Posts: 21052
85
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But then you still need to store it on the server, something Maksim does not want.
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But then you still need to store it on the server

No, POI can create files in memory, from where they can be streamed to any output stream, including a servlet output stream.
 
Rob Spoor
Sheriff
Posts: 21052
85
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're right of course, you need to manually store it.
 
Maksim Ustinov
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So, as I see, there is no way to stream file back to the user without saving it on the server? But I'm sure there should be some way of doing that.

The problem with HTML table and then saving it as .XLS file is that when user will open a file, Excel will worn a user the the file has wrong file format. Of course I can use Formulas in HTML table but still, HTML is not a solution to this problem.
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ummm, did you read what I wrote above? I said specifically that it is possible. Not from a JSP, but from a servlet.
 
Maksim Ustinov
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, i miss understood your answer.
Where can I find an example of stream file from Servlet using JSF?
Thanks
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
An example of streaming from a servlet can be found at http://faq.javaranch.com/java/CodeBarnSimpleStream
That example is about streaming a file, so it's not exactly what you're asking, but the basics of the code are similar. Really, all it is is to save the HSSFWorkBook to the servlet output stream. (for which there is a method in the HSSFWorkBook class).

JSF plays no part in this. I'm sure it's possible to forward a request coming in to a JSF URL to the servlet that streams the content (or to stream binary content directly from JSF), I just don't know how.
[ September 16, 2008: Message edited by: Ulf Dittmer ]
 
Maksim Ustinov
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply.
I just found an example of how to do streaming in JSF. Here is that page:
http://devpinoy.org/blogs/lamia/archive/2007/05/30/writing-to-a-servlet-response-stream-servletresponsestream-in-jsf.aspx

I'll let you know when I make code working.
 
Ulf Dittmer
Rancher
Posts: 42972
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kool. Just for reference, this is the JavaRanch thread that helped the author of that blog entry.
[ September 16, 2008: Message edited by: Ulf Dittmer ]
 
Maksim Ustinov
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I got it to work without any problem.
Here is what I did. First I have created a servlet class called HttpJSFUtil here is the code:
package englearn.elc.bean;


then in my backing bean made sample method that generates POI Excel object:


and in my JSF I made this button:



Works perfectly for me!!!
 
ramesh maredu
Ranch Hand
Posts: 210
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this helped me a lot thanks.
 
Armen Arzumanyan
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Good example, if you have additional examples or codes for reading/writing excel document please publish, API examples not enough! I really need
Read excel file from JSF datatable. Give list to datatable............

THANKS
 
Armen Arzumanyan
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
how can i fill cells from List or Vector?
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!