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;
}
}
