How to make Excel wrap text in formula cell with ClosedXml

Question

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

Shutter

Try worksheet.Cell("A3").WrapText = True followed by worksheet.Row(3).EntireRow.AutoFit – Prognosis

@PeterAlbert I suppose this worth an answer! – Philippa

@PeterAlbert have checked this yourself? Besides there is no property EntireRow in IXLRow....I suppose you meant AdjustToContent ?? However this changes nothing. – Shutter

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

@KonstantinVasilcov No. I didnt used your code. Maybe try to record a macro and see what code macro recorder will produce when you are change cell format to format that you need. – Lollar

@Lollar 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? – Shutter

You want code that will resize column after column value update? You have to use Columns(A:A).EntireColumn.AutoFit after each value change. – Lollar

Answer 1

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;

Masker

Why WrapText after AdjustToContents? – Escribe

Answer 2

I use this

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

Silicate

ClosedXML doesn't have this type of Range indexer. Did you maybe post EPPlus code? – Anthropography

This syntax seems to have changed with the lastest version of ClosedXml, as text wrapping is more a styling feature. The current syntax looks like this (C#) : worksheet.FirstCell().Style.Alignment.SetWrapText(true); – Platonism

Answer 3

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.

Alvinaalvine

Answer 4

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.

Anthropography

Answer 5

The SetWrapText(); worked for me

Adrianaadriane

Answer 6

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

Mhd