asp实现excel中的数据导⼊数据库
asp实现excel中的数据导⼊数据库
<% Response.CodePage=65001%>
<% Response.Charset="UTF-8" %>
<%
wenjian = request.Form("select")
'获取⽂件扩展名
ext = FileExec(wenjian)
'判断⽂件扩展名
if ext <> "xls" then
response.Write("<script>alert('⽂件类型不对，请核实！');window.location.href='index.html';</script>")
response.End()
end if
Dim objConn,objRS
Dim strConn,strSql
set objConn=Server.CreateObject("ADODB.Connection")
set objRS=Server.CreateObject("ADODB.Recordset")
excelFile = server.mappath(wenjian)
'针对excel 2007
strConn = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & excelFile & ";" & "Extended Properties=Excel 8.0;"
objConn.Open strConn
strSql="SELECT * FROM [Sheet1$]"
objRS.Open strSql,objConn,1,1
objRS.MoveFirst
%><!--#include file="conn.asp"--><%
'循环excel中所有记录
while f
set rs = Server.CreateObject("Adodb.Recordset")
'查询语句
sql_s = "select * from ceshi where lname='" & objRS(0) & "' and old='" & objRS(1) & "' and sex='" & objRS(2) & "' and guojia='" & objRS(3) & "' and QQ='" & objRS(4) & "'"  rs.open sql_s, conn, 1, 1
'重复的数据不做录⼊操作
f then
'插⼊语句
'****excel中第⼀条不会被录⼊****
sql = "insert into ceshi (lname, old, sex, guojia, QQ)values ('" & objRS(0) & "', '" & objRS(1) & "', '" & objRS(2) & "', '" & objRS(3) & "', '" & objRS(4) & "')"
'执⾏插⼊
end if
objRS.MoveNext
rs.close
set rs = nothing
wend
'⼜到了各种关闭的时候
conn.close
set conn = nothing
objRS.Close
objConn.Close
set objRS = Nothing
set objConn = Nothing
response.Write("<script>alert('导⼊成功');window.location.href='index.html';</script>")
response.End()
Function FileExec(fileName)
FileExec = Mid(fileName,Instr(fileName,".")+1,Len(fileName)-Instr(fileName,"."))
End Function
%>
asp数据
再分享⼀个简化版的代码
wenjian=request.Form("floor")
fileext=mid(wenjian,InStrRev(wenjian,".")+1)
if lcase(fileext)<>"xls" then
response.write "<script>alert ('⽂件格式不对，请上传Excel⽂件');window.location.href='updateFloor.asp';</script>"
end if
set conne=server.CreateObject("ADODB.Connection")
connStre="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath( ""&wenjian&"" )&";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"
conne.open connStre
Sqle="select * from [sheet1$] "
Set rse = Server.CreateObject("ADODB.Recordset")
rse.open sqle,conne,1,1
'验证
hang=2
do while f
'名称不能为空
if trim(rse(0))<>"" then
else
mess="第"& hang &"⾏名称为空，请检查！"
response.Write"<script>alert('"& mess &"').window.location.href='updateFloor.asp'</script>"
response.End()
end if
hang=hang+1
loop
do while f
set rst=server.CreateObject("dset")
sqlt="select * from Sellman"
rst.open sqlt,conn,1,3
rst.addnew()
rst("CompanyName")=c2(rse(0))
rst("CompanyInfo")=c2(rse(1))
rst("address")=c2(rse(2))
rst("tel")=c2(rse(3))&"  "&c2(rse(7))
rst("Fax")=c2(rse(4))
rst("linkman")=c2(rse(5))
rst("Homepage")=c2(rse(8))
rst("Email")=c2(rse(6))
rst.update()
rst.close
set rst=nothing
loop
rse.close
set rse=nothing
response.Write "<script>alert('导⼊成功!');location.href='updateFloor.asp';</script>"
其实简单的说象access 数据库⼀样，把excel⽂件打开，再进⾏读再写到access中你要写到sqlserver中就把写的过程改⼀下就成了看下代码：
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 = ute(sql)
while f
sql = "insert into xxx([a],[b],[c],[d]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"')"
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。