Re: Problem transforming Excel XML Workbook (or how to

Play the video

Subject: Re: Problem transforming Excel XML Workbook (or how to implement one-more-counter-yet in XSLT)
From: Robert Koberg <rob@xxxxxxxxxx>
Date: Wed, 02 Nov 2005 07:13:53 -0500

Nacho Jimenez wrote:

Dear all,

I'm stuck like glue trying to get data out of an XML Workbook in
Excel, and can't find a solution without recursing to a
procedural-style counter.. Let me explain the situation i'm stuck in:

Excel produces an XML file with lots of spurious content, but in the
middle of it, i get the following nodes:

This is OT, but you don't really need XSL for this if you can deal with XML Schema. Excel has a cool ability to take an XML Schema as a 'map'. For example a schema for your spreadsheet would probably look like:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="programas">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="program" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="name" type="xs:string" maxOccurs="1"/>
              <xs:element name="line" type="xs:string" maxOccurs="1"/>
              <xs:element name="company" type="xs:string" maxOccurs="1"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

You would import the schema as an XML Map. Then select the cells for a particular element and drop that element on the selection. Then hit Data -> Export and your done.

best,
-Rob

[....]
   <Row>
    <Cell><Data ss:Type="String">E-TF-010-16425</Data></Cell>
    <Cell><Data ss:Type="String">B38379715</Data></Cell>
    <Cell><Data ss:Type="String">X</Data></Cell>
    <Cell><Data ss:Type="String">S</Data></Cell>
    <Cell><Data ss:Type="Number">11144.55</Data></Cell>
    <Cell ss:Index="16"><Data ss:Type="String">AGP</Data></Cell>
    <Cell><Data ss:Type="String">Po</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">E-TF-012-09930</Data></Cell>
    <Cell><Data ss:Type="String">B38037511</Data></Cell>
    <Cell><Data ss:Type="String">X</Data></Cell>
    <Cell><Data ss:Type="String">S</Data></Cell>
    <Cell ss:StyleID="s45"><Data ss:Type="Number">15734.4</Data></Cell>
    <Cell><Data ss:Type="String">X</Data></Cell>
    <Cell ss:Index="16"><Data ss:Type="String">AGP</Data></Cell>
    <Cell><Data ss:Type="String">Po</Data></Cell>
   </Row>
  [...]

<snip/>

<programas>
   <name>E-TF-012-09930</name>
   <line>Po</line>
   <company>AGP</company>
</programas>

Current Thread
Problem transforming Excel XML Workbook (or how to implement one-more-counter-yet in XSLT) Nacho Jimenez - 2 Nov 2005 11:12:18 -0000 Robert Koberg - 2 Nov 2005 12:13:58 -0000 <= Michael Kay - 2 Nov 2005 12:45:03 -0000 Message not available Nacho Jimenez - 2 Nov 2005 15:37:16 -0000 JBryant - 2 Nov 2005 19:31:48 -0000 <Possible follow-ups> Kaila Kaarle - 2 Nov 2005 11:18:44 -0000

<- Previous	Index	Next ->
Problem transforming Excel XM, Nacho Jimenez	Thread	RE: Problem transforming Exce, Michael Kay
RE: Problem transforming Exce, Kaila Kaarle	Date	RE: Problem transforming Exce, Michael Kay
	Month

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Subscribe in XML format

RSS 2.0
Atom 0.3

XML Editor - Download a 15 Day Free Trial Now >

See What's New in Stylus Studio >

Buy Stylus Studio - XML Editor - Now >