Harambe Harambe - 1 year ago 105
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

values as
dd/MM/yyyy 00:00:00
, which, when displaying them on a
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

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

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


Answer Source
  • 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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download