Link to home
Start Free TrialLog in
Avatar of pgilfeather
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
ASKER CERTIFIED SOLUTION
Avatar of b1xml2
b1xml2
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pgilfeather
pgilfeather

ASKER

A more detailed explanation of my problem is as follows.

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.MapPath("orderBeverages.xml"))


 ' Set up the response for Excel.
   context.Response.ContentType = "application/vnd.ms-excel"
   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.Server.MapPath("XSLTFile.xslt"))
   xslTran.Transform(xmlDoc, Nothing, context.Response.OutputStream, xmlResolve)



----------------EXAMPLE XML File ------------------------------------------
<?xml version="1.0" encoding="utf-8"?>
<Order>
   <Items>
      <CategoryName>Beverages</CategoryName>
      <Year>1996</Year>
      <ProductName>Chai</ProductName>
      <Value>1808</Value>
   </Items>
   <Items>
      <CategoryName>Beverages</CategoryName>
      <Year>1996</Year>
      <ProductName>Chang</ProductName>
      <Value>3435</Value>
   </Items>
   <Items>
      <CategoryName>Beverages</CategoryName>
      <Year>1997</Year>
      <ProductName>Chartreuse verte</ProductName>
      <Value>3830</Value>
   </Items>
   <Items>
      <CategoryName>Beverages</CategoryName>
      <Year>1997</Year>
      <ProductName>Côte de Blaye</ProductName>
      <Value>29512</Value>
   </Items>
    <Items>
      <CategoryName>Condiments</CategoryName>
      <Year>1996</Year>
      <ProductName>Grandma's Boysenberry Spread</ProductName>
      <Value>720</Value>
   </Items>
   <Items>
      <CategoryName>Condiments</CategoryName>
      <Year>1996</Year>
      <ProductName>Gula Malacca</ProductName>
      <Value>2139</Value>
   </Items>
   <Items>
      <CategoryName>Condiments</CategoryName>
      <Year>1997</Year>
      <ProductName>Louisiana Fiery Hot Pepper Sauce</ProductName>
      <Value>2604</Value>
   </Items>
   <Items>
      <CategoryName>Condiments</CategoryName>
      <Year>1997</Year>
      <ProductName>Louisiana Hot Spiced Okra</ProductName>
      <Value>408</Value>
   </Items>
   <Items>
      <CategoryName>Condiments</CategoryName>
      <Year>1996</Year>
      <ProductName>Northwoods Cranberry Sauce</ProductName>
      <Value>4480</Value>
   </Items>
   <Items>
      <CategoryName>Confections</CategoryName>
      <Year>1996</Year>
      <ProductName>Gumbär Gummibärchen</ProductName>
      <Value>3984</Value>
   </Items>
   <Items>
      <CategoryName>Confections</CategoryName>
      <Year>1996</Year>
      <ProductName>Maxilaku</ProductName>
      <Value>1920</Value>
   </Items>
   <Items>
      <CategoryName>Confections</CategoryName>
      <Year>1997</Year>
      <ProductName>NuNuCa Nuß-Nougat-Creme</ProductName>
      <Value>795</Value>
   </Items>
   <Items>
      <CategoryName>Confections</CategoryName>
      <Year>1997</Year>
      <ProductName>Pavlova</ProductName>
      <Value>3503</Value>
   </Items>
   <Items>
      <CategoryName>Confections</CategoryName>
      <Year>1997</Year>
      <ProductName>Schoggi Schokolade</ProductName>
      <Value>1404</Value>
   </Items>
</Order>


---------------- EXAMPLE XSLT File -----------------------------------------

<xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
      xmlns:msxsl="urn:schemas-microsoft-com:xslt"
      xmlns:user="urn:my-scripts"
      xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
>

<xsl:template  match="Order">   <!-- NAME OF DATASET -->

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 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">CategoryName</Data></Cell>
                  <Cell ss:StyleID="s21"><Data ss:Type="String">ProductName</Data></Cell>
                  <Cell ss:StyleID="s21"><Data ss:Type="String">Year</Data></Cell>
                  <Cell ss:StyleID="s21"><Data ss:Type="String">Value</Data></Cell>
            </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:value-of select="CategoryName"/></Data></Cell>
    <Cell><Data ss:Type="String"><xsl:value-of select="ProductName"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="Year"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="Value"/></Data></Cell>
   </Row>
 </xsl:template>

</xsl:stylesheet>