C # ---- Data Engine Method for reading data from Excel

To read data from an Excel table, you can connect to the Excel Data Engine.

The Code is as follows:

/// <Summary> /// convert EXCEL to able /// </Summary> /// <Param name = "FILENAME"> Excel file name </param> // /<Param name = "sheet"> Excel form name </param> // <returns> </returns> private datatable exceltodatatable (string filename, string sheet) {datatable dt = new datatable (); // checks whether the object exists if (! File. exists (filename) {devexpress. xtraeditors. xtramessagebox. Show (filename + "file don't exist! ");} Else {// get string extension = path by file extension name. getextension (filename); // Excel Data Engine connection string strconn = ""; if (extension = ". xls ") {strconn = @" provider = Microsoft. jet. oledb.4.0; Data Source = "+ filename +"; extended properties = Excel 8.0; ";} else if (extension = ". XLSX ") {strconn = @" provider = Microsoft. ace. oledb.12.0; Data Source = "+ filename +"; extended properties = \ "Excel 12.0 XML; HDR = NO \"";} Arraylist sheetlist = new arraylist (); // obtain the form List sheetlist = exceltables (filename, strconn); // if the single name of the data table does not exist, take the first data form in Excel if (sheetlist. indexof (sheet) <0) {sheet = sheetlist [0]. tostring (). trim ();} // create an Excel Data Engine to connect to the system. data. oledb. oledbconnection dbconn = new system. data. oledb. oledbconnection (strconn); // query table command system. data. oledb. oledbcommand cmd = new system. data. oledb. oledbcommand ("select * from ["+ Sheet +" $] ", dbconn); system. data. oledb. oledbdataadapter adapter = new system. data. oledb. oledbdataadapter (CMD); try {If (dbconn. state = connectionstate. closed) {dbconn. open ();} adapter. fill (DT);} catch (exception ex) {Throw ex;} finally {If (dbconn. state = connectionstate. open) {dbconn. close () ;}}return DT ;}/// <summary> // obtain the Excel form List /// </Summary> /// <Param name = "filena Me "> </param> /// <returns> </returns> private arraylist exceltables (string filename, string strconn) {datatable dt = new datatable (); arraylist sheetlist = new arraylist (); // determines whether the file name exists and whether the connection string is correct if (file. exists (filename )&&! String. isnullorempty (strconn) {using (system. data. oledb. oledbconnection conn = new system. data. oledb. oledbconnection (strconn) {try {Conn. open (); // return the Architecture Information of the Excel Data Engine dt = Conn. getoledbschematable (system. data. oledb. oledbschemaguid. tables, new object [] {null, "table"}) ;}catch (exception ex) {Throw ex ;} int sheetcount = DT. rows. count; // obtain the Excel form list for (INT I = 0; I <sheetcount; I ++) {string tablename = DT. rows [I] [2]. tostring (). trim (). trimend ('$'); If (sheetlist. indexof (tablename) <0) {sheetlist. add (tablename) ;}}} return sheetlist ;}

View code