首页 > 代码库 > EXCEL数据导入dataset

EXCEL数据导入dataset

一、开工必备

    1、micorosoft office2007

    2、VS2010、Oracle 11

二、界面

三、内部代码

   (1)获取数据库连接,定义全局变量           

         private static string connString = System.Configuration.ConfigurationSettings.AppSettings[ "connStr" ];
         DataSet dTable;

   (2)选择Excel文件导入dataset  

          if(openFileDialog1.ShowDialog()==DialogResult.OK)
             {
                 ctlPath.Text = openFileDialog1.FileName;
                 ExceltoDataSet(ctlPath.Text);
             }   

   (3)加载Excel文件数据

             public DataSet ExceltoDataSet(string path)
         {
             MessageBox.Show( "正在获取数据....请稍候" );
             //
             string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+path+";Extended Properties=‘Excel 12.0;HRD=Yes;IMEX=1‘;" ;
             OleDbConnection conn = new OleDbConnection(strConn);
             conn.Open();
            System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
            
            string tableName = schemaTable.Rows[0][2].ToString().Trim();
            
             string strExcel =  " " ;
             OleDbDataAdapter myCommand = null;
             DataSet ds = null;
             strExcel = "Select * from [" + tableName + "]";
             myCommand = new OleDbDataAdapter(strExcel,strConn);
             ds = new DataSet();
             try
             {
                 myCommand.Fill(ds, tableName);
             }
             catch(Exception ex)
             {
                 MessageBox.Show(ex.Message);
             }
                 dTable = ds;
           
             if (ds != null)
             {
                 button2.Visible = true;
               
         
             }
             return ds;
     
        
         }

   (4)显示导入数据 

          DataTable dt = dTable.Tables[0];        

      try {

                 for (int i = 0; i > 0; i++)  

                {  

                   dt.Rows.Remove(dt.Rows[i]);  

                }

                 dataGridView1.DataSource = dt;      

        } catch (Exception ex)  

            {  

                 throw ex;    

          }

   (5)将数据导入数据库

  1.           DataTable dt = dTable.Tables[0];
  2.             
  3.              for (int i = 1; i < dt.Rows.Count; i++)
  4.              {
  5.                
  6.                  string sql1 = string.Format( "insert  /*+nologging*/ into TPB_CARCONFIG(LINERNUM,RESID,CARTYPEID,RESFNAME,RESURL,BRAND" +
  7.                    " values(‘" + dTable.Tables[0].Rows[i][0] + "‘,‘101‘,‘" + dTable.Tables[0].Rows[i][0] + "‘,‘汽车之家‘,‘www.autohome.com.cn/‘, " +
  8.                      " ‘" +dTable.Tables[0].Rows[i][181]+ "‘,‘" +dTable.Tables[0].Rows[i][182]+ "‘,‘" +dTable.Tables[0].Rows[i][183]+ "‘,‘" +dTable.Tables[0].Rows[i][1]+ "‘)" );
  9.                     OracleConnection oconn = new OracleConnection(connString);   
  10.                           oconn.Open();   
  11.                 OracleCommand cmd;  
  12.                 try {
  13.                      cmd = new OracleCommand(sql1.ToString(), oconn);
  14.                       int a = Convert.ToInt32(cmd.ExecuteNonQuery());
  15.                      if (a > 0)   {

 

  1.                      }     
  2.                        } catch (Exception ex)  {     
  3.   注:这里收集重复信息的编号,保存到txt文本
  4.                  string path =  "c:错误信息.txt" ;         
  5.              FileStream fs = new FileStream(path,FileMode.Append);          
  6.             StreamWriter sw = new StreamWriter(fs);                              
  7.          sw.WriteLine( "Excel编号"  + dTable.Tables[0].Rows[i][0]+ "数据已与数据表中重复" );       
  8.                sw.Close();             
  9.          fs.Close();                  
  10.     fs.Dispose();                
  11.       continue;             
  12.      }           
  13.                    oconn.Close();      
  14.         }

    注:该程序我没有使用线程,所以在导入大量数据的时候,winform窗体假死,但是程序依然在奋斗,请耐心等待复活。

四、效果图
      1、导入前

    2、导入后→点击显示数据

五、源码下载http://pan.baidu.com/s/1sj4U2i5

 

 

 

 

   

EXCEL数据导入dataset