Combine two Excel files and display in a GridView
I have two different Excel files (.xls). In excel1.xls
there is a column named KATIP
, and in excel2.xls
is a column named SAVCI
. I want to get these columns and combine them into a table named Nobet
with two columns: SAVCI
and KATIP
.
Then I want to show them in a Gridview control in ASP.NET. I have a working code, but when I run it, it gets the first column named KATIP
, then it gets the column named SAVCI
. They are not merged and appear as separate columns.
OleDbConnection DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +
Server.MapPath("~/App_Data/excel1.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
string SQLString = "SELECT * FROM [Page1$]";
OleDbCommand DBCommand = new OleDbCommand(SQLString, DBConnection);
OleDbDataAdapter da = new OleDbDataAdapter(DBCommand);
DataSet ds = new DataSet("Nobet");
da.Fill(ds,"Nobet");
DBConnection.Close();
DBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +Server.MapPath("~/App_Data/excel2.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
DBCommand = new OleDbCommand(SQLString, DBConnection);
da = new OleDbDataAdapter(DBCommand);
da.Fill(ds,"Nobet");
GridView1.DataSource = ds.Tables["Nobet"];
GridView1.DataBind();
DBConnection.Close();
a source to share
I solved my problem ... here is what I did:
OleDbConnection DBConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +
Server.MapPath("~/App_Data/savcilik_katip.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
OleDbCommand DBCommand = new OleDbCommand("SELECT * FROM [Sayfa1$]", DBConnection);
OleDbDataAdapter da = new OleDbDataAdapter(DBCommand);
DataSet ds = new DataSet("Nobet");
da.Fill(ds, "Nobet");
DBConnection.Close();
DBConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" +
Server.MapPath("~/App_Data/savcilik_savci.xls") + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes\"");
DBConnection.Open();
DBCommand = new OleDbCommand("SELECT * FROM [Sayfa1$]", DBConnection);
da = new OleDbDataAdapter(DBCommand);
da.Fill(ds, "Nobetci");
DBConnection.Close();
for (int i = 0; i < ds.Tables["Nobet"].Rows.Count; i++)
{
ds.Tables["Nobet"].Rows[i]["SAVCI"] = ds.Tables["Nobetci"].Rows[i]["SAVCI"];
}
GridView1.DataSource = ds.Tables["Nobet"];
GridView1.DataBind();
a source to share