IXLRows.AdjustToContents() ignores text wrapping

[weskerjiang]

Read and complete the full issue template

Do you want to request a feature or report a bug?

• Bug
• Feature

Version of ClosedXML

0.93

What is the current behavior?

The IXLWorkSheet.Rows().AdjustToContens() does not work.

What is the expected behavior or new feature?

I want the row's height can change when the cell's content is too long.

Did this work in previous versions of our tool? Which versions?

No! It does not work in 0.92.1

Reproducibility

Code to reproduce problem:

public void Main()
{
    XLWorkbook wb = new XLWorkbook();
    IXLWorksheet sheet = wb.Worksheets.Add("data");
    sheet.Column("A").Width = 6;
    sheet.Cell("A1").Value = "This is a very long content to test the AdjustToContents function.";
    sheet.Row(1).Style.Alignment.WrapText = true;
    sheet.Row(1).AdjustToContents();    // does not work

    wb.SaveAs(@"C:\demo.xlsx");
}

• I attached a sample spreadsheet. (You can drag files on to this issue)
  demo2.xlsx

[igitur]

Complete the full issue template.

[igitur]

Did this work in previous versions of our tool? Which versions?

[igitur]

Also, I can't run your code sample. Read the instructions in the issue template again. I'll reopen the issue when you've completed it fully.

[weskerjiang]

I've changed the demo code.

[igitur]

Still can't run it.

[igitur]

I attached a sample spreadsheet. (You can drag files on to this issue)

See this part?

[weskerjiang]

So I've attached a xlsx file.

[igitur]

You should be calling

sheet.Column(1).AdjustToContents();    

[Pankraty]

Ehm, adjusting row heights and column widths are completely different operations, aren't they?

[weskerjiang]

I think sheet.Column(1).AdjustToContents() is wrong because my row's width is a fixed value like 10 or 5.
If I use sheet.Column(1).AdjustToContents(),the row's width will change.
What i want is in the file demo3.xlsx i attached.
demo3.xlsx

[igitur]

Ok, I didn't read properly. That's what happens when people submit incomplete issue templates.

Yes, you're right. Text wrapping is not currently taken into account when calculating a row's height.

[igitur]

@weskerjiang Sorry for being rude earlier.

[weskerjiang]

Never mind!^_^

[machj]

Sorry, any progress? I have this issue too. Any plans to fix this?

[machj]

I've found that it works in current 0.94 Dev version, when you call ws.Row(1).AdjustToContents(); followed by ws.Row(1).ClearHeight(); . Without calling ClearHight(), inside xlsx is the file /xl/worksheets/sheet1.xml with an attribute customHeight="1" and the row height is not adjusted in Excel.

<row r="1" spans="1:1" customFormat="1" ht="60.872385" customHeight="1">

When I call ClearHeight(), this attribute is removed and row height is displayed in Excel correctly (the Row(1).Height is still wrong, so I can't test it with Assert() function). To sum up, it will help to call ClearHeight() as a part of the AdjustToContents() function.

        static void Main()
        {
            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Sheet1");
            ws.Column(1).Width = 100;
            ws.Row(1).Style.Alignment.WrapText = true;

            ws.Cell(1,1).Value= "Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Duis ante orci, molestie vitae vehicula venenatis, tincidunt ac pede. \r\nFusce tellus. Donec odio tempus molestie, porttitor ut, iaculis quis, sem. Pellentesque arcu. Phasellus faucibus molestie nisl. Nulla non lectus sed nisl molestie malesuada.\nAliquam erat volutpat.Nullam eget nisl.Mauris dictum facilisis augue. Mauris metus. Aliquam ante.\nMaecenas sollicitudin. Duis risus. Mauris dolor felis, sagittis at, luctus sed, aliquam non, tellus.\nInteger in sapien.Ut tempus purus at lorem.Quisque porta. In convallis. Aliquam erat volutpat.Duis sapien nunc, commodo et, interdum suscipit, sollicitudin et, dolor.\r\nMorbi leo mi, nonummy eget tristique non, rhoncus non leo. Pellentesque sapien. In convallis. Etiam ligula pede, sagittis quis, interdum ultricies, scelerisque eu.\r\nAliquam erat volutpat.In enim a arcu imperdiet malesuada.";

            ws.Row(1).AdjustToContents();
            //ws.Row(1).ClearHeight();  //uncomment this to get it work

            wb.SaveAs("result.xlsx");
        }

[Pankraty]

In fact, you don't even need ws.Row(1).AdjustToContents();. When the row height is set to null (this is what ClearHeight() does) Excel renders it high enough to include the full contents (unless the cells are merged - those are ignored).

[machj]

So, why there is a ws.Row(1).AdjustToContents(); function? To set the row Height value? It doesn't work, the Height is not calculated correctly (it's about 60.8 in my Example, correct Height value is 165).

[igitur]

AFAIK, ClearHeight() removes the specified height and, if text wrapping is also set, lets Excel render it according to its algorithms. Once you clear the height in ClosedXML, you cannot retrieve the height of a row using row.Height.

AdjustToContents() in ClosedXML uses an algorithm that looks at font size etc to calculate and explicitly set the row height, which is then retrievable by the user. If you don't need to use the row height, ClearHeight() + enabling text wrapping is probably better. If you need the row height, then AdjustToContents() is the only way, but, yes, the current implementation isn't perfect. If text wrapping is enabled, the row height is dependent on the column width and the occurrence of whitespace in the cell contents.

[machj]

If you need the row height, then AdjustToContents() is the only way, but, yes, the current implementation isn't perfect. If text wrapping is enabled, the row height is dependent on the column width and the occurrence of whitespace in the cell contents.

OK, but even when I call row(1).AdjustToContents(), I expect that the attribute customHeight="1" is not set in XML. When I click "Format > AutoFit Row Height" in Excel, it's also removed from the row definition in XML (like with ClearHeight).

ClearHeight() should set height to the default worksheet row height value (not null), what it IMHO does.

[Pankraty]

ClearHeight() should set height to the default worksheet row height value (not null), what it IMHO does.

Setting any particular value will disable the automatic change of row height. See #352, for example

[machj]

I just wanted to highlight that calling AdjustToContents() followed by ClearHight() does what is expected, and it's the same result as AutoFit Row Height in Excel. Without ClearHight(), resulting XML is different (attribute customHeight="1" is present).

[MSAppsDev]

Hi,
It does not seems to be working when columns merged.

static void Main()
        {
            var wb = new XLWorkbook();
            var ws = wb.AddWorksheet("Sheet1");
            ws.Row(1).Style.Alignment.WrapText = true;

            ws.Cell(1,1).Value= "Testing merged columns row auto height with the closed xml; ssgsgsgdgdfgdfgdgdfgdgdfgdfgjknuidghniupghispgjnsipjgkndjksipzsjfniudhfubnsifjbifjsfhbhidjfbiusgyaitrfbfgsyfgsyfgsyfgsdyfgsfysfgfsf skdjfhsduifhdsuifshdufihsdfuihsuifhsduifhsduifhsuifhsuifhsdifhsduifhsdifhsfisbdfiusfisdhfsduifhsduifhsuifsdfyuhfsdfbuishdfisudhgsdiuhsuighsdiugsbugsbgiusghsisugihsuighsufhsdighusighsuighsuighsuighshg";
ws.Range("A1:E1").Merge();
            ws.Row(1).AdjustToContents();
            ws.Row(1).ClearHeight(); 

            wb.SaveAs("Test1.xlsx");
        }

[Pankraty]

Again,

When the row height is set to null (this is what ClearHeight() does) Excel renders it high enough to include the full contents (unless the cells are merged - those are ignored).

Automatically expand the row height to include the contents is Excel's responsibility. And Excel ignores the contents of the merged cells, whether it's about row height or about column width. This is by design. You can try it yourself by double-clicking on row delimiters in Excel - it treats merged cells as if they were empty.

[MSAppsDev]

yeah.. I see its Excel problem by design, it can be fixed in by custom code. Thanks for your time.

[pankajparkar]

@MSAppsDev I'm facing the same issue,

It does not seems to be working when columns merged.

Can you please guide me, how you have fixed this issue?

[AntonAndalex]

@MSAppsDev I'm facing the same issue,

It does not seems to be working when columns merged.

Can you please guide me, how you have fixed this issue?

Hello. I faced the same problem and cannot solve it. Have you solved it somehow? If yes then could you explain how?

Thank you.

[thiago1186]

I got it using
workSheet.Rows().Style.Alignment.WrapText = true;
workSheet.ExpandRows();

[SUDALV92]

I can't believe it's still doesn't work =\
workarounds also doesn't work for me...

i don't have any merged cells, but AdjustToContents() or ExpandRows() doesn't with or without ClearHeight()

[kirichenec]

Any progress with this issue?

[josephceth]

            ws.Row(1).AdjustToContents();
            ws.Row(1).ClearHeight(); 

This answer, posted above, just worked for me. Not very intuitive, but it does work

[kirichenec]

Sorry, worked for me too.. If open file with Excel)) So it's Libreoffice bug)
Thanks