如何在VB.net中使用EPPlus统计Excel多列关联的DUS条目总数

1. 前置依赖说明 #

• 确保项目已引用EPPlus稳定版本包，无需额外证书配置
• 提前引入所需命名空间：

Imports OfficeOpenXml
Imports System.IO
Imports System.Collections.Generic
Imports System.Linq

2. 核心实现逻辑 #

2.1 自定义统计结果存储结构 #

先定义类存储分组统计结果，方便后续填充到Excel：

Public Class ShipTypeStat
    ' 船名
    Public Property ShipName As String
    ' 类型（CASA/PANL）
    Public Property Type As String
    ' 关联DUS条目总数
    Public Property DusCount As Integer
End Class

2.2 读取数据+分组统计代码 #

代码自动适配任意SHIP取值，无需硬编码船名：

' 入参说明：excelFilePath为目标Excel文件路径，targetSheetIndex为待统计工作表序号（从1开始）
Public Function CalculateDusCount(excelFilePath As String, targetSheetIndex As Integer) As List(Of ShipTypeStat)
    Dim resultList As New List(Of ShipTypeStat)()

    ' 用Using包裹自动释放资源，符合EPPlus操作规范
    Using excelPackage As New ExcelPackage(New FileInfo(excelFilePath))
        Dim workSheet As ExcelWorksheet = excelPackage.Workbook.Worksheets(targetSheetIndex)
        Dim totalRow As Integer = workSheet.Dimension.End.Row
        Dim totalCol As Integer = workSheet.Dimension.End.Column

        ' 第一步：定位SHIP/TYPE/DUS三列的列索引，适配列顺序变更场景
        Dim shipColIndex As Integer = -1
        Dim typeColIndex As Integer = -1
        Dim dusColIndex As Integer = -1
        For col = 1 To totalCol
            Dim headerText = workSheet.Cells(1, col).Text.Trim().ToUpper()
            Select Case headerText
                Case "SHIP" : shipColIndex = col
                Case "TYPE" : typeColIndex = col
                Case "DUS" : dusColIndex = col
            End Select
        Next

        ' 列校验，避免表头缺失报错
        If shipColIndex = -1 OrElse typeColIndex = -1 OrElse dusColIndex = -1 Then
            Throw New Exception("待统计工作表缺少必填列：SHIP/TYPE/DUS")
        End If

        ' 第二步：遍历数据行分组统计
        Dim statDict As New Dictionary(Of Tuple(Of String, String), Integer)()
        ' 从第2行开始遍历，跳过表头行
        For row = 2 To totalRow
            Dim shipVal = workSheet.Cells(row, shipColIndex).Text.Trim()
            Dim typeVal = workSheet.Cells(row, typeColIndex).Text.Trim().ToUpper()
            Dim dusVal = workSheet.Cells(row, dusColIndex).Text.Trim()

            ' 过滤无效行：仅统计TYPE为CASA/PANL、SHIP和DUS非空的行
            If Not String.IsNullOrEmpty(shipVal) AndAlso 
               (typeVal = "CASA" OrElse typeVal = "PANL") AndAlso 
               Not String.IsNullOrEmpty(dusVal) Then
                Dim groupKey = Tuple.Create(shipVal, typeVal)
                If statDict.ContainsKey(groupKey) Then
                    statDict(groupKey) += 1
                Else
                    statDict.Add(groupKey, 1)
                End If
            End If
        Next

        ' 第三步：转换字典为结果列表
        For Each item In statDict
            resultList.Add(New ShipTypeStat With {
                .ShipName = item.Key.Item1,
                .Type = item.Key.Item2,
                .DusCount = item.Value
            })
        Next
    End Using

    Return resultList
End Function

3. 结果填充示例 #

和你原有EPPlus填充逻辑完全兼容，示例：

' 调用统计方法
Dim statResult = CalculateDusCount("C:\你的Excel文件路径.xlsx", 1)
' 新增工作表存储统计结果
Dim resultSheet = excelPackage.Workbook.Worksheets.Add("DUS统计结果")
' 写入表头
resultSheet.Cells(1, 1).Value = "SHIP"
resultSheet.Cells(1, 2).Value = "TYPE"
resultSheet.Cells(1, 3).Value = "DUS条目总数"
' 逐行写入统计数据
For i = 0 To statResult.Count - 1
    resultSheet.Cells(i + 2, 1).Value = statResult(i).ShipName
    resultSheet.Cells(i + 2, 2).Value = statResult(i).Type
    resultSheet.Cells(i + 2, 3).Value = statResult(i).DusCount
Next
' 保存文件逻辑复用你原有代码即可

适配说明 #

• 如果不需要过滤DUS空值，直接删除判断条件Not String.IsNullOrEmpty(dusVal)即可
• 如果表头不在第一行，修改遍历表头的行号和数据遍历起始行号即可
• 代码自动适配所有SHIP取值，无需硬编码任何船名参数

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