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"};

Problem

When I retrieve the value of a cell which has a Date format in Excel, I do not get the formatted Date; I get a number. How do I retrieve the formatted Date instead?

Solution

Excel Dates are stored in a “serial date format” behind-the-scenes. So, for example, 2/4/2004 is stored as 38021 in Excel. This number represents the number of days elapsed since January 1, 1900.

When you display a Date in Excel, a built-in conversion is performed which converts this serial format into a string for display in the cell depending on the format you apply to that cell.

More information on Excel Serial Dates can be found at http://www.cpearson.com/excel/datetime.htm.

If you want to use a Date from a cell in your code, all you will be able to retrieve is the serial value of this Date. To use it as a formatted string you will have to convert it.

A set of conversion methods can be found at Code Project.

You will need to use one of them in your application in order to convert the serial value of the date to a formatted string. Once you have a formatted string, you can also create a Date object in .NET (using Convert.ToDateTime() for example) and use the built in .NET Date functions if desired.

A simple example in VB.NET

This example retrieves a serial date from an Excel cell and uses a VB.NET version of the ExcelSerialDateToDMY() method from The Code Project to convert it to its day, month, and year components. To run this example, you would call ReadDate() from the PageLoad event or a ButtonClick event, for example. ReadDate() reads the Date (in serial date format) from cell “E1” of sample2.xls. It then calls ExcelSerialDateToDMY() to retrieve the converted day, month, and year values of the date. It then creates a new .NET Date object using those values. Finally, it writes the .NET Date’s short string value to the Response, so you can see that the correct values were retrieved.

Private Sub ReadDate()
'Create Application object and load Excel file from disk
Dim xlw As New SAExcelApplicationDotNet xlw.Open(Server.MapPath("./sample2.xls"), False)
Dim sheet As SAWorksheet sheet = xlw.Worksheets(1)

'Read cell E1, it's a date
Dim sd As Integer = sheet.Cells("E1").Value
' The following 3 variables are used as "output" parameters to the ExcelSerialDateToDMY()
' method, since the method uses the parameters By Reference, not By Value.
Dim d As Integer ' Will contain the day as an Integer
Dim m As Integer ' Will contain the month as an Integer
Dim y As Integer ' Will contain the year as an Integer

' Retrieve the day, month, and year values of the serial date.
ExcelSerialDateToDMY(sd, d, m, y)

' Create a new .NET Date object with the retrieved values, and display it on the page.
Dim dt As Date = New Date(y, m, d)
Response.Write(dt.ToShortDateString)
End Sub

Private Sub ExcelSerialDateToDMY(ByRef nSerialDate As Integer, ByRef nDay As Integer, _
ByRef nMonth As Integer, ByRef nYear As Integer)
' This function is courtesy of The Code Project.
' http://www.codeproject.com/datetime/exceldmy.asp?df=100&forumid=4548&exp=0&select=590906

' nSerialDate is the serial date from Excel
' nDay, nMonth, and nYear are the "output parameters" where the method will store
' the Day, Month, and Year values as Integers that it extracts from nDate

' Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a
' leap year, but Excel/Lotus 123 think it is...
If nSerialDate = 60 Then
nDay = 29
nMonth = 2
nYear = 1900
Return
ElseIf nSerialDate < 60 Then
' Because of the 29-02-1900 bug, any serial date
' under 60 is one off... Compensate.
nSerialDate = nSerialDate + 1
End If

' Modified Julian to DMY calculation with an addition of 2415019
Dim l As Integer = nSerialDate + 68569 + 2415019
Dim n As Integer = CInt(Fix((4 * l) / 146097))
l = l - CInt(Fix((146097 * n + 3) / 4))
Dim i As Integer = CInt(Fix((4000 * (l + 1)) / 1461001))
l = l - CInt(Fix((1461 * i) / 4)) + 31
Dim j As Integer = CInt(Fix((80 * l) / 2447))
nDay = l - CInt(Fix((2447 * j) / 80))
l = CInt(Fix(j / 11))
nMonth = j + 2 - (12 * l)
nYear = 100 * (n - 49) + i + l
End Sub

Problem

In the creation of an Excel workbook, sometimes a column contains numerical values stored as strings. For example, a percent column can display percents as strings. This poses a problem if you want to use these values for equations, or if you want to clean up the display of the file, as Excel will mark these strings with a green corner indicating that they can be displayed as numerals, along with a warning message: “The number in this cell is stored as text or preceded by an apostrophe.”

Solution

ExcelApplication allows you to change the NumberFormat of a column so that it will automatically format numerals. This alone is not enough to change the strings into formatted numerals, though, as you must trim any characters off the strings and convert them to doubles to be handled correctly by Excel.

Hence, converting the values for an entire column requires three steps:

1. Gather the values from the column into a collection and convert them to numeric values.
2. Apply the desired Number Format to the styles of column.
3. Cycle through the collection and add the values back to each cell of the column.

For a code example of the three steps outlined above, please see the sample in our documentation for how to Convert strings to formatted numerical values.