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,objRSDim strConn,strSQL set objConn=Server.CreateObject("ADODB.Connection")set objRS=Server.CreateObject("ADODB.Recordset") excelFile = server.mappath(wenjian) '针对excel 2007strConn = "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,1objRS.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 = nothingwend '又到了各种关闭的时候conn.closeset conn = nothingobjRS.CloseobjConn.Closeset objRS = Nothingset 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 

您可能感兴趣的文章:

• PHP将Excel导入数据库及数据库数据导出至Excel的方法
• Drupal读取Excel并导入数据库实例
• 利用phpexcel把excel导入数据库和数据库导出excel实现
• PHP 如何利用phpexcel导入数据库
• Excel导入数据库时出现的文本截断问题解决方案
• ASP.NET下将Excel表格中的数据规则的导入数据库思路分析及实现
• C++ 中实现把EXCEL的数据导入数据库(ACCESS、MSSQL等)实例代码

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！