如何用asp读取Excel文件(比较全面)

1.ASP对Excel的基本操作

(1) 建立Excel对象

创建Excel对象可以通过下面的代码来实现:

<%

set objExcelApp = CreateObject("Excel.Application") objExcelApp.DisplayAlerts = false '不显示警告objExcelApp.Application = false '不显示界面

%>

(2) 新建Excel文件

新建Excel文件可以通过以下代码来实现:

<%

objExcelApp.WorkBooks.add

set objExcelBook = objExcelApp.ActiveWorkBook

set objExcelSheets = objExcelBook.Worksheets

set objExcelSheet = objExcelBook.Sheets(1)

%>

(3) 读取已有的Excel文件

读取已有的Excel文件可以通过下面的代码来实现

<%

strAddr = Server.MapPath(".")

objExcelApp.WorkBooks.Open(strAddr & "TempletTable.xls") set objExcelBook = objExcelApp.ActiveWorkBook

set objExcelSheets = objExcelBook.Worksheets

set objExcelSheet = objExcelBook.WorkSheets(1)

%>

(4) 另存Excel文件

另存Excel文件可以通过以下代码来实现

<%

objExcelBook.SaveAs strAddr & "templateTables.xls"

%>

(5) 保存Excel文件

保存Excel文件可以通过以下代码来实现:

<%

objExcelBook.Save

%>

(6) 退出Excel操作

<%

objExcelApp.Quit '一定要退出

set objExcelApp = nothing

%>

2 读取Excel文件的实例

<%

set xlApp = server.CreateObject("Excel.Application")

strsource = server.MapPath("xl.xls")

set xlbook = xlApp.WorkBooks.Open(strsource)

set xlsheet = xlbook.Worksheets(1)

i = 1

response.write "

"

while xlsheet.cells(i,1) <> ""

response.write "

"

response.write "

"

response.write "

"

response.write "

"

response.write "

"

i = i + 1

wend

response.write "

" & xlsheet.Cells(i,1) & "	" & xlsheet.Cells(i,2) & "	" & xlsheet.Cells(i,3) & "

"

set xlsheet = nothing

set xlbook = nothing

xlApp.quit

'千万记住要加这一句，否则每运行一次你的机器里就增加一个Excel进程，而且无法释放'set xlApp = nothing 是不行的

%>

================================================================ 3.怎样将数据从Excel导入到SQL Server中

(1)

<%

sub dataIntoSqlServer_ceritificate(strFileName,strSheetName,myConn)

dim myConnection

dim strName

dim rsXsl,rsSql

dim myConn_Xsl

dim cmd

dim i,j

dim maxId

strName = strFileName

set myConnection = Server.CreateObject("ADODB.Connection")

set rsXsl = Server.CreateObject("ADODB.Recordset")

set rsSql = Server.CreateObject("ADODB.Recordset")

set cmd = server.CreateObject("mand")

cmd.ActiveConnection = myConn

myConn_Xsl = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strName & _ ";Extended Properties=Excel 8.0"

'打开连接

myconnection.open myConn_Xsl

'打开表

str_Xsl = "select * from [" & strSheetName & "$]"

rsXsl.open str_Xsl,myconnection,1,1

j = 1

Do while not rsXsl.eof

'取出最大值

str_sql = "select Max(id) as maxId from exceltosql"

rsSql.open str_Sql,myConn,1,3

if Not rsSql.eof then

if not isNull(rsSql("maxId")) then

maxId=CLng(rsSql("maxId")) + 1

else

maxId = 1

end if

else

maxId = 1