how to create a date cell using openxml 2.0

Question

Trying to create a date cell using the openXML 2.0 lib. In some cases the date is shown but excel gives an error when opening the file. If I remove the date cells it opens with no errors. Anyone know what's wrong?

Protected Function CreateCell(columnIndex As Integer, rowIndex As Integer, value As DateTime) As Cell

    Dim cell As New Cell()
    cell.DataType = CellValues.Date
    Dim v As CellValue = New CellValue()
    v.Text = value.ToString()
    cell.CellValue = v
    Return cell

End Function

Protected Function CreateCell(columnIndex As Integer, rowIndex As Integer, value As Double) As Cell
    Dim cell As New Cell()

    cell.DataType = CellValues.Number
    'cell.CellReference = getColumnName(columnIndex) & rowIndex
    cell.CellValue = New CellValue()
    cell.CellValue.Text = value.ToString()
    Return cell
End Function

Answer 1

If you download the code from this article and look in ExcelInterface.vb, you'll see some code around detecting implicit cell formats for dates, based on (ECMA-376 Part 1, 18.8.30). You should be able to adapt this to setting them as well. Also see the routines to convert Excel dates and times to and from .NET, as you will want to put the Excel(spreadsheet) date values into date-formatted cells.