EPPlus - AutoFitColumns () method fails when a column merges cells

Question

I was wondering if anyone came up with a workaround for this problem. I noticed that the AutoFitColumns () method does not work in columns with merged cells. I gave an example of the base code 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(); 

As a result, the worksheet will have cells in rows 1 and 2, combined (correctly) for the column in the column variable, but this particular cell is not taken into account by the AutoFitColumns () method.

Any help would be appreciated.

Answer 1

Mostly...

AutoFitColumns documented to ignore merged cells. This is not a failure, at least in the sense that it is faulty.

Excel autofill ignores merged cells.

Apparently, through Excel 2007, you cannot use the AutoFit function for rows or columns that contain merged cells in general.

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

• Auto Row ignores all row-related cells in that row.

• Auto-matching a column ignores all the cells associated with the column in that column.

In other words, you can use AutoFit in rows and columns with merged cells, it simply ignores any cells that have been merged to the same size that you automatically set.

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

Finally, I'm not sure if I see how it makes sense to auto-fit relative to the merged cell. For example, suppose you have a merged cell in A1: B1 with content that fills the width of two columns by default.

If you automatically set column A, what should happen? Is pillar A worth it to be wide enough to fit all A1: B1, sort of like processing a merged cell as if it were only contained in A1, the top left source cell? This may be reasonable, as I cannot immediately see if it is possible in some circumstances to imply strange behavior.

If something like this is desirable, I would paste the contents, automatically install and only then merge.

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

Here you might want column B to become wide enough to display all the content in A1: B1. (The content is just the text "good hello world.")

There is not a single liner for this, I'm afraid. Here is one way to do this:

• Paste the contents into the cell with the invisible top left.

• Save the current width of this cell column.

• Auto-adjust column.

• Save the new column width.

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

• Merge the cells you want to merge.

• Match the width of all the merged columns, but the last.

• Subtract this value from the width saved in step 4.

• Set the last combined column width to the result of step 8.

More generally, you can divide the total width of the unattended installation from step 4 among the merged columns in any way that suits you.

To do all this, you will want to use ExcelColumn.AutoFit and ExcelColumn.Width (and ExcelWorksheet.Column to capture ExcelColumn objects).

But the easiest way ...

If your content is static (or at least dynamic, but not too variable in length), you can just set a reasonable fixed width for the column (s) in question and distribute, but you would like to.