CODEWITHSUNDEEP
c#exceloledb
Mike
Mike

Reputation: 6050

Insert carriage returns in Excel via OleDb c#

I am inserting text into a Memo field in a Excel cell via a insert statement using OleDb command object.

When I try to insert a carriage return, char 10, what is displayed is a black square (MS Sans Serif). When I look at the top edit cell (don't know the offical name) the text is formatted correctly including carriage returns.

I am trying to duplicate what happens when a user presses Alt+Enter in a cell.

I have tried \n, \r, \r\n and char.ConvertFromUtf32(10).

Nothing seems to change the text of the cell.

Upvotes: 1

Views: 3044

Answers (4)

Brian Wells
Brian Wells

Reputation: 1582

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.

Upvotes: 1

Erik
Erik

Reputation: 9

I had the same same blank squares hou had. I managed to make it work by inserting a \n character and by activating the option "Wrap text" in the format of the Excel cell.

Upvotes: 0

Lisa
Lisa

Reputation: 11

Try expanding the height of the row in excel. I was beating my head against the wall over this as well, and finally got it to work using \n. But then one day it stopped working for no reason other than the cell was too smal to display all lines of data.

Upvotes: 1

ChickSentMeHighE
ChickSentMeHighE

Reputation: 1706

I'm assuming you meant \n and \r\n?

Also have you tried Environment.Newline?

Upvotes: 2

Related Questions