Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to read the ADO.NET Excel data file, the article is very detailed, has a certain reference value, interested friends must read it!

If we use ADO.NET to operate the database, we will be more Excel dealing with each other, which is inevitable. Now it's time to introduce how to read ADO.NET Excel data dynamically, which means that you don't know in advance what the structure of the Excel file is, or you can't predict, for example, how many sheet there are in an .xls file, and the structure of each sheet may be different, and so on. In fact, we can dynamically construct query statements by obtaining the "schema information" of Excel. The meaning of "schema information" here is the same as that of "database schema information" in the database domain (also known as "metadata"). For the whole database, these "metadata" usually include the database or the catalogs and existing constraints that can be obtained through the data sources, tables and views in the database, while for the tables in the database, the schema information includes primary keys, columns, and auto-numbered fields.

Among the various objects provided by a relational database (tables, views, stored procedures, and so on), the ADO.NET Excel data source provides only the equivalent of a table, which consists of worksheets and defined named ranges in the specified workbook. Named ranges 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. Note: for those who are not familiar with OLE DB schema rowsets, they are basically standardized schemas constructed by databases defined by ANSI SQL-92. Each schema rowset has a set of columns (called "restriction columns" in the .NET document) that provide definition metadata for the specified construct. In this way, if you request schema information (for example, schema information for a column or collation), you will know exactly what type of data you can get.

The following is a fragment of the program that reads the "table" definition metadata in the ADO.NET Excel data file and displays it:

/ / read Excel data and populate 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 broadcast Noter1\ ";" +

/ / specify that the extension attribute is Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002), and the 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 tblNames = null

/ / initialize the connection and open it

Conn = new OleDbConnection (connStr)

Conn.Open ()

/ / get 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 ()

The above is all the contents of the article "how to read ADO.NET Excel data Files". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.