excel导入access

1,导入到数据库的函数
function import_db(filepath,userid)
  bz=0
 if filepath<>"" then
  e_open filepath 
  bz=1
  sql="select * from [sheet1$]"
set rr=e_conn.execute(sql)
do while not rr.eof 
sql="insert into resource_xx_xx(catalogName,subCatalogName,breed,spec,material,city,factory,warehouse,price,quantity,contactorName,member_id) values('"&rr(0)&"','"&rr(1)&"','"&rr(2)&"','"&rr(3)&"','"&rr(4)&"','"&rr(5)&"','"&rr(6)&"','"&rr(7)&"','"&rr(8)&"','"&rr(9)&"','"&rr(10)&"','"&userid&"')"
conn.execute(sql)  
rr.movenext
loop
rr.close
set rr=nothing
  e_close()
bz=2
 
 end if
 import_db=bz
end function
2.连接execl的过程
dim e_Conn
sub e_open(filepath)
'建立Connection对象 
Set e_Conn = Server.CreateObject("ADODB.Connection") 
Driver = "Driver={Microsoft Excel Driver (*.xls)};" 
DBPath = "DBQ=" & Server.MapPath(filepath) 
'调用Open 方法打开数据库 
e_Conn.Open Driver & DBPath
end sub 
3,把数据库导入excel
function export_db(FileNames,userid)
Set ExcelApp =CreateObject("Excel.Application") 
ExcelApp.Application.Visible = True 
Set ExcelBook = ExcelApp.Workbooks.Add 
ExcelBook.WorkSheets(1).cells(1,1).value ="品种大类"  
ExcelBook.WorkSheets(1).cells(1,2).value = "品种小类" 
ExcelBook.WorkSheets(1).cells(1,3).value = "品名" 
ExcelBook.WorkSheets(1).cells(1,4).value = "规格" 
ExcelBook.WorkSheets(1).cells(1,5).value = "材质"
ExcelBook.WorkSheets(1).cells(1,6).value = "城市" 
ExcelBook.WorkSheets(1).cells(1,7).value = "钢厂" 
ExcelBook.WorkSheets(1).cells(1,8).value = "仓库" 
ExcelBook.WorkSheets(1).cells(1,9).value = "价格" 
ExcelBook.WorkSheets(1).cells(1,10).value = "数量" 
ExcelBook.WorkSheets(1).cells(1,11).value = "联系人"  

sql="select * from resource_xx_xx where member_id='"&userid&"' order by id desc"
set rr=conn.execute(sql)
i=2
do while not rr.eof
ExcelBook.WorkSheets(1).cells(i,1).value = rr("catalogName") 
ExcelBook.WorkSheets(1).cells(i,2).value = rr("subCatalogName") 
ExcelBook.WorkSheets(1).cells(i,3).value = rr("breed") 
ExcelBook.WorkSheets(1).cells(i,4).value = rr("spec")
ExcelBook.WorkSheets(1).cells(i,5).value = rr("material")
ExcelBook.WorkSheets(1).cells(i,6).value = rr("city")
ExcelBook.WorkSheets(1).cells(i,7).value = rr("factory")
ExcelBook.WorkSheets(1).cells(i,8).value = rr("warehouse")
ExcelBook.WorkSheets(1).cells(i,9).value = rr("price")
ExcelBook.WorkSheets(1).cells(i,10).value =rr("quantity")
ExcelBook.WorkSheets(1).cells(i,11).value =rr("contactorName")
rr.movenext
i=i+1
loop
rr.close
set rr=nothing

Excelbook.SaveAs FileNames  
ExcelApp.Application.Quit '导出以后退出Excel 
Set ExcelApp = Nothing '注销Excel对象 
export_db=1
end function

♡ 点赞 0

☆ 收藏 0

留言区（共0条）