17

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

horgh
  • 17,918
  • 22
  • 68
  • 123
  • 1
    Try `worksheet.Cell("A3").WrapText = True` followed by `worksheet.Row(3).EntireRow.AutoFit` – Peter Albert Mar 04 '13 at 13:58
  • @PeterAlbert I suppose this worth an answer! – Peter L. Mar 04 '13 at 17:11
  • @PeterAlbert have checked this yourself? Besides there is no property `EntireRow` in `IXLRow`....I suppose you meant `AdjustToContent` ?? However this changes nothing. – horgh Mar 05 '13 at 00:10
  • 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 Mar 05 '13 at 01:13
  • @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. – Kamil Mar 05 '13 at 01:23
  • 1
    @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 Mar 05 '13 at 01:26
  • You want code that will resize column after column value update? You have to use `Columns(A:A).EntireColumn.AutoFit` after each value change. – Kamil Mar 05 '13 at 01:29

6 Answers6

26

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;
Smit Patel
  • 2,992
  • 1
  • 26
  • 44
8

I use this 

 xlWorkSheet.Range["A4:A4"].Cells.WrapText = true;
Moayad Myro
  • 294
  • 1
  • 3
  • 12
  • 1
    `ClosedXML` doesn't have this type of `Range` indexer. Did you maybe post `EPPlus` code? – Francois Botha Sep 26 '17 at 08:33
  • 1
    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);` – Ishikawa Nov 05 '19 at 10:12
0

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.

bubi
  • 6,414
  • 3
  • 28
  • 45
0

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.

Francois Botha
  • 4,520
  • 1
  • 34
  • 46
0

The SetWrapText(); worked for me

Ali Nouman
  • 3,304
  • 9
  • 32
  • 53
0

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

Vadim Chernyshov
  • 1