当列合并单元格时，EPPlus - AutoFitColumns（）方法失败 [英] EPPlus - AutoFitColumns() method fails when a column has merged cells

问题描述

我想知道是否有人提出了解决这个问题的解决方法。我注意到AutoFitColumns（）方法在合并单元格的列上失败。我已经包含了一个基本的代码示例：

  var cellRange = worksheet.Cells [1，column，2，column]; 
 cells.Merge = true; 
 cells.Style.Horizo​​ntalAlignment = OfficeOpenXml.Style.ExcelHorizo​​ntalAlignment.Center; 
 cells.Value =Some Text Goes Here; 
 worksheet.Cells.AutoFitColumns（）; 
  

生成的工作表将使行1和2中的单元格（正确）合并到列变量，但该特定单元格被AutoFitColumns（）方法忽略。

任何帮助将不胜感激。

解决方案

基本上...

AutoFitColumns 是记录的忽略合并的单元格。这不是失败的，至少在有缺陷的意义上。

Excel中的AutoFit也忽略合并的单元格。

显然，通过Excel 2007，您不能对包含合并单元格的行或列使用AutoFit功能所有。

我只用Excel 2013测试，似乎有不同的表现：

• 自动拟合一行将忽略该行中的所有行合并单元格。




• 自动调整列忽略该列中的所有列合并的单元格。

换句话说，您可以在已合并的行和列中使用AutoFit单元格，它只会忽略已经被合并到同一个维度上的任何单元格，这个单元格是自动拟合的。

AutoFit w / r / t合并单元格的期望效果？ （+解决方法）

最后，我不知道我是否看到自动适应合并的单元格是否有意义。例如，假设您在A1：B1中有一个合并的单元格，其内容将填充默认的两列宽度。

如果您自动适应列A，应该会发生列A应该变得足够宽以适合A1：B1，像处理合并的单元格一样，就像它的内容只存在于A1，左上方的原始单元格？这可能是合理的，只要我不能立即看到在某些情况下是否可能暗示出奇怪的行为。

如果需要这样的话，我会插入内容，自动适应，然后才合并。

但是，如果您想在列B自动适应，如下所示：

在这里，您可能希望列B足够宽，以便A1：B1中的所有内容显示。 （内容只是文本好你好世界。）

恐怕没有一个班级。这是一种方法：

1. 将内容插入尚未解决的左上角单元格。 / p>




2. 保存此单元格列的当前宽度。




3. 自动调整列。




4. 保存新列宽




5. 将列宽重设为保存




6. 合并要合并的单元格。




7. 所有合并列，但最后一列的宽度。




8. 从您在步骤4中保存的宽度中减去总计。




9. 将上一个合并列的宽度设置为步骤8的结果。

更一般地说，您可以按合适的方式从合并的列中的第4步中分离出自动适合的总宽度。

要做到这一切，你会想要使用 ExcelColumn.AutoFit 和 ExcelColumn.Width （和 ExcelWorksheet.Column 获取 Excel列对象）。

但最简单的...

如果您的内容是静态的（或至少是动态的，但长度不可能太长），您可以简单地为有问题的列设置合理的固定宽度（分配），但是您想要分配。

I was wondering if anyone has come up with a workaround to this problem. I've noticed that the AutoFitColumns() method is failing on columns with merged cells. I've included a basic code example below:

var cellRange = worksheet.Cells[1, column, 2, column];
cells.Merge = true;
cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
cells.Value = "Some Text Goes Here";
worksheet.Cells.AutoFitColumns();

The resulting worksheet will have the cells in rows 1 and 2 merged (correctly) for the column in the column variable, but that particular cell is disregarded by the AutoFitColumns() method.

Any help would be appreciated.

解决方案

Basically...

AutoFitColumns is documented to ignore merged cells. It isn't failing, at least in the sense of being defective.

AutoFit within Excel ignores merged cells, too.

Apparently up through Excel 2007, you cannot use the AutoFit feature for rows or columns that contain merged cells at all.

I've only tested with Excel 2013, which seems to behave differently:

• Auto-fitting a row ignores all row-merged cells in that row.

• Auto-fitting a column ignores all column-merged cells in that column.

In other words, you can use AutoFit in rows and columns with merged cells, it'll just ignore any cells that have been merged along the same dimension you're auto-fitting.

Desired effect of AutoFit w/r/t merged cells? (+ workarounds)

Finally, I'm not sure whether I see how it makes sense to auto-fit with respect to a merged cell. For example, suppose you have a merged cell at A1:B1 with content that fills a default two-column width.

If you auto-fit on column A, what is supposed to happen? Is column A supposed to become wide enough to fit all of A1:B1, sort of like treating the merged cell as if it's content existed only in A1, the top-left original cell? That might be reasonable, insofar as I can't immediately see whether strange behaviors might be implied in some circumstances.

If something like that is desired, I'd insert the content, auto-fit, and only then merge.

But what if you want to auto-fit on column B, in a situation like this:

Here, you might want column B to get wide enough so that all of the content in A1:B1 shows. (The content is just the text "well hello world".)

There's no one-liner for this, I'm afraid. Here's one way to do it, though:

1. Insert the content in the as-yet-unmerged top-left cell.

2. Save the current width of this cell's column.

3. Auto-fit the column.

4. Save the new column width.

5. Reset the column width to what you saved in step 2.

6. Merge the cells you want to merge.

7. Total up the widths of all your merged columns but the last one.

8. Subtract this total from the width you saved in step 4.

9. Set the last merged column's width to the result of step 8.

More generally, you can split up the total auto-fitted width from step 4 among the merged columns in any way you see fit.

To do all this, you'll want to use ExcelColumn.AutoFit and ExcelColumn.Width (and ExcelWorksheet.Column to grab the ExcelColumn objects).

But most simply...

If your content is static (or at least dynamic but not too variable in length), you can simply set reasonable fixed width(s) for the column(s) in question, distributed however you'd like.

这篇关于当列合并单元格时，EPPlus - AutoFitColumns（）方法失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！