VB.NET中DataGridView导出到Excel时如何去除日期字段末尾的时间

使用VB.NET导出DataGridView中的数据列表时，导出后的日期字段末尾会附带多余的时间，DataGridView内展示时没有时间后缀，导出为Excel、PDF时都会出现该问题。

相关截图：

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

问题出在导出时直接调用了单元格Value的ToString()方法，DateTime类型的默认序列化规则会携带时间部分，且Excel会自动识别内容为日期时间类型，默认附带时间展示。可以用以下两种方法解决：

• 方案1：主动判断类型格式化输出，支持自定义日期格式
  将循环写入单元格的代码替换为以下内容：

For columnIndex As Integer = 1 To columnsCount - 1
    Dim cellValue = row.Cells(columnIndex).Value
    ' 检测当前单元格内容是否为日期时间类型
    If TypeOf cellValue Is DateTime Then
        ' 自定义格式输出仅日期部分，格式可按需修改为yyyy/MM/dd等
        xlWorkSheet.Cells(i + 2, columnIndex + 1).Value = CType(cellValue, DateTime).ToString("yyyy-MM-dd")
        ' 可选：设置Excel单元格格式为日期，避免自动识别异常
        xlWorkSheet.Cells(i + 2, columnIndex + 1).NumberFormat = "yyyy-MM-dd"
    Else
        xlWorkSheet.Cells(i + 2, columnIndex + 1).Value = cellValue.ToString()
    End If
Next

• 方案2：直接读取单元格格式化后的展示值，和DataGridView界面显示完全一致，代码更简洁
  直接将赋值语句中的Value替换为FormattedValue即可：

xlWorkSheet.Cells(i + 2, columnIndex + 1).Value = row.Cells(columnIndex).FormattedValue.ToString

内容的提问来源于stack exchange，提问作者lowkey