帮忙一个上传Excel到数据库的问题-领先建站CMS提供服务商我们专注于CMS建站产品的研发

2335 查看	1 回复

主题：[求助]帮忙一个上传Excel到数据库的问题 [收藏主题]

≮雨滴≯ 当前离线

167 主题	1 广播	0 粉丝

级别:一年级

用户积分:1266 分
登录次数:106 次
注册时间:2009/3/23
最后登录:2015/2/11

≮雨滴≯ 发表于：2011/2/1 20:59:35 | 只看该作者查看该作者主题楼主

有生源找课程就上科汛课堂街!

由于想在系统中整合学生成绩系统，那么就要把Excel成绩导入到数据库中，现在的问题是用ASP导入成功，但是当数据库的学生考号与Excel的考号相同时，成绩无法导入（比如想半期考的成绩和期考的成绩放在一起，学生一查就能查到半期考和期考的成绩，字段为code），不知为什么，请高手帮忙看看代码！谢啦。

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />

<title>EXECL数据导入</title>

</head>

<body>

<p>

  <%

session.CodePage=936

Server.ScriptTimeOut=600000

set upload=new upload_5xsoft

set file=upload.file("file1")

if file.fileSize>1200000 then

%>

  <script>

alert("您选择的文件过大!");

window.close();

  </script>

  <% end if

if file.fileSize>0 then

filename=year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)

filename=filename+"."

filenameend=file.filename

filenameend=split(filenameend,".")

if filenameend(1)="xls" then

      filename=filename&filenameend(1)

      file.saveAs Server.mappath("uploadfile/"&filename)

else

      response.write "数据格式不对！"

      response.write "<a href=upload.asp>返回</a>"

  response.end()

end if

set file=nothing

else

      response.write "文件不能为空！"

      response.write "<a href=upload.asp>返回</a>"

  response.end()

End if

set upload=nothing

'上传XLS文件结束，下面从上传的XLS文件中读取数据写入到SQL数据库

strAddr=server.MapPath("uploadfile/"&filename)

set excelconn=server.createobject("adodb.connection")

excelconn.open "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strAddr+";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"

sql="select * from [Sheet1$]"

set rs=server.CreateObject("adodb.recordset")

rs.open sql,excelconn,1,1

if not(rs.bof and rs.eof) then

  rs.movenext

  do while not rs.eof

dim ConnStr,DB

set conn=server.createobject("adodb.connection")

DB = "../data/data2369.mdb"

ConnStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(db)

conn.open ConnStr

sql1="select * from data where code ='"&rs(0)&"'and xingzhi = '"&rs(33)&"'"

set rs1=server.CreateObject("adodb.recordset")

rs1.open sql1,conn,1,3

if (rs1.eof and rs1.bof) then

   rs1.addnew

rs1("code")=rs(0)

   rs1("name")=rs(1)

   rs1("class")=rs(2)

   rs1("yuwen")=rs(3)

rs1("yuwenb")=rs(4)

rs1("yuwend")=rs(5)

   rs1("shuxue")=rs(6)

rs1("shuxueb")=rs(7)

rs1("shuxued")=rs(8)

   rs1("yingyu")=rs(9)

rs1("yingyub")=rs(10)

rs1("yingyud")=rs(11)

rs1("wuli")=rs(12)

rs1("wulib")=rs(13)

rs1("wulid")=rs(14)

rs1("huaxue")=rs(15)

rs1("huaxueb")=rs(16)

rs1("huaxued")=rs(17)

rs1("shengwu")=rs(18)

rs1("shengwub")=rs(19)

rs1("shengwud")=rs(20)

rs1("zhengzhi")=rs(21)

rs1("zhengzhib")=rs(22)

rs1("zhengzhid")=rs(23)

rs1("lishi")=rs(24)

rs1("lishib")=rs(25)

rs1("lishid")=rs(26)

rs1("dili")=rs(27)

rs1("dilib")=rs(28)

rs1("dilid")=rs(29)

rs1("zongfen")=rs(30)

rs1("bangmin")=rs(31)

rs1("mingci")=rs(32)

rs1("xingzhi")=rs(33)

   rs1.update

   rs1.close

   else

  set rs1=nothing

dim ConnStr1,DB1

set conn=server.createobject("adodb.connection")

DB1 = "../data/data2369.mdb"

ConnStr1 = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(db)

conn.open ConnStr1

sql2="select * from exam"

set rs2=server.CreateObject("adodb.recordset")

rs2.open sql2,conn,1,3

rs2.addnew

rs2("code")=rs(0)

   rs2("name")=rs(1)

rs2("class")=rs(3)

rs2.update

rs2.close

end if

rs.movenext

  loop

end if

rs.close()

set rs=nothing

set rs1=nothing

excelconn.Close()

set excelconn=nothing

%>

  <script>

</script>

  <br>

</p>

<p>

  <script>alert("数据导入成功！");

  </script>



  <span class="STYLE1">数据批量导入完成！</span></p>

<p>返回<a href="chengji.asp">成绩管理</a>查看</p>

<p>

  <%

response.write("以下数据由于数据库已经存在相关记录，所以没有被输入！")

%>



  <%

dim rs3 ,sql3,rs4,sql4

set rs3=server.createobject("adodb.recordset")

sql3 = "select * from exam"

rs3.open sql3,conn,1,3

%>

</p>