VB.NET按ID过滤Excel、存储行索引生成合并精简Excel的实现方法

1. 原有代码语法、逻辑问题修正 #

• 字段定义错误：Public WEIGHT New List(Of String) 缺少As关键字，正确写法为Public WEIGHT As New List(Of String)
• 提取函数逻辑错误：原While循环内已经执行了indice += 1，循环外多余的indice += 1会跳过一行数据，需要删除。

2. 放弃平行List，用实体类绑定字段 #

多个独立List靠索引对应数据的方式，一旦过滤、排序就会出现字段错位，直接定义实体类绑定单条数据的所有字段：

' 单条Excel数据对应实体
Public Class ExcelRow
    Public Property ID As String
    Public Property WEIGHT As String
    Public Property NAME As String
    Public Property COLOR As String
    Public Property VOLUME As String
    Public Property OriginalRowIndex As Integer ' 直接存原Excel行号，不需要额外映射
End Class

3. 改造Excel提取函数，自动关联字段与行号 #

Function ExtraerDatosExcel(ByVal ruta As String, ByVal esPrimerArchivo As Boolean) As List(Of ExcelRow)
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial
    Dim resultado As New List(Of ExcelRow)
    Try
        Using stream = System.IO.File.OpenRead(ruta)
            Using package = New OfficeOpenXml.ExcelPackage(stream)
                ' 直接取第一个工作表
                Dim hoja = package.Workbook.Worksheets(0)
                ' 从第2行开始读（第1行为表头）
                Dim filaIndice As Integer = 2
                While filaIndice < 5000
                    ' 跳过空ID行
                    Dim idValor = hoja.Cells("A" & filaIndice).Value?.ToString().Trim()
                    If String.IsNullOrEmpty(idValor) Then Exit While

                    ' 初始化当前行实体，直接存原行号
                    Dim fila = New ExcelRow With {
                        .ID = idValor,
                        .OriginalRowIndex = filaIndice
                    }
                    ' 按文件类型提取对应字段，列号根据实际Excel调整
                    If esPrimerArchivo Then
                        fila.WEIGHT = hoja.Cells("B" & filaIndice).Value?.ToString().Trim()
                        fila.NAME = hoja.Cells("C" & filaIndice).Value?.ToString().Trim()
                    Else
                        fila.COLOR = hoja.Cells("B" & filaIndice).Value?.ToString().Trim()
                        fila.VOLUME = hoja.Cells("C" & filaIndice).Value?.ToString().Trim()
                    End If

                    resultado.Add(fila)
                    filaIndice += 1
                End While
            End Using
        End Using
    Catch ex As Exception
        MsgBox(ex.ToString())
    End Try
    Return resultado
End Function

4. 按ID交集合并数据 #

不需要提前单独过滤第二个Excel，直接通过ID匹配生成合并结果：

' 分别提取两个Excel的完整数据
Dim datosArchivo1 = ExtraerDatosExcel("第一个Excel的实际路径.xlsx", True)
Dim datosArchivo2 = ExtraerDatosExcel("第二个Excel的实际路径.xlsx", False)

' 将第二个Excel转为ID为键的字典，匹配效率远高于循环遍历List
Dim dictArchivo2 = datosArchivo2.ToDictionary(Function(x) x.ID)

' 生成仅保留共有ID的合并数据
Dim datosCombinados = New List(Of ExcelRow)
For Each fila1 In datosArchivo1
    Dim fila2 As ExcelRow = Nothing
    ' 匹配到共有ID就合并字段
    If dictArchivo2.TryGetValue(fila1.ID, fila2) Then
        datosCombinados.Add(New ExcelRow With {
            .ID = fila1.ID,
            .WEIGHT = fila1.WEIGHT,
            .NAME = fila1.NAME,
            .COLOR = fila2.COLOR,
            .VOLUME = fila2.VOLUME
        })
    End If
Next

5. 写入合并后的Excel #

直接遍历合并好的实体列表写入，不需要手动对应List索引：

Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial
    Dim path = seleccionardirectorio("Excel|.xlsx")
    If String.IsNullOrWhiteSpace(path) Then Exit Sub

    Using excel = New ExcelPackage(New FileInfo(path))
        Dim hoja = excel.Workbook.Worksheets.Add("Hoja1")
        ' 执行原有表头初始化逻辑，按ID、WEIGHT、NAME、COLOR、VOLUME顺序写表头即可
        INICIALIZAR_PLANILLA(hoja)

        ' 从第2行开始写合并数据
        Dim filaIndice As Integer = 2
        For Each fila In datosCombinados
            hoja.Cells("A" & filaIndice).Value = fila.ID
            hoja.Cells("B" & filaIndice).Value = fila.WEIGHT
            hoja.Cells("C" & filaIndice).Value = fila.NAME
            hoja.Cells("D" & filaIndice).Value = fila.COLOR
            hoja.Cells("E" & filaIndice).Value = fila.VOLUME
            filaIndice += 1
        Next
        ' 保存文件
        excel.Save()
    End Using
End Sub

注：如果需要保留ID对应的原Excel行号做其他操作，直接读实体的OriginalRowIndex字段即可，不需要额外维护行号映射表。

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