• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

cell formatting in excel using jsp

 
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 1847
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Avneet Singh
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic