Note: I am using MSXML 4 and aspx.

I am trying to convert the xml below into excel xml spreadsheet using xslt but I am having a problem to set an attribute in the <Table> tag. I need to set the ss:ExpandedColumnCount to the value of the colspan variable.

First, I get a count of how many columns I need to span
<xsl:variable name="colspan" select="number($msrs+$cols)"/>

Then

<Table ss:ExpandedColumnCount="{$colspan}" ss:ExpandedRowCount="6" x:FullColumns="1" x:FullRows="1">

or

<xsl:attribute name ="ss:ExpandedColumnCount">
  <xsl:value-of select ="$colspan"/>
</xsl:attribute>

But when I assign the value to the ss:ExpandedColumnCount using any of the above and try to open the xml in excel I get an error saying that there is a problem with the Table tag. If I hardcode the value
ss:ExpandedColumnCount="5" then it works.

Also it doesn't like this line

<xsl:decimal-format name="fd1" decimal-separator="." grouping-separator="," NaN="#160;"/>

Can someone shed some light ?

Cheers,

<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:strip-space elements="*"/>

 <xsl:param name="axisHeads" select="'false'"/>



  <xsl:param name="sortCol" select="0"/>
  <xsl:param name="sortCell" select="1"/>
  <xsl:param name="dataType" select="'text'"/>
  <xsl:param name="sortOrder" select="'ascending'"/>
  <xsl:param name="ltCurFormat" select="'$##,###.00'"/>
  <xsl:param name="ltNumFormat" select="'###.00'"/>
  <xsl:param name="height" select="300"/>
  <xsl:param name="width" select="400"/>
  <xsl:param name="id" select="'g'"/>

  <xsl:variable name="msrs">
    <xsl:choose>
      <xsl:when test="//Measure">
        <xsl:value-of select="count(//Measure)"/>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="1"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:variable>

  <xsl:variable name="cols">
    <xsl:value-of select="count(//Col)"/>
  </xsl:variable>

  <xsl:variable name="rows">
    <xsl:value-of select="count(//Row)"/>
  </xsl:variable>



  <xsl:template match="/">
    <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="s24">
          <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
        </Style>
      </Styles>
      <xsl:apply-templates/>
    </Workbook>
  </xsl:template>
  <xsl:template match="Report">
    <xsl:variable name="colspan" select="number($msrs+$cols)"/>

    <Worksheet>
      <xsl:attribute name="ss:Name">

        <xsl:value-of select="'Audit'"/>
      </xsl:attribute>
      <Table ss:ExpandedRowCount="6" x:FullColumns="1" x:FullRows="1">
        <xsl:attribute name ="ss:ExpandedColumnCount">
          <xsl:value-of select ="$colspan"/>
        </xsl:attribute>
        <Column ss:Width="73.5"/>
        <Column ss:Width="57"/>
        <Column ss:Width="57"/>
        <Column ss:Width="47.25"/>
        <Row>
          <Cell ss:MergeDown="1"/>
          <Cell ss:MergeAcross="1" ss:StyleID="s24">
            <Data ss:Type="Number">2003</Data>
          </Cell>
          <Cell ss:MergeAcross="1" ss:StyleID="s24">
            <Data ss:Type="Number">2004</Data>
          </Cell>
        </Row>
     </Table>
    </Worksheet>
  </xsl:template>
  <xsl:template match="/*/*">
    <Row>
      <xsl:apply-templates/>
    </Row>
  </xsl:template>
  <xsl:template match="/*/*/*">
    <Cell>
      <Data ss:Type="String">
        <xsl:value-of select="."/>
      </Data>
    </Cell>
  </xsl:template>
</xsl:stylesheet>


XML

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl"
<Report xmlns="">
    <Measures>
        <Measure idx="1" heading="Total Pages"/>
        <Measure idx="2" heading="Cost"/>
    </Measures>
    <Columns>
        <ColGrp heading="Year">
            <Col heading="2003"/>
            <Col heading="2004"/>
        </ColGrp>
    </Columns>
    <Rows>
        <RowGrp heading="Journal Group Name">
            <Row heading="Gastroenterology">
                <Cell/>
                <Cell>
                    <Msr idx="1" val="42.00"/>
                    <Msr idx="2" val="64230"/>
                </Cell>
            </Row>
            <Row heading="General Medicine">
                <Cell/>
                <Cell>
                    <Msr idx="1" val="36.00"/>
                    <Msr idx="2" val="222490"/>
                </Cell>
            </Row>
            <Row heading="Orthopedics">
                <Cell/>
                <Cell>
                    <Msr idx="1" val="44.00"/>
                    <Msr idx="2" val="107400"/>
                </Cell>
            </Row>
            <Row heading="Other Specialty">
                <Cell/>
                <Cell>
                    <Msr idx="1" val="36.00"/>
                    <Msr idx="2" val="35820"/>
                </Cell>
            </Row>
            <Row heading="Pharmacy">
                <Cell>
                    <Msr idx="1" val="14.20"/>
                    <Msr idx="2" val="128030"/>
                </Cell>
                <Cell/>
            </Row>
            <Row heading="Rheumatology">
                <Cell/>
                <Cell>
                    <Msr idx="1" val="60.00"/>
                    <Msr idx="2" val="125520"/>
                </Cell>
            </Row>
        </RowGrp>
    </Rows>
</Report>

>But when I assign the value to the ss:ExpandedColumnCount using any of the above and try to open the xml in excel I get an error saying that there is a problem with the Table tag. If I hardcode the value ss:ExpandedColumnCount="5" then it works.

Then presumably your stylesheet has put something other than 5 in the value (NaN perhaps?). It should be a simple matter to open in the XML in an editor and find out.

>Also it doesn't like this line

<xsl:decimal-format name="fd1" decimal-separator="." grouping-separator="," NaN="#160;"/>

>Can someone shed some light ?

Can you shed some light? What was the error message?

My guess would be that you left out the ampersand in the NaN entity reference.

Michael Kay
http://www.saxonica.com/
Author, XSLT Programmer's Reference and XPath 2.0 Programmer's Reference

When I run the transform I get 4 for the expandedColumnCount which seems right. If it's not the correct value for Excel I suggest you create a file in Excel that matches the one you desire and save to XML. You'll then see what the value should be. I don't get an error about the decimal-format element.

--

Joe (Microsoft MVP - XML)

Hi Michael,

I am converting the value to number and have checked it in XmlSpay and it is a number for sure. It is really strange that we cannot add the attribute dynamically to the <table> tag.

Joe,

To get the error you will have to use a server side language like C# and transform the xml with the xslt. however, set the Response.ContentType = "application/vnd.ms-excel"; and
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls");

Please let me know if you spot anything else I am missing..

Cheers

I do get the error on opening in Excel, however the actual XML is correct as far as your transform goes.

--

Joe (Microsoft MVP - XML)