aspose file tools*
The moose likes XML and Related Technologies and the fly likes Parse Excel & generate XML file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Engineering » XML and Related Technologies
Bookmark "Parse Excel & generate XML file" Watch "Parse Excel & generate XML file" New topic
Author

Parse Excel & generate XML file

Mahesh Pinnamaneni
Ranch Hand

Joined: Jul 02, 2004
Posts: 100
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

Joined: Mar 22, 2000
Posts: 12671
    
    5
A google search reveals that this question comes up frequently.

Here is one of the top references.

Bill


Java Resources at www.wbrogden.com
Mahesh Pinnamaneni
Ranch Hand

Joined: Jul 02, 2004
Posts: 100
Thanks for the response. I looked at this thread before but found not so useful for my requirement.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18113
    
    8

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

Joined: Jul 02, 2004
Posts: 100
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
Marshal

Joined: Mar 22, 2005
Posts: 39535
    
  27
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.


Ping & DNS - updated with new look and Ping home screen widget
Mahesh Pinnamaneni
Ranch Hand

Joined: Jul 02, 2004
Posts: 100
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

Joined: Jul 02, 2004
Posts: 100
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

Joined: Mar 22, 2000
Posts: 12671
    
    5
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
 
 
subject: Parse Excel & generate XML file
 
Similar Threads
read xml file
how to generate java class from XML contenct
What do i choose???
Customizing JAXB
How to do a Comparator with two properties ?