Harambe Harambe - 4 days ago 6
Vb.net Question

Excel export date formatting goes wrong, but only for some date values

I am formatting my datetime values into dates when exporting from access to excel, using vb.net.

I have got the following code

Dim formatRange As Excel.Range

formatRange = xlWorksheet.Range("C1", "C9999")
formatRange.NumberFormat = "dd/MM/yyyy"

formatRange = xlWorksheet.Range("D1", "D9999")
formatRange.NumberFormat = "dd/MM/yyyy"


This works for most dates in my database, however, some of them are still displaying with the time portion of 00:00:00, I think when the dates are higher than 20..

Why is this, and how do I fix it?

EDIT

My full code is:

Dim sPath As String = sDt.Rows(0).Item("excelPath")
Dim i, j As Integer

Dim xlapp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim misvalue As Object = Reflection.Missing.Value

xlapp = New Excel.Application
xlWorkbook = xlapp.Workbooks.Add(misvalue)
xlWorksheet = xlWorkbook.Sheets.Add
xlWorksheet.Name = "CommissionInformation"

Dim formatRange As Excel.Range

formatRange = xlWorksheet.Range("C1", "C9999")
formatRange.NumberFormat = "dd/MM/yyyy"

formatRange = xlWorksheet.Range("D1", "D9999")
formatRange.NumberFormat = "dd/MM/yyyy"

For k As Integer = 1 To dgvExport.Columns.Count
xlWorksheet.Cells(1, k) = dgvExport.Columns(k - 1).HeaderText
Next

For i = 0 To dgvExport.RowCount - 1
For j = 0 To dgvExport.ColumnCount - 1
xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value.ToString
Next
Next

xlWorksheet.Columns.AutoFit()

Answer

As guessed, you export text:

   xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value.ToString

That will force your default date format on your text expressions for the date values.

Date values carries no format. So insert the value:

   xlWorksheet.Cells(i + 2, j + 1) = dgvExport(j, i).Value
Comments