Autofit Row Height of Merged Cell in EPPlus

Question

I'm using EPPlus and C# and trying to autosize/autofit the height of a row to accommodate the height needed to show all of the contents of a merged cell with text wrapping. However no matter what I try the text always truncates. Since I'm repeating this process with various text sizes on various worksheets, I don't want to hard code the row height (except to enforce a minimum height for the row). If possible I'd like to do this within EPPlus/C#.

With the cells A2:E2 merged and WrapText = true:

Cell with Text Truncated

Here's what it should look like with desired Cell Height

Here's my relevant and short C# code

Int32 intToCol;
intToCol = 5;
eppWorksheet.Cells[2, 1, 2, intToCol].Merge = true;
eppWorksheet.Cells[2, 1].Style.WrapText = true; 
//Check if at the minimum height. If not, resize the row
if (eppWorksheet.Row(2).Height < 35.25)
{
    eppWorksheet.Row(2).Height = 35.25;
}

I've looked at Autofit rows in EPPlus and it didn't seem to directly answer my question unless I'm reading it wrong.

This is a documented limitation of excel: https://support.microsoft.com/en-us/kb/212010. No easy solution I am afraid other then gusimating the height, here is a post about autofit of widths but same principal: http://stackoverflow.com/questions/18894671/epplus-autofitcolumns-method-fails-when-a-column-has-merged-cells/23528323 — Ernie S, Jan 13 '17 at 21:49
I was aware of the Excel limitation but didn't see solutions online that fit well and was curious if others had any solutions. — cmbarnett87, Jan 14 '17 at 18:57

Ben Gripka · Accepted Answer · 2020-04-06T20:34:01.260

30

Here is the solution in a reusable method. Pass in the text value, font used for the cell, summed width of the columns merged, and receive back the row height. Set the row height with the result.

Use of Method

eppWorksheet.Row(2).Height = MeasureTextHeight(cell.Value, cell.Style.Font, [enter the SUM of column widths A-E]);

Reuseable Method

    public double MeasureTextHeight(string text, ExcelFont font, double width)
    {
        if (text.IsNullOrEmpty()) return 0.0;
        var bitmap = _bitmap ?? (_bitmap = new Bitmap(1, 1));
        var graphics = _graphics ?? (_graphics = Graphics.FromImage(bitmap));

        var pixelWidth = Convert.ToInt32(width * 7);  //7 pixels per excel column width
        var fontSize = font.Size * 1.01f;
        var drawingFont = new Font(font.Name, fontSize);
        var size = graphics.MeasureString(text, drawingFont, pixelWidth, new StringFormat { FormatFlags = StringFormatFlags.MeasureTrailingSpaces });

        //72 DPI and 96 points per inch.  Excel height in points with max of 409 per Excel requirements.
        return Math.Min(Convert.ToDouble(size.Height) * 72 / 96, 409);
    }

edited Apr 06 '20 at 20:34

answered May 12 '17 at 16:31

Ben Gripka

16,012
6
45
41

4

VERY good solution. Just note that you will need to dispose of the `Bitmap` and `Graphics` objects. – Dylan May 14 '17 at 15:28
1

Great Solution. Does what it says on the tin. Cheers mate. – Ocean Airdrop Jul 09 '17 at 15:21
1

it should be / 0.75 instead of * for converting points to pixel. var pixelWidth = Convert.ToInt32(width / 7.5) – Kalpesh Popat Aug 06 '18 at 11:57
2

Great general idea. A few comments that might help others: - There are 7.0017094 (7) pixels per excel column width. I think you may have been trying to take line spacing and formatting into account? - After switching to 7 pixels / column width, I found decent results by: a) multiplying the `font.Size * 1.01` and b) passing in a `StringFormat` parameter to `MeasureString` with `FormatFlags = StringFormatFlags.MeasureTrailingSpaces` – Jedidja Mar 18 '20 at 13:58
2

@Jedidja thank you for posting the suggestions. I've edited my post to reflect them. These edits have been tested in our application and resolved the issues we were seeing! – Ben Gripka Apr 06 '20 at 20:35
It is very strange that Excel does not support this behavior by default. You have to download additional helper libraries to be able to autofit row height of merged cells... Stupid. Thank you, @BenGripka for this solution <3 – Dorin Baba Aug 23 '21 at 13:42

score 2 · Answer 2 · answered Jun 14 '19 at 07:46

I have used a workaround for this and I a had print area A:Q.

I copied merged cells value to column z.
set width of column z to merge cells width.
Then set auto row height true in format.
Hide the z column.
Set print area A:Q

Cons: There are duplicate data. But we are okay since report is printing and not print z column.

Pros: Row height works correctly not like calculation method.

score 0 · Answer 3 · answered Aug 16 '19 at 12:27

Had to tweak the code a little bit by removing the multiplication factor at the return line. May be because i am using this code to get the width of the column.

ws1.Column(colIndx).Width * 7

The multiplication factor is the number of columns been merged.

Autofit Row Height of Merged Cell in EPPlus

3 Answers3

Linked