How to make Excel wrap text in formula cell with ClosedXml

15
votes

The problem is that the cell content is not wrapped, when that cell contains a formula referring to a cell with some long string.

On CodePlex I found a thread on this issue and a 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 to set row height manually instead of wrapping the cell:

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

However to no success either.

Is there anything I can do about this?

Following the comment by Peter Albert, I tried to make the set row's AutoFit. The only thing I managed to find to do this in ClosedXML is workSheet.Row(3).AdjustToContent();. But this did not work either (neither adjusting the content of certain column).

6
c#excelclosedxml
Try worksheet.Cell("A3").WrapText = True followed by worksheet.Row(3).EntireRow.AutoFit – Peter Albert
@PeterAlbert I suppose this worth an answer! – Peter L.
@PeterAlbert have checked this yourself? Besides there is no property EntireRow in IXLRow....I suppose you meant AdjustToContent ?? However this changes nothing. – horgh
I tried just .WrapText = True on cell, that reffers to other cell with long text and it worked for me (cell growed up horizontally). I don't understand your problem... – Kamil
@Kamil could you, please, show a small working programm (like in my question), generating a xlsx with a cell with formula, which changes it's width according to the displayed value? – horgh

6 Answers

21
votes

Instead of Applying the Adjust to Contents, you can apply the 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 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;
8
votes

I use this 

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

Sorry, I can't still write comments... AutoFit is not a property of ClosedXML. About AdjustToContents, in my version (26/07/2014, I think 0.72.3) ignores WordWrap property (that split long lines). This is the main 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 in case a cell is more than one line because of autowrap. So, AdjustToContents + AutoWrap does not work. If you need to have the height of the size of the content you need to avoid to call AdjustToContents. This behaviour is not compatible with XL IsAutoHeight property.

0
votes

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

This one took a while to figure out.

Excel is actually cheating when you set the wrap text on a cell that points to another. It calculates the required height and then sets row height property. This is something I can't do here.

You'll have to do without.

To me this implies that this feature is not possible.

0
votes

The SetWrapText(); worked for me

0
votes

Found the following solution (Only excel):

  1. I set row.Cells("start cell : end cell").Style.Alignment.SetWrapText(true) for all cells used

  2. I wrote a VBA macro with the following text: Range("A" & Row & ":N" & Row).EntireRow.AutoFit (For Row) Range("A" & Row & ":N" & Row).EntireColumn.AutoFit (For Column)

  3. Assigned it to run at document startup