[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1089
  • Last Modified:

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
0
pgilfeather
Asked:
pgilfeather
1 Solution
 
b1xml2Commented:
we hope what you expressed as Excel pivot tables are in actuality HTML Tables that express data the same way as the pivot table in Excel does.

If such is the case, provide a simple xml file relevant to your circumstances and the expected HTML output. Nothing aids learning and understanding more than when using real live examples.

HTH
0
 
pgilfeatherAuthor Commented:
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>
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now