VB.net通过Excelpackage对(打开状态)的excel取数以及创建新的excel文件

目录

一、从excel内读取数据

二、创建新的Excel文件

---

一、从excel内读取数据

代码实例是获取B列去重数据 并且在该Excel打开状态下取数；

Dim yourFilePath As String = "你需要打开的excel路径"
'提取B列数据（去重）
Dim uniqueValues As New HashSet(Of Object)()
Using fileStream As FileStream = File.Open(yourFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
    '打开Excel文件
    Using excelPackage As New ExcelPackage(fileStream)
        '获取第一个工作表 如果工作表名是固定的，也可以用 "你的excel的工作表名称"
        Dim worksheet As ExcelWorksheet = excelPackage.Workbook.Worksheets(1)
        ' 获取B列的数据范围
		'忽略表头，从第二行开始   开始行可自行修改为自己需要的
        Dim startRow As Integer = 2 
		'最后一行  也可根据需求改成自己需要的固定结束行
        Dim endRow As Integer = worksheet.Dimension.End.Row 
        For rowIndex As Integer = startRow To endRow
		'2表示列，可根据需求自行修改
            Dim cellValue As Object = worksheet.Cells(rowIndex, 2).Value    
            If cellValue IsNot Nothing AndAlso Not uniqueValues.Contains(cellValue) Then
                uniqueValues.Add(cellValue)
            End If
        Next
    End Using
End Using

如果想获取某范围内的数据可使用:

Dim needData = worksheet.Cells(startRow, startColumn, endRow, worksheet.Dimension.Columns).Value

二、创建新的Excel文件

'列名不是固定的
Dim ExcelTitle As String() = {"列名1", "列名2", "列名3", "列名4-1", "列名4-2"} 
If maxKeyCount > 1 Then
   For i = 2 To maxKeyCount
       Array.Resize(ExcelTitle, ExcelTitle.Length + 2) '将数组的大小调整为原来的长度加上 2
       ExcelTitle(ExcelTitle.Length - 2) = "列名" & i+3 & "-1"
       ExcelTitle(ExcelTitle.Length - 1) = "列名" & i+3 & "-2"
   Next
End If
'创建Excel文件
Using excelPackage As New ExcelPackage()
    Dim worksheet As ExcelWorksheet = excelPackage.Workbook.Worksheets.Add("工作表的名称{例：Sheet1}")
    ' 写入表头
    For i As Integer = 0 To ExcelTitle.Length - 1
        worksheet.Cells(1, i + 1).Value = ExcelTitle(i)
    Next
    Dim row As Integer = 2 ‘行
    ' 遍历外部字典 
	'我这里是在字典内取得数据，字典结构为 列名1，列名2，列名3，{(列名4-1，列名4-2),(……，……),……}
	'Dim dicFillExcel As New Dictionary(Of String, Dictionary(Of String, Dictionary(Of String, Dictionary(Of String, List(Of String)))))
	
    For Each outerKey As String In dicFillExcel.Keys
        Dim middleDictionary As Dictionary(Of String, Dictionary(Of String, Dictionary(Of String, List(Of String)))) = dicFillExcel(outerKey)
        ' 遍历中间字典
        For Each middleKey As String In middleDictionary.Keys
            Dim innerDictionary As Dictionary(Of String, Dictionary(Of String, List(Of String))) = middleDictionary(middleKey)
            ' 遍历内部字典
            For Each innerKey As String In innerDictionary.Keys
                Dim innerSon As Dictionary(Of String, List(Of String)) = innerDictionary(innerKey)
                ' 写入每个元素到Excel的一行
                worksheet.Cells(row, 1).Value = outerKey
                worksheet.Cells(row, 2).Value = middleKey
                worksheet.Cells(row, 3).Value = innerKey
                Dim i As Integer = 4
                For Each innerSonKey As String In innerSon.Keys
                    Dim innerKeyCount As Integer = innerSon.Keys.Count
                    Dim innerList As List(Of String) = innerSon(innerSonKey)
                    Dim innerListString As String = String.Join(",", innerList)
                    ' 写入每个元素到Excel的一行
                    worksheet.Cells(row, 0 + i).Value = innerSonKey
                    worksheet.Cells(row, 1 + i).Value = innerListString
                    i += 2
                   '插入超链接
                   'worksheet.Cells(row, 0+i).Hyperlink = New Uri($"file:///{例如：excel路径}")
                Next
            Next
            row += 1
        Next
    Next
    ' 自动调整所有列的宽度
    worksheet.Cells.AutoFitColumns()
    ' 保存Excel文件
    Dim filePath As String = "路径" & "\Excel的名字.xlsx"
    ' 检查文件是否存在
    If File.Exists(filePath) Then
        ' 如果文件存在，则删除
        File.Delete(filePath)
    End If
    excelPackage.SaveAs(New IO.FileInfo(filePath))
End Using

代码内是在字典内取得数据，可根据数据存放的方式等自行修改。
可用例子中的遍历方式填充数据；
如用list存数的话可以使用 ExcelPackage 提供的 LoadFromCollection 方法，它可以将集合的数据直接加载到 Excel 工作表的指定列中。

 ' 准备要写入的数据
    Dim list1 As New List(Of String)()
    list1.Add("数据1")
    list1.Add("数据2")
    list1.Add("数据3")

    ' 将数据加载到工作表的 A 列
    worksheet.Cells("A1").LoadFromCollection(list1)