Autofit rows in EPPlus

Question

I cannot find a way to autofit row height using the EPPlus Excel library. When I used Excel Interop, I could do sheet.Rows.AutoFit(). I'm looking around the interface using ILSpy, but so far I didn't find anything useful. Is there some workaround, or am I missing something?

UPDATE: Row 4 is giving me problems:

enter image description here

I tried doing sheet.Row(4).CustomHeight = false, but it didn't work... I also tried setting the property before and after loading the cell content, and it's still the same. It does adjust the row height to a different value, but it's not a good one. Maybe EPPlus has issues with measuring string height? I know I did have lots of problems with that when I messed with some legacy GDI+ code in which I needed to measure strings...

score 12 · Accepted Answer · answered Jul 19 '15 at 00:44

Actually, if you look at the property CustomHeight for the row object you will see that it is set to false by default. Which means that Excel will (should) automatically set the height of the row when opened. If you wanted to stop that you would either set it to false or set the row height manually which will automatically set it to false.

The only wrinkle is that you if you rely on the autofit for the row then you cannot know what that height is going to be at the time you build in EPPlus since Excel will decide that when it first opens the file. Kind of like you cannot know what the column width will be if you use the AutoFitColumn function.

This demonstrates the logic of the property:

[TestMethod]
public void Row_Height_Test()
{
    //http://stackoverflow.com/questions/31496172/autofit-rows-in-epplus
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof (int)),
        new DataColumn("Col2", typeof (int)),
        new DataColumn("Col3", typeof (int))
    });


    for (var i = 0; i < 20; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = i * 100;
        datatable.Rows.Add(row);
    }

    var existingFile2 = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile2.Exists)
        existingFile2.Delete();

    using (var package = new ExcelPackage(existingFile2))
    {
        //Add the data
        var ws = package.Workbook.Worksheets.Add("Sheet1");
        ws.Cells.LoadFromDataTable(datatable, true);

        //CustomHeight is set to false by default on all rows and giving such a large font 
        //will cause it to autosize to a bigger height by Excel when first opened
        ws.Row(1).Style.Font.Size = 20;

        //Setting the height manually will automatically set CustomHeight to true
        //preventing excel from automatically setting the height
        ws.Row(2).Height = 30;
        ws.Row(2).Style.Font.Size = 20;

        //row 1 height will be around 26 when opened in Excel (but cannot know that now), 
        //row 2 height will be 30 as set above, 
        //row 3 height will be the DefaultHeight (usually 15) of the worksheet since it can fit the default font
        Console.WriteLine("{{{0} : {1}}}", ws.Row(1).Height, ws.Row(1).CustomHeight);
        Console.WriteLine("{{{0} : {1}}}", ws.Row(2).Height, ws.Row(2).CustomHeight);
        Console.WriteLine("{{{0} : {1}}}", ws.Row(3).Height, ws.Row(3).CustomHeight);

        //Save the file
        package.Save();
    }
}

Here is the console log output:

{15 : False}
{30 : True}
{15 : False}

Excel Rendered Output

I tried setting CustomHeight = false for the row that's giving me problems, but it's not working. Could it be because the text is not in the first column? — dario_ramos, Jul 19 '15 at 02:46
@dario_ramos Thats interesting. Epplus does nothing to calculate the height if you set CustomHeight to true, Excel does all of the work which it seems to be not doing it properly. Could you post more of your code - a unit test might help isolate it. — Ernie S, Jul 19 '15 at 12:27
I isolated the code which fills and gives style to row 4 and it works fine... there is some weird interaction with the rest. I'll try adding it one at a time and see if I find the conflict. I'm willing to bet it's the images I inserted above it. — dario_ramos, Jul 20 '15 at 02:14
It is a definite possibility that the image is the problem. Check out the EditAs setting as noted here: http://stackoverflow.com/questions/27873762/weird-behavior-when-setting-a-rows-height-on-epplus. — Ernie S, Jul 20 '15 at 12:00

score 8 · Answer 2 · answered Oct 27 '16 at 15:43

Another twist to this that has taken me hours to figure out: How to enable word wrap in a given column or row. This is how I did it:

(Horizontal and vertical alignments have been set to "Center")

      // Increase the height of the header row by .5
      // (The height is already at 2 times the default value)
      wsDT.Row(1).Height *= 1.5;

      // Column index to Notes, Anniversary Month and Day
      //   add "1" since column index is relative to 0
      int colIdx = dt.Columns.IndexOf("Notes") + 1;

      // Set the column width 
      // This is a long text field - so width and word wrap are set
      wsDT.Column(colIdx).Width = 50;
      wsDT.Column(colIdx).Style.WrapText = true;

      // Set width of anniversary month column
      // Purpose here is to wrap header text 
      colIdx = dt.Columns.IndexOf("Lease Anniversary Month") + 1;
      wsDT.Column(colIdx).Width = 15;
      wsDT.Column(colIdx).Style.WrapText = true;

Header row showing wrapped text and set width Contents of "Notes" row has wrapped text when appropriate

Autofit rows in EPPlus

2 Answers2

Linked