Winfrom importing Excel tables to an Access database (from the Small lottery system)

There are a lot of this method on the Internet, I just for my own system to achieve

 //Import Excel Table        Private voidImporttsmenu_click (Objectsender, EventArgs e) {OpenFileDialog OPENFD=NewOpenFileDialog (); Openfd.filter="Excel file |*.xls"; if(Openfd.showdialog ( This. Owner) = =DialogResult.OK) {stringstrFileName =Openfd.filename; if(!file.exists (strFileName)) {MessageBox.Show ("file does not exist, please re-select the file! "); return; }                //string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + strFileName + "; Extended properties= ' Excel 8.0; Hdr=yes;imex=1, ' ";                stringstrconn ="Provider=microsoft.ace.oledb.12.0;data source="+ strFileName +"; Extended properties= ' Excel 12.0; hdr=yes;imex=1; '"; //about OLE DB connecting Excel's Extended properties (extended attributes) Hdr=yes; imex=2//Hdr=yes, this means that the first row is the title, not as data use, if used Hdr=no, then the first row is not the title, as data to use. The system default is Yes//the parameter Excel 8.0 uses extended Properties=excel with Excel 8.0,2007 or 2010 for Excel 97 to 2003 versions 12.0//There are three modes of IMEX://when imex=0 is "Export mode", the Excel file opened by this mode can only be used for "write" purposes. //when Imex=1 is "Import Mode", the Excel file opened in this mode can only be used for "read" purposes. //when imex=2 is "connected mode", the Excel file opened in this mode can support both "read" and "write" purposes. OleDbDataAdapter Oldada =NewOleDbDataAdapter ("select * FROM [sheet1$]", strconn); DataSet DS=NewDataSet ();                    Oldada.fill (DS); //C # import execl cannot find installable ISAM, if Office2003 version//string strconn = "Provider=Microsoft.Jet.OLEDB.4.0; Data source= "+ Excelfile +"; Extended properties= ' Excel 8.0; Hdr=yes;                Imex=1 ' "; //if it is more than 2007//string Strcon = "Provider=microsoft.ace.oledb.12.0;data source=" + FilePath + "; Extended properties= ' Excel 12.0; Hdr=yes;imex=1 ' ";DataTable dt = ds. tables[0];                InsertData (DT); //This . Ftlucky_load (sender, E);GetData (); }        }

View Code

There is a for loop to insert into the database may not be efficient, there should be a batch import, as if SQL is used SqlBulkCopy, as for access, because of the time is tight, and the requirements are not high, temporarily with for instead of

 #region3. Inserting Excel data into the database/// <summary>        ///inserting data from a DataTable into a database/// </summary>        /// <param name= "DT" >Excel Data</param>        Private voidinsertdata (DataTable dt) {Try{Oledcon=Sqlhelper.oledcon ();                Oledcon.open (); stringStrinsert ="";  for(inti =0; i < dt. Rows.Count; i++) {Strinsert="insert into information ([Username],[phone]) VALUES ('"+ dt. rows[i][0] +"', '"+ dt. rows[i][1] +"')"; Oledcmd=NewOleDbCommand (Strinsert, Oledcon);                Oledcmd.executenonquery (); } MessageBox.Show ("Import Successful! "); }            Catch(Exception ex) {MessageBox.Show ("Import Error, Reason:"+Ex.            Message.tostring ()); }            finally{oledcon.dispose ();            Oledcon.close (); }        }         #endregion

View Code

Winfrom importing Excel tables to an Access database (from the Small lottery system)