How to get rid of time when export datagridview into excel file VB.NET

Question

hello guys i got question about how to get rid of time at the back of date when exporting data list in datagridview using vb.net

here picture

this is date in datagridview which is no time at all

but after exporting the data become datetime..same goes to export into pdf..still have time..

   Private Sub tsbtnExcel_Click(sender As Object, e As EventArgs) Handles tsbtnExcel.Click
    With SaveFileDialog1

    End With
    If SaveFileDialog1.ShowDialog() = DialogResult.OK Then
        Dim filename As String
        filename = SaveFileDialog1.FileName

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        'Export Header Names Start
        Dim columnsCount As Integer = DataGridView1.Columns.Count
        For k As Integer = 1 To columnsCount
            xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
        Next
        'Export Header Name End

        'Export Each Row Start
        Dim i As Integer = 0
        For Each row As DataGridViewRow In DataGridView1.Rows
            Dim checkselect As Integer = Convert.ToInt16(row.Cells("chkCheck").Value)
            If checkselect = 1 Then
                For columnIndex As Integer = 1 To columnsCount - 1
                    xlWorkSheet.Cells(i + 2, columnIndex + 1).Value = row.Cells(columnIndex).Value.ToString
                Next
                i += 1
            End If
        Next
        'Export Each Row End
        xlWorkSheet.SaveAs(filename + ".csv")
        xlWorkBook.Close()
        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        MessageBox.Show("Export excel file successfully",
                        "Information",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information)
    Else
        MessageBox.Show("Canceled, Export data failed",
                        "Information",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information)
    End If

End Sub

this is the coding for exporting data into excel..and i use checkbox datagridview so that i can choose which i one to export..so i hope u guy can help me get rid the time when exporting into excel

The field is a DateTime so does contain the time, even if only 00:00. In your DataGridView you have chosen to format it so it displays as a date without the time (proably dd/MM//yyy), so do the same in the spreadsheet and format the column to display as date only. Or, as you are writing it as a string, check if it's a Date Value then format the string so it's Date only. — Jon Roberts, Nov 24 '21 at 11:05
https://learn.microsoft.com/en-us/dotnet/desktop/winforms/controls/how-to-format-data-in-the-windows-forms-datagridview-control?view=netframeworkdesktop-4.8 — Mary, Nov 24 '21 at 17:44
@JonRoberts i kinda not understand what you mean..im new with this — lowkey, Nov 26 '21 at 01:45

Lorenzo Martini · Answer 1 · 2021-11-26T10:50:15.307

0

A quick solution is to just convert your date into a string in the gridview.

date.ToString("dd/MM/yyyy")

So the import will see those two columns as simple strings.

Or else

dataGridView1.Columns(12).DefaultCellStyle.Format = "dd/MM/yyyy"

Assuming 12 is the index of the column you want to edit

edited Nov 26 '21 at 10:50

answered Nov 24 '21 at 14:21

Lorenzo Martini

373
1
8

do i need write the code in load or export button? – lowkey Nov 26 '21 at 01:03
i write this code `datagridview1.columns(12).ToString("dd/MM/yyyy")` but error occur – lowkey Nov 26 '21 at 01:15
what type is your column(12) accepting? Cause the ideal would be to pass the date.Tostring to the gridview. It goes to error because you are trying to do a cast on all the dates into a single date format (you are selecting the column with all the dates inside it and trying to convert that array of Dates into a string with format "dd/MM/yyyy"), while you should try to convert one date at time. – Lorenzo Martini Nov 26 '21 at 10:39
if you can, post your code when you populate the gridview. – Lorenzo Martini Nov 26 '21 at 10:43
i edited the answer, check also this: https://stackoverflow.com/questions/4033113/how-to-format-datetime-columns-in-datagridview it seems the issue is the same – Lorenzo Martini Nov 26 '21 at 10:52
..column 12 and 13 suppose to be date in SQL Server so i thought column 12 and 13 will be date `Dim conn As New SqlConnection("Server=" + lblHost.Text + ";Database=PHMStaffList;User ID = ;password = ")` `conn.Open()` `Dim adapter As New SqlDataAdapter("SELECT * FROM MasterStaffList", conn)` `Dim table As New DataTable()` `adapter.Fill(table)` `DataGridView1.DataSource = table` – lowkey Nov 30 '21 at 00:35
..this is how i load datagridview from sql server... the column data type should be same with sql server – lowkey Nov 30 '21 at 00:38
`dataGridView1.Columns(12).DefaultCellStyle.Format = "dd/MM/yyyy"` this coding just change format for datagridview when load the form...but when i try export it into excel still have the time – lowkey Nov 30 '21 at 00:39

score 0 · Accepted Answer · answered Dec 10 '21 at 09:30

I got it xlWorkSheet.Cells(i + 2, columnIndex + 1).Value = row.Cells(columnIndex).Value.ToString1 this coding suppose to be xlWorkSheet.Cells(i + 2, columnIndex + 1).Value = row.Cells(columnIndex).Value ... the ToString take date with time when exporting DGV into excel

How to get rid of time when export datagridview into excel file VB.NET

2 Answers2