Asp.net C # merge cell content in Excel

Asp tutorial. net c # use excel to merge cell content
Read excel Data and fill in dataset
// Connection string
String xlspath = server. mappath ("~ /Www. bKjia. c0m/somefile.xls ");
String connstr = "provider = microsoft. jet. oledb.4.0;" +
"Extended properties =" excel 8.0; hdr = no; imex = 1 ";" + // specify the extension attribute as microsoft excel 8.0 (97) 9.0 (2000) 10.0 (2002 ), and the first row is returned as data and read as text.
"Data source =" + xlspath;
String SQL _f = "select * from [{0}]";

Oledbconnection conn = null;
Oledbdataadapter da = null;
Datatable tblschema = null;
Ilist <string> tblnames = null;

// Initialize the connection and open it
Conn = new oledbconnection (connstr );
Conn. open ();

// Obtain the table definition metadata of the Data Source
// Tblschema = conn. getschema ("tables ");
Tblschema = conn. getoledbschematable (oledbschemaguid. tables, new object [] {null, "table "});

// Gridview1.datasource = tblschema;
// Gridview1.databind ();

// Close the connection
// Conn. close ();

Tblnames = new list <string> ();
Foreach (datarow row in tblschema. rows ){
Tblnames. add (string) row ["table_name"]); // read Table Name
}

// Initialize the adapter
Da = new oledbdataadapter ();
// Prepare data and import dataset
Dataset ds = new dataset ();

Foreach (string tblname in tblnames ){
Da. selectcommand = new oledbcommand (string. format (SQL _f, tblname), conn );
Try {
Da. fill (ds, tblname );
}
Catch {
// Close the connection
If (conn. state = connectionstate. open ){
Conn. close ();
}
Throw;
}
}

// Close the connection
If (conn. state = connectionstate. open ){
Conn. close ();
}

// Process each sheet imported into the dataset
// Display only
Gridview1.datasource = ds. tables [0];
Gridview1.databind ();

Gridview2.datasource = ds. tables [1];
Gridview2.databind ();

// More codes
//.
Here we do not need to perform "Hard encoding" on the selec statement. We can dynamically construct the "table name" of the from statement as needed ".

You can also obtain the field names and Field Types in each table:
Tblschema = conn. getoledbschematable (oledbschemaguid. columns, new object [] {null, null });
In ado.net 1.x, only oledb provides the getoledbschematable method, but sqlclient or orcaleclient does not have the corresponding method, because the corresponding database tutorial has provided similar functions for access by stored procedures or system table supply programs, for example, for SQL server: select *
From northwind. information_schema.columns
Where table_name = n 'customer'

In ado.net 2.0, each xxxconnenction implements the getschemal of the base class system. data. common. dbconnection.

Private dataset binddsfromexcel (string strfiledir, string strdataname)
{
String strconn;
Strconn = "provider = microsoft. jet. oledb.4.0; data source =" + strfiledir + "; extended properties = 'excel 8.0; hdr = false; imex = 1 '";
Oledbconnection oleconn = new oledbconnection (strconn );
Oleconn. open ();
String SQL = "select * from [" + strdataname + "$]"; // if you do not know the name, use sheets [1].

Oledbdataadapter oledaexcel = new oledbdataadapter (SQL, oleconn );
Dataset oledsexcle = new dataset ();
Oledaexcel. fill (oledsexcle, strdataname );
Oleconn. close ();
Return oledsexcle;
}