C# OpenXML：在 Excel 中自动调整列宽
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18268620/
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
OpenXML: Auto Size column width in Excel
提问by Mittal Patel
I have written a code to generate Excel file using OpenXML. Below is the code which generates the Columns in the Excel.
我编写了一个代码来使用 OpenXML 生成 Excel 文件。下面是在 Excel 中生成列的代码。
Worksheet worksheet = new Worksheet();
Columns columns = new Columns();
int numCols = dt1.Columns.Count;
for (int col = 0; col < numCols; col++)
{
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, 20.42578125D);
columns.Append(c);
}
worksheet.Append(columns);
Also, I tried below line to create columns.
另外，我尝试在下面的行中创建列。
Column c = new Column
{
Min = (UInt32Value)1U,
Max = (UInt32Value)1U,
Width = 25.42578125D,
BestFit = true,
CustomWidth = true
};
I thought using BestFitit should work. But it doesn't set the auto size.
我认为使用BestFit它应该有效。但它没有设置自动大小。
采纳答案by Vincent Tan
The BestFit property is an information property (possibly for optimisation by Excel). You still need to provide the Width for the Column. This means you have to actually calculate the column width depending on the cell contents. Open XML SDK doesn't do this automatically for you, so it's better that you use a third-party library for this.
BestFit 属性是一个信息属性（可能用于 Excel 优化）。您仍然需要提供列的宽度。这意味着您必须根据单元格内容实际计算列宽。Open XML SDK 不会自动为您执行此操作，因此您最好为此使用第三方库。
回答by Cyclion
Here the possible formula width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256
这里可能的公式 width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256
回答by Hath
You have to calculate it your self unfortunately
不幸的是你必须自己计算
This is what I've got. It works for my data that's tabular with some extra code in to take care of some styles I have set. Its not perfect by any means but works for what I need it for.
这就是我所拥有的。它适用于我的表格数据，其中包含一些额外的代码来处理我设置的某些样式。它无论如何都不完美，但可以满足我的需要。
private WorksheetPart mySheetPart;
private void WriteToTable()
{
//Get your sheet data - write Rows and Cells
SheetData sheetData = GetSheetData();
//get your columns (where your width is set)
Columns columns = AutoSize(sheetData);
//add to a WorksheetPart.WorkSheet
mySheetPart.Worksheet = new Worksheet();
mySheetPart.Worksheet.Append(columns);
mySheetPart.Worksheet.Append(sheetData);
}
private Columns AutoSize(SheetData sheetData)
{
var maxColWidth = GetMaxCharacterWidth(sheetData);
Columns columns = new Columns();
//this is the width of my font - yours may be different
double maxWidth = 7;
foreach (var item in maxColWidth)
{
//width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256
double width = Math.Truncate((item.Value * maxWidth + 5) / maxWidth * 256) / 256;
//pixels=Truncate(((256 * {width} + Truncate(128/{Maximum Digit Width}))/256)*{Maximum Digit Width})
double pixels = Math.Truncate(((256 * width + Math.Truncate(128 / maxWidth)) / 256) * maxWidth);
//character width=Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100
double charWidth = Math.Truncate((pixels - 5) / maxWidth * 100 + 0.5) / 100;
Column col = new Column() { BestFit = true, Min = (UInt32)(item.Key + 1), Max = (UInt32)(item.Key + 1), CustomWidth = true, Width = (DoubleValue)width };
columns.Append(col);
}
return columns;
}
private Dictionary<int, int> GetMaxCharacterWidth(SheetData sheetData)
{
//iterate over all cells getting a max char value for each column
Dictionary<int, int> maxColWidth = new Dictionary<int, int>();
var rows = sheetData.Elements<Row>();
UInt32[] numberStyles = new UInt32[] { 5, 6, 7, 8 }; //styles that will add extra chars
UInt32[] boldStyles = new UInt32[] { 1, 2, 3, 4, 6, 7, 8 }; //styles that will bold
foreach (var r in rows)
{
var cells = r.Elements<Cell>().ToArray();
//using cell index as my column
for (int i = 0; i < cells.Length; i++)
{
var cell = cells[i];
var cellValue = cell.CellValue == null ? string.Empty : cell.CellValue.InnerText;
var cellTextLength = cellValue.Length;
if (cell.StyleIndex != null && numberStyles.Contains(cell.StyleIndex))
{
int thousandCount = (int)Math.Truncate((double)cellTextLength / 4);
//add 3 for '.00'
cellTextLength += (3 + thousandCount);
}
if (cell.StyleIndex != null && boldStyles.Contains(cell.StyleIndex))
{
//add an extra char for bold - not 100% acurate but good enough for what i need.
cellTextLength += 1;
}
if (maxColWidth.ContainsKey(i))
{
var current = maxColWidth[i];
if (cellTextLength > current)
{
maxColWidth[i] = cellTextLength;
}
}
else
{
maxColWidth.Add(i, cellTextLength);
}
}
}
return maxColWidth;
}
回答by Aske B.
I haven't had the time to look into it, but instead of just leaving a comment and a link, I thought I'd share a comment from somebody who has seemingly done some research on this.
我没有时间去研究它，但我想我应该分享一个似乎对此做了一些研究的人的评论，而不是仅仅留下评论和链接。
I personally had issues getting the official formulasto fit with reality. I.e. Short strings got too small cells, longer strings got too big cells and most of all, the value presented in Excel was proportionally smaller than the value I inserted into the DocumentFormat.OpenXml.Spreadsheet.Column's Width-property. My quick solution was just to have a minimum width.
我个人在让官方公式符合现实时遇到了问题。即短字符串的单元格太小，长字符串的单元格太大，最重要的是，Excel 中显示的值成比例地小于我插入到DocumentFormat.OpenXml.Spreadsheet.Column's Width-property 中的值。我的快速解决方案只是有一个最小宽度。
Anyway, here's the comment:
无论如何，这是评论：
I had to do this in the end because the xlsx files I am interested in are auto generated and should look nice as soon as they are opened so I looked into this a little further and found there are a couple of issues to accurately sizing columns in Excel.
Need to use accurate character sizing, which means that instead of using MeasureString you need to use MeasureCharacterRanges, see http://groups.google.com/group/microsoft.public.office.developer.com.add_ins/browse_thread/thread/2fc33557feb72ab4/adaddc50480b8cff?lnk=raot
Despite the spec saying to add 5 pixels (1 for border and 2 for each side margin) Excel seems to use 9 – 1 for the border, 5 for the leading space and 3 for the trailing space – I only found this by using the accessibility app. Magnifier and counting the pixels after using Excel to auto fit the columns
Actually I was basing my calculations on underlying font metrics so I don't actually use either MeasureCharacterRanges or MeasureString. If anyone is interested in doing this from font metrics then:
Width = Truncate( {DesiredWidth} + 9 / {MaxDigitWidth} ) / 256{MaxDigitWidth} is an integer rounded to the nearest pixel of any of the 0..9 digits at 96 dpi {DesiredWidth} is the sum of adding all character widths together where each character width is the width of the character at 96 dpi rounded to the nearest integer. Note that each character is rounded not the overall sum
最后我不得不这样做，因为我感兴趣的 xlsx 文件是自动生成的，并且在打开它们后应该看起来不错，所以我进一步研究了这一点，发现在准确调整列大小方面存在一些问题优秀。
需要使用准确的字符大小，这意味着您需要使用 MeasureCharacterRanges 而不是使用 MeasureString，请参阅http://groups.google.com/group/microsoft.public.office.developer.com.add_ins/browse_thread/thread/2fc33557feb72ab4 /addaddc50480b8cff?lnk=raot
尽管规范说要添加 5 个像素（边框 1 个像素，每个边距 2 个像素），但 Excel 似乎使用 9 – 1 个用于边框，5 个用于前导空格，3 个用于尾随空格 – 我只是通过使用可访问性找到了这一点应用程序。放大镜和使用 Excel 自动适应列后计算像素
实际上，我的计算基于底层字体指标，因此我实际上并没有使用 MeasureCharacterRanges 或 MeasureString。如果有人有兴趣从字体指标中做到这一点，那么：
Width = Truncate( {DesiredWidth} + 9 / {MaxDigitWidth} ) / 256{MaxDigitWidth} 是一个整数，四舍五入到 96 dpi 时任何 0..9 位数字的最接近像素 {DesiredWidth} 是将所有字符宽度加在一起的总和，其中每个字符宽度是 96 dpi 时字符的宽度四舍五入为最接近的整数。请注意，每个字符都是四舍五入而不是总和
回答by Lucy82
@Mittal Patel take a look at my answer. Posted code is in DOM method, but I created a solution with SAX approach too and results are outstanding - no memory drain (writing directly from DataReader) and much faster than Interop library. Only down-side is autofit feature - you ave to read same data twice.
@Mittal Patel 看看我的回答。发布的代码采用 DOM 方法，但我也使用 SAX 方法创建了一个解决方案，结果非常出色 - 没有内存消耗（直接从 DataReader 写入）并且比 Interop 库快得多。唯一的缺点是自动调整功能 - 您必须读取相同的数据两次。
