VB.NET和VBA教程-读取Range(“A1:C10“).Value得到数组的区别

前面已学“VBA对单元格区域读取”的知识：

在VB.NET 中，数组默认是从 0 开始索引的，但处理 Excel 数据时有特殊情况。以下是详细说明：

1.VB.NET 默认数组行为

' 标准数组 - 从 0 开始Dim arr(5) As Integer ' 索引 0-5arr(0) = 10arr(5) = 20

2.通过 Excel Interop 获取的数据数组

当通过 COM Interop 操作 Excel 时，有两种情况：

情况 A：使用 Value2 属性

Imports Excel = Microsoft.Office.Interop.ExcelDim excelApp As New Excel.ApplicationDim workbook As Excel.Workbook = excelApp.Workbooks.Open("文件路径")Dim worksheet As Excel.Worksheet = workbook.Sheets(1)' 获取范围值 - 返回 Object(,) 二维数组Dim data As Object(,) = worksheet.Range("A1:C10").Value2' 索引从 1 开始！与 VBA 相同Dim firstValue As Object = data(1, 1) ' A1Dim lastValue As Object = data(10, 3) ' C10' 检查数组维度Console.WriteLine("LBound: " & LBound(data, 1)) ' 输出 1Console.WriteLine("UBound: " & UBound(data, 1)) ' 输出 10

情况 B：通过 COM Interop 返回的数组

3.完整示例代码

Imports System.Runtime.InteropServicesPublic Sub ReadExcelData() Dim excelApp As New Excel.Application() Dim workbook As Excel.Workbook = Nothing Dim worksheet As Excel.Worksheet = Nothing Try ' 打开工作簿 workbook = excelApp.Workbooks.Open("C:\test.xlsx") worksheet = workbook.Sheets(1) ' 方法1：获取原始 Excel 数组 Dim excelData As Object(,) = worksheet.Range("A1:C10").Value2 Console.WriteLine("Excel 数组边界:") Console.WriteLine($"行: {LBound(excelData, 1)} 到 {UBound(excelData, 1)}") Console.WriteLine($"列: {LBound(excelData, 2)} 到 {UBound(excelData, 2)}") ' 访问数据（从1开始） For i As Integer = LBound(excelData, 1) To UBound(excelData, 1) For j As Integer = LBound(excelData, 2) To UBound(excelData, 2) Console.Write($"{excelData(i, j)} ") Next Console.WriteLine() Next ' 方法2：转换为 .NET 标准数组（从0开始） Dim rows As Integer = UBound(excelData, 1) - LBound(excelData, 1) + 1 Dim cols As Integer = UBound(excelData, 2) - LBound(excelData, 2) + 1 Dim netArray(rows - 1, cols - 1) As Object For i As Integer = 0 To rows - 1 For j As Integer = 0 To cols - 1 netArray(i, j) = excelData(i + 1, j + 1) ' 注意偏移 Next Next Finally ' 清理 COM 对象 If worksheet IsNot Nothing Then Marshal.ReleaseComObject(worksheet) If workbook IsNot Nothing Then workbook.Close(False) Marshal.ReleaseComObject(workbook) End If excelApp.Quit() Marshal.ReleaseComObject(excelApp) End TryEnd Sub

4.不同场景对比表

5.重要注意事项

1）内存泄漏预防

' 必须正确处理 COM 对象Marshal.FinalReleaseComObject(worksheet)worksheet = NothingOption Base 的影响' VB.NET 中也可以使用 Option BaseOption Base 1 ' 设置数组默认从1开始' 但这不影响 Excel Interop 返回的数组

2）错误处理

Try Dim data As Object(,) = worksheet.Range("A1").Value2 ' 单个单元格返回 Object，不是数组！ If TypeOf data Is Array Then ' 处理数组 Else ' 处理单个值 End IfCatch ex As Exception Console.WriteLine("错误: " & ex.Message)End Try

6.最佳实践建议

1）始终检查数组边界

Dim data As Object(,) = range.Value2If data Is Nothing Then Exit SubDim startRow As Integer = LBound(data, 1)Dim endRow As Integer = UBound(data, 1)

2）转换为标准 .NET 数组处理

Function ConvertToZeroBased(excelArray As Object(,)) As Object(,) Dim rows = excelArray.GetUpperBound(0) ' 注意：GetUpperBound 返回最大索引 Dim cols = excelArray.GetUpperBound(1) Dim result(rows - 1, cols - 1) As Object For i = 0 To rows - 1 Array.Copy(excelArray, i + 1, result, i, cols) Next Return resultEnd Function

3）使用现代库替代 Interop

'考虑使用 EPPlus（.NET Core/5+ 兼容） '或 ClosedXML（简单 API） '这些库通常使用 1-based 索引，与 Excel 一致

总结

⚠️ 如果使用更新的 Excel 库（如 EPPlus 5+），请注意其数组索引策略可能有所不同，需查阅对应文档。

作者开发的插件：