How to insert programmatically a new line in an Ex

Question

I'm using the Aspose library to create an Excel document. Somewhere in some cell I need to insert a new line between two parts of the text.

I tried "\r\n" but it doesn't work, just displays two square symbols in cell. I can however press Alt+Enter to create a new line in that same cell.

How do I insert a new line programmatically?

Answer 1

What worked for me:

worksheet.Cells[0, 0].Style.WrapText = true;
worksheet.Cells[0, 0].Value = yourStringValue.Replace("\\r\\n", "\r\n");

My issue was that the \r\n came escaped.

Answer 2

Actually, it is really simple.

You may edit an xml version of excel. Edit a cell to give it new line between your text, then save it. Later you may open the file in editor, then you will see a new line is represented by 


Have a try....

Answer 3

Internally Excel uses U+000D U+000A (CR+LF, \r\n) for a line break, at least in its XML representation. I also couldn't find the value directly in a cell. It was migrated to another XML file containing shared strings. Maybe cells that contain line breaks are handled differently by the file format and your library doesn't know about this.

Answer 4

SpreadsheetGear for .NET does it this way:

        IWorkbook workbook = Factory.GetWorkbook();
        IRange a1 = workbook.Worksheets[0].Cells["A1"];
        a1.Value = "Hello\r\nWorld!";
        a1.WrapText = true;
        workbook.SaveAs(@"c:\HelloWorld.xlsx", FileFormat.OpenXMLWorkbook);

Note the "WrapText = true" - Excel will not wrap the text without this. I would assume that Aspose has similar APIs.

Disclaimer: I own SpreadsheetGear LLC

Answer 5

Have you tried "\n" I guess, it should work.

Answer 6

If anyone is interested in the Infragistics solution, here it is.

1. Use

   Environment.NewLine

2. Make sure your cell is wrapped

   dataSheet.Rows[i].Cells[j].CellFormat.WrapText = ExcelDefaultableBoolean.True;