0

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()
David
  • 2,298
  • 6
  • 22
  • 56
Harambe
  • 423
  • 3
  • 29
  • Maybe this [question](http://stackoverflow.com/questions/7106238/changing-the-date-format-to-yyyy-mm-dd) can help – Cal-cium Nov 28 '16 at 17:07
  • @Harmambe from what I read in the question, thats what the answer is doing formatting the excel cell. `cell.NumberFormat = "yyyy-mm-d;@"` – Cal-cium Nov 28 '16 at 17:21
  • You would have to loop through the column and format the cells then – Cal-cium Nov 28 '16 at 17:24

1 Answers1

1
  • 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

David
  • 2,298
  • 6
  • 22
  • 56
  • I have already found this link yesterday, and should have probably removed the question, but yes this is the way I did it. Thanks – Harambe Nov 29 '16 at 11:32