在ASP中利用ADO显示Excel文件内容的函数

 

Function  SwitchExcelInfo(xlsFileName)
Dim  xlsStr
Dim  rs
Dim  i,j,k
Dim  ExcelConn
Dim  ExcelFile
Dim  objExcelApp
Dim  objExcelBook
Dim  bgColor
Dim  Er

Er  =   False
xlsStr  =   ""

If  xlsFileName  =   ""   Then
    Er  =   True
End   If

If  Er  =   False   AND   UCase ( Mid (file.fileName, InStrRev (file.filename,  " . " ), Len (file.Filename))) <> " xls "   Then
    Er  =   False
End   If

If  Er  =   False   Then
ExeclFile  =  Server.MapPath(xlsFileName)
Set  objExcelApp  =   CreateObject ( " Excel.Application " ) 
objExcelApp.Quit
Set  objExcelApp  =   CreateObject ( " Excel.Application " ) 
objExcelApp.Quit
objExcelApp.DisplayAlerts  =   false   ' 不显示警告 
objExcelApp.Application.Visible  =   false   ' 不显示界面 
objExcelApp.WorkBooks.Open(ExeclFile) 
set  objExcelBook  =  objExcelApp.ActiveWorkBook 
reDim  arrSheets(objExcelBook.Sheets.Count)
For  i = 1   to  objExcelBook.Sheets.Count
arrSheets(i)  =  objExcelBook.Sheets(i).Name
Next
objExcelApp.Quit
Set  objExeclApp = nothing

Set  ExcelConn  =  Server.CreateObject( " ADODB.Connection " )
ExcelDriver  =   " Driver={Microsoft Excel Driver (*.xls)};DBQ= "   &  ExeclFile
ExcelConn.Open ExcelDriver
Set  rs  =  Server.CreateObject( " Adodb.RecordSet " )
For  i  =   1   to   UBound (arrSheets)
Sql  =   " SELECT * FROM [ " &  arrSheets(i)  & " $]  "
' '显示各工作表的名称
' 'xlsStr = xlsStr & "
" & arrSheets(i) & "
"
xlsStr  =  xlsStr  &   ""
rs.Open Sql,ExcelConn, 1 , 1
k  =   1
While   Not  rs.eof
If  k  Mod   2   <>   0   Then  bgColor  =   " bgColor=#E0E0E0 "   Else  bgColor  =   ""
xlsStr  =  xlsStr  &   ""
xlsStr  =  xlsStr  &   "" &  rs( 0 )  & ""
For  j  =   0   to  rs.Fields.Count - 1
xlsStr  =  xlsStr  &   "" &  rs(j)  & ""
Next
xlsStr  =  xlsStr  &   ""
rs.movenext
k  =  k  +   1
Wend
xlsStr  =  xlsStr  &   "
"
rs.Close
Next
ExcelConn.Close
Set  ExcelConn  =   Nothing
End   If
SwitchExcelInfo  =  xlsStr
End Function