This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Other Open Source Projects and the fly likes Writing on excel file using POI Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Writing on excel file using POI" Watch "Writing on excel file using POI" New topic
Author

Writing on excel file using POI

ram kumar
Ranch Hand

Joined: May 22, 2008
Posts: 146
Dear All ranchers,

Am using apache POI to write an excel

with one row, one cell, value "HAI"

row -5 cell -6 , value - hai




I have 2 clarifications.

1.when i say this line

<%@ page contentType="application/vnd.ms-excel" %>

It shows me a dialog box that says <OPEN> <SAVE> <CANCEL>

but none of the excel sheet contents are getting printed in the excel sheet , am getting a plain excel sheet

why ?

I need that dialog box and the content as HAI in the ROW -5 and Cell - 6 location.

2.when i comment that line and execute its working fine

as

< commented >
<%//@ page contentType="application/vnd.ms-excel" %>

3.when i say

style1.setFillBackgroundColor(HSSFColor.YELLOW.index);

it fills the cell with normal black.

color "yellow" is not coming in the background.

everything else is fine


I have given the code too :

Please clarify me, as am very new to this forum..















Discussion - the powerfull way to excellence!
Ankit Garg
Sheriff

Joined: Aug 03, 2008
Posts: 9291
    
  17

Ram what are you doing. You have added some HTML to that page, then later on you are creating a excel sheet in your JSP . You should separate the two things. Also when you say that the excel file is empty, what do you mean by that?? Do you mean to say that the excel file saved at C:\excelExample.xls??


SCJP 6 | SCWCD 5 | Javaranch SCJP FAQ | SCWCD Links
ram kumar
Ranch Hand

Joined: May 22, 2008
Posts: 146
Ankit Garg wrote:Ram what are you doing. You have added some HTML to that page, then later on you are creating a excel sheet in your JSP . You should separate the two things. Also when you say that the excel file is empty, what do you mean by that?? Do you mean to say that the excel file saved at C:\excelExample.xls??


Yes ! there are no contents in the excel file , saying size 0 kb.

how do you seperate the html and jsp code in this page !

i need both ! atleast to display title of the page.

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41062
    
  43
You can't stream a binary file like XLS from a JSP page; you need to use a servlet instead.


Ping & DNS - my free Android networking tools app
ram kumar
Ranch Hand

Joined: May 22, 2008
Posts: 146
Code corrected.

Hi Ankit, can you just answer some other questions in this scenario

Is that ok.

Still can any one help me in bringing the background color.

and

bringing the dialog box that will popup if i have used

contentType = "vnd.msexcel"

<%//i need this line to popup the dialog box.
//but it does not print any thing in the excel.
//@ page contentType="application/vnd.ms-excel"
%>



Ankit Garg
Sheriff

Joined: Aug 03, 2008
Posts: 9291
    
  17

Ram you are still doing it wrong. You need to do something like this in a Servlet

ram kumar
Ranch Hand

Joined: May 22, 2008
Posts: 146
Ankit Garg wrote:Ram you are still doing it wrong. You need to do something like this in a Servlet



Yes ! Ankit am trying to integrate that ina servlet as Ulf said.

Of course, do you think that the background

color as i mentioned in the first post will change by making it as a servlet.

also the dialog box.

If so let me have touch on it.

Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41062
    
  43
Instead of speculating what might change if you approached this right way (using a servlet), why don't you rewrite it as one and see what happens? You'll need to do that no matter what. The problem may all be follow-ups to that fundamental issue.
Ankit Garg
Sheriff

Joined: Aug 03, 2008
Posts: 9291
    
  17

Just read this to find out what you are doing wrong.

The pop-up thing will work with this servlet solution (I think you want the pop-up to appear as it is the correct behavior)...
ram kumar
Ranch Hand

Joined: May 22, 2008
Posts: 146
Ankit Garg wrote:Just read this to find out what you are doing wrong.

The pop-up thing will work with this servlet solution (I think you want the pop-up to appear as it is the correct behavior)...



The pop-up thing will work with this servlet solution (I think you want the pop-up to appear as it is the correct behavior)...


Yes ! Isint it right !
Ankit Garg
Sheriff

Joined: Aug 03, 2008
Posts: 9291
    
  17

ram kumar wrote:The pop-up thing will work with this servlet solution (I think you want the pop-up to appear as it is the correct behavior)...



Yes ! Isint it right !

Well it is the right behavior. Browsers don't support excel files so they will ask you what you want to do with the response. Usually the browser lets you save the file or open it using a helper application...
ram kumar
Ranch Hand

Joined: May 22, 2008
Posts: 146
Hey ankit and All !

After Converting from jsp to Servlet code for generating excel,

1.It does not change its color in the background and fore ground.
am not able to get the OPEN/SAVE/CLOSE dialog box.

2.if i say res.setContentType("application/vnd.ms-excel");



its giving me an empty excel file with no contents.

Please help in this regard.

I have give the servlet Code too,







Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41062
    
  43
1.It does not change its color in the background and fore ground.
am not able to get the OPEN/SAVE/CLOSE dialog box.

2.if i say res.setContentType("application/vnd.ms-excel");
its giving me an empty excel file with no contents.

Why should the browser do anything if you don't create any response? You need to stream the data to the ServletOutputStream, not save it to a file.
ram kumar
Ranch Hand

Joined: May 22, 2008
Posts: 146
Ankit Garg wrote:
ram kumar wrote:The pop-up thing will work with this servlet solution (I think you want the pop-up to appear as it is the correct behavior)...



Yes ! Isint it right !


Well it is the right behavior. Browsers don't support excel files so they will ask you what you want to do with the response. Usually the browser lets you save the file or open it using a helper application...

No , I need to save the file to the disk .

I should give an option like that and that is the requirement too.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41062
    
  43
ram kumar wrote:No , I need to save the file to the disk .

I should give an option like that and that is the requirement too.

That had better not be a requirement, because that's completely out of the hand of the web app. If the user has set her browser to open the file (as opposed to storing it), there's nothing the web app can do about that.
shibaram sahoo
Greenhorn

Joined: Sep 21, 2005
Posts: 17
which version of POI you are using ?


We can do it !!
http://letitsearch.googlepages.com/a.html
 
 
subject: Writing on excel file using POI
 
Similar Threads
Rendering Excel spreadsheet in JSP OR via CSV File Download
Generate large excel using POI
Web Page to Excel Sheet
How to save a file in client machine
Identifier Expected,Misplaced Constructs