EXCEL导入导出

/// <summary>
  /// 导出考生基本信息到Excel文件
  /// </summary>
  private void ExportToExcel()
  {
   try
   {
    string FileName=this.ofdOutput.FileName;
    string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName +";Extended Properties=Excel 8.0;";
    OleDbConnection objConn = new OleDbConnection(connString);
    OleDbCommand objCmd = new OleDbCommand();
    objCmd.Connection = objConn;
    if(File.Exists(FileName))//存在同名文件，删除原文件
     File.Delete(FileName);
    this.panProgress.Visible=true;//显示进度条
    this.label1.Text="导出进度";
    //创建表结构
    objCmd.CommandText = @"CREATE TABLE [sheet1](学籍号 varchar,考点代码 varchar,姓名 varchar,性别 varchar,班号 varchar)";
    objConn.Open();
    objCmd.ExecuteNonQuery();
    objConn.Close();
    //建立插入动作的Command 
    OleDbParameter[] parm=new OleDbParameter[5];   
    parm[0]=new OleDbParameter("@xjh", OleDbType.VarChar);
    objCmd.Parameters.Add(parm[0]);
    parm[1]=new OleDbParameter("@xxdm", OleDbType.VarChar);
    objCmd.Parameters.Add(parm[1]);
    parm[2]=new OleDbParameter("@xm",OleDbType.VarChar);
    objCmd.Parameters.Add(parm[2]);
    parm[3]=new OleDbParameter("@xb",OleDbType.VarChar);
    objCmd.Parameters.Add(parm[3]);
    parm[4]=new OleDbParameter("@bh",OleDbType.VarChar);
    objCmd.Parameters.Add(parm[4]);
    //遍历DataTable将数据插入新建的Excel文件中
    for(int i=0;i<ds.Tables["XS_KSXX"].Rows.Count;i++)
    {
     this.lblMsg.Text="正在导出考生"+ds.Tables["XS_KSXX"].Rows[i]["xm"].ToString().Trim();
     this.pbProgress.Value=0;
     parm[0].Value=ds.Tables["XS_KSXX"].Rows[i]["xjh"].ToString();
     parm[1].Value=ds.Tables["XS_KSXX"].Rows[i]["xxdm"].ToString();
     parm[2].Value=ds.Tables["XS_KSXX"].Rows[i]["xm"].ToString();
     parm[3].Value=ds.Tables["XS_KSXX"].Rows[i]["xb"].ToString();
     parm[4].Value=ds.Tables["XS_KSXX"].Rows[i]["bh"].ToString();
     objCmd.CommandText = "insert into [Sheet1](学籍号,考点代码,姓名,性别,班号) values(@xjh,@xxdm,@xm,@xb,@bh)";
     objConn.Open();
     objCmd.ExecuteNonQuery();
     objConn.Close(); 
     this.pbProgress.Value=100;
     System.Threading.Thread.Sleep(5);
     this.pbProgressAll.Value=(int)((i+1)/ds.Tables["XS_KSXX"].Rows.Count*100);
     Application.DoEvents();             
    }
   }
   catch(Exception ex)
   {
    MessageBox.Show("导出失败"+ex.ToString());
   }
   finally
   {
    this.panProgress.Visible=false;
   }
   
  } 

 

/// <summary>
  /// 导入考生基本信息fromExcel
  /// </summary>
  private void InputFromExcel()
  {
   try
   {
    this.panProgress.Visible=true;
    this.label1.Text="导入进度";
    string FileName=this.ofdInput.FileName;
    OleDbConnection myCon= new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileName+";Extended Properties=Excel 8.0");       
    myCon.Open();//打开数据库连接        
    OleDbDataAdapter myAdapter = new OleDbDataAdapter("select * from [Sheet1]", myCon);
    DataSet dsExcel=new DataSet();
    myAdapter.Fill(dsExcel,"input");//学生基本信息表
    //导入学生信息 判断学籍号 如果存在(更新),不存在(添加)-----------------------------------------------------------------------------------------------
    for(int j=0;j< dsExcel.Tables["input"].Rows.Count;j++)
    { 
     ds.Tables["XS_KSXX"].Clear();
     DataBind("");
     DataRow[] drs=ds.Tables["XS_KSXX"].Select("XJH='"+dsExcel.Tables["input"].Rows[j][0].ToString().Trim()+"'");
     if(drs.Length>0)
     {
      this.lblMsg.Text="正在更新学生"+dsExcel.Tables["input"].Rows[j][2].ToString().Trim()+"的信息";
      this.pbProgress.Value=0;
      string strUpdate="update xs_jbxx set xxdm='"+dsExcel.Tables["input"].Rows[j][1].ToString().Trim()+"',xm='"+dsExcel.Tables["input"].Rows[j][2].ToString().Trim()+"',xb='"+dsExcel.Tables["input"].Rows[j][3].ToString().Trim()+"',bh='"+dsExcel.Tables["input"].Rows[j][4].ToString().Trim()+"' where xjh='"+dsExcel.Tables["input"].Rows[j][0].ToString().Trim()+"'";
      DB db=new DB();
      db.ExecCommand(strUpdate);
     }
     else
     {     
        
      this.lblMsg.Text="正在添加学生"+dsExcel.Tables["input"].Rows[j][2].ToString().Trim()+"的信息";
      this.pbProgress.Value=0;
      string strInsert="INSERT INTO XS_JBXX(xjh,xxdm,xm,xb,bh) VALUES('"+dsExcel.Tables["input"].Rows[j][0].ToString().Trim()+"','"+dsExcel.Tables["input"].Rows[j][1].ToString().Trim()+"','"+dsExcel.Tables["input"].Rows[j][2].ToString().Trim()+"','"+dsExcel.Tables["input"].Rows[j][3].ToString().Trim()+"','"+dsExcel.Tables["input"].Rows[j][4].ToString().Trim()+"')";
      DB db=new DB();
      db.ExecCommand(strInsert);  
     }
     //进度条变化
     this.pbProgress.Value=100;
     this.pbProgressAll.Value=(int)((j+1)/dsExcel.Tables["input"].Rows.Count*100);
     Application.DoEvents(); 
     DataBind("");
    }
    
   }
   catch(Exception ex)
   {
    MessageBox.Show("导入失败"+ex.ToString());
   }
   finally
   {
    this.panProgress.Visible=false;
   }
       
  }