VB.NET：Excel Range转DataTable时数组添加至DataTable遇问题求助

原代码的核心问题是索引不匹配：Excel返回的二维数组是1起始索引，而DataTable的行、列都是0起始索引，直接用i、j赋值会导致索引越界或赋值错误。另外，新增行后直接用i访问行也会引发索引超出范围的异常。

修正后的代码 #

Public Sub ExcelRangeToDataTable()
    Dim xlApp As Excel.Application
    Dim WB As Excel.Workbook
    Dim WS As Excel.Worksheet
    Dim DataRange As Excel.Range
    Dim LR As Integer

    xlApp = GetObject(, Constants.ExcelApp)
    WB = xlApp.Workbooks("TestWB")
    WS = WB.Sheets("Sheet1")
    LR = WS.Cells(WS.Rows.Count, "A").End(Excel.XlDirection.xlUp).Row
    DataRange = WS.Range("A" & Constants.First_Row & ":Y" & LR)

    Dim DT As DataTable = New DataTable()
    Call CreateDataTable(DT) '创建DataTable及所有列

    Dim excelRows As Object(,) = DataRange.Value '创建二维数组

    '将二维数组添加至DataTable
    For i As Integer = 1 To excelRows.GetUpperBound(0)
        '新增行后，直接获取当前新增的行对象
        Dim newRow As DataRow = DT.Rows.Add()
        For j As Integer = 1 To excelRows.GetUpperBound(1)
            'Excel数组是1-based，DataTable列是0-based，做索引转换
            '处理Excel空单元格，转为DataTable支持的空值格式
            newRow(j - 1) = If(excelRows(i, j) Is Nothing, DBNull.Value, excelRows(i, j))
        Next j
    Next i
End Sub

关键修正点 #

• 行对象直接赋值：用DT.Rows.Add()返回的DataRow对象操作，避免手动计算行索引出错
• 列索引转换：将Excel数组的1起始索引j转为j-1，匹配DataTable的0起始列索引
• 空值兼容处理：把Excel中的空单元格（Nothing）转为DBNull.Value，符合DataTable的空值规范

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