ASP将Excel导入到Access数据库的方法实例

Excel中的数据导入到Access中，前提是在Access中的表已经建好。

dim conn

dim conn2

set 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:Database

Password=;Extended properties=Excel 5.0;Data Source=c:\book1.xls"

sql = "select * FROM [Sheet1$]"

set rs = conn2.execute(sql)

while not rs.eof

sql = "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

wend

conn.close

set conn = nothing

conn2.close

set conn2 = nothing

function fixsql(str)

dim newstr

newstr = str

if isnull(newstr) then

newstr = ""

else

newstr = replace(newstr,"'","'")

end if

fixsql = newstr

end function

导入到Sql Server数据库中时，如果Excel文件和数据库不在同一台服务器上时，请参考上面的代码。在同一机器上可以参考下面代码（不需要先把表建表，程序会自己动建表，用Excel 中的第一行数据做为表的字段名）：

dim conn

set 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.close

set conn = nothing

以下代码优点,不用打开EXCEL进程,不会出现EXCEL进程无法结束而死机.缺点:可能会出现导入空格的现像．

<%

sub dataIntoSqlServer_ceritificate(strFileName,strSheetName,myConn)

'定义

dim myConnection

dim strName

dim rsXsl,rsSql

dim str_Xsl,str_Sql

dim myConn_Xsl

dim cmd

dim i,j

dim maxId

strName=strFileName

set myConnection=server.createobject("adodb.connection")

set rsXsl=Server.Createobject("ADODB.Recordset")

set rsSql=Server.CreateObject("ADODB.Recordset")

set cmd=Server.CreateObject("mand")

cmd.ActiveConnection=myConn

myConn_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 & ";Extended

Properties=Excel 8.0"

'打开连接

myConnection.open myConn_Xsl

'打开表

str_Xsl="select * from ["& strSheetName &"$]"

rsXsl.open str_Xsl,myConnection,1,1

Do While not rsXsl.eof

'取出最大值

str_Sql="select Max(id) as maxId from new"

rsSql.open str_Sql,myConn,1,3

If Not rsSql.Eof Then

If not isNull(rsSql("maxId")) Then

maxId=Clng(rsSql("maxId"))+1

Else

maxId=1

End if

else

maxId=1

End if

rsSql.close'//关闭对象

'加入数据库

strqbsr=""

yssr=""

str_Sql="insert into new

values("&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_Sql

cmd.Execute()

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

j=j+1

rsXsl.movenext

Loop

response.write "共导入" & j-1 & "条记录.
"

response.write "确定"

set rsXsl=nothing

set rsSql=nothing

set myconnection=nothing

set cmd=nothing

end sub

file1="'"&request.form("filename2")&"'"

strtj=mid(request.form("filename2"),instrrev(file1,"\"),(instrrev(file1,".")-instrrev(file1,"\")-1)) if file1="" then

response.write "请选择您要导入的Excel表!

"

%>

上一页