我正在尝试使用DataTable.Merge()选项合并多个Excel文件
For Each fileName As String In Directory.GetFiles("C:\TEMP\.", "*.xls") Dim connectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;""", fileName) Dim adapter As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString) Dim ds As New DataSet adapter.Fill(ds, "anyNameHere") Dim TempTable As DataTable TempTable = ds.Tables.Item("anyNameHere") table1.Merge(TempTable) MsgBox(fileName) Next DataGridView1.DataSource = table1 MsgBox(table1.Rows.Count)
但在合并时会出现以下错误
.ColumnName and
这是因为excel中的一列被读作文本而另一列被读为double,而两者都有数值.
为了避免这种情况,我还在连接字符串中提到了IMEX = 1,仍然出现此错误.
在合并中使用MissingSchemaAction.Ignore作为MissingSchemaAction参数
table1.Merge(TempTable, True, MissingSchemaAction.Ignore)