Search code examples
c#exceloledb

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.

Solution

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