Harambe Harambe - 9 days ago 5
Vb.net Question

Exporting to Excel - Make DateTime columns into Date

I've got an Export to Excel function in my application.
There are no problems with the program itself, but when exporting data, it shows

DateTime
values as
dd/MM/yyyy 00:00:00
, which, when displaying them on a
DataGridView
before importing, shows empty cells in some places.

I need to adapt my export code to remove the time portion of the data, and just display the date. When I highlight the cell in Excel, I am formatting it to be 'Date' of format
dd/MM/yyyy
.

How can I do this programatically?

my code

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 = "SupplierInformation"
xlWorksheet.Cells.NumberFormat = "@"

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
  • Declare a variable that will be used for referring to a range, eg;

Dim eRange as Excel.Range

  • Then, set the range in your Worksheet

eRange = xlWorksheet.Range("A1", B5")

  • Then set the format, using .NumberFormat

eRange.NumberFormat = "dd/MM/yyyy"

FYI: This link contains pretty much all of the information you could ever need on how to format Excel data and cells using VB