pgilfeather
asked on
GENERATE EXCEL PIVOT TABLE FROM XML & XSLT files?
GENERATE EXCEL PIVOT TABLE FROM XML & XSLT files?
My current project is an Internet Reporting system.
It works by using XML and XSLT files to generate HTML pages with Excel spreadsheets on then.
This works fine when the format of the Excel spreadsheet is a simple straight forward layout.
What I would like to do is generate an Excel pivot table, or something similar, using XSLT.
Is this possible or am I asking for the impossible?
Regards
Paul G
My current project is an Internet Reporting system.
It works by using XML and XSLT files to generate HTML pages with Excel spreadsheets on then.
This works fine when the format of the Excel spreadsheet is a simple straight forward layout.
What I would like to do is generate an Excel pivot table, or something similar, using XSLT.
Is this possible or am I asking for the impossible?
Regards
Paul G
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
At the moment I generate reports for the internet in the following way
1. Create a Dataset containing the data to be displayed in the brower.
2. Generate a XSLT file, with the desired layout.
3. Transform the two files into excel and display the output to a browser.
My problem is with step2.
At the moment the XSLT file generated contains a straight forward simple layout. What I would like to do is design a XSLT file which would create an Excel pivot table.
The code below is an example of how I execute the process, together with examples of my XML and XSL files.
Dim ds As New DataSet("Order")
' Read in XML from file
ds.ReadXml(context.Server.
' Set up the response for Excel.
context.Response.ContentTy
context.Response.Charset = ""
' Transform the DataSet XML using XSLTFile.xslt
' and return the results to the client in Response.Outputstream.
Dim tw As XmlTextWriter
Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
Dim xslTran As XslTransform = New XslTransform
Dim xmlResolve As XmlResolver
xslTran.Load(context.Serve
xslTran.Transform(xmlDoc, Nothing, context.Response.OutputStr
----------------EXAMPLE XML File --------------------------
<?xml version="1.0" encoding="utf-8"?>
<Order>
<Items>
<CategoryName>Beverages</C
<Year>1996</Year>
<ProductName>Chai</Product
<Value>1808</Value>
</Items>
<Items>
<CategoryName>Beverages</C
<Year>1996</Year>
<ProductName>Chang</Produc
<Value>3435</Value>
</Items>
<Items>
<CategoryName>Beverages</C
<Year>1997</Year>
<ProductName>Chartreuse verte</ProductName>
<Value>3830</Value>
</Items>
<Items>
<CategoryName>Beverages</C
<Year>1997</Year>
<ProductName>Côte de Blaye</ProductName>
<Value>29512</Value>
</Items>
<Items>
<CategoryName>Condiments</
<Year>1996</Year>
<ProductName>Grandma's Boysenberry Spread</ProductName>
<Value>720</Value>
</Items>
<Items>
<CategoryName>Condiments</
<Year>1996</Year>
<ProductName>Gula Malacca</ProductName>
<Value>2139</Value>
</Items>
<Items>
<CategoryName>Condiments</
<Year>1997</Year>
<ProductName>Louisiana Fiery Hot Pepper Sauce</ProductName>
<Value>2604</Value>
</Items>
<Items>
<CategoryName>Condiments</
<Year>1997</Year>
<ProductName>Louisiana Hot Spiced Okra</ProductName>
<Value>408</Value>
</Items>
<Items>
<CategoryName>Condiments</
<Year>1996</Year>
<ProductName>Northwoods Cranberry Sauce</ProductName>
<Value>4480</Value>
</Items>
<Items>
<CategoryName>Confections<
<Year>1996</Year>
<ProductName>Gumbär Gummibärchen</ProductName>
<Value>3984</Value>
</Items>
<Items>
<CategoryName>Confections<
<Year>1996</Year>
<ProductName>Maxilaku</Pro
<Value>1920</Value>
</Items>
<Items>
<CategoryName>Confections<
<Year>1997</Year>
<ProductName>NuNuCa Nuß-Nougat-Creme</ProductN
<Value>795</Value>
</Items>
<Items>
<CategoryName>Confections<
<Year>1997</Year>
<ProductName>Pavlova</Prod
<Value>3503</Value>
</Items>
<Items>
<CategoryName>Confections<
<Year>1997</Year>
<ProductName>Schoggi Schokolade</ProductName>
<Value>1404</Value>
</Items>
</Order>
---------------- EXAMPLE XSLT File --------------------------
<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microso
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-m
xmlns:user="urn:my-scripts
xmlns:o="urn:schemas-micro
xmlns:x="urn:schemas-micro
xmlns:ss="urn:schemas-micr
>
<xsl:template match="Order"> <!-- NAME OF DATASET -->
<Workbook xmlns="urn:schemas-microso
xmlns:o="urn:schemas-micro
xmlns:x="urn:schemas-micro
xmlns:ss="urn:schemas-micr
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Font ss:Bold="1"/>
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table>
<!-- Set Column Widths for first 3 Columns -->
<Column ss:AutoFitWidth="0" ss:Width="150"/>
<Column ss:AutoFitWidth="0" ss:Width="100"/>
<Column ss:AutoFitWidth="0" ss:Width="75"/>
<xsl:apply-templates select="title"/>
<Row> <!-- Set Column Headings -->
<Cell ss:StyleID="s21"><Data ss:Type="String">CategoryN
<Cell ss:StyleID="s21"><Data ss:Type="String">ProductNa
<Cell ss:StyleID="s21"><Data ss:Type="String">Year</Dat
<Cell ss:StyleID="s21"><Data ss:Type="String">Value</Da
</Row>
<xsl:apply-templates select="Items"/>
</Table>
</Worksheet>
</Workbook>
</xsl:template> <!-- End OF DataSet Template -->
<xsl:template match="title">
</xsl:template>
<xsl:template match="Items"> <!-- Set DATA for DataTable -->
<Row>
<Cell><Data ss:Type="String"><xsl:valu
<Cell><Data ss:Type="String"><xsl:valu
<Cell><Data ss:Type="Number"><xsl:valu
<Cell><Data ss:Type="Number"><xsl:valu
</Row>
</xsl:template>
</xsl:stylesheet>