ASP.NET中如何用程序将excel中的数据导入到数据库中（C#）

问题描述

ASP.NET中如何用程序将excel中的数据导入到数据库中（C#），比较不同的时，excel只有一个表，里面是全部的详细信息，导入到sqlserver中的是多个表，这个如何实现？希望有具体的例子！

解决方案

解决方案二：
大致原理：先将EXCEL导入GridView,然后再操作GridView中的数据存入数据库相应表中！在这里不能给你提供原码了，但你可以在Google内收到很多！
解决方案三：
用的是vs.net2003啊，应该如何？
解决方案四：
以下是我的一个真实的例子,供参考!<tr><tdalign="right">选择附件：</td><td><INPUTid="File1"type="file"size="15"name="File1"runat="server"><FONTface="宋体">&nbsp;</FONT><asp:buttonid="btnReadData"runat="server"Width="55px"Text="读取"></asp:button><FONTface="宋体">&nbsp;</FONT><asp:buttonid="btnSave"runat="server"Width="59px"Text="导入"></asp:button></td></tr><tr><tdalign="center"valign="top"><asp:datagridid="dgExportProject"runat="server"Width="95%"BorderWidth="1px"BackColor="White"BorderColor="#CC9966"BorderStyle="None"CellPadding="4"><FooterStyleForeColor="#330099"BackColor="#FFFFCC"></FooterStyle><SelectedItemStyleFont-Bold="True"ForeColor="#663399"BackColor="#FFCC66"></SelectedItemStyle><ItemStyleForeColor="#330099"BackColor="White"></ItemStyle><HeaderStyleFont-Bold="True"ForeColor="#FFFFCC"BackColor="#990000"></HeaderStyle><PagerStyleHorizontalAlign="Center"ForeColor="#330099"BackColor="#FFFFCC"></PagerStyle></asp:datagrid></td></tr>privatevoidbtnReadData_Click(objectsender,System.EventArgse){//获取Excep文件的完整路径stringfileName=System.IO.Path.GetFileName(File1.PostedFile.FileName);stringfilePath="";if(this.File1.Value==""){Comm.Jscript.Alert("请先选择您要导入的文件！");}else{intindex=fileName.LastIndexOf(".");if(index>0){if(fileName.Substring(index)==".xls"){DateTimenow=DateTime.Now;fileName=now.ToShortDateString()+now.ToLongTimeString();fileName=fileName.Replace("-","").Replace(":","").Replace("","");filePath=@"../uploads/"+fileName+".xls";this.File1.PostedFile.SaveAs(Server.MapPath(filePath));}else{Comm.Jscript.Alert("读入的文件不是XLS");}}}if(filePath!=""){fileName=Request.MapPath(filePath);stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+fileName+";ExtendedProperties=Excel8.0";stringquery="SELECT*FROM[Sheet1$]";OleDbCommandoleCommand=newOleDbCommand(query,newOleDbConnection(strConn));OleDbDataAdapteroleAdapter=newOleDbDataAdapter(oleCommand);DataSetmyDataSet=newDataSet();//将Excel的[Sheet1]表内容填充到DataSet对象try{oleAdapter.Fill(myDataSet,"[Sheet1$]");//数据绑定this.dgExportProject.DataSource=myDataSet;this.dgExportProject.DataMember="[Sheet1$]";this.dgExportProject.DataBind();this.dgExportProject.Visible=true;this.btnSave.Visible=true;}catch(Exceptionexx){Response.Write(exx.Message);Comm.Jscript.Alert("注意：请用默认的Sheet1$页名称！");}finally{if(File.Exists(filePath)){File.Delete(filePath);}}}}privatevoidbtnSave_Click(objectsender,System.EventArgse){//dgExportProject取出所有数据，保存到项目/客户数据库中if(this.ddlProjectType.SelectedIndex<1){Comm.Jscript.Alert("请选择项目类型!");return;}else{stringprojectId=String.Empty;stringprojectName=String.Empty;stringtoucher=String.Empty;stringtel=String.Empty;stringemail=String.Empty;stringmanager=String.Empty;stringarea=String.Empty;intprojectTypeId=int.Parse(this.ddlProjectType.SelectedItem.Value);dccsData.ProjectInfoproject=newdccsData.ProjectInfo();using(SqlConnectionsqlConn=newSqlConnection(dccsData.Config.StrConn)){try{if(sqlConn.State==ConnectionState.Closed){sqlConn.Open();}foreach(DataGridItemiteminthis.dgExportProject.Items){if(item.Cells.Count==7){projectId=item.Cells[0].Text.Trim().Replace("&nbsp;","");projectName=item.Cells[1].Text.Trim().Replace("&nbsp;","");toucher=item.Cells[2].Text.Trim().Replace("&nbsp;","");tel=item.Cells[3].Text.Trim().Replace("&nbsp;","");email=item.Cells[4].Text.Trim().Replace("&nbsp;","");manager=item.Cells[5].Text.Trim().Replace("&nbsp;","");area=item.Cells[6].Text.Trim().Replace("&nbsp;","");//将上述值写入到数据库if(projectId!="&nbsp;"&&projectId!=String.Empty){project.Insert(projectId,projectName,toucher,tel,email,projectTypeId,manager,area,sqlConn);}}else{Comm.Jscript.Alert("注意：请按照模板格式导入项目信息！");return;}}}catch(Exceptionexx){Response.Write(exx.Message);}finally{if(sqlConn.State==ConnectionState.Open){sqlConn.Close();}}}this.dgExportProject.Visible=false;this.btnSave.Visible=false;}}思路是先把excel文件传到服务器再读,读完删除见http://topic.csdn.net/u/20071224/09/0527e1b5-7c2c-4ee0-97d1-961f8c8c2cf4.html
解决方案五：
顶。
解决方案六：
ding
解决方案七：
usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Data.OleDb;usingSystem.Threading;usingSystem.IO;usingModel;usingBLL;namespaceDbTools{internalclassDbToolFunc{publicdelegatevoidErrorHandler(stringerrorMessage);publiceventErrorHandlererrorMessage;publicdelegatevoidDataCompletedHandler(DataSetdata);publiceventDataCompletedHandlerdataCompleted;publicdelegatevoidImportDataCompletingHandler(stringinfoMessage);publiceventImportDataCompletingHandlerimportDataCompling;publicdelegatevoidImportDataCompletedHandler(stringinfoMessage);publiceventImportDataCompletedHandlerimportDataCompled;privateThread_threadShow;privateThread_threadImport;publicDbToolFunc(){}publicDbToolFunc(stringxlsPathName){_xlsPathName=xlsPathName;}privatestring_xlsPathName=null;publicstringxlsFileName{set{this._xlsPathName=value;}}privateDataSet_data=null;publicDataSetImportData{set{this._data=value;}}privateDataSet_errordata=null;publicDataSetErrorData{get{returnthis._errordata;}}privatevoidGetDataProc(){OleDbConnectionobjConn=null;try{DataSetds=newDataSet();if(String.IsNullOrEmpty(_xlsPathName)){if(this.errorMessage!=null){if(this.errorMessage!=null)this.errorMessage("文件名没有指定!");}return;}if(!File.Exists(_xlsPathName)){if(this.errorMessage!=null){if(this.errorMessage!=null)this.errorMessage("文件不存在!");}return;}stringstrConn="Provider=Microsoft.Jet.OleDb.4.0;"+"datasource="+_xlsPathName+";ExtendedProperties='Excel8.0;HDR=YES;IMEX=1'";objConn=newOleDbConnection(strConn);objConn.Open();DataTableschemaTable=objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);OleDbDataAdaptersqlada=newOleDbDataAdapter();foreach(DataRowdrinschemaTable.Rows){stringstrSql="Select*From["+dr[2].ToString().Trim()+"]";OleDbCommandobjCmd=newOleDbCommand(strSql,objConn);sqlada.SelectCommand=objCmd;sqlada.Fill(ds,dr[2].ToString().Trim());}objConn.Close();if(this.dataCompleted!=null)this.dataCompleted(ds);}catch(Exceptionex){objConn.Close();if(this.errorMessage!=null)this.errorMessage(ex.Message);}}publicvoidGetDataSetFromExcel(){if(this._threadShow!=null&&this._threadShow.ThreadState==ThreadState.Running){return;}this._threadShow=newThread(newThreadStart(GetDataProc));this._threadShow.Start();}privatevoidImportDataProc(){try{if(_data==null){if(this.errorMessage!=null)this.errorMessage("没有数据要导入!");}/*Area,Adscription,Province,Attribute,IMEINo,PackingCode*,CustomerID,ClassCode,ModelCode,ColorCode,Quantity,CreateTime*/DataTabletb=_data.Tables[0];stringArea=tb.Rows[0][0].ToString();stringAdscription=tb.Rows[0][1].ToString();stringProvince=tb.Rows[0][2].ToString();stringAttribute=tb.Rows[0][3].ToString();stringCustomerID=tb.Rows[0][9].ToString();stringClassCode=tb.Rows[0][6].ToString();stringModelCode=tb.Rows[0][7].ToString();stringColorCode=tb.Rows[0][8].ToString();intQuantity=Convert.ToInt32(tb.Rows[0][10]);DateTimeCreateTime=Convert.ToDateTime(tb.Rows[0][11]);ptShipMentbll=newptShipMent();stringCode=bll.BuildptShipMentNo(Area,Province,Adscription,Attribute);if(importDataCompling!=null)importDataCompling("得到出货单号:"+Code);ptShipMentInfomaster=newptShipMentInfo();master.CODE=Code;master.AREA=Area;master.ADSCRIPTION=Adscription;master.ATTRIBUTE=Attribute;master.PROVINCE=Province;master.MODELCODE=ModelCode;master.CLASSCODE=ClassCode;master.COLORCODE=ColorCode;master.CUSTOMERID=CustomerID;master.QUANTITY=Quantity;master.CREATETIME=CreateTime;master.CREATEUSER="admin";master.LASTTIME=CreateTime;master.LASTUSER="admin";master.STATUS="1";master.REMARK="导入";if(importDataCompling!=null)importDataCompling("增加主体数据...");List<ptShipmentListInfo>details=newList<ptShipmentListInfo>();foreach(DataRowrowintb.Rows){ptShipmentListInfoitem=newptShipmentListInfo();item.CODE=Code;item.IMEINO=row[4].ToString();item.PACKINGCODE=row[5].ToString();details.Add(item);if(importDataCompling!=null)importDataCompling("增加明细数据..."+item.IMEINO+""+item.PACKINGCODE);}if(importDataCompled!=null)importDataCompled("开始导入数据...");bll.InsertptShipMentNoDTC(master,details);if(importDataCompled!=null)importDataCompled("导入成功!");}catch(Exceptionex){if(this.errorMessage!=null)this.errorMessage(ex.Message);}}publicvoidImportDataToSQLServer(){if(this._threadImport!=null&&this._threadImport.ThreadState==ThreadState.Running){return;}this._threadImport=newThread(newThreadStart(ImportDataProc));this._threadImport.Start();}}}

参考一下吧，自己写的导入类用到了自己的数据，但思路是不变的。