Excel 2003 XML格式- AutoFitWidth不工作

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.

我有一个用Excel 2003 XML格式编制Excel工作簿的程序。它对一个问题很好，我不能让列宽度自动设置。

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.

这不会将列设置为autofit。我尝试过不设置宽度，我尝试过很多东西，我被困住了。

Thanks.

谢谢。

4 个解决方案

#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

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

#2

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

将字符串长度传递给XML并构造ss:Width="length"。

#3

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

Autofit对带有字符串的单元格不起作用。尝试用以下代码替换示例中的列行:

    <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.

说明:它对字符串长度(最长的字符串)进行排序，取第一行已排序的字符串，取该字符串的长度* 5.25，您将得到一个合理的自动拟合。

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.

因为长度被当做字符串处理，2在10之后，这是你不想要的。所以你应该用鼠标左键填上长度，这样才能把它们排对(因为002在010之前)。然而，由于我找不到那个填充函数，我通过使长度与长度的关系来解决它。长度为100的字符串将被转换为3100(第一个数字是长度的长度)，您将看到解决方案总是得到正确的字符串排序。例如:2将是“12”，而10是“210”，所以这条线是正确的。只有长度为> 9时才会出现问题，但长度为100000000的字符串不能用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

我想把弦的长度最大化到200，但是我不能让最小函数工作，比如

              <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!

我希望你现在能自动健身!

#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.

我知道这篇文章已经过时了，但如果有人还在使用openXml，我将用我编写的解决方案来更新它。它适用于大文件和小文件。

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

该算法是在vb中，它需要一个arraylist的字符串列表(可以根据需要更改)来实现一个excel数组。

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

我使用Windows窗体查找显示文本的宽度，并使用链接只选择最大的单元格(对于大文件效率)

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

#1