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 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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "cell formatting in excel using jsp" Watch "cell formatting in excel using jsp" New topic
Author

cell formatting in excel using jsp

Avneet Singh
Ranch Hand

Joined: Apr 25, 2006
Posts: 51
Hi

I have to create a report in excel using jsp.Which has been done by setting the content type as "application\vnd.ms-excel" .
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.

Thanks
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41032
    
  43
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 ]

Ping & DNS - my free Android networking tools app
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 http://www.wotsit.org has it).



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



42
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.
Avneet.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: cell formatting in excel using jsp
 
Similar Threads
Excel Treating String as Date
How to generate real excel reports
Saving data to Excel files
Reduce Excel XML file size
Applying styles conditionally to an excel spreadsheet rendered as an xml