ADO.NET reads the EXCEL implementation code of of c
- 2020-05-19 04:31:35
- OfStack
// connection string
// Concatenation 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;
// The query
string sql = "SELECT * FROM [Sheet1$]";
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr);
da.Fill(ds); // fill DataSet
// Here to DataSet Manipulate the data in
// Output, bind data
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
Simple, right? ! 1 cut is just like database 1, only need to pay attention to:
1. The data provider USES Jet and needs to specify the Extended Properties keyword to set Excel specific properties. Different versions of Excel correspond to different property values: the valid Excel version 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 the Microsoft Excel 4.0 workbook, use Excel 4.0.
For the Microsoft Excel 3.0 workbook, use Excel 3.0.
ref:
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp
2. The data source path USES the physical absolute path (same as Access)
3. How do I reference a table name?
A valid reference to a table (or scope) in an Excel workbook.
To refer to the scope of a fully used worksheet, specify the worksheet name followed by a dollar sign. Such as:
select * from [Sheet1$]
To refer to a specific address range on a worksheet, specify the worksheet name followed by a dollar sign and that range. Such as:
select * from [Sheet1$A1:B10]
To refer to a specified range, use the name of the range. Such as:
select * from [MyNamedRange]
ref:
http://msdn.microsoft.com/library/chs/default.asp?url=/library/CHS/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodataset.asp
Description:
You can refer to three objects in the Excel workbook:
The & # 8226; Entire worksheet: [Sheet1$], Sheet1 is the name of the worksheet
The & # 8226; Named cell regions on the worksheet: [MyNamedRange] (you do not need to specify the worksheet because the entire xls named region can only be 1)
XLS naming method: select the cell scope "insert" name "definition
The & # 8226; Unnamed cell area on worksheet: [Sheet1$A1:B10]
(among the various objects provided by relational databases (tables, views, stored procedures, and so on), the Excel data source provides only the equivalent objects of the table, which consist of the worksheets specified in the workbook and the named regions defined. Named areas are treated as "tables" and worksheets as "system tables")
Note:
The & # 8226; Must use [] (square brackets), otherwise:
Syntax error in FROM clause
The & # 8226; Must follow $(dollar sign), otherwise quote:
The Microsoft Jet database engine could not find object 'Sheet2 '. Make sure the object exists and write its name and path correctly.
The & # 8226; If the worksheet name is incorrect, or does not exist, it will report:
'Sheet2$' is not a valid name. Make sure it does not contain invalid characters or punctuation and the name is not too long.
The & # 8226; How can ADO be used in Visual Basic or VBA to process Excel data that can be used
~ and '(wavy lines and single quotation marks) instead of [], use ADO. NET test failed, report:
Syntax error in FROM clause
The & # 8226; When a reference is made to the work indication name ([Sheet1$]), the data provider thinks that the data table starts with the nonempty cell at the top left of the specified worksheet. For example, if the worksheet starts at row 3, column C, and rows 1 and 2 are all empty before row 3, column C, then only the data starting at row 3, column C will be displayed. Ends with the largest range of non-empty cells in the final table;
The & # 8226; Therefore, if you want to read the range accurately, you should use the named region [NamedRange], or specify the address: [Sheet1$A1:C10]
4. How do I reference a column name?
The & # 8226; According to the default connection string, the data provider will take the first line in the valid region as the column name. If a cell in this line is empty, it will be represented by F1 and F2.
The & # 8226; If you want line 1 to be displayed as data, rather than as a column name, you can specify in the Extended Properties property of the connection string: HDR=NO
The default value is: HDR=NO 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 you need to use double quotation marks (here the backslash is an escape from C#)
ref:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.chs/WD_ADONET/html/745c5f95-2f02-4674-b378-6d51a7ec2490.htm
Link Excel (note: in my own MSDN, it is wrong to use two double quotation marks in its example and fail the test.
Note that the double quotation marks required for Extended Properties must also be double quotation marks.
)
In this case, all the column names begin with F, followed by the index, starting with F1, F2, F3...
5. Why is the valid cell data not displayed?
The possible reason for this is that in the default connection, the data provider extrapolates the data types of subsequent cells based on the previous cells.
You can specify IMEX=1 by Extended Properties
"IMEX = 1;" The notification driver always reads the "miscible" data column as text
ref: 4
PS: when asked about the question "baidu", some netizens said, "it is a case plan to put quotation marks on each unit, but the workload is huge, and it is not odd work. I am glad that I have found a" cure for the root cause ".
more ref:
How do I use ADO in Visual Basic or VBA to process Excel data
http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599
Should be
Applications often need to interact with Excel for data. ADO.NET is based on ADO.NET
Basic methods and techniques for reading Excel. Now I'm going to show you how to dynamically read Excel data, which means you don't know what the structure of the Excel file is, or you can't predict it
Test, for example, how many sheet there are in a.xls file, and each sheet may not have the same structure, etc.
In fact, we can get the "architecture information" of Excel to be dynamic
Construct the query statement. The term "schema information" has the same meaning as "database schema information" (also known as "metadata") in the database domain, which usually includes the database or passable for the entire database
Directories obtained from data sources, tables and views in the database, as well as existing constraints; For tables in a database, schema information includes primary keys, columns, and auto-numbered fields.
Mentioned above
Among the various objects provided by relational databases (tables, views, stored procedures, and so on), the Excel data source provides only objects equivalent to tables, which consist of the worksheets specified in the workbook and the named regions defined. Named areas are treated as "tables" and worksheets as "system tables")
Here we treat Excel as a "database" as well, and then use the OleDbConnection.GetOleDbSchemaTable method
To obtain the required schema information, the schema information obtained by this method is compatible with ANSI SQl-92:
note
For those unfamiliar with the OLE DB architecture rowset, they are essentially ANSI SQL-92
A standardized schema for defined database constructs. Each schema rowset has a group of columns (called.NET) that provide definition metadata for a specified construct
"Restricted columns" in the document). This way, if you request schema information (for example, schema information for a column or schema information for a collation), you know exactly what type of data is available. If you want to know more about the letter
For information, please visit Appendix B:Schema Rowsets.
ref: http: / / www. microsoft. com/china/msdn/library office/office/odatanet2 mspx? mfr = true
The following is the program fragment that reads the "table" definition metadata in the Excel file and displays it:
// read Excel Data, fill DataSet
// Concatenation 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 the extension property as
Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002) And the first 1 Rows are 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();
// Gets the table definition metadata for the data source
//tblSchema = conn.GetSchema("Tables");
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
GridView1.DataSource = tblSchema;
GridView1.DataBind();
// Close the connection
conn.Close();
GetOleDbSchemaTable A detailed description of the method can be found at:
http://msdn2.microsoft.com/zh-CN/library/system.data.oledb.oledbconnection.getoledbschematable.aspx
Then there is 1 Section USES "architectural information" to dynamically read Excel The program fragment of the internally defined form or named region:
// read Excel Data, fill DataSet
// Concatenation 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 the extension property as
Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002) And the first 1 Rows are 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();
// Gets the table definition metadata for the data source
//tblSchema = conn.GetSchema("Tables");
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, 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 the name of the table
}
// Initialize adapter
da = new OleDbDataAdapter();
// Prepare the data, import it 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();
}
// For the import DataSet Each of the sheet For processing
// This is just for display
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
GridView2.DataSource = ds.Tables[1];
GridView2.DataBind();
Instead of "hard-coding" the SELEC statement, we can dynamically construct the "table name" of the FROM sentence as needed.
Can not only get the indication, but also the field name, field type and other information in each table:
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, null });
In ADO nET
1. When x only OleDb provided GetOleDbSchemaTable
Method, while SqlClient or OrcaleClient have no corresponding method, because the corresponding database already provides similar functionality for stored procedures or system tables to be accessed by applications, such as pairs
In Sql Server:
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
In ADO. NET 2.0 each xxxConnenction implementation in the base class System Data. Common. DbConnection GetSchemal method
To get schema information for the data source.
http://msdn2.microsoft.com/zh-cn/library/system.data.common.dbconnection.getschema.aspx
//---------------------------------------------------------------------------
/ / IMEX is � � method
string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +";Extended Properties='Excel 8.0;IMEX=1;'"; //HDR=NO;IMEX=1
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;
}
}
}