VB.NET中DataGridView导入Excel时生成F1/F2列而非使用现有列问题

问题描述 #

在VB.NET环境下将Excel文件导入DataGridView时，程序未使用DataGridView的现有列，反而生成了F1、F2这类新列。已尝试设置frmdata.datagridview1.AutoGenerateColumns = False，但问题仍未解决。

导入前界面：

导入后界面：

现有导入函数代码 #

Function ExcelImport() As Boolean
Try
Dim fpath As String
fpath = "C:\Users\rajes\OneDrive\Documents\Visual Studio 2010\Projects\CARA Bank Import\CARA Bank Import\bin\Debug\CARATemplate.xlsx"
Dim MyConnection As OleDb.OleDbConnection
Dim Ds As System.Data.DataSet
Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fpath & ";Extended Properties=""Excel 12.0;HDR=NO;""")

MyAdapter = New System.Data.OleDb.OleDbDataAdapter("Select [F1],[F2], [F3],[F4], [F5],[F6], [F7],[F8] from [Bank$A8:H10]", MyConnection)
Ds = New System.Data.DataSet
MyAdapter.Fill(Ds)

With FrmData.DataGridView1
   .DataSource = Ds.Tables(0)
End With

ExcelImport = True
Catch ex As Exception
MsgBox(ex.Message)
ExcelImport = False
End Try
End Function

尝试过的代码：

frmdata.datagridview1.AutoGenerateColumns = False

问题原因 #

1. 连接字符串设置了HDR=NO，表示Excel没有表头，OleDb会自动用F1、F2...作为DataTable的列名。
2. 即使关闭了自动生成列，DataGridView的现有列没有和DataTable的列（F1/F2等）建立绑定关系，所以无法匹配。

解决方法 #

方法一：手动绑定列的DataPropertyName

在绑定数据源之前关闭自动生成列，然后为每个现有列指定对应的DataTable列名：

Function ExcelImport() As Boolean
Try
    Dim fpath As String
    fpath = "C:\Users\rajes\OneDrive\Documents\Visual Studio 2010\Projects\CARA Bank Import\CARA Bank Import\bin\Debug\CARATemplate.xlsx"
    Dim MyConnection As OleDb.OleDbConnection
    Dim Ds As System.Data.DataSet
    Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
    MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fpath & ";Extended Properties=""Excel 12.0;HDR=NO;""")

    MyAdapter = New System.Data.OleDb.OleDbDataAdapter("Select [F1],[F2], [F3],[F4], [F5],[F6], [F7],[F8] from [Bank$A8:H10]", MyConnection)
    Ds = New System.Data.DataSet
    MyAdapter.Fill(Ds)

    With FrmData.DataGridView1
        .AutoGenerateColumns = False ' 必须在绑定数据源前设置
        ' 根据你现有列的Name，对应绑定F1到F8
        .Columns("colTransactionDate").DataPropertyName = "F1"
        .Columns("colParticulars").DataPropertyName = "F2"
        .Columns("colVoucherNo").DataPropertyName = "F3"
        .Columns("colDebit").DataPropertyName = "F4"
        .Columns("colCredit").DataPropertyName = "F5"
        .Columns("colBalance").DataPropertyName = "F6"
        .Columns("colNarration").DataPropertyName = "F7"
        .Columns("colStatus").DataPropertyName = "F8"
        .DataSource = Ds.Tables(0)
    End With

    ExcelImport = True
Catch ex As Exception
    MsgBox(ex.Message)
    ExcelImport = False
End Try
End Function

注意：替换代码中的colTransactionDate等为你DataGridView实际的列Name。

方法二：给Excel查询列起别名，匹配现有列的DataPropertyName

修改查询语句，给F1-F8起和现有列DataPropertyName一致的别名，这样无需手动绑定：

Function ExcelImport() As Boolean
Try
    Dim fpath As String
    fpath = "C:\Users\rajes\OneDrive\Documents\Visual Studio 2010\Projects\CARA Bank Import\CARA Bank Import\bin\Debug\CARATemplate.xlsx"
    Dim MyConnection As OleDb.OleDbConnection
    Dim Ds As System.Data.DataSet
    Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
    ' 给查询列起别名，对应现有列的DataPropertyName
    Dim query As String = "Select [F1] as TransactionDate, [F2] as Particulars, [F3] as VoucherNo, [F4] as Debit, [F5] as Credit, [F6] as Balance, [F7] as Narration, [F8] as Status from [Bank$A8:H10]"
    MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fpath & ";Extended Properties=""Excel 12.0;HDR=NO;""")

    MyAdapter = New System.Data.OleDb.OleDbDataAdapter(query, MyConnection)
    Ds = New System.Data.DataSet
    MyAdapter.Fill(Ds)

    With FrmData.DataGridView1
        .AutoGenerateColumns = False ' 绑定前设置
        .DataSource = Ds.Tables(0)
    End With

    ExcelImport = True
Catch ex As Exception
    MsgBox(ex.Message)
    ExcelImport = False
End Try
End Function

注意：别名必须和现有列的DataPropertyName完全一致，大小写敏感。

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