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
您可能感興趣的文章:- PHP將Excel導入數據庫及數據庫數據導出至Excel的方法
- Drupal讀取Excel并導入數據庫實例
- 利用phpexcel把excel導入數據庫和數據庫導出excel實現
- PHP 如何利用phpexcel導入數據庫
- Excel導入數據庫時出現的文本截斷問題解決方案
- ASP.NET下將Excel表格中的數據規則的導入數據庫思路分析及實現
- C++ 中實現把EXCEL的數據導入數據庫(ACCESS、MSSQL等)實例代碼
