File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JSP and the fly likes cell formatting in excel using jsp Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "cell formatting in excel using jsp" Watch "cell formatting in excel using jsp" New topic

cell formatting in excel using jsp

Avneet Singh
Ranch Hand

Joined: Apr 25, 2006
Posts: 51

I have to create a report in excel using jsp.Which has been done by setting the content type as "application\" .
Report comes out fine, but a field which has no. in it comes like
"9.19812E+11" the actual data once u double click on the cell comes as "919811911126" . To correct this we have to format the cell by rt clicking(cell formatting) on it and making it no. with 0 decimals.

I am creating this excel file using <table></table> in jsp.Data gets populated to the cells.

Can we do this formatting at time of report creation on jsp.

Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
Hello Avn-

Welcome to JavaRanch.

On your way in you may have missed that JavaRanch has a policy on display names, and yours does not comply with it - please adjust it accordingly, which you can do right here. Thanks for your prompt attention to this matter.

As to your question, Excel will treat anything that looks like a number as a number -even if it is not- and handle it as it sees fit, which by default means scientific nmber format. I think -since there is no way to specify formatting in CSV or HTML- there is no way to prevent this. Do you have a particular reason why you're not generating an actual XLS file, where you can control the formatting of the field? An API like POI is not hard to use.
[ April 25, 2006: Message edited by: Ulf Dittmer ]
Jeroen T Wenting
Ranch Hand

Joined: Apr 21, 2006
Posts: 1847
You're going to have to do a lot more work than just telling IE that an HTML table is an Excel sheet.
You're going to have to create the data as a SYLK document and stream that to the browser.

Best do that in a servlet rather than a JSP, maybe using a script rather than hardcoding the SLK layout for flexibility.

Here's a small example, see the file format specs for more advanced features (you can find it online, I believe has it).

This creates a simple spreadsheet containing 3 columns with different formating options.

Avneet Singh
Ranch Hand

Joined: Apr 25, 2006
Posts: 51
Thaks for your help guys il use the poi api then. I had a look and it seems easy to implement. Il do this and get to you guys if i run into some prob.

Thanks alot.
I agree. Here's the link:
subject: cell formatting in excel using jsp
It's not a secret anymore!