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

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?

are you using the right encoding? A .net string is UTF16 ... — Mitch Wheat, Jul 24 '09 at 09:16
I have no idea really. I'm just modifying someone else's code. Any way to encode a piece of string differently? — User, Jul 24 '09 at 09:18

score 32 · Accepted Answer · edited Mar 26 '17 at 18:08

32

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;

edited Mar 26 '17 at 18:08

Christian Gollhardt

16,510
17
74
111

answered Jul 24 '09 at 13:48

Ahmad Mageed

94,561
19
163
174

received this exception: 'System.__ComObject' does not contain a definition for 'IsTextWrapped' – hakan Apr 21 '16 at 18:26
1

Try with "worksheet.Cells[0, 0].Style.WrapText = true;" instead. – JCO9 Mar 15 '17 at 15:05

score 10 · Answer 2 · edited Jun 23 '11 at 16:27

10

cell.Text = "your firstline<br style=\"mso-data-placement:same-cell;\">your secondline";

If you are getting the text from DB then:

cell.Text = textfromDB.Replace("\n", "<br style=\"mso-data-placement:same-cell;\">");

edited Jun 23 '11 at 16:27

DarthJDG

16,511
11
49
56

answered Jan 14 '11 at 22:31

Kiran

109
1
2

score 5 · Answer 3 · edited Jun 20 '20 at 09:12

You need to insert the character code that Excel uses, which IIRC is 10 (ten).

EDIT: OK, here's some code. Note that I was able to confirm that the character-code used is indeed 10, by creating a cell containing:

A

B

...and then selecting it and executing this in the VBA immediate window:

?Asc(Mid(Activecell.Value,2,1))

So, the code you need to insert that value into another cell in VBA would be:

ActiveCell.Value = "A" & vbLf & "B"

(since vbLf is character code 10).

I know you're using C# but I find it's much easier to figure out what to do if you first do it in VBA, since you can try it out "interactively" without having to compile anything. Whatever you do in C# is just replicating what you do in VBA so there's rarely any difference. (Remember that the C# interop stuff is just using the same underlying COM libraries as VBA).

Anyway, the C# for this would be:

oCell.Value = "A\nB";

Spot the difference :-)

EDIT 2: Aaaargh! I just re-read the post and saw that you're using the Aspose library. Sorry, in that case I've no idea.

Well, U+000A is \n. But I think if that worked then inserting \r\n would have worked too, but would have left a square on the first cell. — Joey, Jul 24 '09 at 09:26
I've never touched nor seen VBA, so I wouldn't be able to do it anyway. Thanks for you time anyway. — User, Jul 24 '09 at 10:29
In the end I went with semicolon since a new line was an option not requirement. The issue is therefore closed. — User, Jul 24 '09 at 10:29

score 4 · Answer 4 · answered Jul 24 '09 at 09:38

4

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.

answered Jul 24 '09 at 09:38

Joey

344,408
85
689
683

It seems that CRLF works fine in Excel 2010, but leads to the unprintable character box being displayed in earlier versions. It seems that using just "\n" (LF) is required for compatibility with older versions of Excel. – Adam Glauser Dec 15 '11 at 16:14

score 2 · Answer 5 · edited Jun 20 '20 at 09:12

2

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

Use

Environment.NewLine
Make sure your cell is wrapped

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 01 '12 at 12:37

Cosmin T.

25
7

ExcelDefaultableBoolean.True there is no such thing. – hakan Apr 21 '16 at 18:43
@hakan : I had no problem finding and using ExcelDefaultableBoolean.True, perhaps it's a version thing with Infragistics? It wouldn't be the first time they changed something. – Auction God Sep 30 '21 at 18:13

score 1 · Answer 6 · answered Jul 24 '09 at 13:35

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

score 0 · Answer 7 · answered Sep 17 '09 at 13:27

0

"\n" works fine. If the input is coming from a multi-line textbox, the new line characters will be "\r\n", if this is replaced with "\n", it will work.

answered Sep 17 '09 at 13:27

score 0 · Answer 8 · answered Aug 30 '13 at 14:01

You can use Chr(13). Then just wrap the whole thing in Chr(34). Chr(34) is double quotes.

VB.Net Example:
TheContactInfo = ""
TheContactInfo = Trim(TheEmail) & chr(13)
TheContactInfo = TheContactInfo & Trim(ThePhone) & chr(13)
TheContactInfo = Chr(34) & TheContactInfo & Chr(34)

score 0 · Answer 9 · edited Nov 27 '12 at 14:48

0

Using PEAR 'Spreadsheet_Excel_Writer' and 'OLE':

Only way I could get "\n" to work was making the cell $format->setTextWrap(); and then using "\n" would work.

edited Nov 27 '12 at 14:48

j0k

22,600
28
79
90

answered Mar 11 '11 at 15:56

Chris

1

score 0 · Answer 10 · answered Dec 17 '18 at 10:38

0

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.

answered Dec 17 '18 at 10:38

SubqueryCrunch

1,325
11
17

score 0 · Answer 11 · answered May 10 '20 at 17:11

0

E.Run runForBreak = new E.Run();

E.Text textForBreak = new E.Text() { Space = SpaceProcessingModeValues.Preserve };
textForBreak.Text = "\n";
runForBreak.Append(textForBreak);
sharedStringItem.Append(runForBreak);

answered May 10 '20 at 17:11

Ompii

3
6

2

While it’s acceptable to provide code-only answers, it’s often more useful for the community if you can also provide an explanation of the code and help people understand _why_ it addresses the problem. That can reduce the number of follow-up questions, and help new developers understand the underlying concepts. That's especially useful here since there are already twelve other answers; what sets your approach apart from them? Can I ask that you update your question with additional details? – Jeremy Caney May 10 '20 at 18:26

score -1 · Answer 12 · edited Jun 23 '11 at 16:26

-1

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

edited Jun 23 '11 at 16:26

DarthJDG

16,511
11
49
56

answered Jun 22 '11 at 17:34

Abdul Muis

7
1

score -2 · Answer 13 · answered Jul 24 '09 at 10:43

-2

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

answered Jul 24 '09 at 10:43

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

13 Answers13

Linked