CODEWITHSUNDEEP
c#excelcom-interop
Thanushka
Thanushka

Reputation: 1445

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

I am writing an Excel sheet using interop. In the sheet I need to put a set of sentences in to a cell. The text should be in a line break manner. How can I achieve this?

Thanks in advance.

Upvotes: 11

Views: 49636

Answers (7)

Thanushka
Thanushka

Reputation: 1445

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.

Upvotes: 17

user742102
user742102

Reputation: 1365

I have encountered this problem but difference is I do not have access to the worksheet in code as I only pass it to memorystream then create file as .csv type. 

foreach (var s in propertyValues)
                streamWriter.WriteLine(s);

            streamWriter.Flush();
            memoryStream.Seek(0, SeekOrigin.Begin);

the I use it from here.

 subjExportData.FileStream = stream;
            subjExportData.FileName = string.Format("SubjectExport_{0}.csv", DateTime.Now.ToString("ddMMyyyy_HHmm"));

So the suggestion here to set text or cell wrap is not an option. Got it working using the above answer plus put a double quote before and after the text/string. The replace is to handle when the sentence has a double quote too inside it. So this solution handles, new line, comma and double quotes inside a sentence or paragraph.

if (value.ToString().Contains("\n"))
                                {
                                    value = value.ToString().Replace("\n", "\r\n");
                                    sb.Append('"'+ value.ToString().Replace(@"""", @"""""") + '"'+ ",");
                                }

Upvotes: 0

GSerg
GSerg

Reputation: 78185

New line within an Excel cell is the LF character, which is "\n" in C#.

Upvotes: 3

Ali Sadri
Ali Sadri

Reputation: 1676

you can add style to cells programically as bellow

worksheet.Cells.Style.WrapText = true;

Upvotes: 7

Paras Chaudhary
Paras Chaudhary

Reputation: 117

New line within an Excel cell is the LF character, which is "\n" in C#. And do not forget to set the WrapText property of the cell to TRUE.

Upvotes: 5

Franck Ngako
Franck Ngako

Reputation: 157

the answer of this issue is to add a "quot" in front and at the end of the string you want to display in your excel cell. In C# it would be something like (Convert.ToChar(34) + stringToExport + Convert.ToChar(34))

Upvotes: -2

KMC
KMC

Reputation: 20046

In VB, or VBA, I used to use vbCrLf (case sensitive) to separate sentences to separate lines in an Excel's Cell like the following:

Dim myString As String
myString = "First sentence" & vbCrLf & "Second sentence"
ActiveCell.ForumulaR1C1 = myString

In C#, I am rather confident that the C# equivalent of VB's vbCrLf is "\r\n", hence:

myString = "First sentence\r\n" + "Second sentence"

Upvotes: 2

Related Questions