C # reads the contents of an Excel file (using a dataset)

C # reads the contents of an Excel file, connects through OLE DB, the key is the path of the connection,
such as: string strconn = "provider=microsoft.ace.oledb.12.0;data source=" + filePath + "; Extended Properties=excel 12.0;";
The path to the connection involves 3 aspects:

1. Provider: OLE DB connection is used, but this technology will be updated from time to date, before using the latest version of the query;

2. Data Source: Is the path of the Excel file;

3. Extended Properties: Specify the version of Excel, as above, query the latest version before use (to be consistent with the read Excel file);

Read different sheet in the same way as SQL:
String strexcel = "SELECT * FROM [Sheet3$]";

1  PublicDataSet ReadFile (stringFilePath)2         {3             stringstrconn ="Provider=microsoft.ace.oledb.12.0;data source="+ FilePath +"; Extended Properties=excel 12.0;"; 4OleDbConnection conn =NewOleDbConnection (strconn);5 Conn. Open ();6             stringStrexcel ="select * FROM [sheet3$]";7OleDbDataAdapter da =NewOleDbDataAdapter (Strexcel, strconn);8DataSet ds =NewDataSet ();9             TryTen             { One da. Fill (DS); A             } -             Catch(Exception ex) -             { the                 Throw NewException ("failed to read Excel:"+Ex. Message); -             } -             returnds; -}

View Code

Exception handling:

1. If the External table is not in the expected format appears.

Most of this is due to the fact that OLE DB or extended properties in the path do not correspond to the current version of the Excel file:

Old: String strconn = "provider=microsoft.jet.oledb.4.0;data source=" + FilePath + "; Extended properties= ' Excel 8.0; Hdr=no;imex=1 ';";

Modified: String strconn = "provider=microsoft.ace.oledb.12.0;data source=" + FilePath + "; Extended Properties=excel 12.0;";

2. If the ' xxxxxxxxx ' provider is not registered on the local machine

That's because Platform target is misconfigured, OLE DB seems to support only x86, so you just need to go to project properties, Bulid, Platform target, x86.

C # reads the contents of an Excel file (using a dataset)