Ado.net read the implementation Code of Excel ((C #)) _ Practical Tips

Source: Internet
Author: User
Tags ole ranges table definition

Connection string

Copy Code code as follows:

Connection string
String xlspath = Server.MapPath ("~/app_data/somefile.xls"); Absolute physical Path
String connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties=excel 8.0;" +
"Data source=" + Xlspath;
Query statement
String sql = "SELECT * FROM [sheet1$]";
DataSet ds = new DataSet ();
OleDbDataAdapter da = new OleDbDataAdapter (sql, CONNSTR);
Da.    Fill (DS); Fill DataSet

Manipulate the data in the dataset here

Output, binding Data
Gridview1.datasource = ds. Tables[0];
Gridview1.databind ();

Is it simple?! It's like manipulating a database, just to be aware of:
1. The data provider uses jet and needs to specify that the Extended properties keyword set Excel-specific properties, and different versions of Excel correspond to different property values: A valid version of Excel for Extended properties values.
For Microsoft Excel 8.0 (97), 9.0 (2000), and 10.0 (2002) workbooks, use Excel 8.0.

For Microsoft Excel 5.0 and 7.0 (95) workbooks, use Excel 5.0.

For Microsoft Excel 4.0 workbooks, use Excel 4.0.

For Microsoft Excel 3.0 workbooks, use Excel 3.0.

Ref
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/ Vbtskcodeexamplereadingexceldataintodataset.asp

2. Data source path uses physical absolute path (with access)

3. How do I refer to the table name?
A valid reference to a table (or range) in an Excel workbook.
To refer to the scope of a fully used worksheet, specify a sheet name followed by a dollar sign. For example:

SELECT * FROM [sheet1$]
To refer to a specific address range on a worksheet, specify the sheet name followed by the dollar sign and the range. For example:

SELECT * FROM [SHEET1$A1:B10]
To refer to a specified range, use the name of the range. For example:

SELECT * FROM [Mynamedrange]
Ref
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/ Vbtskcodeexamplereadingexceldataintodataset.asp
Description
You can reference three types of objects in an Excel workbook:
• Entire sheet: [sheet1$], Sheet1 is the name of the worksheet
• Named range of cells on the worksheet: [Mynamedrange] (you do not need to specify a worksheet because the named range in XLS can only be unique)
XLS naming method: Select range of cells insert name "definition"
• Unnamed range of cells on the worksheet: [SHEET1$A1:B10]
(in a variety of objects provided by a relational database (tables, views, stored procedures, and so on), the Excel data source provides only objects that are equivalent to tables, consisting of worksheets and defined named ranges in the specified workbook. Named ranges are treated as "tables" and worksheets are treated as "system tables"

Attention:
• must use [] (square brackets), no will be reported:
FROM clause syntax error
• Must be with $ (dollar sign), otherwise reported:
The Microsoft Jet database engine could not find the object ' sheet2′. Make sure the object exists and correctly write its name and path.
• If the worksheet name is incorrect or does not exist, it will be reported:
' sheet2$ ' is not a valid name. Make sure it does not contain invalid characters or punctuation, and that the name is not too long.
• Using ADO in Visual Basic or VBA to work with Excel data refers to the use of
~ and ' (wavy lines and single quotes) instead of [], using ADO. NET test did not succeed, reported:
FROM clause syntax error
• When referencing a work-name ([sheet1$]), the data provider considers the data table to start with a non-empty cell on the top left of the specified worksheet. For example, the worksheet starts at row 3rd, column C, row 3rd, column C, and 1th or 2 lines, only the data from the 3rd row, column C, is displayed, ending with a non-empty cell in the maximum range of the last table;
• Therefore, if you need an accurate read range, you should use a named range [NamedRange], or specify an address: [SHEET1$A1:C10]

4. How do I refer to a column name?
• According to the default connection string, the data provider takes the first row in the valid range as the column name, and if a cell in this row is empty, it is represented by F1, F2, where the ordinal number is the same as the cell, starting at 1;
• If you want the first row to be displayed as data, not as a column name, you can specify the Extended properties property of the connection string: Hdr=no
The default value is: The Hdr=no format is as follows:

String connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended properties=\" Excel 8.0; Hdr=no\ ";" +
"Data source=" + Xlspath;
Note: Excel 8.0; Hdr=no need to use double quotes (here's the backslash, which is escaping in C #)

Ref
Ms-help://ms. Vscc.v80/ms. Msdn.v80/ms. Visualstudio.v80.chs/wd_adonet/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm
In the Connect Excel section in my own MSDN, it's an example that uses two double quotes that are wrong, and the test fails, as the original article says:

Note that the double quotes required for Extended Properties must also be enclosed in double quotes.

In this case, all the column names start with F, and then with the index, starting with F1, f2,f3 ...

5. Why are valid cell data not displayed?
A possible reason for this is that, in the default connection, the data provider infers the data type of subsequent cells based on the preceding cell.
You can specify Imex=1 in Extended Properties

"Imex=1;" Notifies the driver that the "mixed" data column is always read as text
Ref: Same 4

PS: In the question of Baidu, a netizen said, each unit is added quotes, this is a plan, but the workload is so big ah, and not odd jobs, happy to find a "root remedy"

More ref:
How to use ADO in Visual Basic or VBA to work with Excel data
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599

Should
Using a program often requires data interaction with Excel, which illustrates the above based on Ado.net
Read the basic methods and techniques of Excel. Now I want to introduce how to read the Excel data dynamically, where the dynamic refers to not knowing the structure of the Excel file in advance, or the
Test, such as a. xls file has a number of sheet, and each sheet structure may be different and so on.
In fact, we can get the Excel "schema information" to dynamically
Constructs a query statement. The "schema information" here is the same as the "database schema information" in the database domain (also known as "metadata"), and for the entire database, these "meta data" typically include databases or accessible
The data sources, tables and views in the database, and the constraints that exist, and for tables in the database, schema information includes primary keys, columns, and AutoNumber fields.
In the above mentioned

In the various objects provided by the relational database (tables, views, stored procedures, and so on), the Excel data source provides only objects that are equivalent to the table, consisting of a worksheet and a defined named range in the specified workbook. Named ranges are treated as "tables" and worksheets are treated as "system tables"

Here we treat Excel as a "database" and then use the OleDbConnection.GetOleDbSchemaTable method
To obtain the required schema information, the schema information obtained by this method is compatible with the ANSI SQl-92:

Note
Meaning: For those unfamiliar with the OLE DB schema rowset, they are essentially ANSI SQL-92
The standardized schema for the defined database construction. Each schema rowset has a set of columns that provide definition metadata for the specified construct, called. NET
Restricted columns in the document. This way, if you request schema information (for example, schema information for a column or schema information for collations), you know exactly what kind of data you can get. If you want to know more about the letter
, please visit Appendix B:schema rowsets.
Ref:http://www.microsoft.com/china/msdn/library/office/office/odatanet2.mspx?mfr=true

The following is a piece of the program that reads the "table" definition metadata in an Excel file and displays it:

Copy Code code as follows:

Reading Excel data, populating a dataset
Connection string
String xlspath = Server.MapPath ("~/app_data/somefile.xls");
String connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Extended properties=\" Excel 8.0; Hdr=no;imex=1\ ";" +///Specify extended property to
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 the
conn = new OleDbConnection (CONNSTR);
Conn. Open ();

To get the table definition metadata for a data source
Tblschema = conn. GetSchema ("Tables");
Tblschema = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] {null, NULL, NULL, "TABLE"});

Gridview1.datasource = Tblschema;
Gridview1.databind ();

Close connection
Conn. Close ();
A detailed description of the GetOleDbSchemaTable method can refer to:
Http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx

Then there is a piece of program that dynamically reads a form or a named range that is defined within Excel using "schema information":

Reading Excel data, populating a dataset
Connection string
String xlspath = Server.MapPath ("~/app_data/somefile.xls");
String connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Extended properties=\" Excel 8.0; Hdr=no;imex=1\ ";" +///Specify extended property to
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 the
conn = new OleDbConnection (CONNSTR);
Conn. Open ();

To get the table definition metadata for a data source
Tblschema = conn. GetSchema ("Tables");
Tblschema = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] {null, NULL, NULL, "TABLE"});

Gridview1.datasource = Tblschema;
Gridview1.databind ();

Close connection
Conn. Close ();

Tblnames = new list<string> ();
foreach (DataRow row in tblschema.rows) {
Tblnames.add ((String) row["table_name"]); Reading table names
}

Initializing adapters
da = new OleDbDataAdapter ();
Prepare data, 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 connection
IF (Conn. state = = ConnectionState.Open) {
Conn. Close ();
}
Throw
}
}

Close connection
IF (Conn. state = = ConnectionState.Open) {
Conn. Close ();
}

Process each sheet of the imported dataset
Here only do show
Gridview1.datasource = ds. Tables[0];
Gridview1.databind ();

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

Here we do not need to "hard-code" the Selec statement, and we can construct the "table name" of the FROM clause dynamically, depending on the need.

Not only can you obtain information about the field name, field type, and so on in each table, but you can also obtain:

Tblschema = conn. GetOleDbSchemaTable (OleDbSchemaGuid.Columns, new object[] {null, NULL, NULL, NULL});

In Ado.net
At 1.x, only OLE DB provided GetOleDbSchemaTable
method, and SqlClient or orcaleclient do not have a corresponding method, because the corresponding database has provided a similar function of stored procedures or system tables for application access, such as the
to SQL Server:

SELECT *
From Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = N ' Customers '

And in Ado.net 2.0, each xxxconnenction implements the Getschemal method of the base class System.Data.Common.DbConnection.
To obtain schema information for the data source.
Http://msdn2.microsoft.com/zh-cn/library/system.data.common.dbconnection.getschema.aspx

//---------------------------------------------------------------------------
IMEX the correct way to write
String connexcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + FilePath + "; Extended properties= ' Excel 8.0;imex=1; '; Hdr=no;imex=1

Copy Code code as follows:

Using System;
Using System.Data;
Using System.Configuration;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;
Using System.Web.UI.HtmlControls;

Using System.Data.SqlClient;
Using System.Data.OleDb;

public partial class _default:system.web.ui.page
{
protected void Page_Load (object sender, EventArgs e)
{

}
protected void Button1_Click (object sender, EventArgs e)
{

String strconn = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + @ "D:/last.xls" + "; Extended Properties=excel 8.0; "; Hdr=no ";//imex=1
OleDbConnection conn = new OleDbConnection (strconn);
DataSet MySet = new DataSet ();
Try
{
Conn. Open ();


string mysql = "select * FROM [sheet1$]";//where CHS <> '

OleDbDataAdapter aper = new OleDbDataAdapter (MySQL, conn);

MySet. Tables.clear ();
Aper. Fill (MySet, "book");

Conn. Close ();

Gridview1.datasource = MySet. tables["book"];
Gridview1.databind ();
}
catch (Exception ex)
{
Conn. Close ();
This.lb_msg. Text = ex. message;
Return
Return ex. message;
}
}
}

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.