Excel 2003 XML format - AutoFitWidth not working

Only date and number values are autofitted :-(quote: "... We do not autofit textual values"

http://msdn.microsoft.com/en-us/library/aa140066.aspx#odc_xmlss_ss:column

Take your string length before passing to XML and construct the ss:Width="length".

Autofit does not work on cells with strings.Try to replace the Column-line in your example by the following code:

    <xsl:for-each select="/*/*[1]/*">      <Column>        <xsl:variable name="columnNum" select="position()"/>        <xsl:for-each select="/*/*/*[position()=$columnNum]">          <xsl:sort select="concat(string-length(string-length(.)),string-length(.))" order="descending"/>          <xsl:if test="position()=1">            <xsl:if test="string-length(.) < 201">              <xsl:attribute name="ss:Width">                <xsl:value-of select="5.25 * (string-length(.)+2)"/>              </xsl:attribute>            </xsl:if>            <xsl:if test="string-length(.) > 200">              <xsl:attribute name="ss:Width">                <xsl:value-of select="1000"/>              </xsl:attribute>            </xsl:if>          </xsl:if>          <xsl:if test = "local-name() = 'Sorteer'">            <xsl:attribute name="ss:Width">              <xsl:value-of select="0"/>            </xsl:attribute>          </xsl:if>        </xsl:for-each>      </Column>    </xsl:for-each>

Explanation: It sorts on string-length (longest string first), take first line of sorted strings, take length of that string * 5.25 and you will have a reasonable autofit.

Sorting line:

        <xsl:sort select="concat(string-length(string-length(.)),string-length(.))" order="descending"/>

explanation: if you just sort on length, like

        <xsl:sort select="string-length(.)" order="descending"/>

because the lengths are handled as strings, 2 comes after 10, which you don't want. So you should left-pad the lengths in order to get it sorted right (because 002 comes before 010). However, as I couldn't find that padding function, I solved it by concattenating the length of the length with the length. A string with length of 100 will be translated to 3100 (first digit is length of length), you will see that the solution will always get string-sorted right. for example: 2 will be "12" and 10 will be "210", so this wil be string-sorted correctly. Only when the length of the length > 9 will cause problems, but strings of length 100000000 cannot be handled by Excel.

Explantion of

            <xsl:if test="string-length(.) < 201">              <xsl:attribute name="ss:Width">                <xsl:value-of select="5.25 * (string-length(.)+2)"/>              </xsl:attribute>            </xsl:if>            <xsl:if test="string-length(.) > 200">              <xsl:attribute name="ss:Width">                <xsl:value-of select="1000"/>              </xsl:attribute>            </xsl:if>

I wanted to maximize length of string to about 200, but I could not get the Min function to work, like

              <xsl:value-of select="5.25 * Min((string-length(.)+2),200)"/>

So I had to do it the dirty way.

I hope you can autofit now!