IDC资讯中心
<%
on error resume next
导入excel电子表格数据到sql sever数据库 by dicky 2004-12-27 16:41:12 qq:25941
function open_conn(sqldatabasename,sqlpassword,sqlusername,sqllocalname,sqlconn)
创建数据库连接对象并打开数据库连接
dim connstr
sql server数据库连接参数：数据库名、用户密码、用户名、连接名（本地用local，外地用ip）
connstr = "provider=sqloledb; user id=" & sqlusername & "; password=" & sqlpassword & "; initial catalog = " & sqldatabasename & "; data source=" & sqllocalname & ";"
set sqlconn = server.createobject("adodb.connection")
sqlconn.open connstr
if err then
err.clear
set sqlconn = nothing
response.write "数据连接错误!"
response.end
end if
end function
function close_conn(sqlconn)
关闭数据库连接并清除数据库连接对象
if isobject(sqlconn) then
sqlconn.close
set sqlconn = nothing
end if
end function
call open_conn("shat_edg","","sa","(local)",sqlconn) 打开sql server数据库连接
function get_emp_cnname(ntaccnt)
根据用户nt帐号得到用户中文名
dim sql,rs
sql = "select emp_cname from rf_employee where emp_ntaccnt="&ntaccnt&""
set rs = server.createobject("adodb.recordset")
rs.open sql,sqlconn,1,1
if rs.eof then
get_emp_cnname = ""
else
get_emp_cnname = rs("emp_cname")
end if
rs.close
set rs = nothing
end function %>
<html>
<head>
<title>导入excel电子表格数据到sql sever数据库</title>
<body bgcolor="#acd9af">
<center><b>导入excel电子表格数据到sql sever数据库</b></center>
<form method="post" name="form1">
請選擇數據源: <input type="file" name="file" size="40" title="请选择需要導出的excel文件"> <input type=submit name=submit value="開始導出">
<br>請選擇目標表:
<select name="table" title="请选择需要导入数据的表">
<option></option>
<% dim rssqldatabasetable
set rssqldatabasetable = sqlconn.openschema(20)
do while not rssqldatabasetable.eof %>
<option<%if trim(request("table"))=rssqldatabasetable(2) then response.write " selected"%>><%=rssqldatabasetable(2)%></option>
<% rssqldatabasetable.movenext:loop
set rssqldatabasetable = nothing %>
</select>
</form>
<font color=blue>導出過程中請不要刷新頁面！</font><br>
<%
if right(trim(request("file")),3) = "xls" then
response.write "<font color=red>您所導出的excel文件是："&trim(request("file"))&"</font><br>"
end if
=====================asp读取excel注事项=====================================
i)将excel97或excel2000生成的xls文件（book）看成一个数据库，其中的每一个工作表(sheet)看成数据库表
ii)ado假设excel中的第一行为字段名.所以你定义的范围中必须要包括第一行的内容
iii)excel中的行标题（即字段名）不能够包含数字. excel的驱动在遇到这种问题时就会出错的。例如你的行标题名为“f1”
iiii)如果你的excel电子表格中某一列同时包含了文本和数字的话,那么excel的odbc驱动将不能够正常, 处理这一行的数据类型，你必须要保证该列的数据类型一致
e-mail:kaxue@hotmail.com qq:484110 homepage:www.flyday.net
整理时间：thursday, may 23, 2002 5:54 pm win2000server+iis5 测式通过
============================================================================
if trim(request("file")) = "" then
response.write "<font color=red>對不起，請選擇需要導出的excel文件！</font>"
response.end
elseif right(trim(request("file")),3) <> "xls" then
response.write "<font color=red>對不起，請確定您要導出的是excel文件！</font>"
response.end
else
列出所選擇的excel文件中的所有工作表
dim execlfile,objexcelapp,objexcelbook
execlfile = trim(request("file"))
set objexcelapp = createobject("excel.application")
objexcelapp.quit
objexcelapp.displayalerts = false 不显示警告
objexcelapp.application.visible = false 不显示界面
objexcelapp.workbooks.open(execlfile)
set objexcelbook = objexcelapp.activeworkbook
redim arrsheets(objexcelbook.sheets.count)
for i=1 to objexcelbook.sheets.count
arrsheets(i) = objexcelbook.sheets(i).name
response.write arrsheets(i)
next
objexcelapp.quit
set objexeclapp = nothing
列出所選擇的excel文件中的所有工作表
end if
dim conn,driver,dbpath,rs,sqlinsert,sqldelete
建立connection对象
set conn = server.createobject("adodb.connection")
driver = "driver={microsoft excel driver (*.xls)};"
dbpath = "dbq=" & server.mappath( "hrb.xls" )
dbpath = "dbq=" & trim(request("file"))
调用open 方法打开数据库
conn.open driver & dbpath
dsn连接方式
conn.open "dsn=test"
注意 表名一定要以下边这种格式 "[表名$]" 书写
for i = 1 to ubound(arrsheets)開始循環所有工作表
sql = "select * from ["& arrsheets(i) &"$] "
set rs = conn.execute(sql)
if rs.eof and rs.bof then
response.write "没有找到您需要的数据!!<br>"
else
response.write "<font color=blue>工作表:</font><font color=green>"&arrsheets(i)&"</font><br>"
do while not rs.eof
sqlinsert = "insert into "&trim(request("table"))&" (edg_project_name,edg_project_no,edg_project_vm,edg_project_vm_cnname,edg_project_m,edg_project_m_cnname,edg_project_director,edg_project_director_cnname) values ("&trim(rs(0))&","&trim(rs(1))&","&trim(rs(2))&","&trim(rs(2))&"("&get_emp_cnname(trim(rs(2)))&")"&","&trim(rs(3))&","&trim(rs(3))&"("&get_emp_cnname(trim(rs(3)))&")"&","&trim(rs(4))&","&trim(rs(4))&"("&get_emp_cnname(trim(rs(4)))&")"&")"
此处插入的值根据实际数据库结构进行调整
sqlconn.execute(sqlinsert)插入excel表格裏所有東東
rs.movenext
loop
response.write "<font color=red>恭喜，成功導出excel文件中工作表[</font><font color=blue>"&arrsheets(i)&"</font><font color=red>]的數據到sql server數據表[<font color=blue>"&trim(request("table"))&"</font>]中！^_^</font><br>"
end if
rs.close
set rs = nothing
next循環所有工作表結束
call close_conn(conn) 关闭excel数据库连接
call close_conn(sqlconn) 关闭sql server数据库连接 %>
</body>
</html>
