|
การอ่านข้อมูลจาก Microsoft Excel
มาดูตัวอย่างการดึงข้อมูลมาก Excel นะครับ
Sample1.asp
<%@ LANGUAGE = VBScript %>
<% Option Explicit %>
<%
Const CDATABASE = "database.xls"
Dim oConn
Dim oRs
Dim Index
Dim sColorcount, sColorstyle
Sub Readdb()
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Excel 8.0;DATABASE=" & Server.MapPath(CDATABASE)
' andere g?ltige Formate anstelle von "Excel 8.0" sind
' dBASE III, dBASE IV, dBASE 5.0, Paradox 3.x, Paradox 4.x, Paradox 5.x,
' Excel 3.0, Excel 4.0, Excel 5.0 (auch f?r Excel 95), Excel 97, HTML Import, Text
Set oRs = oConn.Execute("SELECT * FROM tabelle")
' -- Tabellenkopf
Response.write("<table cellspacing=""0"" cellpadding=""2"">") & vbcrlf
Response.write("<tr>") & vbcrlf
For Index=0 to (oRs.fields.count-1)
Response.write("<th>" & oRs(Index).Name & "</th>") & vbcrlf
Next
Response.write("<tr>") & vbcrlf
sColorcount = 1
' -- Tabelle
Do while (Not oRs.eof)
Response.write("<tr>") & vbcrlf
if (sColorcount and 1) = 0 Then sColorstyle = " class=""marked""" Else sColorstyle = ""
For Index=0 to (oRs.fields.count-1)
Response.write("<td" & sColorstyle & myAutoAlign(oRs(Index)) & ">" & myNumFormat(oRs(Index)) & "</td>") & vbcrlf
Next
Response.write("</tr>") & vbcrlf
oRs.MoveNext
sColorcount = sColorcount + 1
Loop
' -- Tabellenende
Response.write("</table>") & vbcrlf
oRs.close
oConn.close
End Sub
' -----------------------------------------------
' Hilfsfunktionen
' -----------------------------------------------
Function myNumFormat(sString)
'Const vbEmpty = 0
'Const vbNull = 1
'Const vbInteger = 2
'Const vbLong = 3
'Const vbSingle = 4
'Const vbDouble = 5
'Const vbCurrency = 6
'Const vbDate = 7
'Const vbString = 8
Select Case VarType(sString)
Case vbEmpty, vbNull
myNumFormat = " "
Case vbInteger, vbLong, vbSingle, vbDouble
myNumFormat = FormatNumber(sString, 2)
Case vbCurrency
myNumFormat = FormatCurrency(sString, 2)
Case vbDate
myNumFormat = FormatDateTime(sString, 0)
Case Else
myNumFormat = sString
End Select
End Function
Function myAutoAlign(sString)
Select Case VarType(sString)
Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
myAutoAlign = " align=""right"""
Case vbDate
myAutoAlign = " align=""center"""
Case Else
myAutoAlign = ""
End Select
End Function
%>
<html>
<head>
<title>Excel</title>
</head>
<body>
<b>Get Data From Excel</b>
<hr size="1" color="#000000">
<% call Readdb %>
</body>
</html>
|