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:

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...

Answer 1

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}