Quote:
select * from [Sheet1$],[Sheet2$]
You're specifying a join with no predicate. That will produce the cross-product of the two sheets - every row from sheet 1 will be joined to every row on sheet 2.
Unfortunately, it looks like there is no applicable predicate you can join on, since you're associating the data based on its row number. You'll need to load the data from both sheets separately and then combine it.
Add a couple of helper methods:
private static DataTable LoadAllDataFromSheet(OleDbConnection connection, string sheetName)
{
using (var command = new OleDbCommand($"SELECT * FROM [{sheetName}$]", connection))
{
var table = new DataTable();
var adapter = new OleDbDataAdapter(command);
adapter.Fill(table);
return table;
}
}
private static object[] CombineRows(object[] row1, object[] row2)
{
object[] result = new object[row1.Length + row2.Length];
row1.CopyTo(result, 0);
row2.CopyTo(result, row1.Length);
return result;
}
private static DataTable MergeTables(DataTable sheet1, DataTable sheet2)
{
var result = new DataTable();
result.BeginInit();
foreach (DataColumn column in sheet1.Columns)
{
result.Columns.Add($"Sheet1.{column.ColumnName}", column.DataType);
}
foreach (DataColumn column in sheet2.Columns)
{
result.Columns.Add($"Sheet2.{column.ColumnName}", column.DataType);
}
result.EndInit();
result.BeginLoadData();
foreach (object[] row in sheet1.Rows.Cast<DataRow>().Zip(sheet2.Rows.Cast<DataRow>(), (r1, r2) => CombineRows(r1.ItemArray, r2.ItemArray)))
{
result.Rows.Add(row);
}
result.EndLoadData();
return result;
}
Then use them to load the data:
using (OleDbConnection con = new OleDbConnection(conn))
{
DataTable sheet1 = LoadAllDataFromSheet(con, "Sheet1");
DataTable sheet2 = LoadAllDataFromSheet(con, "Sheet2");
dtexcel = MergeTables(sheet1, sheet2);
}
Output:
Sheet1.F1 | Sheet2.F1
=======================
name | department
hemal | Engineering
NB: Assuming the values in the first row are the column headings, you can change your connection string to specify
HDR=YES
, and you'll get:
Sheet1.name | Sheet2.department
=================================
hemal | Engineering
If the column headings are unique, then you can change the
MergeTables
function so that it doesn't prefix the column names with the sheet names.