问题描述
privatevoidbtn_Import_Click(objectsender,System.EventArgse){OpenFileDialogdlg=newOpenFileDialog();dlg.Filter="Excel文件|*.xls";if(dlg.ShowDialog()==DialogResult.OK){stringstrPath=dlg.FileName;stringsConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource='+strPath+';"+"ExtendedProperties=Excel8.0;";//stringsConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=strPath;ExtendedProperties=Excel8.0";try{OleDbConnectionoleDbConnection=newOleDbConnection(sConnectionString);oleDbConnection.Open();//获取excel表DataTabledataTable=oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);//获取sheet名，其中[0][1]...[N]:按名称排列的表单元素stringtableName=dataTable.Rows[0][2].ToString().Trim();tableName="["+tableName.Replace("'","")+"]";//利用SQL语句从Excel文件里获取数据stringquery="SELECT移动电话,机主姓名,年龄,月收入,月消费,车辆价格,所在公司名称,主营产品或服务,主营行业,企业类型,注册资金,地址,邮编FROM"+tableName;DataSetdataset=newDataSet();OleDbDataAdapteroleAdapter=newOleDbDataAdapter(query,sConnectionString);oleAdapter.Fill(dataset,"LSHXHMK");//this.DataGrid1.DataBind();//创建OldDbDataAdapter执行sql语句//OleDbDataAdapterexcelDA=newOleDbDataAdapter("select*from[1全市总表$]",sConnectionString);////创建DataSet对象//DataSetExcelDs=newDataSet();////执行Sql语句,并将结果填充到DataSet中//excelDA.Fill(ExcelDs);//从web.config中取得数据库连接//string//con=System.Configuration.ConfigurationManager.AppSe//ttings["ConnectionString"].ToString();SqlConnectionsqlcon=newSqlConnection("server=localhost;database=hao;UserID=sa;");sqlcon.Open();//fot(i=0;if<)//从excel文件获得数据后，插入记录到SQLServer的数据表DataTabledataTable1=newDataTable();SqlDataAdaptersqlDA1=newSqlDataAdapter(@"SELECT移动电话,机主姓名,年龄,月收入,月消费,车辆价格,所在公司名称,主营产品或服务,主营行业,企业类型,注册资金,地址,邮编FROMLSHXHMK",sqlcon);SqlCommandBuildersqlCB1=newSqlCommandBuilder(sqlDA1);sqlDA1.Fill(dataTable1);foreach(DataRowdataRowindataset.Tables["LSHXHMK"].Rows){//sql里数据dataRow1DataRowdataRow1=dataTable1.NewRow();dataRow1["移动电话"]=dataRow["移动电话"];dataRow1["机主姓名"]=dataRow["机主姓名"];dataRow1["年龄"]=dataRow["年龄"];dataRow1["月收入"]=dataRow["月收入"];dataRow1["月消费%"]=dataRow["月消费%"];dataRow1["车辆价格"]=dataRow["车辆价格"];dataRow1["所在公司名称"]=dataRow["所在公司名称"];dataRow1["主营产品或服务"]=dataRow["主营产品或服务"];dataRow1["企业类型"]=dataRow["企业类型"];dataRow1["注册资金"]=dataRow["注册资金"];dataRow1["地址"]=dataRow["地址"];dataRow1["邮编"]=dataRow["邮编"];dataTable1.Rows.Add(dataRow1);}Console.WriteLine("新插入"+dataTable1.Rows.Count.ToString()+"条记录");sqlDA1.Update(dataTable1);oleDbConnection.Close();}catch(Exceptionex){MessageBox.Show(ex.Message.ToString());}}}}
解决方案
解决方案二：
为什么运行到stringtableName=dataTable.Rows[0][2].ToString().Trim();就是读不到数据
解决方案三：
给你一个例子吧。。。：给你一个例子吧。。。：自己看看给你一个例子吧。。。：自己看看给你一个例子吧。。。：自己看看给你一个例子吧。。。：自己看看
解决方案四：
usingSystem;usingSystem.Data;usingSystem.Drawing;usingSystem.Web;usingSystem.Web.UI.WebControls;usingSystem.Web.UI.HtmlControls;usingSystem.Data.SqlClient;usingSystem.Text;usingSystem.Web.UI;usingSystem.Data.OleDb;usingSystem.Configuration;usingSystem.Text.RegularExpressions;namespaceBaseDataImport{///<summary>///BaseData的摘要说明。///</summary>publicclassBaseData:System.Web.UI.Page{protectedSystem.Web.UI.WebControls.LabelLblShowMsg;protectedSystem.Web.UI.HtmlControls.HtmlInputFilemyFile;protectedSystem.Web.UI.HtmlControls.HtmlSelectsltType;protectedSystem.Web.UI.HtmlControls.HtmlInputButtonbtnShow;protectedSystem.Web.UI.HtmlControls.HtmlInputButtonbtnParser;protectedSystem.Web.UI.HtmlControls.HtmlInputButtonbtnImport;protectedSystem.Web.UI.WebControls.DataGriddgExcel;protectedSystem.Web.UI.HtmlControls.HtmlTableResultTable;privatevoidPage_Load(objectsender,System.EventArgse){//在此处放置用户代码以初始化页面}#regionWeb窗体设计器生成的代码overrideprotectedvoidOnInit(EventArgse){////CODEGEN:该调用是ASP.NETWeb窗体设计器所必需的。//InitializeComponent();base.OnInit(e);}///<summary>///设计器支持所需的方法-不要使用代码编辑器修改///此方法的内容。///</summary>privatevoidInitializeComponent(){this.btnShow.ServerClick+=newSystem.EventHandler(this.btnShow_ServerClick);this.btnParser.ServerClick+=newSystem.EventHandler(this.btnParser_ServerClick);this.Load+=newSystem.EventHandler(this.Page_Load);}#endregionprivatevoidbtnShow_ServerClick(objectsender,System.EventArgse){try{LblShowMsg.Text="正在加载数据......";stringtmpFilePathName="";stringtmpFileName="";stringtmpExtendedName="";tmpFilePathName=myFile.PostedFile.FileName;stringsUrl=HttpContext.Current.Request.Url.AbsoluteUri;System.IO.FileInfofi=newSystem.IO.FileInfo(tmpFilePathName);tmpFileName=fi.Name;tmpExtendedName=fi.Extension;stringsServerPath=Server.MapPath("UploadFile/");System.IO.DirectoryInfodi=newSystem.IO.DirectoryInfo(sServerPath);if(!di.Exists){di.Create();}myFile.PostedFile.SaveAs(sServerPath+tmpFileName);stringsTName=sltType.Value;DataSetds=this.GetDataFromExcel(sServerPath+tmpFileName,sTName);dgExcel.DataSource=ds;dgExcel.DataBind();this.btnParser.Disabled=false;}catch(Exceptionex){LblShowMsg.Text=ex.Message;}LblShowMsg.Text="加载成功！";}///<summary>///获取Excel文档后，转换为DataSet///</summary>///<paramname="sExcelFilePath">Excel文件</param>///<returns></returns>publicDataSetGetDataFromExcel(stringsExcelFilePath,stringsTableName){DataSetmyDataSet=newDataSet();stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+sExcelFilePath+";"+"ExtendedProperties=Excel8.0;";OleDbConnectionconn=newOleDbConnection(strConn);try{conn.Open();//DataTableSchemaTable=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null});OleDbDataAdaptermyCommand=newOleDbDataAdapter("SELECT*FROM["+sTableName+"$]",strConn);myCommand.Fill(myDataSet);}catch(ExceptionEx){LblShowMsg.Text=Ex.Message;}finally{conn.Close();conn.Dispose();}returnmyDataSet;}///<summary>///分析数据///</summary>///<paramname="sender"></param>///<paramname="e"></param>privatevoidbtnParser_ServerClick(objectsender,System.EventArgse){SqlConnectionsc;SqlCommandcmd;try{sc=newSqlConnection(ConfigurationSettings.AppSettings["ConnStr_Local"].ToString());sc.Open();cmd=sc.CreateCommand();//------先清空表中数据------cmd.CommandText="TrunCateTable"+sltType.Value;cmd.ExecuteNonQuery();//------先清空表中数据------}catch{LblShowMsg.Text="无法操作数据库，操作被终止。";return;}boolbTag=true;//foreach(DataGridItemitemindgExcel.Items)//{//foreach(TableCellcellinitem.Cells)//{//if(cell.Text==" ")//{//bTag=false;//cell.BackColor=Color.Red;//break;//}////if(cell.Text=="中文")cell.Text="1";//if(cell.Text=="英文")cell.Text="2";//}////if(!bTag)break;//if(!this.ImportDataToTable(item,sltType.Value,cmd,sltType.Value))//{//bTag=false;//break;//}//}stringtmpFilePathName="";stringtmpFileName="";stringtmpExtendedName="";tmpFilePathName=myFile.PostedFile.FileName;stringsUrl=HttpContext.Current.Request.Url.AbsoluteUri;System.IO.FileInfofi=newSystem.IO.FileInfo(tmpFilePathName);tmpFileName=fi.Name;tmpExtendedName=fi.Extension;stringsServerPath=Server.MapPath("UploadFile/");System.IO.DirectoryInfodi=newSystem.IO.DirectoryInfo(sServerPath);if(!di.Exists){di.Create();}myFile.PostedFile.SaveAs(sServerPath+tmpFileName);stringsTName=sltType.Value;DataSetds=this.GetDataFromExcel(sServerPath+tmpFileName,sTName);
解决方案五：
dgExcel.DataSource=ds;dgExcel.DataBind();//for(inti=0;i<ds.Tables[0].Columns.Count;i++)//{//LblShowMsg.Text+=ds.Tables[0].Columns[i].ColumnName+"____";//}for(inti=0;i<ds.Tables[0].Rows.Count;i++){for(intj=0;j<ds.Tables[0].Columns.Count;j++){if(ds.Tables[0].Rows[i]["语言"].ToString()==" "){bTag=false;//cell.BackColor=Color.Red;break;}if(ds.Tables[0].Rows[i]["语言"].ToString()=="中文")ds.Tables[0].Rows[i]["语言"]="1";if(ds.Tables[0].Rows[i]["语言"].ToString()=="英文")ds.Tables[0].Rows[i]["语言"]="2";}if(!bTag)break;if(!this.ImportDataToTable(ds.Tables[0].Rows[i]["国家代码"].ToString(),ds.Tables[0].Rows[i]["国家名称"].ToString(),ds.Tables[0].Rows[i]["语言"].ToString(),ds.Tables[0].Rows[i]["所属区域"].ToString(),sltType.Value,cmd,sltType.Value)){bTag=false;break;}}if(bTag==false){LblShowMsg.Text="数据分析不正确，导入终止。";this.btnParser.Disabled=true;}else{LblShowMsg.Text="数据正确，并已入库！";this.btnParser.Disabled=true;}cmd.Dispose();sc.Close();sc.Dispose();}///<summary>///数据入库///</summary>///<paramname="Dgi"></param>///<paramname="sTable"></param>///<paramname="cmd"></param>privateboolImportDataToTable(stringstr1,stringstr2,stringstr3,stringstr4,stringsTable,SqlCommandcmd,stringsTableName){try{stringsSQL="";//区域//if(sTableName=="Inter_AirArea")//sSQL="INSERTINTOInter_AirArea(AreaName,LangCode,IsValid)VALUES(N'"+Dgi.Cells[1].Text+"',"+Dgi.Cells[2].Text+",1)";//国家if(sTableName=="Inter_AirCountry"){stringsAreaID="0";cmd.CommandText="selectAreaIDfromInter_AirAreawhereAreaName=N'"+str4+"'andLangCode="+str3;objectobj=cmd.ExecuteScalar();if(obj==null||obj.ToString().Trim()==""){//Dgi.Cells[3].BackColor=Color.Red;returnfalse;}sAreaID=obj.ToString();sSQL="INSERTINTOInter_AirCountry(CountryCode,CountryName,LangCode,AreaID)VALUES"+"(upper(N'"+str1+"'),N'"+str2+"',"+str3+","+sAreaID+")";//Dgi.Cells[3].Text=sAreaID;}//城市//if(sTableName=="Inter_AirCity")//{//stringsCountryName="";//cmd.CommandText="selectCountryNamefromInter_AirCountrywhereCountryCode=N'"+Dgi.Cells[3].Text.Trim()+"'andLangCode="+Dgi.Cells[2].Text;//objectobj=cmd.ExecuteScalar();//if(obj==null)//{//Dgi.Cells[3].BackColor=Color.Red;//returnfalse;//}////sCountryName=obj.ToString();//////sSQL="INSERTINTOInter_AirCity(CityCode,CityName,LangCode,CountryCode)VALUES"+//"(upper(N'"+Dgi.Cells[0].Text+"'),N'"+Dgi.Cells[1].Text+"',"+Dgi.Cells[2].Text+",'"+Dgi.Cells[3].Text+"')";////Dgi.Cells[3].Text=sCountryName;//}//机场//if(sTableName=="Inter_AirPort")//{//stringsCityName="";//cmd.CommandText="selectCityNamefromInter_AirCitywhereCityCode=N'"+Dgi.Cells[3].Text.Trim()+"'andLangCode="+Dgi.Cells[2].Text;//objectobj=cmd.ExecuteScalar();//if(obj==null)//{//Dgi.Cells[3].BackColor=Color.Red;//returnfalse;//}////sCityName=obj.ToString();//////sSQL="INSERTINTOInter_AirPort(AirPortCode,AirPortName,LangCode,CityCode)VALUES"+//"(upper(N'"+Dgi.Cells[0].Text+"'),N'"+Dgi.Cells[1].Text+"',"+Dgi.Cells[2].Text+",'"+Dgi.Cells[3].Text+"')";////Dgi.Cells[3].Text=sCityName;//}//航空公司//if(sTableName=="Inter_AirLine")//{//stringsCountryName="";//cmd.CommandText="selectCountryNamefromInter_AirCountrywhereCountryCode=N'"+Dgi.Cells[4].Text.Trim()+"'andLangCode="+Dgi.Cells[3].Text;//objectobj=cmd.ExecuteScalar();//if(obj==null)//{//Dgi.Cells[4].BackColor=Color.Red;//returnfalse;//}////sCountryName=obj.ToString();////sSQL="INSERTINTOInter_AirLine(AirLineCode,AirLineName,AirLineShortName,LangCode,CountryCode)VALUES"+//"(upper(N'"+Dgi.Cells[0].Text+"'),N'"+Dgi.Cells[1].Text+"','"+Dgi.Cells[2].Text+"',"+Dgi.Cells[3].Text+",'"+Dgi.Cells[4].Text+"')";////Dgi.Cells[4].Text=sCountryName;//}if(sSQL!=""){cmd.CommandText=sSQL;cmd.ExecuteNonQuery();}}catch{returnfalse;}returntrue;}}}
解决方案六：
谢谢，问题解决了，就是把双引号写成单引号了
解决方案七：
yong用Ado怎么搞