Problem

In Excel, to have text displayed on multiple lines, two conditions must be met:

1. The cell must be formatted with Wrap Text
2. The text must contain the new line character (hard carriage return)

When you press ALT+ENTER in Excel, Excel automatically formats the cell to have Wrap Text and inserts a new line character into the cell.

This post covers how to insert a hard carriage return into data that is imported into an Excel file with ExcelWriter.

Solution

Text Wrap

If you are importing data using ExcelTemplate, make sure to format the cell alignment with “Text Wrap” in the cell that contains the data marker. This also applies if you are importing data using ExcelApplication with a partial template.

To set “Wrap Text” in Excel:

1. Right-click on the cell
2. Go to the Alignment tab
3. Under Text Control, check off “Wrap Text”

If you aren’t working with a template, you can also set this property on a cell using ExcelApplication:

 //The WrapText property must be set to true in the cell containing the new line character worksheet.Cells["A1"].Style.WrapText = true; 

New Line Character

To import the new line character with your data, just add the new line character to your string of data.

Here is an example of using ExcelApplication to write a string into a cell. The string contains the new line character:

 worksheet.Cells["A1"].Value = "Fred Smith," + '\n' + "President"; 

Here is an example of some data that might be bound to a ExcelTemplate file:

 object[] dataArray = new object {"Fred Smith \n President", "2003", "Boston, MA"};

Follow

Related posts: