This project has moved. For the latest updates, please go here.

New Line in Excel Cell value

Nov 15, 2011 at 1:23 PM

Hi, Is there anyway to add new line char between two text in Excel cell value? I tried using RTF new line tag but it still no success.

Here is the code snippet which assign text in a cell:

ws.Cells["A1"].Value = @"<r><rPr><b /></rPr><t>TEXT1</t></r><r><rPr></rPr><t>TEXT2</t></r>";
ws.Cells["A1"].IsRichText = true;

In above code, I just want to render TEXT1 and TEXT2 in two separate lines in excel cell.

Thanks,

- Firoz Ansari

Coordinator
Nov 17, 2011 at 5:42 AM

Yes, use something like..

worksheet.Cells["A1"].Style.WrapText=true; 

There is a bug if you use the \r\n in the text. It is fixed but not included in the current release.

Download the latest source and build the library from the version 2.9 folder and it shoud be ok.

Nov 17, 2011 at 1:16 PM

Thanks Jan. I appreciate your help.

I build the libarary using latest source code from CodePlex and used "\r\n" as you suggested. Instead of new line char its rendering "_x00D_".  Surely I am missing something here.

Here is my modified code:

ws.Cells["A1"].Value = @"<r><rPr><b /></rPr><t>TEXT1\r\n</t></r><r><rPr></rPr><t>TEXT2</t></r>";
ws.Cells["A1"].IsRichText = true;
ws.Cells["A1"].Style.WrapText = true;

Output I am getting:
TEXT1_x00D_TEST2

Firoz Ansari

 

Coordinator
Nov 18, 2011 at 1:11 PM

Use the Richtext property (if you need richtext in the cell)...

            using (var rng = ws.Cells["A1"])
            {
                var rt1 = rng.RichText.Add("TEXT1\r\n");
                rt1.Bold = true;
                rng.Style.WrapText = true;
                var rt2=rng.RichText.Add("TEXT2");
                rt2.Bold = false;
            }

Otherwise use the Value Propery.
Nov 18, 2011 at 6:08 PM

Awesome! I am now able to get new line in rich text. Thank you very much for your prompt response.

Firoz Ansari

Dec 5, 2011 at 11:47 AM

Hi, I tried to fix new line problem _x00D_.
I downloaded the latest source and build the library from the version 2.9.
After that when I create a new worksheet from template and save it, images are missing.
btw  newlines are now ok,, but layout is not.

-Harri

Coordinator
Dec 8, 2011 at 7:54 PM

Hmm, missing images, can you mail med your template? epplusdocs@gmail.com

Dec 9, 2011 at 6:02 AM

Hi Jan

Thanks for answering (btw: EPPlus is excellent).

That problem became after I built EPPlus newest source code.

Template has image logo but in result it is disappeared.

best regards

-Harri

code:

using (ExcelPackage package = new ExcelPackage(template, true))

{

worksheet = package.Workbook.Worksheets["Hintakirja"];

BeginTask();

ReadTree(rootName, -1);

EndTask();

Byte[] bin = package.GetAsByteArray();

String file = newFile.FullName;

File.WriteAllBytes(file, bin);

}

From: JanKallman [email removed]
Sent: 8. joulukuuta 2011 22:54
To: Väinölä Harri
Subject: Re: New Line in Excel Cell value [EPPlus:279512]

From: JanKallman

Hmm, missing images, can you mail med your template? epplusdocs@gmail.com

Jan 11, 2012 at 4:01 AM

Not work for me. :(

Jan 19, 2012 at 11:25 AM

Version 3.0.0 Jan 2012 seems to fix new line and template image problems.
thanks

Sep 14, 2012 at 1:30 PM

I use version 3.0.0.2, and have "new line" problem. 

worksheet.Cells[1, 1].Value = "1\n2";

worksheet.Cells[1, 1].Value = "1\r\n2";

Both shown code fragments result to cell with one line only. In Excel if I press F2 and then Enter, text becames two-line.

Apr 2, 2014 at 11:28 AM
Just to clarify, it is still necessary to enable the wraptext property and to invoked AutoFitColumn() when using new line. To do otherwise can result in undesired layout display during user access of the file.

However the AutoFitColumn() command will ignore cells where WrapText is true.

A valid approach is

1.Process all your cells, including Environment.NewLine as required. I have not tested with \r\n or \r
    foreach(var c in RCdata){  Worksheet.Cells[c.Row,c.Column]....; } 
2.Process the worksheet to autofit, then enable wraptext
    WorkSheet.Cells[WorkSheet.Dimension.Address].AutoFitColumns();

    WorkSheet.Cells.Style.WrapText = true;
3.Save the file