VB.NET（基于MS Access）实现DataGridView导出至Excel：保留可见列与列标题并维持日期格式

Let's sort out your export issues—you're missing column headers after adding visible column checks, and you need to keep date formats intact. Here's what went wrong in your code, plus the corrected version:

Key Issues in Your Original Code #

1. Variable Mismatch: In the header loop, you used dgvViewMode.Columns(i).HeaderCell.Visible but the loop variable is col (not i), which would throw an error and skip header assignments entirely.
2. Array Index Misalignment: Your rawData array is sized for all columns (minus 2), but you're skipping hidden columns—this leaves empty spots in the array, leading to missing or misaligned headers/data.
3. No Date Format Handling: Assigning values directly via Value2 converts dates to Excel's serial number format instead of keeping them as readable dates.

Corrected Code #

Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
    If SaveFileDialog1.ShowDialog = DialogResult.OK Then
        Dim Ex As Object = Nothing
        Dim Wb As Object = Nothing
        Dim Ws As Object = Nothing
        Try
            ' Initialize Excel objects
            Ex = CreateObject("Excel.Application")
            Wb = Ex.Workbooks.Add()
            Ws = Wb.Worksheets(1) ' Use first sheet directly
            
            ' Collect indices of visible columns (exclude hidden ones)
            Dim visibleColumns As New List(Of Integer)()
            For col As Integer = 0 To dgvViewMode.Columns.Count - 1
                ' Skip the last 2 columns as per your original code's logic
                If col <= dgvViewMode.Columns.Count - 2 AndAlso dgvViewMode.Columns(col).Visible Then
                    visibleColumns.Add(col)
                End If
            Next
            
            ' Write column headers
            For headerIndex As Integer = 0 To visibleColumns.Count - 1
                Dim colIndex As Integer = visibleColumns(headerIndex)
                Ws.Cells(1, headerIndex + 1).Value = dgvViewMode.Columns(colIndex).HeaderText.ToUpper()
                ' Optional: Format header style for better visibility
                Ws.Cells(1, headerIndex + 1).Font.Bold = True
            Next
            
            ' Write row data and handle date formats
            For row As Integer = 0 To dgvViewMode.Rows.Count - 2 ' Skip last row if it's a new empty row
                For colIndex As Integer = 0 To visibleColumns.Count - 1
                    Dim dgvColIndex As Integer = visibleColumns(colIndex)
                    Dim cellValue As Object = dgvViewMode.Rows(row).Cells(dgvColIndex).Value
                    
                    If TypeOf cellValue Is Date Then
                        ' Set cell value as date and apply your preferred date format
                        Ws.Cells(row + 2, colIndex + 1).Value = cellValue
                        Ws.Cells(row + 2, colIndex + 1).NumberFormat = "yyyy-mm-dd" ' Adjust format as needed
                    Else
                        Ws.Cells(row + 2, colIndex + 1).Value = cellValue
                    End If
                Next
            Next
            
            ' Auto-fit columns for cleaner output
            Ws.UsedRange.Columns.AutoFit()
            
            ' Save the workbook
            Wb.SaveAs(SaveFileDialog1.FileName)
            MsgBox("Exported Successfully.", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox($"Export failed: {ex.Message}", MsgBoxStyle.Critical)
        Finally
            ' Ensure Excel objects are properly released to avoid lingering processes
            If Ws IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(Ws)
            End If
            If Wb IsNot Nothing Then
                Wb.Close(True)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(Wb)
            End If
            If Ex IsNot Nothing Then
                Ex.Quit()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(Ex)
            End If
            GC.Collect()
            GC.WaitForPendingFinalizers()
        End Try
    End If
End Sub

What Changed? #

• Visible Column Collection: We first gather only the indices of columns that are visible (and skip the last 2 columns as your original code intended), so we don't waste space on hidden columns and avoid alignment issues.
• Header Fix: Headers are written directly to Excel cells using the visible column list, eliminating the variable typo and empty array spots that caused missing headers.
• Date Format Preservation: We check if a cell value is a Date type, then assign it to Excel and explicitly set the number format to keep dates readable instead of converting them to serial numbers.
• Safer Cleanup: Added a Try/Finally block to ensure Excel objects are properly released even if an error occurs, preventing lingering Excel processes running in the background.

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