如何在VB.NET中关联Access两表并在DataGridView合并同发票行显示

一、数据预处理：清理重复主信息 #

先通过内连接获取完整数据，必须确保SQL查询按invoice_id排序（添加ORDER BY invoice.invoice_id），之后在内存中处理数据，将同一invoice_id的后续行主信息字段（invoice_id、invoice_date、client_name）置空，仅保留首行的主信息。

示例代码：

' 假设已通过DataAdapter获取合并后的数据集到DataTable dtAll
Dim processedDt As DataTable = dtAll.Clone()
Dim lastInvoiceId As String = String.Empty

For Each row As DataRow In dtAll.Rows
    Dim newRow As DataRow = processedDt.NewRow()
    ' 复制所有字段值
    newRow.ItemArray = row.ItemArray.Clone()
    
    ' 对比当前行与上一行的发票ID，重复则清空主信息字段
    If newRow("invoice_id").ToString() = lastInvoiceId Then
        newRow("invoice_id") = DBNull.Value
        newRow("invoice_date") = DBNull.Value
        newRow("client_name") = DBNull.Value
    Else
        lastInvoiceId = newRow("invoice_id").ToString()
    End If
    
    processedDt.Rows.Add(newRow)
Next

' 绑定处理后的DataTable到DataGridView
DataGridView1.DataSource = processedDt

二、DataGridView显示优化 #

为让空白主信息单元格更整洁，可在CellFormatting事件中处理显示样式：

Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
    ' 定位主信息列且值为空的单元格
    Dim targetCols As List(Of Integer) = {
        DataGridView1.Columns("invoice_id").Index,
        DataGridView1.Columns("invoice_date").Index,
        DataGridView1.Columns("client_name").Index
    }.ToList()
    
    If targetCols.Contains(e.ColumnIndex) AndAlso e.Value Is DBNull.Value Then
        e.Value = String.Empty
        ' 去掉左侧边框，模拟视觉合并效果
        DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Style.BorderLeft = DataGridViewAdvancedBorderStyle.None
    End If
End Sub

三、PDF/Excel导出处理 #

基于预处理后的DataTable直接导出即可，无需额外调整结构，可通过样式区分主信息行：

1. Excel导出（使用EPPlus，需NuGet安装） #

Using package As New ExcelPackage()
    Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets.Add("发票报表")
    ' 写入表头
    For col As Integer = 0 To processedDt.Columns.Count - 1
        worksheet.Cells(1, col + 1).Value = processedDt.Columns(col).ColumnName
    Next
    ' 写入数据
    worksheet.Cells(2, 1).LoadFromDataTable(processedDt, False)
    
    ' 为主信息行添加背景色区分
    Dim lastInvId As String = String.Empty
    For row As Integer = 2 To processedDt.Rows.Count + 1
        Dim invId As String = worksheet.Cells(row, 1).Value?.ToString()
        If Not String.IsNullOrEmpty(invId) AndAlso invId <> lastInvId Then
            worksheet.Cells(row, 1, row, 3).Style.Fill.PatternType = ExcelFillStyle.Solid
            worksheet.Cells(row, 1, row, 3).Style.Fill.BackgroundColor.SetColor(Color.LightGray)
            lastInvId = invId
        End If
    Next
    
    ' 保存文件
    Dim saveDialog As New SaveFileDialog() With {.Filter = "Excel文件 (*.xlsx)|*.xlsx"}
    If saveDialog.ShowDialog() = DialogResult.OK Then
        package.SaveAs(New FileInfo(saveDialog.FileName))
    End If
End Using

2. PDF导出（使用iTextSharp，需NuGet安装） #

Dim document As New Document()
Dim saveDialog As New SaveFileDialog() With {.Filter = "PDF文件 (*.pdf)|*.pdf"}
If saveDialog.ShowDialog() = DialogResult.OK Then
    PdfWriter.GetInstance(document, New FileStream(saveDialog.FileName, FileMode.Create))
    document.Open()
    
    ' 创建PDF表格
    Dim pdfTable As New PdfPTable(processedDt.Columns.Count)
    pdfTable.WidthPercentage = 100
    
    ' 添加表头
    For Each col As DataColumn In processedDt.Columns
        Dim cell As New PdfPCell(New Phrase(col.ColumnName))
        cell.BackgroundColor = New BaseColor(Color.LightGray)
        pdfTable.AddCell(cell)
    Next
    
    ' 添加数据行
    Dim lastInvId As String = String.Empty
    For Each row As DataRow In processedDt.Rows
        For Each col As DataColumn In processedDt.Columns
            Dim cellValue As String = If(row(col) Is DBNull.Value, String.Empty, row(col).ToString())
            ' 主信息行添加背景色
            If col.ColumnName = "invoice_id" AndAlso Not String.IsNullOrEmpty(cellValue) AndAlso cellValue <> lastInvId Then
                Dim cell As New PdfPCell(New Phrase(cellValue))
                cell.BackgroundColor = New BaseColor(Color.LightBlue)
                pdfTable.AddCell(cell)
                lastInvId = cellValue
            Else
                pdfTable.AddCell(New Phrase(cellValue))
            End If
        Next
    Next
    
    document.Add(pdfTable)
    document.Close()
End If

关键注意事项 #

• SQL查询必须按invoice_id排序，否则会出现主信息重复或遗漏的问题。
• 导出时通过样式区分主信息行，避免导出后数据可读性差。
• 若使用.NET Core/.NET 5+，推荐使用EPPlus 5+和iText7替代旧版库。

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