ASP操作EXCEL和ACCESS的方法1

这种方法是通过过程的形式进行调用  效果不错

先查询Excel  再导入到ACCESS中

功能函数如下：

function exctoacc(excpath,mdbpath)
    Dim excConn,Coon
    Dim excStrConn,strConn
    Dim excrs,rs
    Dim excSql
    Set excconn=Server.CreateObject("ADODB.Connection") 
    Set excrs = Server.CreateObject("ADODB.Recordset")
    excStrConn="Driver={Microsoft Excel Driver (*.xls)};DriverId=790; DBQ="&Server.MapPath(excpath)
    excconn.Open excStrConn
    excSql="select * from [Sheet1$]"  '查询excel语句
    excrs.Open excSql,excconn,2,2  
        set rs=server.createObject("ADODB.Recordset")
        Set conn=Server.CreateObject("ADODB.Connection") 
        StrConn="provider=microsoft.jet.oledb.4.0; data source="&Server.MapPath(mdbpath)
        conn.Open StrConn
        sql="select * from grade"  '修改
        rs.open sql,conn,1,3
            do while Not excrs.EOF 
                rs.addnew
                    for i=0 to excrs.Fields.Count-1
                        rs(i+1)=excrs(i)
                    next 
                rs.update
                excrs.MoveNext
            Loop
        rs.close
        set rs=nothing
    excrs.close
    set excrs=nothing
    excConn.close
    set excConn=nothing
    conn.close
    set conn=nothing
    exctoacc="数据导入成功！"
End function

函数调用方法很简单：

response.write exctoacc("data.xls","Holmesian.mdb")

ASP操作EXCEL和ACCESS的方法2

'上传数据库
'文件路径及其名称存入filename

connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("test.mdb")

'这里用来写导入数据库的程序
         kl=FileName
         op=len(kl)
        op2= mid(kl,3,op-2)     
    strAddr =   Server.MapPath(""&kl)
    set excelconn=server.createobject("adodb.connection")
    'strAddr = file
    ''输出源文件名
    excelconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strAddr
    ''建立excel记录集
    set rs2=server.createobject("adodb.recordset")
set rs3= Server.CreateObject("ADODB.Recordset")
  set rs3 = excelconn.OpenSchema(20) 
  op=rs3("TABLE_NAME")
    sql="select * from "+"["+ op+"]"
    rs2.open sql,excelconn,1,1
    '找到表

connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("test.mdb")
set conn = server.createobject("ADODB.CONNECTION")

   conn.open connstr
     set rs4= Server.CreateObject("ADODB.Recordset")
    rs4.open "select * from Sheet1",conn,3,3


'导入过程

   for j = 1 to rs2.recordCount

   rs4.addnew
    for i = 0 to rs2.Fields.Count-1

    rs4.Fields(i)=rs2.Fields(i)

    next
    rs2.movenext
  
    next
  
end if

conn.close
response.write "成功 "
response.redirect "index.asp"

直接放在需要使用的地方

ASP操作EXCEL和ACCESS的方法3

<%dim conn     '定义一个连接变量
dim conn2        '定义第二个连接变量
'On Error Resume Next
Server.ScriptTimeOut = 999999       '超时时间
set conn=CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source="&Server.MapPath("st_info.mdb") '要导入的数据库名称，这里是St_info.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="&Server.MapPath("st_info.xls") '要导入的EXCEL表名称
'-----------------------------------------------------------------------------------------------------------------
'这里要说明的是，数据库和Excel表和该文件必须在网站同一目录里
'-----------------------------------------------------------------------------------------------------------------
sql = "SELECT * FROM [Sheet1$]"      '要导入的Excel数据里面的表的名称，后面一定要加$
set rs = conn2.execute(sql)
while not rs.eof
sql = "insert into 200501GK([CORP_CODE],[GB],[CORP_NAME],[UNIT],[BZ],[PEDM]) values('"& fixsql(rs(0)) &"','"& fixsql(rs(1)) &"','"& fixsql(rs(2)) &"','"& fixsql(rs(3)) &"','"&fixsql(rs(4))&"',"&fixsql(rs(5))&")"
'response.write sql
'response.end
conn.execute(sql)
rs.movenext
Response.Write "正在插入 "&sql&"<Br>"
Response.Flush
wend


conn.close
set conn = nothing
conn2.close
set conn2 = Nothing

If Err = 0 Then
Response.Write "导入成功"
Else
Response.Write "导入失败!"
End If

function fixsql(str)
dim newstr
newstr = str
if isnull(newstr) then
newstr = ""
else
newstr = replace(newstr,"'","''")
end if
fixsql = newstr
end Function
%>