Join/merge two excel files in asp.net and display them in gridview

Question

I am trying to merge two excel files in asp.net and display them in gridview as one table. The code below is displaying only one table. Can anyone tell me what is the problem with the code below? If you have a better idea please let me know.

protected void MergTables()
        {
            string connString = ConfigurationManager.ConnectionStrings[hwTypes].ConnectionString;
            OleDbConnection DBConnection = new OleDbConnection(connString);
            DBConnection.Open();
            OleDbCommand DBCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", DBConnection);
            OleDbDataAdapter da = new OleDbDataAdapter(DBCommand);
            DataSet ds = new DataSet("Stock");
            da.Fill(ds, "HWTypes");
            DBConnection.Close();
            string _stockConn = ConfigurationManager.ConnectionStrings[stockConn].ConnectionString;
            DBConnection = new OleDbConnection(_stockConn);
            DBConnection.Open();
            DBCommand = new OleDbCommand("SELECT * FROM [Stock_voorlopig$]", DBConnection);
            da = new OleDbDataAdapter(DBCommand);
            da.Fill(ds, "Stock");

            DBConnection.Close();
            for (int i = 0; i < ds.Tables["HWTypes"].Rows.Count; i++)
            {
                ds.Tables["HWTypes"].Rows[i]["ProductID"] = ds.Tables["Stock"].Rows[i]["Partno"];
            }

            GridView1.DataSource = ds.Tables["Stock"];
            GridView1.DataBind();
        }

score 2 · Accepted Answer · answered Dec 18 '12 at 09:36

2

The problem is that you are using just a single DataTable in your GridView and you haven't yet joined both.

Here's an approach which uses Linq-To-DataSet to join both tables and creates an anonymous type as datasource for the GridView.

DataSet ds = new DataSet("Stock");
using (var dbConnection = new OleDbConnection(connString))
using (var dbCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", dbConnection))
using (var da = new OleDbDataAdapter(dbCommand))
{
    da.Fill(ds, "HWTypes");
}

using (var dbConnection = new OleDbConnection(stockConn))
using (var dbCommand = new OleDbCommand("SELECT * FROM [Stock_voorlopig$]", dbConnection))
using (var da = new OleDbDataAdapter(dbCommand))
{
    da.Fill(ds, "Stock");
}

var joined = from rType in ds.Tables["HWTypes"].AsEnumerable()
             join rStock in ds.Tables["Stock"].AsEnumerable()
             on rType.Field<string>("ProductID") equals rStock.Field<string>("Partno")
             select new
             {
                 ProductID = rType.Field<string>("ProductID")
                 // add the other columns you need here
             };


GridView1.DataSource = joined;
GridView1.DataBind();

answered Dec 18 '12 at 09:36

Tim Schmelter

450,073
74
686
939

Hi Tim, I have another question for your. It is the same thing as above but now I would like to display two columns, one from "Sheet1" and one from "Stock_voorlopig$", in a dropdownlist.DataTextField = "FullName", for example. (eg: Select ProductID +' - ' + Name +' - ' as HWTypeFullName), how can I do that? – Imir Hoxha Dec 18 '12 at 10:39
@IForti: Just select these fields where i have commented my code with `// add the other columns you need here`. Then you can use the anonymous type as datasource for your DropDownList and set the `DataTextField` and `DataValueField` accordingly to the names of the properties in the anonymous type (like `ProductID` above). – Tim Schmelter Dec 18 '12 at 10:42
Thanks again for your help. One more question. Do you know how can I add the Linq query result above - ie: joined- into a datatable or dataset so that I can query the datatabe or the dataset? – Imir Hoxha Dec 19 '12 at 09:45
@IForti: i don't understand the question. Why do you need to join something when you want to query it from the database anyway? Or do you want to update the database from the result above somehow? Maybe you need to ask another quation since it's difficult to answer in the comments. – Tim Schmelter Dec 19 '12 at 09:49
Hi Tim, thanks a gain for your help. I will put it in the question above. – Imir Hoxha Dec 19 '12 at 11:09
Hi Tim, I created a second post. Can you have a look here: http://stackoverflow.com/questions/13951331/reuse-linq-result-by-adding-it-in-a-datatable-or-dataset? thank you – Imir Hoxha Dec 19 '12 at 11:26

Join/merge two excel files in asp.net and display them in gridview

1 Answers1

Linked