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 not objRS.eof
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
'重复的数据不做录入操作
if rs.eof then
'插入语句
'****excel中第一条不会被录入****
sql = "insert into ceshi (lname, old, sex, guojia, QQ)values ('" objRS(0) "', '" objRS(1) "', '" objRS(2) "', '" objRS(3) "', '" objRS(4) "')"
'执行插入
conn.execute(sql)
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
%>
再分享一个简化版的代码
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>"
response.end
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 not rse.eof
'名称不能为空
if trim(rse(0))>"" then
else
mess="第" hang "行名称为空，请检查！"
response.Write"script>alert('" mess "').window.location.href='updateFloor.asp'/script>"
response.End()
end if
rse.movenext
hang=hang+1
loop
rse.movefirst
do while not rse.eof
set rst=server.CreateObject("adodb.recordset")
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))"nbsp;nbsp;"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
rse.movenext
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 = conn2.execute(sql)
while not rs.eof
sql = "insert into xxx([a],[b],[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
