问题描述
现在有一张excel表有五列姓名电话地址回访日期回访记录想要导入到数据库中一张回访表但回访表列不只上面这五项姓名电话地址回访日期回访记录级别邮寄日期想问一下要怎么数据导入了
解决方案
解决方案二：
privatevoidButton1_Click(objectsender,System.EventArgse){//CreateTable();//先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面//EXCEL的连接串File1if(this.File1.Value==""){Response.Write("<script>alert('请选择你所要导入的EXCEL表格!')</script>");}stringsConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+File1.PostedFile.FileName.ToString()+";"+"ExtendedProperties=Excel8.0;";//建立EXCEL的连接OleDbConnectionobjConn=newOleDbConnection(sConnectionString);objConn.Open();OleDbCommandobjCmdSelect=newOleDbCommand("SELECT*FROM[Sheet1$]",objConn);OleDbDataAdapterobjAdapter1=newOleDbDataAdapter();objAdapter1.SelectCommand=objCmdSelect;DataSetobjDataset1=newDataSet();objAdapter1.Fill(objDataset1,"XLData");DataTabledt=objDataset1.Tables[0];DataViewmyView=newDataView(dt);//SQLSERVER的数据库连接SqlConnectionconn;conn=newSqlConnection("Server=.;Database=pubs;uid=sa;pwd=sa");SqlCommandcmd=conn.CreateCommand();cmd.CommandType=CommandType.StoredProcedure;cmd.CommandText="Proc_Address";intcount=0;//用来记录出错的条数try{foreach(DataRowViewmyDrvinmyView){count++;//要关闭上一次的SQLServer的连接if(conn.State.ToString()!="Closed")conn.Close();//每一次都要清空所有的CMD的参数cmd.Parameters.Clear();//执行存储过程SqlParameterc_mainName=cmd.Parameters.Add("@c_mainName",SqlDbType.VarChar,100);SqlParameterc_byCity=cmd.Parameters.Add("@c_byCity",SqlDbType.VarChar,100);SqlParameterc_netName=cmd.Parameters.Add("@c_netName",SqlDbType.VarChar,100);SqlParameterc_infoName=cmd.Parameters.Add("@c_infoName",SqlDbType.VarChar,100);SqlParameterc_infoCode=cmd.Parameters.Add("@c_infoCode",SqlDbType.VarChar,100);//表示是输出参数c_mainName.Direction=ParameterDirection.Input;c_byCity.Direction=ParameterDirection.Input;c_netName.Direction=ParameterDirection.Input;c_infoName.Direction=ParameterDirection.Input;c_infoCode.Direction=ParameterDirection.Input;//参数赋值c_mainName.Value=myDrv[1].ToString().Trim();c_byCity.Value=myDrv[2].ToString().Trim();c_netName.Value=myDrv[3].ToString().Trim();c_infoName.Value=myDrv[4].ToString().Trim();c_infoCode.Value=myDrv[5].ToString().Trim();conn.Open();cmd.ExecuteNonQuery();//写入SQL数据库}Response.Write("<script>alert('数据导入成功!')</script>");}catch(Exceptionex){Page.Response.Write("<script>alert('导入数据失败,请重新导入!')</script>");objConn.Close();//关闭EXCEL的连接}}
解决方案三：
publicstringsConnectionString{get{//先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面//stringxlsPath=Server.MapPath("~/app_data/book1.xls");//EXCEL的连接串stringsConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+File1.PostedFile.FileName.ToString()+";"+"ExtendedProperties=Excel8.0;";returnsConnectionString;}}privatevoidButton1_Click(objectsender,System.EventArgse){stringsqlStr="select*from[Sheet1$]";myConn=newOleDbConnection(connStr);myConn.Open();myCmd=newOleDbCommand(sqlStr,myConn);myda=newOleDbDataAdapter(myCmd);myDs=newDataSet();myda.Fill(myDs,"userinfo");DataGrid1.DataSource=myDs;stringstrConn=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];SqlConnectionmySqlConn=newSqlConnection(strConn);mySqlConn.Open();stringstrCmd="";introws=myDs.Tables[0].Rows.Count;intcols=myDs.Tables[0].Columns.Count;for(inti=0;i<rows;i++){strCmd="insertintouserinfo(userID,userName)values('";for(intj=0;j<cols;j++){if(j==0){strCmd=strCmd+myDs.Tables[0].Rows[i].ItemArray[j].ToString();}else{strCmd+="','"+myDs.Tables[0].Rows[i].ItemArray[j].ToString();}}strCmd+="')";SqlCommandmySqlCmd=newSqlCommand(strCmd,mySqlConn);Label1.Text=strCmd;mySqlCmd.ExecuteNonQuery();}mySqlConn.Close();myConn.Close();}
解决方案四：
取出数据，拼sql
解决方案五：
学习