Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Parse Excel & generate XML file

 
Mahesh Pinnamaneni
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys,

I need to generate the XML. The XML file will have the definition like this:



All the values in name,birthdaye,gender will come from the Excel file. I am having 100 rows in the Excel file with the data and I want to parse this excel file and create the XML file with the above template.

Any ideas how to do this?

Thanks,
Mahesh
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13074
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A google search reveals that this question comes up frequently.

Here is one of the top references.

Bill
 
Mahesh Pinnamaneni
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the response. I looked at this thread before but found not so useful for my requirement.
 
Paul Clapham
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You haven't said what is your problem. Is it reading the Excel document? Is it producing the XML document? (I didn't follow Bill's link because I didn't feel I would be able to evaluate why it didn't help you. But perhaps you could explain that too.)
 
Mahesh Pinnamaneni
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
as i said my requirement is to read the excel file and create the XML file with the format shown in my first thread...where as the IBM JXL library will output the XML to command line. we can also create the XML which this library shows in the command line just by saving the excel as XML. I want to know what technology (SAX/XPATH etc..) I need to use to achieve my goal.
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't really think that the article is all that helpful.

The starting point would be to read the file (using a library like jExcelApi or POI). Both come with example code how to do that.

The next step is to write the XML to a file; if the XML is simple you might even do it directly using the classes in the java.io package, without the help of any XML API. If you're determined to use an API, there are a number that are easier to use than DOM; like XOM, JDOM and dom4j.
 
Mahesh Pinnamaneni
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply..

I am looking at a bigger picture...My Goal is to achieve the following:
1) I will have the data in Excel File & I want to generate the XML file with this Data in Excel.
2) For generating an XML file, I will have to use the DTD for format.
3) I have to build a strategy to get the value from a particular cell in Excel and assign it to the Element / Attribute.
This will be easy we can implement like this because in future if there are any more attributes / elements added, we just need to simply give the strategy.

Hope this helps...

Thanks,
mahesh
 
Mahesh Pinnamaneni
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
for better understanding...please see below:

I need a strategy or technology to use to achieve the following:
1) Create an XML file with the values taken from Excel file.
2) Substitute the values as shown below in the XML template file:
<node type="physicalitem" action="create">
<location>Enterprise:Box: + EXCEL_CELLVALUE[A,i]</location> // WHERE [A,i] IS A COLUMN AND i row (i=1,2,3...)
<title> EXCEL_CELLVALUE[B,i]</title>
<mediatype>87244</mediatype> // remains as a constant
<physicallocation>TIC</physicallocation> // remains as constant
<physicalproperty name="SpecNoTransNo" value=EXCEL_CELLVALUE[C,i]></physicalproperty>
<physicalproperty name="ManufacturerName" value=EXCEL_CELLVALUE[D,i]></physicalproperty>
<physicalproperty name="BoxBarcodeNumber" value=EXCEL_CELLVALUE[E,i]></physicalproperty>
<category name="Livelink Categories:Test Category">
<attribute name="Name">EXCEL_CELLVALUE[F,i]</attribute>
<attribute name="Email">EXCEL_CELLVALUE[G,i]</attribute>
</category>
</node>

Here EXCEL_CELLVALUE[B,i] is the variable or some kind that i need to replace with Excel cell values.

Any idea how can I acheieve this?

Thanks,
Mahesh
 
William Brogden
Author and all-around good cowpoke
Rancher
Posts: 13074
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you look into the POI project toolkit for extracting data from Excel files as suggested by Ulf?

If - after you have mastered extraction of data - you have trouble creating an XML document, this forum can help.

Bill
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic