Autofit Row Height of Merged Cell in EPPlus

Ben Gripka

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, int width)
    {
        if (string.IsNullOrEmpty(text)) return 0.0;
        var bitmap = new Bitmap(1, 1);
        var graphics = Graphics.FromImage(bitmap);

        var pixelWidth = Convert.ToInt32(width * 7.5);  //7.5 pixels per excel column width
        var drawingFont = new Font(font.Name, font.Size);
        var size = graphics.MeasureString(text, drawingFont, pixelWidth);

        //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);
    }

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

  1. I copied merged cells value to column z.
  2. set width of column z to merge cells width.
  3. Then set auto row height true in format.
  4. Hide the z column.
  5. 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.

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.