Taking apart the spreadsheets

[Micah Dubinko <micah@dubinko.info>]

Progress checkpoint:

A really rough first cut at an XSLT 2.0 spreadsheet that takes apart the 
1.0 source spreadsheet to generate info that's needed from the script 
that generates the input specifications.

Stylesheet attached. (the way OpenOffice can group adjacent blank cells 
requires some care!)

Here's what the typical output looks like, given input of the 
OrderResponseSimple spreadsheet. I still have to figure out some kind of 
big merge operation, either cut-n-paste to make one uber-spreadsheet out 
of the 12, or else post-processing this kind of XML.

<?xml version="1.0" encoding="UTF-8"?>
<ubl-info>
   <ubl-name name="ID">
      <label>Identifier</label>
      <cardinality>1</cardinality>
      <defn>The Order Response Id element is a unique number assigned to 
the OrderResponse by the Seller.</defn>
   </ubl-name>
   <ubl-name name="CopyIndicator">
      <label>Copy</label>
      <cardinality>0..1</cardinality>
      <defn>Indicates whether a document is a copy (true) or not 
(false)</defn>
   </ubl-name>
   <ubl-name name="GUID">
      <label>Identifier</label>
      <cardinality>0..1</cardinality>
      <defn>a computer generated unique identifier for the document, 
which is guaranteed to be unique</defn>
   </ubl-name>
   <ubl-name name="IssueDate">
      <label>Issue Date</label>
      <cardinality>1</cardinality>
      <defn>a date (and potentially time) stamp denoting when the order 
response was issued.</defn>
   </ubl-name>
   <ubl-name name="DocumentStatusCode">
      <label>Document Status</label>
      <cardinality>0..1</cardinality>
      <defn>Identifies the status of the document with regard to its 
original state</defn>
   </ubl-name>
   <ubl-name name="Note">
      <label>Note</label>
      <cardinality>0..1</cardinality>
      <defn>contains any free form text pertinent to the entire document 
or to the document message itself. This element may contain notes or any 
other similar information that is not contained explicitly in another 
structure.</defn>
   </ubl-name>
   <ubl-name name="AcceptedIndicator">
      <label>Accepted</label>
      <cardinality>1</cardinality>
      <defn>indicates whether the order was accepted (true) or not 
(false).</defn>
   </ubl-name>
   <ubl-name name="RejectionNote">
      <label>Rejection Note</label>
      <cardinality>0..1</cardinality>
      <defn>the reason for rejection if the order was not accepted.</defn>
      <examples>"Out of Stock","Not able to supply","Unable to fulfill 
within the contracted conditions", "Buyer Account not Recognised"</examples>
   </ubl-name>
   <ubl-name name="OrderReference">
      <label>Order Reference</label>
      <cardinality>1</cardinality>
      <defn>associates the Order Response (Simple) with the Order it 
refers to.</defn>
   </ubl-name>
   <ubl-name name="BuyerParty">
      <label>Buyer Party</label>
      <cardinality>1</cardinality>
      <defn>associates the Order with information about the buyer 
involved in the transaction.</defn>
   </ubl-name>
   <ubl-name name="SellerParty">
      <label>Seller Party</label>
      <cardinality>1</cardinality>
      <defn>associates the Order with information about the seller 
involved in the transaction.</defn>
   </ubl-name>
</ubl-info>

Note <examples> even shows up, where it's defined in the spreadsheet. -m

-- 
  Available for consulting. XForms, web forms, information overload.
  Micah Dubinko                           mailto:micah@dubinko.info
  Brain Attic, L.L.C.                        http://brainattic.info
  Yahoo IM: mdubinko                                +1 623 298 5172
  Learn XForms today: http://xformsinstitute.com
  BPM in plain english: http://bpmfocus.com

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
  xmlns:office="http://openoffice.org/2000/office";
  xmlns:text="http://openoffice.org/2000/text";
  xmlns:table="http://openoffice.org/2000/table";
  xmlns:number="http://openoffice.org/2000/datastyle";
  xmlns:math="http://www.w3.org/1998/Math/MathML";
  exclude-result-prefixes="office text table number math"
>
  <xsl:output indent="yes"/>


  <xsl:template match="office:document-content">
  <ubl-info>
    <xsl:for-each-group select="office:body/table:table[1]/table:table-row[position() &gt; 1]" group-by="table:table-cell[1]/text:p">
    <ubl-name name="{current-grouping-key()}">
      <xsl:for-each select="current-group()">
        <xsl:for-each select="table:table-cell">
          <xsl:variable name="pos" select="position() + sum(preceding-sibling::*/@table:number-columns-repeated) - count(preceding-sibling::*/@table:number-columns-repeated)"/>
          <xsl:choose>
            <xsl:when test="$pos = 8"><label><xsl:value-of select="text:p"/></label></xsl:when>
            <xsl:when test="$pos = 15"><cardinality><xsl:value-of select="text:p"/></cardinality></xsl:when>
            <xsl:when test="$pos = 17"><defn><xsl:value-of select="text:p"/></defn></xsl:when>
            <xsl:when test="$pos = 18 and text:p"><examples><xsl:value-of select="text:p"/></examples></xsl:when>
          </xsl:choose>
        </xsl:for-each>
      </xsl:for-each>
    </ubl-name>

    </xsl:for-each-group>
    </ubl-info>
  </xsl:template>

</xsl:stylesheet>
<?xml version="1.0" encoding="UTF-8"?>
<ubl-info>
   <ubl-name name="ID">
      <label>Identifier</label>
      <cardinality>1</cardinality>
      <defn>The Order Response Id element is a unique number assigned to the OrderResponse by the Seller.</defn>
   </ubl-name>
   <ubl-name name="CopyIndicator">
      <label>Copy</label>
      <cardinality>0..1</cardinality>
      <defn>Indicates whether a document is a copy (true) or not (false)</defn>
   </ubl-name>
   <ubl-name name="GUID">
      <label>Identifier</label>
      <cardinality>0..1</cardinality>
      <defn>a computer generated unique identifier for the document, which is guaranteed to be unique</defn>
   </ubl-name>
   <ubl-name name="IssueDate">
      <label>Issue Date</label>
      <cardinality>1</cardinality>
      <defn>a date (and potentially time) stamp denoting when the order response was issued.</defn>
   </ubl-name>
   <ubl-name name="DocumentStatusCode">
      <label>Document Status</label>
      <cardinality>0..1</cardinality>
      <defn>Identifies the status of the document with regard to its original state</defn>
   </ubl-name>
   <ubl-name name="Note">
      <label>Note</label>
      <cardinality>0..1</cardinality>
      <defn>contains any free form text pertinent to the entire document or to the document message itself. This element may contain notes or any other similar information that is not contained explicitly in another structure.</defn>
   </ubl-name>
   <ubl-name name="AcceptedIndicator">
      <label>Accepted</label>
      <cardinality>1</cardinality>
      <defn>indicates whether the order was accepted (true) or not (false).</defn>
   </ubl-name>
   <ubl-name name="RejectionNote">
      <label>Rejection Note</label>
      <cardinality>0..1</cardinality>
      <defn>the reason for rejection if the order was not accepted.</defn>
      <examples>"Out of Stock","Not able to supply","Unable to fulfill within the contracted conditions", "Buyer Account not Recognised"</examples>
   </ubl-name>
   <ubl-name name="OrderReference">
      <label>Order Reference</label>
      <cardinality>1</cardinality>
      <defn>associates the Order Response (Simple) with the Order it refers to.</defn>
   </ubl-name>
   <ubl-name name="BuyerParty">
      <label>Buyer Party</label>
      <cardinality>1</cardinality>
      <defn>associates the Order with information about the buyer involved in the transaction.</defn>
   </ubl-name>
   <ubl-name name="SellerParty">
      <label>Seller Party</label>
      <cardinality>1</cardinality>
      <defn>associates the Order with information about the seller involved in the transaction.</defn>
   </ubl-name>
</ubl-info>