Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EPPlus - AutoFitColumns() method fails when a column has merged cells

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.

like image 354
Ben Avatar asked Sep 19 '13 12:09

Ben


People also ask

How do I merge cells in Epplus?

If you want to merge cells dynamically, you can also use: worksheet. Cells[FromRow, FromColumn, ToRow, ToColumn].

How do you autosize merged cells in Excel?

When a group of cells in a row are merged and the text wraps to two or more lines, double-clicking the row border just to the left of column A (or Format>Row>Autofit) auto-heights the row to one line of text. Either of these actions should heighten the row to fit the text in the merged cells.

Can you wrap text in a merged cell?

Answer:Select the merged cells that you wish to wrap text. Right-click and then select "Format Cells" from the popup menu. When the Format Cells window appears, select the Alignment tab. Check the "Wrap text" checkbox.

What is AutoFit feature?

AutoFit is a feature in Microsoft Excel that automatically adjusts the width or height of a cell. Below are the steps on how to utilize this feature. Tip. You may double-click the vertical line between two column headers or the horizontal line between two row headers to AutoFit cells without having to go through a menu ...


Video Answer


1 Answers

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:

enter image description here

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.

like image 71
William Avatar answered Oct 04 '22 16:10

William