CODEWITHSUNDEEP
vb.netopenxml
user117499
user117499

Reputation:

how to create a date cell using openxml 2.0

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

Upvotes: 3

Views: 2693

Answers (2)

Rick Spiewak
Rick Spiewak

Reputation: 21

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.

Upvotes: 2

Hans
Hans

Reputation: 13070

Please note, that the CellValues.Date enumeration member is only available in Microsoft Office 2010 and above (see MSDN library for more information).

So, if you open an Excel spreadsheet which contains a cell of type CellValues.Date with Microsoft Office 2007 you will get an error.

Furthermore the value of a cell of type CellValues.Date must be in ISO 8601 format (see Office Open XML Part 1 specification, section 18.17.4.1).

Summary ISO 8601 format:

  • Date in ISO 8601 is expressed in the following format: YYYY-MM-DD.
  • Time is stored in the following format: hh:mm:ss.
  • Date and time are separated using the capital letter T: YYYY-MM-DDThh:mm:ss.

Please refer to the following article for more information (WIKIPEDIA) on the ISO 8601 format.

The following code sample shows the creation of a cell of type CellValues.Date:

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

  Dim cell As Cell = New Cell()

  cell.DataType = CellValues.Date

  Dim v As CellValue = New CellValue()

  v.Text = value.ToString("yyyy-MM-ddThh:mm:ss") ' Use ISO 8601 format for date value
  cell.CellReference = "" ' Set cell reference here! E.g. A1
  cell.CellValue = v
  cell.StyleIndex = styleIndex

  Return cell

End Function    

Protected Function CreateNumberingFormatForDateCells() As NumberingFormat

  Dim numberingFormat As NumberingFormat = New NumberingFormat()

  numberingFormat.NumberFormatId = CType(165U, UInt32Value)
  numberingFormat.FormatCode = "dd-mm-yyyy"

  return numberingFormat

End Function

Protected Function CreateCellFormatForDateCells() As CellFormat

  Dim cellFormat As CellFormat = New CellFormat()

  cellFormat.NumberFormatId = CType(165U, UInt32Value) 
  cellFormat.ApplyNumberFormat = true

  return cellFormat

End Function

Sub Main()

  ' ... Code to get/create your workbook
  Dim workbookPart As WorkbookPart ...

  ' Add number format and cell style for date cells.
  Dim nf As NumberingFormats = New NumberingFormats()

  workbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats = nf
  workbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats.Append(CreateNumberingFormatForDateCells());
  workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(CreateCellFormatForDateCells());

  ' Call CreateCell() to create date cells
  Dim c As Cell = CreateCell(0,0,DateTime.Now, workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count())

  ' Code to append cell to spreadsheet

End Sub

Please note, by setting the cell type to CellValues.Date we only specifies that the date value is stored in ISO 8601 format. We still have to specify a display cell style for the date. In the above sample I've created a cell style (and set the the cell style index) in order to tell excel how to display the date value.

Upvotes: 0

Related Questions