如何将多个数据表合并为一个数据表？

Question

我有多个excel文件，我打开为数据表，我想合并所有这些数据表在.net中的单个datatable。

例如，Apr09.xls，May09.xls，Jun09.xls

所有数据如下

Apr09.xls

EMPCODE,PresentDays
0001   ,30
0002   ,21

May09.xls

EMPCODE,PresentDays
0001   ,25
0002   ,30

新的datatable如下所示

EMPCODE,PresentDays
0001   ,30
0002   ,21
0001   ,25
0002   ,30

如何创建它。

Answer 1

Datatable.Merge的用法如下

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)