java - xml generation from excel sheet content

I have a xsd and a MS-Excel spreadsheet. The excel sheet contains Xpath like representation of all the elements/fields of the xsd along with the corresponding data type as specified in the xsd.

For example if xsd contains a element as follows:

<xsd:complexType name="person">开发者_开发问答
  <xsd:all>
   <xsd:element name="name" type="xsd:string" minOccurs="0"/>
   <xsd:element name="age" type="xsd:string" minOccurs="0"/>
  </xsd:all>
</xsd:complexType>

The excel sheet content is as follows:

Column 2 (element/field name)        Column 3 (data type)
person.name                          String
person.age                           String

I have a requirement to check if the content of the excel sheet is valid and complete. Thus I want to a generate xml file from a MS-Excel spreadsheet and validate it aganist an existing xsd.

The requirement details are as follows:

1. Read the content of the second and third column of a MS-Excel spreadsheet.
2. Ignore the rows whose content does not contain a dot separator
3. generate xml
4. validate it aganist the user specified xsd

How can this be done in a java program?

There are several Java libraries to read Excel documents, including jxl and JExcel. (You might also consider using a CSV file (Comma Separated Values), which is simpler than Excel and not proprietary.)

To parse the XSD file, use Java's built-in XML parser, like this:

DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
Document doc = domBuilder.parse(new FileInputStream("file.xsd"));

You can use XInclude to insert the values into your document. There is an example of it here: Default support for xinclude in Java 6?

You will need to write your own entity resolver, which can then read the Excel-file and find the right values there. I don't have any experience with jxl, but JExcel is a solid library for manipulating Excel-files.

How about https://community.informatica.com/solutions/b2bdt_automation_template_excel_generator if you have Informatica DT installed? You can generate the script through java, and execute it on command line using java!