Excel电子表格导入数据库

private  DataSet xsldata( string  filepath)

       {

            string  strCon  =   " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "   +  filepath  +   " ;Extended Properties='Excel 8.0;IMEX=1' " ;

           System.Data.OleDb.OleDbConnection Conn  =   new  System.Data.OleDb.OleDbConnection(strCon);

            string  strCom  =   " SELECT * FROM [Sheet1$] " ;

           Conn.Open();

           System.Data.OleDb.OleDbDataAdapter myCommand  =   new  System.Data.OleDb.OleDbDataAdapter(strCom, Conn);

           DataSet ds  =   new  DataSet();
           
           myCommand.Fill(ds,  " [Sheet1$] " );
           dataGridView1.DataSource  =  ds.Tables[ 0 ];
           Conn.Close();

            return  ds;

       }

 

 

 private void button1_Click(object sender, EventArgs e)
        {
            if(txtpath.Text=="")
            {
               MessageBox.Show("请选择要导入的Excel文档！","系统提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                   
                    return;
               
            }
            string filepath=txtpath.Text;
            SqlConnection conn =new SqlConnection(strcon);//链接数据库

            conn.Open();

            try

            {

              

               DataSet ds = new DataSet();

// 取得数据集

//调用上面的函数

               ds  =  xsldata(filepath);
                // dataGridView2.DataSource = ds.Tables[0];
                int  errorcount  =   0 ; // 记录错误信息条数

                int  insertcount  =   0 ; // 记录插入成功条数

                int  updatecount  =   0 ; // 记录更新信息条数

                 for  ( int  i  =   0 ; i  <  ds.Tables[ 0 ].Rows.Count; i ++ )

               {

                    int   cardtypeid  =  Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 0 ].ToString());

                    string  cardnum  =  ds.Tables[ 0 ].Rows[i][ 1 ].ToString();

                    string  cardpwd  =  ds.Tables[ 0 ].Rows[i][ 2 ].ToString();
                    int  officeid  =  Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 3 ].ToString());
                   DateTime  stdt  =  Convert.ToDateTime(ds.Tables[ 0 ].Rows[i][ 4 ].ToString());
                   
                    string  cardstate =  ds.Tables[ 0 ].Rows[i][ 5 ].ToString();
                    int  userid = Convert.ToInt32(ds.Tables[ 0 ].Rows[i][ 6 ].ToString());
                  
                    if  (cardtypeid  !=   0   &&  cardnum  !=   ""   &&  cardpwd  !=   ""   &&  userid  !=   0  )

                   {

                       SqlCommand selectcmd  =   new  SqlCommand( " select count(*) from cardsinfo where CardNum=' "   +  cardnum  +   " 'and CardPwd=' "   +  cardpwd  +   " ' and CardState='1' and TypeID= " + cardtypeid, conn);

                        int  count  =  Convert.ToInt32(selectcmd.ExecuteScalar());

                        if  (count  >   0 )

                       {
                           updatecount ++ ;
                          
                       }
                        else
                       {

                           SqlCommand insertcmd =   new  SqlCommand( " insert into cardsinfo(TypeID,CardNum,CardPwd,OutByOffID,CardTime,CardState,CardByUser) values( "   +  cardtypeid  +   " ,' "   +  

                                                                 cardnum  +   " ',' "   +  cardpwd  +   " ', "   +  officeid  +   " ,' "   +  stdt +   " ','1', " + userid + " ) " , conn);

                           insertcmd.ExecuteNonQuery();

                           insertcount ++ ;

                       }

                      

                   }
                    else

                   {

                        // MessageBox.Show("电子表格信息有错！");
                       errorcount ++ ;
                       
;  

                    }
                 

               }

               MessageBox.Show( insertcount  +   " 条数据导入成功！ "   +  updatecount  +   " 条数据重复！ "   +  errorcount  +   " 条数据部分信息为空没有导入！ " );

           }

            catch  (Exception ex)

           {

              MessageBox.Show(ex.Message);

           }

            finally

           {

               conn.Close();

           } 
        }