一个Excel导入到Access的实例
Excel导入Access执行文件 xlstomdb.asp ,代码如下:
- <%
- dim conn,data
- dim conn2,data2
- data="data.mdb"
- data2="data.xls"
- set conn=CreateObject("ADODB.Connection")
- conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(data)
- set conn2=CreateObject("ADODB.Connection")
- conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended properties=Excel 5.0;Data Source=" & Server.MapPath(data2)
- sql = "SELECT * FROM [Sheet1$]"
- set rs = conn2.execute(sql)
- while not rs.eof
- sql = "insert into info([公司名称],[公司地址],[联系人],[电话],[传真]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"','"& fixsql(rs(4)) &"')"
- conn.execute(sql)
- rs.movenext
- wend
- conn.close
- set conn = nothing
- conn2.close
- set conn2 = nothing
- Response.Write "<div style='margin:100px;font-size:14px;text-align:center'>操作成功!</div>"
- function fixsql(str)
- dim newstr
- newstr = str
- if isnull(newstr) then
- newstr = ""
- else
- newstr = replace(newstr,"'","''")
- end if
- fixsql = newstr
- end function
- %>
需要数据库data.mdb,表info;Excel文件data.xls;