CODEWITHSUNDEEP
c#.netexcelaspose
User
User

Reputation: 30945

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

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?

Upvotes: 28

Views: 144787

Answers (13)

Ompii
Ompii

Reputation: 3

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

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

Upvotes: 0

SubqueryCrunch
SubqueryCrunch

Reputation: 1495

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.

Upvotes: 0

Ahmad Mageed
Ahmad Mageed

Reputation: 96487

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;

Upvotes: 33

Brian Daul
Brian Daul

Reputation: 1

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)

Upvotes: 0

Chris
Chris

Reputation: 1

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.

Upvotes: 0

Cosmin T.
Cosmin T.

Reputation: 35

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;

Upvotes: 3

Kiran
Kiran

Reputation: 109

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;\">");

Upvotes: 10

Abdul Muis
Abdul Muis

Reputation: 7

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 &#10;

Have a try....

Upvotes: -1

t pck
t pck

Reputation:

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

Upvotes: 0

Joe Erickson
Joe Erickson

Reputation: 7217

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

Upvotes: 1

Reputation:

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

Upvotes: -3

Gary McGill
Gary McGill

Reputation: 27516

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.

Upvotes: 5

Joey
Joey

Reputation: 354566

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.

Upvotes: 4

Related Questions