CODEWITHSUNDEEP
c#excelclosedxml
horgh
horgh

Reputation: 18563

How to make Excel wrap text in formula cell with ClosedXml

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

Upvotes: 22

Views: 58166

Answers (6)

Vadim Chernyshov
Vadim Chernyshov

Reputation: 11

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

Upvotes: 1

Ali Nouman
Ali Nouman

Reputation: 3412

The SetWrapText(); worked for me

Upvotes: 0

Francois Botha
Francois Botha

Reputation: 4849

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.

Upvotes: 0

Smit Patel
Smit Patel

Reputation: 3267

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;

Upvotes: 31

Moayad Myro
Moayad Myro

Reputation: 294

I use this 

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

Upvotes: 8

bubi
bubi

Reputation: 6501

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.

Upvotes: 0

Related Questions