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

Editor to share with you how to use ADO.NET Excel to read files, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Among the various objects provided by a relational database (tables, views, stored procedures, and so on), the 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 ADO.NET Excel reads as a "database", and then use the OleDbConnection.GetOleDbSchemaTable method to get the required schema information, which is compatible with ANSI SQl-92.

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. For more information, visit Appendix B:Schema Rowsets. The following is a fragment of the ADO.NET Excel reader that reads the "table" definition metadata in the Excel file and is displayed:

Code// reads Excel data and fills in the DataSet// connection string string xlsPath = Server.MapPath ("~ / app_data/somefile.xls"); string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\" Excel 8.0 "+ / / specifies that the extension attribute is Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002), and * rows are returned as data and read" data source= "+ xlsPath;string sql_F =" SELECT * FROM [{0}] "in text; OleDbConnection conn = null;OleDbDataAdapter da = null; DataTable tblSchema = null;IList tblNames = null; / / initialize the connection and open 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 ()

This is followed by a program snippet that uses "schema information" to dynamically read a form or named range defined within Excel:

Codexcel data, fill in the DataSet// connection string string xlsPath = Server.MapPath ("~ / app_data/somefile.xls"); string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=\" Excel 8.0 "+ / / specifies that the extension attribute is Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002), and * rows are returned as data and read" data source= "+ xlsPath;string sql_F =" SELECT * FROM [{0}] "in text; OleDbConnection conn = null;OleDbDataAdapter da = null;DataTable tblSchema = null; IList tblNames = null; / / initialize the connection and open 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 connection / / conn.Close (); tblNames = new List () Foreach (DataRow row in tblSchema.Rows) {tblNames.Add ((string) row ["TABLE_NAME"]); / / read table name} / / initialize adapter da = new OleDbDataAdapter (); / / prepare data and import DataSetDataSet 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 ();} / / A pair of sheet imported into DataSet is processed / / here only shows GridView1.DataSource = ds.Tables [0] GridView1.DataBind (); GridView2.DataSource = ds.Tables [1]; GridView2.DataBind (); / / more codes / / here we don't need to "hard code" the SELEC statement, but we can dynamically construct the "table name" of the FROM sentence as needed. The above is all the contents of the article "how to use ADO.NET Excel to read Files". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.