epplus: How do I get a row's height after setting column's wraptext style to true?

Question

After I set a column's WrapText=true, I want to see what the new height of the row will be (i.e. if the text wraps, for how many lines). It appears that the Height property of a row is not updated.

        ExcelPackage pkg = new ExcelPackage();
        ExcelWorksheet sheet = pkg.Workbook.Worksheets.Add("Test");

        // height is 15.0
        double heightBefore = sheet.Row(1).Height;


        sheet.Cells[1, 1].Value = "Now is the time for all good men to come to the aid of their country";

        ExcelColumn col = sheet.Column(1);

        // this will resize the width to 60
        col.AutoFit();

        if (col.Width > 50)
        {
            col.Width = 50;

            // this is just a style property, and doesn't actually execute any recalculations
            col.Style.WrapText = true;
        }

        // so this is still 15.0.  How do I get it to compute what the size will be?
        double heightAfter = sheet.Row(1).Height;

        // open the xls, and the height is 30.0
        pkg.SaveAs(new System.IO.FileInfo("text.xlsx"));

In fact, a search for the Height property (or the underlying field _height) shows that it is only set by the property setter, and does not ever seem to be set based on anything else (like content in the row).

Any ideas on how I can get a refreshed Height for a row?

Thanks

Answer 1

The general pattern I've noticed with EPPlus is that it generates the framework of the document with the minimum amount of information necessary. Then, when you open the file, Excel fills out the remaining XML structure, which is why you always have to save the file after opening an EPPlus generated document.

For your question, I'm assuming that Excel is updating the row heights after you open the Excel file so EPPlus would not have the updated row height information. I'm not absolutely certain that the library doesn't support this, but like you I was unable to find a way to get the updated values.

One workaround however could be to just calculate what the value would be since you know your text length and column width:

ExcelPackage pkg = new ExcelPackage();
ExcelWorksheet sheet = pkg.Workbook.Worksheets.Add("Test");

// height is 15.0
double heightBefore = sheet.Row(1).Height;

var someText = "Now is the time for all good men to come to the aid of their country. Typewriters were once ground-breaking machines.";
sheet.Cells[1, 1].Value = someText;

ExcelColumn col = sheet.Column(1);
ExcelRow row = sheet.Row(1);

// this will resize the width to 60
col.AutoFit();

if (col.Width > 50)
{
    col.Width = 50;

    // this is just a style property, and doesn't actually execute any recalculations
    col.Style.WrapText = true;
}

// calculate the approximate row height and set the value;
var lineCount = GetLineCount(someText, (int)col.Width);
row.Height = heightBefore * lineCount;

// open the xls, and the height is 45.0
pkg.SaveAs(new System.IO.FileInfo("text.xlsx"));

Here's the method to calculate the number of lines:

private int GetLineCount(String text, int columnWidth)
{
    var lineCount = 1;
    var textPosition = 0;

    while (textPosition <= text.Length)
    {
        textPosition = Math.Min(textPosition + columnWidth, text.Length);
        if (textPosition == text.Length)
            break;

        if (text[textPosition - 1] == ' ' || text[textPosition] == ' ')
        {
            lineCount++;
            textPosition++;
        }
        else
        {
            textPosition = text.LastIndexOf(' ', textPosition) + 1;

            var nextSpaceIndex = text.IndexOf(' ', textPosition);
            if (nextSpaceIndex - textPosition >= columnWidth)
            {
                lineCount += (nextSpaceIndex - textPosition) / columnWidth;
                textPosition = textPosition + columnWidth;
            }
            else
                lineCount++;
        }
    }

    return lineCount;
}

One thing to keep in mind is that Excel has a max row height of 409.5 so you'll want to make sure your column width is not so narrow that you'll reach this limit.

Also, another thing I noticed is that the column widths that you manually set with EPPlus don't actually set the columns to the expected value. For example, if you set your column width to 50, you'll notice that the actual column width is set to 49.29 so you may want to factor that in as well.