execl 数据导入sql 2000要领

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Sheet1$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\学习\Test.xls', [Sheet1$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Sheet1$]')

 

环境:sql server 2000 ,execl 2003 .

注意:导入时一定要关闭execl.

 

c#代码实现  Oledb 查询循环插入数据库 (对已存在的数据库表)

 

 

public void inputData()
  {
   if(checkedListBox1.CheckedItems.Count==checkedListBox2.CheckedItems.Count && checkedListBox1.CheckedItems.Count>0)
   {
                 this.IsDelete();
    this.richTextBox1.Text="";
    //yi2
    strPath=fName;
                string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + strPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";  //  连接语句注意 Extended Properties='Excel 8.0;HDR=Yes;IMEX=1; '   需加引号     

OleDbConnection myConn = new OleDbConnection ( strCon ) ;

    string strSeleCmd="select ";
    for (int i = 0; i <checkedListBox1.CheckedItems.Count; i++)
    {
     if(i==checkedListBox1.CheckedItems.Count-1)
     {
      strSeleCmd=strSeleCmd+checkedListBox1.CheckedItems[i].ToString();
     }
     else
     {
      strSeleCmd=strSeleCmd+checkedListBox1.CheckedItems[i].ToString()+",";
     }
   
    
    }
    strSeleCmd=strSeleCmd+" FROM [Sheet1$] ";
    myConn.Open ( ) ;
    OleDbDataAdapter myCommand = new OleDbDataAdapter ( strSeleCmd , myConn ) ;
    myDataSet4= new DataSet ( ) ;
    myCommand.Fill ( myDataSet4 , "[Sheet1$]" ) ;
    rowsNum=myDataSet4.Tables[0].Rows.Count;
    myConn.Close ( ) ;
  //yi1
    excepNum=0;
    string strInse="insert into "+tableName+"( ";
    for (int i = 0; i <checkedListBox2.CheckedItems.Count;  i++)
    {
     if(i==checkedListBox2.CheckedItems.Count-1)
     {
      strInse=strInse+checkedListBox2.CheckedItems[i].ToString();
     }
     else
     {
      strInse=strInse+checkedListBox2.CheckedItems[i].ToString()+",";
     }
   
    
     //   MessageBox.Show(checkedListBox2.CheckedItems[i].ToString());
    }
    strInse=strInse+")";
    string strCmd=strInse;
    //yuanlai input
       DateTime datetime1=System.DateTime.Now;
    timeStart=datetime1.ToString();
                
 

                SqlConnection con = new SqlConnection("server=" + severName + ";Database=" + datasource + ";uid=" + uidName + ";pwd=" + uidPwd + "");

               // SqlConnection con = new SqlConnection("server=.Database=htgl;uid=sa;pwd=");

    //OracleConnection con=new OracleConnection("server="+severName+";Data Source="+datasource+";uid="+uidName+";pwd="+uidPwd+"");
    con.Open();
    progressBar1.Minimum=0;
    progressBar1.Maximum=rowsNum-1;
    progressBar1.Value=0;
     
    for(int i=0;i<rowsNum;i++)
    {
                    
     strInse=strCmd+" values(";
     for (int s = 0; s < checkedListBox2.CheckedItems.Count; s++)
     {
      if(s==checkedListBox2.CheckedItems.Count-1)
      {
       strInse=strInse+"'"+myDataSet4.Tables[0].Rows[i][s].ToString()+"'";
      }
      else
      {
       strInse=strInse+"'"+myDataSet4.Tables[0].Rows[i][s].ToString()+"'"+",";
      }
     }

     strInse=strInse+")";

                    SqlCommand cmd = new SqlCommand();
     cmd.Connection=con;
     //OracleCommand cmd=new OracleCommand();
     cmd.CommandText=strInse;
     try
     {
      cmd.ExecuteNonQuery();
      progressBar1.Value=i;
      
     }
     catch
     {
      excepNum++;
      this.richTextBox1.Text+= "导入语句："+strInse+"没有成功；\n";
      progressBar1.Value=i;
      if(excepNum>100)
      {
       DialogResult result=MessageBox.Show("Excel表列与Oracle表列类型不符合的记录太多，请检查整理后再导入!","提示",MessageBoxButtons.OK);
       if(result==DialogResult.OK)
       {
        return;
       }
      }
      
     }
    }
    DateTime datetime2=System.DateTime.Now;
    timeEnd=datetime2.ToString();
    timeSum=Convert.ToString(datetime2-datetime1).Substring(0,8);
   
    this.richTextBox1.Text+="导入开始时间:"+timeStart+"；\n";
    this.richTextBox1.Text+="导入结束时间:"+timeEnd+"；\n";
    this.richTextBox1.Text+="导入用时:"+timeSum+"。\n";

                //MessageBox.Show("数据已导入,详细情况请看日志");
 
   
    con.Close();
   }
   else
   {
    MessageBox.Show("请您重新选择Excel或oracle表列！");
   }
   
  }