. NET open an Excel document and convert to a DataTable

        /// <summary>        ///open an Excel document and convert to a DataTable/// </summary>        /// <returns></returns>         Public StaticDataTable Excelworksheettodatatable () {DataTable dtexecl=NewDataTable (); DoubleVer =Getexcelver (); if(Ver <=0) {MessageBox.Show ("The computer does not has Excel installed.","prompt"); return NULL; }            stringExcelfile =""; OpenFileDialog OFD=NewOpenFileDialog (); if(OFD. ShowDialog () = =DialogResult.OK) {OleDbConnection conn=NULL; Excelfile=OFD.                FileName; if(Excelfile.length >0)                {                    Try                    {                        stringstrconn ="provider={0};"+"Data source="+ Excelfile +";"+"Extended properties= ' Excel {1}; Hdr=yes; Imex=1 '"; if((NewSystem.IO.FileInfo (Excelfile). Extension). ToLower () = =". xlsx") {strconn=string. Format (strconn,"microsoft.ace.oledb.12.0","12.0"); }                        Else{strconn=string. Format (strconn,"microsoft.jet.oledb.4.0","8.0"); } Conn=NewOleDbConnection (strconn); DataSet DS=NewDataSet (); Conn.                        Open (); OleDbDataAdapter mycommand=NULL; MyCommand=NewOleDbDataAdapter ("select * FROM [sheet1$]", strconn); Mycommand.fill (DS,"Sheet1"); if(ds! =NULL&& ds. Tables.count >0) {dtexecl= ds. tables[0]; if(!dtexecl.columns.contains ("Intauid") {DTEXECL.COLUMNS.ADD ("Intauid",typeof(int)); }                            inti =1; foreach(DataRow Drinchdtexecl.rows) {dr["Intauid"] =i; I+=1; }                        }                    }                    Catch(Exception e) {MessageBox.Show (e.tostring ()); return NULL; }                    finally{Conn.                        Close (); Conn.                    Dispose (); }                }            }            returndtexecl; }

View Code

Code by Blog Park-Cao Yongxi

        /// <summary>        ///Gets the current computer installation Excel version number/// </summary>        /// <returns></returns>        Private Static DoubleGetexcelver () {Type Objexceltype= Type.gettypefromprogid ("Excel.Application"); if(Objexceltype = =NULL)            {                return 0; }            Objectobjapp =Activator.CreateInstance (Objexceltype); if(objapp = =NULL)            {                return 0; }            ObjectObjver = Objapp.gettype (). InvokeMember ("Version", BindingFlags.GetProperty,NULL, Objapp,NULL); DoubleVer =convert.todouble (objver.tostring ()); returnVer; }

View Code