How to make Excel wrap text in a formula cell using ClosedXml

Question

The problem is that the contents of the cell are not wrapped when this cell contains a formula related to a cell with some long row.

On CodePlex I found a thread on this issue and simple code to see the problem:

var generated = new XLWorkbook(); var workSheet = generated.AddWorksheet("Test"); workSheet.Cell("B1").Style.Alignment.WrapText = true; workSheet.Cell("B1").Value = "hello hello hello hello hello"; workSheet.Cell("A3").FormulaA1 = "B1"; workSheet.Cell("A3").Style.Alignment.WrapText = true; generated.SaveAs("Generated.xlsx"); 

I also tried setting the row height manually instead of wrapping the cell:

 workSheet.Row(3).Height = workSheet.Row(1).Height; 

However, there will be no success.

Is there anything I can do with this?

---

Following Peter Albert's comment, I tried to make a given AutoFit line. The only thing I could find in ClosedXML was workSheet.Row(3).AdjustToContent(); . But that didn't work either (nor adjusting the contents of a particular column).

Answer 1

I use this

  xlWorkSheet.Range["A4:A4"].Cells.WrapText = true; 

Answer 2

Instead of applying the Fit to Content option, you can apply a Wraptext like this

 var generated = new XLWorkbook(); var workSheet = generated.AddWorksheet("Test"); worksheet.Cell(3, 2).Value = "Hello Hello Hello Hello Hello Hello Name"; worksheet.Cell(3, 2).Style.Alignment.WrapText = true; 

And if you want to apply both options, use it after AdjustToContents.

 var generated = new XLWorkbook(); var workSheet = generated.AddWorksheet("Test"); worksheet.Columns(2, 20).AdjustToContents(); worksheet.Cell(3, 2).Value = "Hello Hello Hello Hello Hello Hello Name"; worksheet.Cell(3, 2).Style.Alignment.WrapText = true; 

Answer 3

Sorry, I can’t write comments ... AutoFit is not a ClosedXML property. About AdjustToContents, in my version (07/27/2014, I think 0.72.3) it ignores the WordWrap property (long line breaks). This is a basic check.

  if (c.HasRichText || textRotation != 0 || c.InnerText.Contains(Environment.NewLine)) { // omissis... } else thisHeight = c.Style.Font.GetHeight( fontCache); 

This implementation ignores the exact height if the cell is larger than one row due to autowrap. Therefore, AdjustToContents + AutoWrap does not work. If you need to have a height-sized content that you need to avoid to call AdjustToContents. This behavior is incompatible with the XL IsAutoHeight property.

Answer 4

Note also that on the same Codeplex page, the author of the library states:

It took some time to understand.

Excel is actually cheating when you set the wrapper text in a cell pointing to another. It calculates the required height, and then sets the row height property. Here I can not do that.

You will have to do without.

For me, this means that this function is not possible.