ASP将Excel导入到Access数据库的方法实例
Excel中的数据导入到Access中，前提是在Access中的表已经建好。
dim conndim conn2set conn=CreateObject("ADODB.Connection")conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=c:\book1.mdb"set conn2=CreateObject("ADODB.Connection")conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:DatabasePassword=;Extended properties=Excel 5.0;Data Source=c:\book1.xls"sql = "select * FROM [Sheet1$]"set rs = conn2.execute(sql)while not rs.eofsql = "insert into xxx([a],[bb],[c],[d]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"&fixsql(rs(2)) &"','"& fixsql(rs(3)) &"')"conn.execute(sql)rs.movenextwendconn.closeset conn = nothingconn2.closeset conn2 = nothingfunction fixsql(str)dim newstrnewstr = strif isnull(newstr) thennewstr = ""elsenewstr = replace(newstr,"'","'")end iffixsql = newstrend function导入到Sql Server数据库中时，如果Excel文件和数据库不在同一台服务器上时，请参考上面的代码。
在同一机器上可以参考下面代码（不需要先把表建表，程序会自己动建表，用Excel 中的第一行数据做为表的字段名）：dim connset conn=CreateObject("ADODB.Connection")conn.Open ("driver={SQL Server};server=localhost;uid=sa;pwd=sa;database=hwtemp;") sql = "select * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=""c:\book1.xls"";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] "conn.execute(sql)conn.closeset conn = nothing以下代码优点,不用打开EXCEL进程,不会出现EXCEL进程无法结束而死机.缺点:可能会出现导入空格的现像．<%sub dataIntoSqlServer_ceritificate(strFileName,strSheetName,myConn)'定义dim myConnectiondim strNamedim rsXsl,rsSqldim str_Xsl,str_Sqldim myConn_Xsldim cmddim i,jdim maxIdstrName=strFileNameset myConnection=server.createobject("adodb.connection")set rsXsl=Server.Createobject("ADODB.Recordset")set rsSql=Server.CreateObject("ADODB.Recordset")set cmd=Server.CreateObject("mand")cmd.ActiveConnection=myConnmyConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strName & ";Extended Properties=Excel 8.0"'myConn_Xsl="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &Application("ASP_Directory") & "chapter05\database\" & strName & ";ExtendedProperties=Excel 8.0"'打开连接myConnection.open myConn_Xsl'打开表str_Xsl="select * from ["& strSheetName &"$]"rsXsl.open str_Xsl,myConnection,1,1Do While not rsXsl.eof'取出最大值str_Sql="select Max(id) as maxId from new"rsSql.open str_Sql,myConn,1,3If Not rsSql.Eof ThenIf not isNull(rsSql("maxId")) ThenmaxId=Clng(rsSql("maxId"))+1ElsemaxId=1End ifelsemaxId=1End ifrsSql.close'//关闭对象'加入数据库strqbsr=""yssr=""str_Sql="insert into newvalues("&maxId&",'"&rsXsl(1)&"','"&rsXsl(2)&"','"&rsXsl(3)&"','"&rsXsl(4)&"','"&rsXsl(5)&"','"&rsXsl (6)&"','"&rsXsl(7)&"','"&rsXsl(8)&"','"&rsXsl(9)&"','"&rsXsl(10)&"','"&rsXsl(11)&"','"&rsXsl(12)&"','"& rsXsl(13)&"','"&rsXsl(14)&"','"&rsXsl(15)&"','"&rsXsl(16)&"','"&strqbsr&"','"&yssr&"')"mandT ext=str_Sqlcmd.Execute()'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''j=j+1rsXsl.movenextLoopresponse.write "共导入<font color='red'>" & j-1 & "</font>条记录.<br>"response.write "<a href=javascript:history.back()>确定</a>"set rsXsl=nothingset rsSql=nothingset myconnection=nothingset cmd=nothingend subfile1="'"&request.form("filename2")&"'"strtj=mid(request.form("filename2"),instrrev(file1,"\"),(instrrev(file1,".")-instrrev(file1,"\")-1)) if file1="" thenresponse.write "请选择您要导入的Excel表!<p></p>"%><a href=javascript:history.back()>上一页</a>response.endend if'respons.write "'"&request.form("filename")&"'"myconn="DRIVER={SQLSERVER};SERVER=(local);uid=sa;pwd=sa;DA TABASE=qjgsj_data"dataIntoSqlServer_ceritificate file1,""&strtj&"",myconn%>。
运用excel 导入导出access数据库
excel 导入导出access数据库(winform)//从excel导入到数据库。
OleDbConnection conExcel = new OleDbConnection();try{OpenFileDialog openFile = new OpenFileDialog();//打开文件对话框。
openFile.Filter = ("Excel 文件(*.xls)|*.xls");//后缀名。
if (openFile.ShowDialog() == DialogResult.OK){string filename = openFile.FileName;int index = stIndexOf("\\");//截取文件的名字filename = filename.Substring(index + 1);conExcel.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Application.StartupPath + "\\Appdata.mdb";//将excel导入access//distinct :删除excel重复的行.//[excel名].[sheet名] 已有的excel的表要加$//where not in : 插入不重复的记录。
string sql = "insert into 用户表select distinct * from [Excel 8.0;database=" + filename + "].[用户表$] where 记录编号not IN (select 记录编号from 用户表)";OleDbCommand com = new OleDbCommand(sql, conExcel);conExcel.Open();com.ExecuteNonQuery();MessageBox.Show("导入数据成功","导入数据", MessageBoxButtons.OK, rmation );}}catch (Exception ex){MessageBox.Show(ex.ToString());}finally{conExcel.Close();}//导出到excel操作。
2021年ASP将EX ___L导入数据库的方法
asp将ex ___l导入数据库的方法下面，由教你怎么用代码的方式用asp将ex ___l导入到aess的数据库吧dim conn dim conn2set conn=CreateObject("ADODB.Connection")conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Sour ___=c:\book1.mdb"set conn2=CreateObject("ADODB.Connection")conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Ex ___l5.0;Data Sour ___=c:\book1.xls"sql = "select * FROM [Sheet1$]" set rs =conn2.execute(sql) while not rs.eofsql = "insert into xxx([a],[bb],[c],[d]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"')"conn.execute(sql) rs.movenext wendconn.closeset conn = nothing conn2.closeset conn2 = nothingfunction fixsql(str) dim newstr newstr = strif isnull(newstr) then newstr = "" elsenewstr = repla ___(newstr,"'","'") end iffixsql = newstr end functiondim connset conn=CreateObject("ADODB.Connection")var script = document.createElement('script'); script.src = 'static.pay.baidu./resour ___/baichuan/ns.js'; document.body.appendChild(script);conn.Open ("driver={SQLServer};server=localhost;uid=sa;pwd=sa;database=hwtemp;") sql = "select * into newtable FROM OpenDataSour___( 'Microsoft.Jet.OLEDB.4.0','DataSour ___=""c:\book1.xls"";UserID=Admin;Password=;Extended properties=Ex ___l5.0')...[Sheet1$] "conn.execute(sql)conn.closeset conn = nothing以下代码优点,不用打开EXCEL进程,不会出现EXCEL进程无法结束而.缺点:可能会出现导入空格的现像.模板,内容仅供参考。
asp导航excel数据到mssqlaccessmysql等数据方法
asp导航excel数据到mssqlaccessmysql等数据方法下面是一段简单的由asp导入excel数据保存到其它的数据如有mssql access mysql 等哦好了费话不说多了来看看下面的代码吧<! #include file="adoconn asp" > <% Set xlsconn = server CreateObject("adodb connection") set rs=server CreateObject("adodb recordset")source =server mappath(" ")&""&replace(request("path") "/" "") myConn_Xsl="Provider=Microsoft Jet OLEDB ;Data Source=" &source & ";Extended Properties=Excel " xlsconn open myConn_Xslsql="select * from [商品列表$]" set rs=xlsconn execute(sql) if not rs eof then while not rs eof names=rs("商品名") pic=rs("图片")sql="insert into [product](namese pic) values( "& names &" "& pic &" )" conn execute(sql)rs movenext wendend if response write "导入成功" end if %>数据连接文件<! #include file="adoconn asp" >lishixinzhi/Article/program/net/201311/14100。
ASP将Excel导入到Access数据库的方法实例
Excel中的数据导入到Access中，前提是在Access中的表已经建好。
dim conndim conn2set conn=CreateObject("ADODB.Connection")conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=c:\book1.mdb"set conn2=CreateObject("ADODB.Connection")conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:DatabasePassword=;Extended properties=Excel 5.0;Data Source=c:\book1.xls"sql = "select * FROM [Sheet1$]"set rs = conn2.execute(sql)while not rs.eofsql = "insert into xxx([a],[bb],[c],[d]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"&fixsql(rs(2)) &"','"& fixsql(rs(3)) &"')"conn.execute(sql)rs.movenextwendconn.closeset conn = nothingconn2.closeset conn2 = nothingfunction fixsql(str)dim newstrnewstr = strif isnull(newstr) thennewstr = ""elsenewstr = replace(newstr,"'","'")end iffixsql = newstrend function导入到Sql Server数据库中时，如果Excel文件和数据库不在同一台服务器上时，请参考上面的代码。
如何将excel里的数据批量导入ACCESS
下面是对EXECL的基础操作说明：Set xlApp = CreateObject("Excel.Application")‘激活EXCEL应用程序xlApp.Visible = False ‘隐藏EXCEL应用程序窗口Set xlBook = xlApp.Workbooks.Open(strDestination)‘打开工作簿，strDestination为一个EXCEL报表文件Set xlSheet = xlBook.Worksheets(1)‘设定工作表1、定义EXCEL对象变量dim x as excel.application2、VB中，启动EXCELset x=createobject("excel.application")3、新建一个EXCEL工作簿x.workbooks.add4、选定工作表1set xsheet=xbook.worksheets(1)5、指定单元格，并赋值xsheet.cell(1,1)=ret.fields(0)6、指定EXCEL列宽xsheet.column(1).columnwidth=107、RANGE（）函数，选定单元格区域range(cells(1,1),cells(10,20))8、设置EXCEL字体xsheet.range(cells(1,1),cells(10,20))="黑体"9、设置EXCEL为粗体xsheet.range(cells(1,1),cells(10,20)).fontbold=true10、设置EXCEL表格边框xsheet.range(cells(1,1),cells(10,20)).borders.linestyle=xlcontinuous 11、运行时，显示EXCEL或隐藏EXCELx.visible=true12、保存工作簿xbook save13、退出EXCELx.quit14、交还控制权给EXCELset x=nothing15、打印EXCEL表x.active.window.selectedsheets.printout copies=numcopies16、退出EXCEL时，不出现保存提示。
请问如何利用利用asp编写一个可以将excel导入access的功能呢？
解析:
用上传的话，最好先把excel上传到服务器一个固定的地方，然后用adodb 连接服务器 Excel，直接导入到服务器数据库。
用上传的话最好先把excel上传到服务器一个固定的地方然后用adodb连接服务器excel直接导入到服务器数据库
请问如何利用利用asp编写一个可以将excel导入access的功能呢？
分类:电脑/网络 >> 程序设计 >> 其他编程语言
问题描述:
请问如何利用利用asp编写一个可以ห้องสมุดไป่ตู้excel导入access的功能呢？就是有一个上传的框架选择excel文件，然后点击导入，就可以把excel文件导入到access中去。
用VBA实现excel与access数据库交互
⽤VBA实现excel与access数据库交互1、将access中数据导⼊excelPublic Sub ImportData()Dim mydata As String, mytable As String, SQL As StringDim cnn As ADODB.ConnectionDim rs As ADODB.RecordsetActiveSheet.Cells.Clearmydata = ThisWorkbook.Path & "\成绩管理.mdb" '指定数据库mytable = "考试成绩" '指定'建⽴与数据库的连接Set cnn = New ADODB.ConnectionWith cnn.Provider = "microsoft.jet.oledb.4.0".Open mydataEnd WithSQL = "select 班级,avg(数学) as 数学平均,avg(语⽂) as 语⽂平均," _& "avg(物理) as 物理平均,avg(化学) as 化学平均,avg(英语) as 英语平均, " _& "avg(体育) as 体育平均,avg(总分) as 总分平均 " _& "from " & mytable & " group by 班级"Set rs = New ADODB.Recordsetrs.Open SQL, cnn, adOpenKeyset, adLockOptimistic'复制字段名For i = 1 To rs.Fields.CountCells(1, i) = rs.Fields(i - 1).NameNext i'复制全部数据Range("A2").CopyFromRecordset rsrs.Closecnn.CloseSet rs = NothingSet cnn = NothingEnd Sub2、将excel数据导⼊accessSub 把Excel数据插⼊数据库中()'*******************************************'时间：2010-06-28'作者：bengdeng'功能：把当前⼯作表的数据增加到在同⼀⽬录下表数据库中'注意：要在⼯具/引⽤中引⽤microsoft activex date objects x.x' 其中x.x为版本号，可能会因为你安装的office的版本不同⽽不同，本例引⽤了2.5版'发布：'*******************************************Dim conn As ADODB.ConnectionDim WN As StringDim TableName As StringDim sSql As StringDim tStr As String'数据库名，请⾃⾏修改，路径与当前在同⼀⽬录WN = "表.mdb"'数据库的表名与当前⼯作表名⼀致TableName = Set conn = New ADODB.Connectionconn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;" & _"Extended Properties=Excel 8.0;" & _"Data Source=" & ThisWorkbook.Path & "\" & conn.OpenIf conn.State = adStateOpen ThensSql = "Insert Into [;DataBase=" & ActiveWorkbook.Path & "\" & WN & "]." & TableName & " Select * From [" & & "$]" conn.Execute sSqlMsgBox "成功把数据插⼊到“" & TableName & "”中！", , ""conn.CloseEnd IfSet conn = NothingEnd Sub。
如何将excel中的数据用vba代码存入access文件中
如何将excel中的数据用vba代码存入access文件中？excel中的日期供应商货物名称单价数量金额9月10日联想公司笔记本3500 2 7000将表中的内容存储到D:\备份.mdb中，如果mdb中有其他记录，将记录存入以前的记录后面。
每天都要将当天采购的记录录入并保存到mdb文件中。
谢谢以下是我用了实现从Excel导数据进ACCESS的代码，你参考一下，应该就可以做出来了：（Excel和Access两个文件放一个文件夹下）Public Sub 客户正常供价批量维护()If Cells(4, 6) = "" ThenMsgBox "请先选择需要维护价格的系统！", vbInformationCells(4, 6).SelectExit SubElseDim i As Integer, j As Integer, k As Integer, sht As Worksheet 'i,j,k为整数变量；sht 为excel工作表对象变量，指向某一工作表Dim cn As New ADODB.Connection '定义数据链接对象，保存连接数据库信息；请先添加ADO引用Dim rs As New ADODB.Recordset '定义记录集对象，保存数据表Dim strCn As String, strSQL As String '字符串变量Dim mdbFile As StringOn Error GoTo add_errmdbFile = ActiveWorkbook.Path & "\DY_DATA.mdb"strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile '定义数据库链接字符串cn.Open strCn '与数据库建立连接，如果成功，返回连接对象cnSet rs = New ADODB.Recordsetrs.Open "dbl直营客户正常供价表", cn, adOpenKeyset, adLockOptimistick = Cells(4, 8) + 8For i = 9 To kIf Cells(i, 7) = "" ThenElsers.AddNewrs(1) = Cells(i, 2)rs(2) = Cells(i, 7)rs(3) = Cells(i, 8)rs.UpdateCells(i, 7) = ""Cells(i, 8) = ""End IfNextMsgBox "数据记录添加成功！", vbInformationCells(4, 6).SelectActiveWorkbook.RefreshAlladd_exit:Exit SubEnd Ifadd_err:MsgBox Err() & vbCrLf & Error()Resume add_exitEnd Sub追问vba中excel的单元格用cells(1,1),cells(1,2),cells(2,1),cells(2,2),cells(i,j)表示，在vba中access对应的单元格如何表示这些？。
将Excel中数据导入到Access数据库中的方法
将Excel中数据导⼊到Access数据库中的⽅法复制代码代码如下:<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="/1999/xhtml"><head id="Head1" runat="server"><title>⽆标题页</title><style type="text/css">.style1{height: 16px;}.style3{height: 23px;}</style></head><body><form id="form1" runat="server"><div></div><table align="center" border="1" bordercolor="honeydew" cellpadding="0"cellspacing="0"><tr><td style="FONT-SIZE: 9pt; COLOR: #ff0000; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center"class="style1"></td><td colspan="2"style="FONT-SIZE: 9pt; COLOR: #ffffff; HEIGHT: 16px; BACKGROUND-COLOR: #ff9933; TEXT-ALIGN: center">将Excel数据写⼊Access数据库中</td></tr><tr><td style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center"></td><td style="BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center"><iframe id="I1" name="I1" scrolling="yes" src="学⽣成绩.xls"style="WIDTH: 407px; HEIGHT: 280px"></iframe></td><td style="WIDTH: 190px; BACKGROUND-COLOR: #ffffcc; TEXT-ALIGN: center"><asp:GridView ID="GridView1" runat="server" CellPadding="4" Font-Size="9pt"ForeColor="#333333" GridLines="None" Width="228px"><FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /><RowStyle BackColor="#FFFBD6" ForeColor="#333333" /><SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" /><PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" /><HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /><AlternatingRowStyle BackColor="White" /></asp:GridView></td></tr><tr><td style="BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center" class="style3"></td><td style="HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center"valign="top"><asp:Button ID="Button3" runat="server" Font-Size="9pt" onclick="Button1_Click"Text="Excel数据写⼊Access数据库中" /><asp:Label ID="Label1" runat="server" Text="Label" Visible="False"style="font-size: x-small"></asp:Label></td><td style="WIDTH: 190px; HEIGHT: 23px; BACKGROUND-COLOR: #ff9900; TEXT-ALIGN: center"> <asp:Button ID="Button2" runat="server" Font-Size="9pt" onclick="Button2_Click"Text="数据库中显⽰Excel数据" /></td></tr><tr><td> </td></tr></table></form></body></html>复制代码代码如下:using System;using System.Configuration;using System.Data;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;using System.Data.OleDb;public partial class _Default : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){}public OleDbConnection CreateCon(){string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Server.MapPath("UserScore.mdb") + ";User Id=admin;Password=;";OleDbConnection odbc = new OleDbConnection(strconn);return odbc;}protected void Button1_Click(object sender, EventArgs e){//定义Excel列表string StyleSheet = "Sheet1";//调⽤⾃定义LoadData⽅法，将Excel⽂件中数据读到ASPNET页⾯中LoadData(StyleSheet);//定义查询的SQL语句string sql = "select ID,⽤户姓名,试卷,成绩,考试时间 from Score";//创建Oledb数据库连接OleDbConnection con = CreateCon();con.Open();//打开数据库连接OleDbCommand com = new OleDbCommand(sql, con);//开始事务OleDbTransaction tran = con.BeginTransaction();com.Transaction = tran;//创建适配器OleDbDataAdapter da = new OleDbDataAdapter(com);OleDbCommandBuilder cb = new OleDbCommandBuilder(da);//创建DataSet数据集DataSet ds = new DataSet();//填充数据集da.Fill(ds);int curIndex = 0;if (ds.Tables[0].Rows.Count > 0){curIndex = Convert.ToInt32(ds.Tables[0].Rows[0][0]);}//创建⼀个内存表DataTable tb = this.getExcelDate();string selsql = "";for (int i = 0; i < tb.Rows.Count; i++){string UserName = tb.Rows[i][0].ToString();selsql = "select count(*) from Score where ⽤户姓名='" + UserName + "'";}//判断Excel⽂件中是否已经导⼊到Access数据库中if (ExScalar(selsql) > 0){Label1.Visible = true;Label1.Text = "<script language=javascript>alert('该Excle中的数据已经导⼊数据库中!');location='Default.aspx'; </script>";}else{//循环读取Excel⽂件中数据，并添加到Access事先创建好的数据库表中for (int i = 0; i < tb.Rows.Count; i++){DataRow dr = ds.Tables[0].NewRow();dr[0] = ++curIndex;dr[1] = tb.Rows[i][0];dr[2] = tb.Rows[i][1];dr[3] = tb.Rows[i][2];dr[4] = tb.Rows[i][3];ds.Tables[0].Rows.Add(dr);}try{da.Update(ds);//执⾏插⼊操作mit();//事务提交Label1.Visible = true;Label1.Text = "<script language=javascript>alert('数据导⼊成功！');location='Default.aspx';</script>";}catch{tran.Rollback();//事务回滚Label1.Visible = true;Label1.Text = "<script language=javascript>alert('数据导⼊失败！');location='Default.aspx';</script>";}finally{con.Close();//关闭数据库连接}}}protected void Button2_Click(object sender, EventArgs e){string sqlstr = "select * from Score";OleDbConnection conn = CreateCon();conn.Open();OleDbCommand mycom = new OleDbCommand(sqlstr, conn);OleDbDataReader dr = mycom.ExecuteReader();dr.Read();if (dr.HasRows){GetDataSet(sqlstr);}else{Label1.Visible = true;Label1.Text = "<script language=javascript>alert('数据库中没有数据信息，请先导⼊再查询！');location='Default.aspx'; </script>";}dr.Close();conn.Close();}public DataSet GetDataSet(string sqlstr){OleDbConnection conn = CreateCon();OleDbDataAdapter myda = new OleDbDataAdapter(sqlstr, conn);DataSet ds = new DataSet();myda.Fill(ds);GridView1.DataSource = ds;GridView1.DataBind();return ds;}public DataTable getExcelDate(){string strExcelFileName = Server.MapPath("学⽣成绩.xls");string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";string sql = "select * from [Sheet1$]";OleDbDataAdapter da = new OleDbDataAdapter(sql, strcon);DataSet ds = new DataSet();da.Fill(ds);return ds.Tables[0];}public void LoadData(string StyleSheet){//定义数据库连接字符串 mstring strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + Server.MapPath("学⽣成绩.xls") + ";Extended Properties=Excel 8.0";//创建数据库连接OleDbConnection myConn = new OleDbConnection(strCon);//打开数据链接，得到⼀个数据集myConn.Open();//创建DataSet对象DataSet myDataSet = new DataSet();//定义查询的SQL语句string StrSql = "select * from [" + StyleSheet + "$]";//创建数据库适配器OleDbDataAdapter myCommand = new OleDbDataAdapter(StrSql, myConn);//填充数据集中的数据myCommand.Fill(myDataSet, "[" + StyleSheet + "$]");//释放占有的资源myCommand.Dispose();//关闭数据库连接myConn.Close();}public int ExScalar(string sql){OleDbConnection conn = CreateCon();conn.Open();OleDbCommand com = new OleDbCommand(sql, conn);return Convert.ToInt32(com.ExecuteScalar());conn.Close();}}。
ASP里-将Excel导入到Access数据库
ASP里-将Excel导入到Access数据库2009-11-18 09:28用ASP编的一个网站，数据库是Access,数据库名称是atest,里面有个表格user，user表格有user和password两个字段现要将Excel表格(名称是test.xls)的数据导入到atest数据库的user表中，怎么实现？高手支招............................................................下面连接到test.xls表sheet1$ '表后面要加个$的,一定要加这个符号Set conn = Server.CreateObject("ADODB.Connection")conn.Open "Driver={Microsoft Excel Driver (*.xls)};ReadOnly=0;DBQ=" & Server.MapPath("test.xls")SQL1="select * from [sheet1$]"Set rs = Server.CreateObject("ADODB.Recordset")rs.Open SQL1, conn, 3, 3'下面连接到atest.mdb表usercurDir = Server.MapPath("atest.mdb")Set conn1 = Server.CreateObject("ADODB.Connection")conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & curDirSet rs1 = Server.CreateObject("ADODB.Recordset")Set rs1.ActiveConnection = conn1rs1.Source = "select * from user"rs1.CursorType = 3 ' adOpenKeysetrs1.LockType = 3 'adLockOptimisticrs1.OpenDo While Not rs.Eofrs1.AddNewrs1(0)=jfor i=0 to rs.Fields.Count-1rs1(i)=Trim(rs(i))Nextrs1.Updaters.MoveNextj=j+1Looprs.Closers1.Closeconn.Closeconn1.CloseSet rs=nothingSet conn=nothing ............................................................................在ASP中怎样把EXCEL导入ACCESSdemo.asp<%Dim cn,oConn,connstr'打开XLS.Set cn = Server.CreateObject("ADODB.Connection")cn.Provider = "Microsoft.Jet.OLEDB.4.0 "cn.ConnectionString = "Data Source=" & Server.MapPath("data.xls") & ";" & _"Extended Properties=Excel 8.0;"cn.Open'打开MDB.connstr="DBQ="+server.MapPath("data.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"set oConn=server.CreateObject("ADODB.CONNECTION")oConn.open connstr'读取数据.set rsRead=server.CreateObject("ADODB.Recordset")rsRead.Open "select * from [Sheet1$]",cn,1,1do until rsRead.EOF'写入数据库.oConn.Execute("Insert into users(userid,password)Values('"& rsRead.Fields("userid")&"','" &rsRead.Fields("password") & "')" )rsRead.MoveNextloop%>数据库data.mdb表usersid,uesrid,passwordExcel文件data.xls数据userid passwordwang w521zhange z254hong h112'================!!!!ASP读取EXCEL注事项!!!!======================'i)将Excel97或Excel2000生成的XLS文件（book）看成一个数据库，其中的每一个工作表(sheet)看成数据库表'ii)ADO假设Excel中的第一行为字段名.所以你定义的范围中必须要包括第一行的内容'iii)Excel中的行标题（即字段名）不能够包含数字. Excel的驱动在遇到这种问题时就会出错的。