加入/在asp.net中合并两个Excel文件，并在GridView中显示出来

问题描述

我想在asp.net中合并两个Excel文件，并在GridView控件作为一个表显示它们。下面的code为只显示一个表。谁能告诉我什么是与下面的code中的问题？如果你有更好的想法，请让我知道。

 保护无效MergTables（）
        {
            字符串CONNSTRING = ConfigurationManager.ConnectionStrings [hwTypes] .ConnectionString;
            OleDbConnection的DBConnection进行=新的OleDbConnection（CONNSTRING）;
            DBConnection.Open（）;
            OleDbCommand的的DBCommand =新的OleDbCommand（SELECT * FROM [工作表Sheet1 $]，DBConnection进行）;
            OleDbDataAdapter的DA =新OleDbDataAdapter的（的DBCommand）;
            DataSet的DS =新的DataSet（股票）;
            da.Fill（DSHWTypes）;
            DBConnection.Close（）;
            字符串_stockConn = ConfigurationManager.ConnectionStrings [stockConn] .ConnectionString;
            DBConnection的=新的OleDbConnection（_stockConn）;
            DBConnection.Open（）;
            的DBCommand =新的OleDbCommand（SELECT * FROM [Stock_voorlopig $]，DBConnection进行）;
            DA =新OleDbDataAdapter的（的DBCommand）;
            da.Fill（DS，股票）;            DBConnection.Close（）;
            的for（int i = 0; I＆LT; ds.Tables [HWTypes] Rows.Count;我++）
            {
                ds.Tables [HWTypes]行[I] [产品ID] = ds.Tables [股票]行[I] [型号封装]。;
            }            GridView1.DataSource = ds.Tables [股票];
            GridView1.DataBind（）;
        }
 

解决方案

的问题是，您只使用一个单一的数据表在的GridView 键，你还没有加入这两个

下面是它使用的方法 LINQ到数据集来加入这两个表，并创建一个匿名类型作为数据源的 GridView控件。

 的DataSet DS =新的DataSet（股票）;
使用（VAR的DbConnection =新的OleDbConnection（CONNSTRING））
使用（VAR的DbCommand =新的OleDbCommand（SELECT * FROM [工作表Sheet1 $]的DbConnection））
使用（VAR DA =新OleDbDataAdapter的（的DbCommand））
{
    da.Fill（DSHWTypes）;
}使用（VAR的DbConnection =新的OleDbConnection（stockConn））
使用（VAR的DbCommand =新的OleDbCommand（SELECT * FROM [Stock_voorlopig $]的DbConnection））
使用（VAR DA =新OleDbDataAdapter的（的DbCommand））
{
    da.Fill（DS，股票）;
}VAR在ds.Tables [HWTypes] =加盟从舍入类型。AsEnumerable（）
             在ds.Tables [股票]加入rStock。AsEnumerable（）
             在rType.Field＆LT;串GT（产品ID）等于rStock.Field＆LT;串GT（型号封装）
             新选择
             {
                 的ProductID = rType.Field＆LT;串GT（产品ID）
                 //添加您需要这里的其他列
             };
GridView1.DataSource =加入;
GridView1.DataBind（）;
 

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();
        }

解决方案

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();

这篇关于加入/在asp.net中合并两个Excel文件，并在GridView中显示出来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！