0

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?

Adam Wagner
  • 15,469
  • 7
  • 52
  • 66
joe
  • 11
  • 1
  • 3

3 Answers3

1

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"));
Dan R.
  • 761
  • 5
  • 21
0

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!

chaity
  • 163
  • 2
  • 11
0

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.

Community
  • 1
  • 1
tveon
  • 579
  • 5
  • 24