Re: [xml-dev] Converting Excel Table to XML document

[Chin Chee-Kai <cheekai@softml.net>]

Title: Re: [xml-dev] XML Schema: "Best used with the ______ tool"

You can run a simple tool UBLish v2.0.alpha [1] to convert Excel spreadsheet into a generic XML-Spreadsheet format, then do your  XSLT to transform the raw XML  form into your intended format.  Description of XML-Spreadsheet format (which is a simple format) can be found at [2].  When you run UBLish, just click on:

    Spreadsheet Conversion --> To XML-Spreadsheet --> Convert A Spreadsheet

and you will be prompted to select the input source Excel spreadsheet.  You will be asked whether there is a header row label to generate.  For your case, it would seem you can enter "1" to indicate Row 1 contains header labels to be reproduced in the <Column> attributes (please see below).


An outline of the generic XML-Spreadsheet format  using your sample data would look like:

<Sheet>
<Information>
  <Filename>MyTable.xls</Filename>
  <GeneratedBy>UBLish v2.0.0.kpo</GeneratedBy>
  <GeneratedOn>20081219 101332</GeneratedOn>
</Information>
<Row rowIndex="1">
  <Column rowIndex="1" columnIndex="1" columnLabel="SourceApp">SourceApp</Column>
  <Column rowIndex="1" columnIndex="2" columnLabel="TargetApp">TargetApp</Column>
</Row>
<Row rowIndex="2">
  <Column rowIndex="2" columnIndex="1" columnLabel="SourceApp">Competitor</Column>
  <Column rowIndex="2" columnIndex="2" columnLabel="TargetApp">Commercial</Column>
</Row>
<Row rowIndex="3">
  <Column rowIndex="3" columnIndex="1" columnLabel="SourceApp">Partner</Column>
  <Column rowIndex="3" columnIndex="2" columnLabel="TargetApp">Business Partner</Column>
</Row>
</Sheet> 

You can then transform this into any customized form using XSLT.

Alternatively, since UBLish is open-sourced scripting, you can edit the XML-Spreadsheet generation function so that it generates directly the XML template you want (sort of merging Excel-->XML-->XSLT into the script).  To do this, use your favorite line editor to open the file "bin\UBLish-v2.0.alpha.kpo" and search for "<Sheet>".  That will bring you to the function that performs the XML generation, at which point you can edit it to your desire.

Hope this is useful to you.


Regards,
Chin Chee-Kai


[1] UBLish v2.0.alpha download, http://sourceforge.net/project/showfiles.php?group_id=238704&package_id=292341&release_id=631432
[2] Description of XML-Spreadsheet format, http://sourceforge.net/forum/forum.php?thread_id=2257107&forum_id=863486


Oscar Rodriguez wrote:

84DC60601283074B9194BACBBC3E8751273748@BE145.mail.lan" type="cite">

Hi - I have the following reference table in Excel

SourceApp    TargetApp

Competitor   Commercial
Partner        Business Partner
...

Does anyone know an easy way to convert the above table to an XML that looks as follows?

<?xml version="1.0" encoding="UTF-8"?>
<dvm name="Customer" isNew="null">
  <description>DVM description</description>
  <columns>
     <column name="SourceApp"/>
     <column name="TargetApp"/>
  </columns>
  <rows>
    <row>
      <cell>Competitor</cell>
      <cell>COMMERICAL</cell>
    </row>
  </rows>
</dvm>

Thanks in advance
- Oscar