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 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))&"  "&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