Excel 2003 XML 格式 - AutoFitWidth 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/171849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2003 XML format - AutoFitWidth not working
提问by Peter
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.
这不会将列设置为自动调整。我试过不设置宽度，我试过很多东西，但我被卡住了。
Thanks.
谢谢。
回答by
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:column
回答by supermankelly
Take your string length before passing to XML and construct the ss:Width="length".
在传递给 XML 之前获取字符串长度并构造 ss:Width="length"。
回答by Mathijs Beentjes
Autofit does not work on cells with strings. Try to replace the Column-line in your example by the following code:
自动调整不适用于带有字符串的单元格。尝试用以下代码替换示例中的 Column-line：
<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.
说明：它按字符串长度（最长的字符串在前）排序，取已排序字符串的第一行，取该字符串的长度 * 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的时候才会出现问题，但是Excel无法处理长度为100000000的字符串。
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
我想将字符串的长度最大化到大约 200，但我无法让 Min 函数起作用，比如
<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!
我希望你现在可以自动适应！
回答by m.nachury
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的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
