Using Oledb+sqlclient to implement Excel bulk import data

Here are some of the classes you write yourself

        /// <summary>        ///get the Excel object/// </summary>        /// <param name= "strconn" >OLE DB connection string</param>        /// <param name= "SQL" >SQL statements</param>        /// <returns></returns>         Public StaticDataTable getexecutedatatable (stringStrconn,stringSQL) {DataTable dt=NewDataTable (); using(OleDbConnection conn =NewOleDbConnection (strconn)) {                using(OleDbDataAdapter ODA =NewOleDbDataAdapter (SQL, conn)) {ODA.                    Fill (DT); returnDT; }            }        }        /// <summary>        ///Import Data/// </summary>        /// <param name= "SQL" >SQL statements</param>        /// <param name= "MyDelegate" >to perform the insertion method</param>        /// <returns>returns the number of rows affected</returns>         Public Static intIntroduction (stringSQL, Func<sqlconnection,int>mydelegate) {            intRowNumber =0;//number of rows affected            using(SqlConnection conn =NewSqlConnection (CONNSTR)) {Conn.                Open (); RowNumber=MyDelegate (conn); }            returnRowNumber; }        /// <summary>        ///Generate INSERT Statement/// </summary>        /// <param name= "table" ></param>        /// <param name= "column" ></param>        /// <returns></returns>         Public Static stringCreateinsertsql (stringTableparams string[] column) {StringBuilder SB=NewStringBuilder ();//Stitching FieldsStringBuilder SB1 =NewStringBuilder ();//Stitching parameter Fields            foreach(stringIteminchcolumn) {sb. Append (string. Format ("{0},", item)); SB1. Append (string. Format ("@{0},", item)); }            return string. Format ("INSERT into {0} ({1}) VALUES ({2})", table, sb. ToString (). TrimEnd (','), SB1. ToString (). TrimEnd (',')); }

stringExcelconn =string. Format ("Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}; Extended Properties=excel 8.0;", Excelpath); stringExcelsql =string. Format ("SELECT [dot number],[dot name],[Dispatch Unit],[Area],[change information] from [table$]"); stringsql = Caihuashun_sqlhelper.createinsertsql ("[caihuashun_units_t]","WDBH","WDMC","PGDW","ssqy","Biangeng"); DataTable Exceldt=caihuashun_sqlhelper.getexecutedatatable (Excelconn, excelsql); Func<sqlconnection,int> mydelegate = (conn) = =            {                inti =0; foreach(caihuashun_units_t Iteminchdttolist (Exceldt)) {                    using(SqlCommand cmd =NewSqlCommand (SQL, conn)) {sqlparameter[] SP= {                                                NewSqlParameter ("@wdbh", ITEM.WDBH),NewSqlParameter ("@wdmc", ITEM.WDMC),NewSqlParameter ("@pgdw", ITEM.PGDW),NewSqlParameter ("@ssqy", item.ssqy),NewSqlParameter ("@biangeng", Item.biangeng)}; Cmd.                        Parameters.addrange (SP); I+=cmd.                    ExecuteNonQuery (); }                }                returni;            }; returnCaihuashun_sqlhelper.introduction (SQL, mydelegate);

Try{OpenFileDialog Open=NewOpenFileDialog (); Open. Filter="execl Files (*.xls) |*.xls";//types of open filesOpen. Title ="Select the files to import"; Open. FilterIndex=0;//file type default firstOpen. Restoredirectory =true;//Last selected directory                if(Open. ShowDialog () = =DialogResult.OK) {function. Mboxsuccess (string. Format ("successfully imported {0} data!", BLL. Introduction (open.                FileName)); }            }            Catch(Exception ex) {function. Mboxerror (ex.            Message); }