XML Spreadsheet to XSLT 1

[Sniipe]

Hello all, unusual subject I know, XML to XSLT...

What I have firstly is an excel sheet, which I've saved as XML spreadsheet. When I open it, its essentially a spreadsheet.

I want to use this sheet as a template as such and input XML into it.

Eventually I would like this XML spreadsheet to become an XSLT file, then transform it with XML data. The end product would be another XML spreadsheet, which when clicked on would open in Excel.

 

[Sniipe]

bah, couldn't get the attachment to work properly.

http://www.galwaywed.com/sniipetmp/blank

test tab1(vals).xml

That shows the XML file that I want to create into an XSLT file. I'm just looking for pointers on how to go about it. I have another XML file with the simple data in it that I want to input into the first XML file.

http://www.galwaywed.com/sniipetmp/mpr002.xml

 

[ChrisHunt]

Erm... what do you mean by "I would like this XML spreadsheet to become an XSLT file"?

You'd write an XSLT file to transform your data from one XML file to another - typically from an XML file to an XHTML output. The XSLT bit remains seperate from your original XML data file.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[tsuji]

I can show you the big layout of how this can be done.

[1] Static components : you can isolate those repetitive/invariant fragments/tree of nodes into external xml document.

[1.1] DocumentProperties.xml
[tt]
<?xml version="1.0" encoding="utf-8" ?>
<DocumentProperties xmlns="urn:schemas-microsoft-comfficeffice">
<Author>rcarroll</Author>
<LastAuthor>rcarroll</LastAuthor>
<Created>2008-03-12T10:32:26Z</Created>
<LastSaved>2008-03-13T10:33:55Z</LastSaved>
<Company>Storm Technology Ltd</Company>
<Version>12.00</Version>
</DocumentProperties>
[/tt]
[1.1.1] This is strictly not a static true as the datetime should reflect the actual date and time. But, the data you are working with do not have them in the file. For simplicity, I treat it as such as if it is invariant.

[1.2] Styles.xml
[tt]
<?xml version="1.0" encoding="utf-8" ?>
<Styles xmlns="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<!-- etc etc ->
</Style>
<!-- etc etc all the styles nodes -->
</Styles>
[/tt]
[1.3] WorksheetOptions.xml
[tt]
<?xml version="1.0" encoding="utf-8" ?>
<WorksheetOptions xmlns="urn:schemas-microsoft-comffice:excel"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
>
<PageSetup>
<Header x:Margin="0.3"/>
<!-- etc etc -->
</PageSetup>
<Unsynced/>
<Print>
<!-- etc etc -->
</Print>
<!-- etc etc -->
</WorksheetOptions>
[/tt]
[1.4] DataValidation.xml
Wrap all those DataValidation tags into a root of arbitrary name, say "root".
[tt]
<?xml version="1.0" encoding="utf-8"?>
<root>
<DataValidation xmlns="urn:schemas-microsoft-comffice:excel">
<Range>R2C3:R16C3</Range>
<Type>List</Type>
<Value>R3C5:R3C6</Value>
</DataValidation>
<DataValidation xmlns="urn:schemas-microsoft-comffice:excel">
<Range>R14C2</Range>
<Type>List</Type>
<UseBlank/>
<Value>R2C5:R2C9</Value>
</DataValidation>
<DataValidation xmlns="urn:schemas-microsoft-comffice:excel">
<Range>R15C2</Range>
<Type>List</Type>
<Value>R4C5:R4C6</Value>
</DataValidation>
<DataValidation xmlns="urn:schemas-microsoft-comffice:excel">
<Range>R16C2</Range>
<Type>List</Type>
<UseBlank/>
<Value>R3C5:R3C6</Value>
</DataValidation>
</root>
[/tt]
[2] Dynamic part

[2.1] Table.xsl: Design the actual in-take of data in an independent xsl document. I just put an dummy xsl document here for illustration. An example of what have to be done, you can refer to a recent thread of this nature.

http://tek-tips.com/viewthread.cfm?qid=1457242&page=1

You will see it consists of a single named template "rowcell"
[tt]
<?xml version="1.0" encoding="utf-8" ?>
<!-- this is the included document xslt -->
<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform"

xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="

http://www.w3.org/TR/REC-html40"

>
<xslutput method="xml" indent="yes" encoding="utf-8" />

<xsl:template match="data/PromotionDetailsTabWorksheet" name="rowcell">
<!-- this is to elaborate -->
<xsl:comment>this is to elaborate - msg from included template</xsl:comment>
</xsl:template>
</xsl:stylesheet>
[/tt]
[3] The main xslt document would be something like this.
[tt]
<?xml version="1.0" encoding="utf-8" ?>
<!-- this is the main document xslt -->
<xsl:stylesheet version="1.0" xmlns:xsl="

http://www.w3.org/1999/XSL/Transform"

xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="

http://www.w3.org/TR/REC-html40"

>

<!-- here contains the template mode="template" matching data/PromotionDetailsTabWorksheet" -->
<xsl:include href="Table.xsl" />

<xslutput method="xml" encoding="utf-8" indent="yes" />
<xsl:template match="/">
<xslrocessing-instruction name="mso-application">progid="Excel.Sheet"</xslrocessing-instruction>
<Workbook>
<xsl:copy-of select="document('DocumentProperties.xml')/*" />
<xsl:copy-of select="document('Styles.xml')" />
<Worksheet ss:Name="Promotion Detail Tab">
<Table [blue]ss:ExpandedColumnCount="9" ss:ExpandedRowCount="16"[/blue] x:FullColumns="1"
x:FullRows="1" ss:StyleID="s62" ssefaultRowHeight="15">
<!-- data part here -->
<xsl:for-each select="data/PromotionDetailsTabWorksheet">
<xsl:call-template name="rowcell" />
</xsl:for-each>
</Table>
<xsl:copy-of select="document('WorksheetOptions.xml')" />
<xsl:copy-of select="document('DataValidation.xml')/root/*" />
</Worksheet>
</Workbook>
</xsl:template>
</xsl:stylesheet>
[/tt]
You see the ExpandedColumnCOunt and ExpandedRowCount can be and probably need be dynamically calculated. This is shown as an example in the link above. For simplicity and the big layout sketch, I leave it static here. Here and there, if there are something of this nature, the same remark applies. As it can drag real long and technical, I leave those simplifications in.

In any case, that is the layout you can elaborate further.

 

[Sniipe]

thanks again tsuji, I actually got it working. I was failing as I had my template inside the workbook.

I will be back again in a short time no doubt with more questions. XSLT is my nemisis!