最新消息:重新回归WordPress,我要比较认真的开始更新我的博客了。

ASP操作Excel技术总结

程序问题 hanlei 659浏览

笔者要用ASP将EXCEL文档导入SQL数据库,在网上找了一下大至有以下两种方法
一,是用Excel.Application,以前没用过,但我用了以后打开页面很慢,似乎是直接打开了EXCEL程序。不解
1、建立Excel对象
setobjExcelApp=CreateObject(“Excel.Application”)
objExcelApp.DisplayAlerts=false不显示警告
objExcelApp.Application.Visible=false不显示界面
2、新建Excel文件
objExcelApp.WorkBooks.add
setobjExcelBook=objExcelApp.ActiveWorkBook
setobjExcelSheets=objExcelBook.Worksheets
setobjExcelSheet=objExcelBook.Sheets(1)
3、读取已有Excel文件
strAddr=Server.MapPath(“.”)
objExcelApp.WorkBooks.Open(strAddr&”TempletTable.xls”)
setobjExcelBook=objExcelApp.ActiveWorkBook
setobjExcelSheets=objExcelBook.Worksheets
setobjExcelSheet=objExcelBook.Sheets(1)
4、另存Excel文件
objExcelBook.SaveAsstrAddr&”TempTable.xls”
5、保存Excel文件
objExcelBook.Save(笔者测试时保存成功,页面报错。)
6、退出Excel操作
objExcelApp.Quit一定要退出
setobjExcelApp=Nothing
7、在一个范围内插入数据
objExcelSheet.Range(“B3:k3”).Value=Array(“67″,”87″,”5″,”9″,”7″,”45″,”45″,”54″,”54″,”10″)
8、在一个单元格内插入数据
objExcelSheet.Cells(3,1).Value=”InternetExplorer”
二,就是用ODBC了,和访问ACCESS差不多,还不错
Set objConnection = Server.CreateObject(“ADODB.Connection”)
Driver = “Driver={Microsoft Excel Driver (*.xls)};”
SourceType = “SourceType=DBF;”
DBPath = “DBQ=” + Server.MapPath(“xqdr.xls”)
objConnection . Open( Driver + SourceType + DBPath)
sqlstatement=”Select * From [Sheet2$]”
Set rs1=Server. CreateObject(“ADODB. Recordset”)
rs1.LockType=3
rs1.CursorType=3
rs1.CursorLocation=3
rs1.Open sqlstatement,objConnection
While Not rs1.eof
response. write rs1(0)&”

rs1.movenext
Wend
要注意的是, [Sheet2$]的格式,是[表名+$]

转载请注明:HANLEI'BLOG » ASP操作Excel技术总结