Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to convert XML to Excel Sheets

 
Jack Nicholson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello,everyone
How to convert XML to Excel Sheets in JSP?
I am using JSP to build XML String & then printing it out.
Help me out!
Thank you much
 
Balaji Loganathan
author and deputy
Bartender
Posts: 3150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jack Nicholson:
hello,everyone
How to convert XML to Excel Sheets in JSP?
I am using JSP to build XML String & then printing it out.
Help me out!
Thank you much

This thread will sure help you http://www.coderanch.com/t/322134/java/java/creating-excel-file-servlet
P.s unless ur input is xml,u don't need to convert the input data into xml and then to excel.
 
Jack Nicholson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Balaji ,
Till we are not using XML it's ok.
But right now in application we are using XML for representation.
& for that we need solution which can convert XML document to Excel Sheet.
I have tried the funda of setting contenttype to xml/Excel like in html, but here it doesn't works.
It puts whole the data into excel sheet. It doesn't recognises Columns.
Thanx for response.
 
Balaji Loganathan
author and deputy
Bartender
Posts: 3150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jack Nicholson:
Hi Balaji ,
Till we are not using XML it's ok.
But right now in application we are using XML for representation.
& for that we need solution which can convert XML document to Excel Sheet.
I have tried the funda of setting contenttype to xml/Excel like in html, but here it doesn't works.
It puts whole the data into excel sheet. It doesn't recognises Columns.
Thanx for response.


Its because of file extension you might have used.
please try like this
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","inline;filename=jack.csv");
then try sending the output as comma delimited according to your column structure, if its still stores data in single column,try sending as data with tab separator "\t"
 
Jack Nicholson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Balaji,
No it didn't worked.
This solution does gives some changes in output but also displaying XML tags along with the data, which i don't want.
It also displays unwanted commented data.
Is there any parser / third party tool to convert the XML based data into XSL Spreadsheets?
cause in this case the data should be parsed first & then convert it finally export it to Excel.
 
Balaji Loganathan
author and deputy
Bartender
Posts: 3150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jack Nicholson:
Hi Balaji,
No it didn't worked.
This solution does gives some changes in output but also displaying XML tags along with the data, which i don't want.
It also displays unwanted commented data.
Is there any parser / third party tool to convert the XML based data into XSL Spreadsheets?
cause in this case the data should be parsed first & then convert it finally export it to Excel.

whatever you type using out.println or out.print will go to excel, if you are using jsp all the commented lines will also go to excel, because jsp lines will be converted into out.println by servlet after jsp compilation.
to avoid this just only output the data that u want to show in excel.
Only you have to chops what should not appear in excel. if you search in javaranch or in google with the keyword "excel and servlet" or "export to excel using servlet" you may get some third party tool.if this is a one-off job better write the code urself. it can be done by trial and error.
your input is XML so you have to first parse it using XML parser and print the data to excel.
[ December 24, 2002: Message edited by: Balaji Loganathan ]
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13055
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is the POI project at jakarta.apache.org - it is for generally manipulating MS formats such as Excel. Thats what the Cocoon project uses for generating Excel spreadsheets.
Comma or Tab delimited output is by far the easiest.
Bill
 
Jack Nicholson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by William Brogden:
There is the POI project at jakarta.apache.org - it is for generally manipulating MS formats such as Excel. Thats what the Cocoon project uses for generating Excel spreadsheets.
Comma or Tab delimited output is by far the easiest.
Bill

Hi
Thanx a lot.
i want to know one more thing..
as i am generating these documents for web application, when simultaneous users uses these reports, will it create bunch of files on server side?
is there any method that these files can be generated on client side?
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13055
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You don't have to create files on the server if you don't want to. A servlet could generate them on the fly. With a correctly formatted link on your HTML page your client could suck the data straight into Excel or get a "save as" dialog and save the file.
If you want to serve XML from the server and convert it on the client to an Excel form to be saved, you would need a signed applet or maybe an application launched with Java Web Start.
Bill
 
Jack Nicholson
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much
 
Balaji Loganathan
author and deputy
Bartender
Posts: 3150
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
http://xreporter.cocoondev.org/
Just visited, if you are interested you give a try on their opensource product
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic