"C # Operations Office"--oledbdataadapter and OleDbDataReader way to read Excel and convert to DataTable

OleDbDataAdapter Way:

        ///<summary>///Read the Excel table into the datatable---OleDbDataAdapter///</summary>
/// <param name= "strSQL" ></param>
<param name= "Excelpath" >excel path </param> ///<returns>DataTable</returns>PublicStatic DataTable Readexcel (String Excelpath,String strSQL) {OleDbConnection objconn =Null; DataTable dt =NewDataTable ();Try{String excelconn ="Provider=Microsoft.Jet.OLEDB.4.0;Data source="+ Excelpath +"; Excel 8.0; Hdr=no;imex=1";//hdr=yes is the title, the first row of No is the title, the Imex=1 is the import mode, and the Excel file opened by this mode can only be used for "write" purposes. There is also an important role: forcing the conversion of mixed data into text to read out the digital content of Excel. objconn = new OleDbConnection (excelconn); objConn.Open ();  OleDbCommand objcmd = new  OleDbCommand (strSQL, objconn); OleDbDataAdapter ADR = new OleDbDataAdapter (); ADR. SelectCommand = objCMD; ADR. Fill (DT); Objconn.close (); return DT;} catch (Exception ex) {MessageBox.Show (ex). Message); return null  

OleDbDataReader Way:

        ///<summary>///Read the Excel table into the datatable---OleDbDataReader///</summary>
<param name= "strSQL" ></param>
<param name= "Excelpath" >excel path </param>///<returns>DataTable</returns>PublicStatic DataTable Readexcel (String Excelpath,String strSQL) {String excelconn ="Provider=Microsoft.Jet.OLEDB.4.0;Data source="+ Excelpath +"; Excel 8.0; Hdr=no;imex=1";//Hdr=yes the first line is the title, no first row is not the title; Imex=1 represents the Import mode, the Excel file opened by this mode can only be used for "write" purposes, but also has an important role: forcing the conversion of mixed data into text, can read the digital content of Excel.DataTable dt =NewDataTable ();Try{using (OleDbConnection connection = new OleDbConnection (excelconn) {oledbcommand command =  New OleDbCommand (strSQL, connection); connection. Open (); OleDbDataReader reader; Reader = command. ExecuteReader (); Dt. Load (reader); // convert reader directly to Datatablereturn DT;} catch (Exception ex) {MessageBox.Show (ex). Message); return null  

From the above two examples is not difficult to see, in fact, Excel is also equivalent to a database, read Excel SQL statements such as: string strSQL = "SELECT * FROM [SHEET1$A10:L24]";// Read the contents of the Sheet1 worksheet A10 to the L24 area

Similarly, to read a database such as Oracle, just change the database engine to the appropriate type.