2

I have an XML file that I save as .xls to work on in Excel:

<results>
    <result>
        <companyName>Fnatic</companyName>
        <serviceID>1045</serviceID>
        <startDate>01-01-2014 00:00:00</startDate>
        <endDate>01-02-2014 00:00:00</endDate>
        <hours>1</hours>
        <description>Couple of paragraphs of text. Like 3 of them.</description>
    </result>
    ... more results
</results>

I use Excel XML to define structure and styles of the XML data. Only thing left to do is make it so the "Description" cell, that is merged set of columns, to adjust height automatically to the amount of text in it. ss:Row seems to have the ss:AutoFitHeight. But no changes happen to the resulting .xls file when I utilize them. Does ss:AutoFitHeight not work with text just like ss:AutoFitWidth doesn't work for text in ss:Column?

... rest of typical declarations

<xsl:template match="results">  

    <Styles>
        <Style ss:ID="Default" ss:Name="Normal">
          <Alignment ss:Vertical="Top" />
          <Borders />
          <Font />
          <Interior />
          <NumberFormat />
          <Protection />
        </Style>
        <Style ss:ID="s1">
          <Font ss:Size="10" ss:Bold="1" />
          <Interior ss:Color="#cef2ce" ss:Pattern="Solid" />
          <Alignment ss:Horizontal="Right" ss:Vertical="Top" />
        </Style>
        <Style ss:ID="s2">
          <Interior ss:Color="#C0C0C0" ss:Pattern="Solid" />
        </Style>
      </Styles>


      <Worksheet ss:Name="Worksheet">
            <Table x:FullColumns="1" x:FullRows="1">
            <Column ss:Width="77" />
            <Column ss:Width="65" />
            <Column ss:Width="246" />
            <Column ss:Width="26" />
            <xsl:apply-templates select="result" />
            <Row><!-- Last Row -->
                <Cell ss:Index="3"><Data ss:Type="String">Total:</Data></Cell>
                <Cell ss:Formula="=SUM(R[-{count(result)}]C:R[-1]C)">
                    <Data ss:Type="Number"></Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</xsl:template>

<xsl:template match="result">
    <Row>
        <xsl:apply-templates select="serviceID" />
        <xsl:apply-templates select="hours" />
    </Row>
    <Row ss:AutoFitHeight="1">
        <xsl:apply-templates select="description" />
    </Row>
</xsl:template>

<xsl:template match="serviceID">
    <Cell ss:StyleID="s1">
        <Data ss:Type="String">Service ID:</Data>
    </Cell>
    <Cell ss:StyleID="s2">
        <Data ss:Type="Number">
            <xsl:value-of select="."/>
        </Data>
    </Cell>
</xsl:template>

<xsl:template match="description">
    <Cell ss:StyleID="s1">
        <Data ss:Type="String">Description:</Data>
    </Cell>
    <Cell ss:MergeAcross="2">
        <Data ss:Type="String">
            <xsl:value-of select="."/>
        </Data>
    </Cell>
</xsl:template>
<xsl:template match="hours">
    <Cell ss:StyleID="s1">
        <Data ss:Type="String">Hours:</Data>
    </Cell>
    <Cell ss:StyleID="s2">
        <Data ss:Type="Number">
            <xsl:value-of select="."/>
        </Data>
    </Cell>
</xsl:template>
user3681280
  • 43
  • 2
  • 5
  • According to this answer, it might not always be possible: http://stackoverflow.com/questions/171849/excel-2003-xml-format-autofitwidth-not-working but you should check the reference: http://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx – helderdarocha May 29 '14 at 19:46
  • @helderdarocha That question is about `ss:AutoFitWidth` for `ss:Column` though. I saw the reference too and they don't mention anything about **NOT** supporting text with `ss:AutoFitHeight` for `ss:Row'. So it should work. – user3681280 May 29 '14 at 19:57

1 Answers1

1

Removing the default row height on the column properties is counterintuitive and seems to contradict the specifications (https://msdn.microsoft.com/en-us/library/office/aa140066(v=office.10).aspx#odc_xmlss_ss:row), but it helps.

philstra
  • 91
  • 6