Excel 2003 XML format - AutoFitWidth not working

Question

I have a program that spits out an Excel workbook in Excel 2003 XML format. It works fine with one problem, I cannot get the column widths to set automatically.

A snippet of what I produce:

  <Table >
   <Column ss:AutoFitWidth="1" ss:Width="2"/>
   <Row ss:AutoFitHeight="0" ss:Height="14.55">
    <Cell ss:StyleID="s62"><Data ss:Type="String">Database</Data></Cell>

This does not set the column to autofit. I have tried not setting width, I have tried many things and I am stuck.

Thanks.

Lajoie

Answer 1

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

Arthro

One wonders why since Excel can autofit text columns very nicely. – Polydeuces

Answer 2

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

Focalize

Any idea why the ss:Width doesn't have any relation to the units used in Excel itself? For example I specify ss:Width: 200 which shows up in Excel as a column width of 37.43. It's not pixels either so what are the ss:Width units? – Polydeuces

@Polydeuces According to the documentation the unit is Points – Virge

Answer 3

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(.) &lt; 201">
              <xsl:attribute name="ss:Width">
                <xsl:value-of select="5.25 * (string-length(.)+2)"/>
              </xsl:attribute>
            </xsl:if>
            <xsl:if test="string-length(.) &gt; 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(.) &lt; 201">
              <xsl:attribute name="ss:Width">
                <xsl:value-of select="5.25 * (string-length(.)+2)"/>
              </xsl:attribute>
            </xsl:if>
            <xsl:if test="string-length(.) &gt; 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!

Telium

Answer 4

I know this post is old, but I'm updating it with a solution I coded if anyone still use openXml. It works fine with big files and small files.

The algorithm is in vb, it takes an arraylist of arraylist of string (can be changed according to needs) to materialise a excel array.

I used a Windows form to find width of rendered text, and links to select only the biggest cells (for big files efficiency)

There:

Dim colsTmp as ArrayList '(of Arraylist(of String))
Dim cols as Arraylist '(of Integer) Max size of cols
'Whe populate the Arraylist
Dim width As Integer
'For each column
For i As Integer = 0 To colsTmp.Count - 1
    'Whe sort cells by the length of their String
    colsTmp(i) = (From f In CType(colsTmp(i), String()) Order By f.Length).ToArray
    Dim deb As Integer = 0
    'If they are more than a 100 cells whe only take the biggest 10%
    If colsTmp(i).length > 100 Then
        deb = colsTmp(i).length * 0.9
    End If
    'For each cell taken
    For j As Integer = deb To colsTmp(i).length - 1
        'Whe messure the lenght with the good font and size
        width = Windows.Forms.TextRenderer.MeasureText(colsTmp(i)(j), font).Width
        'Whe convert it to "excel lenght"
        width = (width / 1.42) + 10
        'Whe update the max Width
        If width > cols(i) Then cols(i) = width
    Next
Next

Euclid