How to Insert Programmatically a New Line in an Excel Cell in C#

How to insert programmatically a new line in an Excel cell in C#?

From the Aspose Cells forums: How to use new line char with in a cell?

After you supply text you should set the cell's IsTextWrapped style to true

worksheet.Cells[0, 0].Style.WrapText = true;

C# Excel interop, put a line break in the text of a cell in a Excel

It was done by either entering "\r\n" or Environment.NewLine. And also must remember to make WrapText property true in order to make the line breaks visible.

Insert carriage returns in Excel via OleDb c#

Got it to work as well via:

1. Insert the newline character \n inside the string
2. Set the column to autofit. The EntireColumn property can also be used to set the width of the column or to force the column to Autofit.

---

public void AutoFitColumn(Worksheet ws, int col) {
    ((Range)ws.Cells[1, col]).EntireColumn.AutoFit();
}

See this link if more info desired.

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

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

How to insert line break within OPENXML spreadsheet cell?

Try CellValues.String instead of CellValues.InlineString.

Assign multiple lines in single cell in Excel using C#

You can just use "\n" in the String

Excel.Range dataRange= (Excel.Range)excelWorksheet.get_Range("C4", "C4");
dataRange.Value2 = "This is the first line\n" +
        "This is the second line\n" +          
        thirdLineString;

---

Related Topics