How to get LineBreaks in a single Cell in EPPlus with RichText

Question

I use the following Code:

using (var package = new ExcelPackage()) {    
    var worksheet = package.Workbook.Worksheets.Add("Test");    
    var cell = worksheet.Cells[1, 1];

    var r1 = cell.RichText.Add("TextLine1" + "\r\n");
    r1.Bold = true;
    var r2 = cell.RichText.Add("TextLine2" + "\r\n");
    r2.Bold = false;

    package.SaveAs(...);
}

But in the Excel file the newLines are gone...

I tried also with "\n" and "\r" but nothing was working...

I think line breaks in openxml are `
` Here's a similar question for Word https://stackoverflow.com/questions/2871887/inserting-newlines-in-word-using-openxml — juharr, Mar 14 '18 at 17:17
OK, but how would the Code look like? If I just replace the "\r\n" with
the
appears as normal Text in the cell... — Markus, Mar 14 '18 at 17:26

score 8 · Accepted Answer · answered Mar 14 '18 at 19:21

Finally I found the solution. Here is a working sample:

    using (var package = new ExcelPackage(fileInfo)) {    
         var worksheet = package.Workbook.Worksheets.Add("Test");    
         var cell = worksheet.Cells[1, 1];
         cell.Style.WrapText = true;
         cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;

         var r1 = cell.RichText.Add("TextLine1" + "\r\n");
         r1.Bold = true;
         var r2 = cell.RichText.Add("TextLine2" + "\r\n");
         r2.Bold = false;

         package.Save();
     }

But I think I found a bug in the Lib: This Code is NOT working:

    using (var package = new ExcelPackage(fileInfo)) {    
         var worksheet = package.Workbook.Worksheets.Add("Test");    
         var cell = worksheet.Cells[1, 1];
         cell.Style.WrapText = true;
         cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;

         var r1 = cell.RichText.Add("TextLine1" + "\r\n");
         r1.Bold = true;
         var r2 = cell.RichText.Add("TextLine2" + "\r\n");
         r2.Bold = false;

         cell = worksheet.Cells[1, 1];
         var r4 = cell.RichText.Add("TextLine3" + "\r\n");
         r4.Bold = true;

         package.Save();
     }

When I get the same range again and add new RichText Tokens, the old LineBreaks are deleted... (They are actually converted to "\n" and this is not working in Excel.)

score 4 · Answer 2 · answered Mar 21 '18 at 16:02

4

The Encoding of new line in excel cell is 10. I think you have to do someihing like thise:

var r1 = cell.RichText.Add("TextLine1" + ((char)10).ToString());

answered Mar 21 '18 at 16:02

Tricinty

63
6

score 0 · Answer 3 · answered Dec 15 '22 at 08:15

 using (var package = new ExcelPackage(fileInfo)) {    
         var worksheet = package.Workbook.Worksheets.Add("Test");    
         var cell = worksheet.Cells[1, 1];
         cell.Style.WrapText = true;
         cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
         var r1 = cell.RichText.Add("TextLine1" + "\n\n");
         r1.Bold = true;
         var r2 = cell.RichText.Add("TextLine2" + "\n\n");
         r2.Bold = false;
         package.Save();
     }

How to get LineBreaks in a single Cell in EPPlus with RichText

3 Answers3