使用server物件Excel.Application來存取(建立)excel檔案
作者:CHOME 日期:2012-05-09
使用這個物件前，必需先作好設定，不然Set xlApp=server.CreateObject("Excel.Application") ，會發生
錯誤類型：
伺服器物件, ASP 0178 (0x80070005)
當檢查使用權限時 Server.CreateObject 的呼叫失敗。拒絕存取此物件。
/ASPTEST/create_excel.asp, line 12
控制台->系統管理工具->元件服務->電腦->我的電腦->DCOM設定->Microsoft Excel 應用程式->內容->安全設定" 將網際網路使用者帳號新增這個方法
存取權限，新增Everyone，遠端存取打勾...
'建立Excel對象
set objExcelApp=CreateObject("Excel.Application")
'不顯示警告
objExcelApp.DisplayAlerts=false
'不顯示界面
objExcelApp.Application.Visible=false
'新建Excel文件
objExcelApp.WorkBooks.add
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
'讀取已有Excel文件
strAddr=Server.MapPath("xls/Table.xls")
objExcelApp.WorkBooks.Open strAddr
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
'更改Sheets1為會員資料表
objExcelSheet.name="會員資料表"
'另存Excel文件
objExcelBook.SaveAs strAddr&"\Temp\Table.xls"
'保存Excel文件
objExcelBook.Save
'退出Excel操作(一定要退出否則檔案會一直處於唯讀)
objExcelApp.Quit
set objExcelApp=Nothing
'指定欄位值
objExcelSheet.Cells(i,j).value="指定這個欄位的值"
註:
ASP最好在CreateObject("Excel.Application")前加上
On Error Resume Next
最後加上
objExcelApp.Quit
set objExcelApp=Nothing
(避免因為錯誤訊息中斷程序執行，而使Excel檔一直處於唯讀狀態)
底下這個示範是：建立一個EXCEL新檔，並依選取條件將Access檔符合條件的資錄匯入EXCEL
1.EXCEL資料表名=選取的歌手名
2.EXCEL檔名=歌手名2008MMDDHHMMSS.XLS
3.Access資料表中的欄位屬性若為時間，需先強制型態轉換成文字，A=Cstr(A)，再寫入..
http://www.chome.idv.tw/ASPTEST/create_excel.asp
實際RUN起來的結果：
建立一個EXCEL檔，只有一張資料表單，50~80筆資料錄，檔案大小20~30K，費時1~2秒..
(WebServer：IIS 處理器：Intel Core 2 Quad Q9550 記憶體：4G 作業系統XP Sp3)
<HEAD><TITLE>建立一個EXCEL檔並依選取條件將Access檔符合條件的資錄錄匯入EXCEL</TITLE>
<META content="text/html; charset=big5" http-equiv=Content-Type>
<link REL="stylesheet" HREF="style.css" TYPE="text/css">
</HEAD>
<body bgcolor=ffffff marginheight=0 marginwidth=0 leftmargin=0 topmargin=0>
<%
area=request("area")
areana=request("areana")
If request("Request_Method")="POST" Then
reareana=request("reareana")
Set objExcelApp=server.CreateObject("Excel.Application")
objExcelApp.DisplayAlerts=false
objExcelApp.Application.Visible=false
objExcelApp.WorkBooks.add
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
objExcelSheet.name=reareana
objExcelSheet.Cells(1,1).value="id"
objExcelSheet.Cells(1,2).value="area"
objExcelSheet.Cells(1,3).value="areana"
objExcelSheet.Cells(1,4).value="reareana"
objExcelSheet.Cells(1,5).value="filename"
objExcelSheet.Cells(1,6).value="url"
objExcelSheet.Cells(1,7).value="gd"
objExcelSheet.Cells(1,8).value="hits"
objExcelSheet.Cells(1,9).value="last_update"
I=1
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("song.mdb")
conn.open Provider & DBPath
Set rs = Server.CreateObject("adodb.recordset")
sqlstr="Select * From song where areana='"&areana&"' And reareana='"&reareana&"' order by [ID] desc"
rs.open sqlstr,conn,3,2
Do until rs.EOF
i=i+1
objExcelSheet.Cells(i,1).value=rs("id")
objExcelSheet.Cells(i,2).value=rs("area")
objExcelSheet.Cells(i,3).value=rs("areana")
objExcelSheet.Cells(i,4).value=rs("reareana")
objExcelSheet.Cells(i,5).value=rs("filename")
objExcelSheet.Cells(i,6).value=rs("url")
objExcelSheet.Cells(i,7).value=rs("gd")
objExcelSheet.Cells(i,8).value=rs("hits")
last_update=rs("last_update")
A=Cstr(last_update)
objExcelSheet.Cells(i,9).value=A
rs.MoveNext
Loop
rs.close
conn.close
strpath="excel/"&reareana
yr=Year(now)
mth=Month(now)
if mth<10 then mth="0"&mth
dy=day(now)
if dy<10 then dy="0"&dy
hr=hour(now)
if hr<10 then hr="0"&hr
mte=minute(now)
if mte<10 then mte="0"&mte
sec=second(now)
if sec<10 then sec="0"&sec
strpath=strpath&yr&mth&dy&hr&mte&sec&".xls"
strAddr=Server.MapPath(strpath)
objExcelBook.SaveAs strAddr
objExcelApp.Quit
set objExcelApp=Nothing
Response.Write "Excel存檔實體路徑檔名："&strAddr&"<BR><BR>"
Response.Write "Excel虛擬相對路徑檔名：<A HREF="&strpath&">"&strpath&"</A><BR><BR>"
Response.Write "Excel檔的資料表單名稱："&reareana&"<BR><BR>"
Response.Write "<A HREF=/><FONT SIZE=5><B>回首頁</B></FONT></A><BR>"
Response.End
End If
%>
<CENTER>
<form action=create_excel.asp method=post>
<table border="1" align="center" width=400>
<tr><td width=75>音樂分類</td><td width=325>
<select name=WebUrl size=1 onChange='location.href=this.options[this.selectedIndex].value;' style='font-size: 12pt; border: 1 solid #000000'>
<option value='' Selected>選擇音樂分類</option>
<%
Set conn=Server.CreateObject("ADODB.Connection")
param = "driver={Microsoft Access Driver (*.mdb)}"
conn.Open param & ";dbq=" & Server.MapPath("song.mdb")
Set rs1=Server.CreateObject("ADODB.Recordset")
SortSql="Select * From area order By [area]"
rs1.Open SortSql, conn, 1,3
Do until rs1.EOF
If areana=rs1("areana") Then
response.write "<option value=Create_excel.asp?areana="&rs1("areana")&" Selected>"&rs1("areana")&"</option>"
Else
response.write "<option value=Create_excel.asp?areana="&rs1("areana")&">"&rs1("areana")&"</option>"
End If
rs1.MoveNext
Loop
rs1.close
Response.write "</select></td></tr>"
Select Case areana
Case "樂器演奏"
area=11
Case "古典歌謠"
area=12
Case "情調音樂"
area=13
Case "國語歌曲"
area=21
Case "台語歌曲"
area=22
Case "西洋歌曲"
area=23
Case "日韓歌曲"
area=24
Case "歌劇演唱"
area=25
Case "國語唱將"
area=27
Case "西洋唱將"
area=28
Case "一般視頻"
area=31
Case "高清視頻"
area=32
End Select
response.write "<input type='hidden' name='area' value='"&area&"'>"
response.write "<input type='hidden' name='areana' value='"&areana&"'>"
response.write "<td>音樂目錄</td><td><select name='reareana' size='1'>"
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("song.mdb")
conn.open Provider & DBPath
Set rs=Server.CreateObject("ADODB.Recordset")
IF areana<>"" Then
SortSql="Select * From List where areana='" &areana& "' order By [Recordid]"
Else
SortSql="Select * From List order By [Recordid]"
End If
rs.Open SortSql, conn, 1,3
Do until rs.EOF
response.write "<option value="&rs("reareana")&" selected>"&rs("reareana")&"</option>"
rs.MoveNext
Loop
rs.close
%>
</select></td></tr>
<tr align="center"><td colspan="2"><input type="submit" name="addphoto" value="建立 (<%=areana%>.xls) 檔 - 資料表_歌手名"><input type="reset" value="取消"></td></tr>
</table>
使用server.CreateObject("Excel.Application") 讀取已有Excel文件，並寫入Access
實例示範
錯誤類型：
伺服器物件, ASP 0178 (0x80070005)
當檢查使用權限時 Server.CreateObject 的呼叫失敗。拒絕存取此物件。
/ASPTEST/create_excel.asp, line 12
控制台->系統管理工具->元件服務->電腦->我的電腦->DCOM設定->Microsoft Excel 應用程式->內容->安全設定" 將網際網路使用者帳號新增這個方法
存取權限，新增Everyone，遠端存取打勾...
'建立Excel對象
set objExcelApp=CreateObject("Excel.Application")
'不顯示警告
objExcelApp.DisplayAlerts=false
'不顯示界面
objExcelApp.Application.Visible=false
'新建Excel文件
objExcelApp.WorkBooks.add
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
'讀取已有Excel文件
strAddr=Server.MapPath("xls/Table.xls")
objExcelApp.WorkBooks.Open strAddr
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
'更改Sheets1為會員資料表
objExcelSheet.name="會員資料表"
'另存Excel文件
objExcelBook.SaveAs strAddr&"\Temp\Table.xls"
'保存Excel文件
objExcelBook.Save
'退出Excel操作(一定要退出否則檔案會一直處於唯讀)
objExcelApp.Quit
set objExcelApp=Nothing
'指定欄位值
objExcelSheet.Cells(i,j).value="指定這個欄位的值"
註:
ASP最好在CreateObject("Excel.Application")前加上
On Error Resume Next
最後加上
objExcelApp.Quit
set objExcelApp=Nothing
(避免因為錯誤訊息中斷程序執行，而使Excel檔一直處於唯讀狀態)
底下這個示範是：建立一個EXCEL新檔，並依選取條件將Access檔符合條件的資錄匯入EXCEL
1.EXCEL資料表名=選取的歌手名
2.EXCEL檔名=歌手名2008MMDDHHMMSS.XLS
3.Access資料表中的欄位屬性若為時間，需先強制型態轉換成文字，A=Cstr(A)，再寫入..
http://www.chome.idv.tw/ASPTEST/create_excel.asp
實際RUN起來的結果：
建立一個EXCEL檔，只有一張資料表單，50~80筆資料錄，檔案大小20~30K，費時1~2秒..
(WebServer：IIS 處理器：Intel Core 2 Quad Q9550 記憶體：4G 作業系統XP Sp3)
復制內容到剪貼板
歌詞或引言或程序代碼
歌詞或引言或程序代碼<HEAD><TITLE>建立一個EXCEL檔並依選取條件將Access檔符合條件的資錄錄匯入EXCEL</TITLE>
<META content="text/html; charset=big5" http-equiv=Content-Type>
<link REL="stylesheet" HREF="style.css" TYPE="text/css">
</HEAD>
<body bgcolor=ffffff marginheight=0 marginwidth=0 leftmargin=0 topmargin=0>
<%
area=request("area")
areana=request("areana")
If request("Request_Method")="POST" Then
reareana=request("reareana")
Set objExcelApp=server.CreateObject("Excel.Application")
objExcelApp.DisplayAlerts=false
objExcelApp.Application.Visible=false
objExcelApp.WorkBooks.add
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
objExcelSheet.name=reareana
objExcelSheet.Cells(1,1).value="id"
objExcelSheet.Cells(1,2).value="area"
objExcelSheet.Cells(1,3).value="areana"
objExcelSheet.Cells(1,4).value="reareana"
objExcelSheet.Cells(1,5).value="filename"
objExcelSheet.Cells(1,6).value="url"
objExcelSheet.Cells(1,7).value="gd"
objExcelSheet.Cells(1,8).value="hits"
objExcelSheet.Cells(1,9).value="last_update"
I=1
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("song.mdb")
conn.open Provider & DBPath
Set rs = Server.CreateObject("adodb.recordset")
sqlstr="Select * From song where areana='"&areana&"' And reareana='"&reareana&"' order by [ID] desc"
rs.open sqlstr,conn,3,2
Do until rs.EOF
i=i+1
objExcelSheet.Cells(i,1).value=rs("id")
objExcelSheet.Cells(i,2).value=rs("area")
objExcelSheet.Cells(i,3).value=rs("areana")
objExcelSheet.Cells(i,4).value=rs("reareana")
objExcelSheet.Cells(i,5).value=rs("filename")
objExcelSheet.Cells(i,6).value=rs("url")
objExcelSheet.Cells(i,7).value=rs("gd")
objExcelSheet.Cells(i,8).value=rs("hits")
last_update=rs("last_update")
A=Cstr(last_update)
objExcelSheet.Cells(i,9).value=A
rs.MoveNext
Loop
rs.close
conn.close
strpath="excel/"&reareana
yr=Year(now)
mth=Month(now)
if mth<10 then mth="0"&mth
dy=day(now)
if dy<10 then dy="0"&dy
hr=hour(now)
if hr<10 then hr="0"&hr
mte=minute(now)
if mte<10 then mte="0"&mte
sec=second(now)
if sec<10 then sec="0"&sec
strpath=strpath&yr&mth&dy&hr&mte&sec&".xls"
strAddr=Server.MapPath(strpath)
objExcelBook.SaveAs strAddr
objExcelApp.Quit
set objExcelApp=Nothing
Response.Write "Excel存檔實體路徑檔名："&strAddr&"<BR><BR>"
Response.Write "Excel虛擬相對路徑檔名：<A HREF="&strpath&">"&strpath&"</A><BR><BR>"
Response.Write "Excel檔的資料表單名稱："&reareana&"<BR><BR>"
Response.Write "<A HREF=/><FONT SIZE=5><B>回首頁</B></FONT></A><BR>"
Response.End
End If
%>
<CENTER>
<form action=create_excel.asp method=post>
<table border="1" align="center" width=400>
<tr><td width=75>音樂分類</td><td width=325>
<select name=WebUrl size=1 onChange='location.href=this.options[this.selectedIndex].value;' style='font-size: 12pt; border: 1 solid #000000'>
<option value='' Selected>選擇音樂分類</option>
<%
Set conn=Server.CreateObject("ADODB.Connection")
param = "driver={Microsoft Access Driver (*.mdb)}"
conn.Open param & ";dbq=" & Server.MapPath("song.mdb")
Set rs1=Server.CreateObject("ADODB.Recordset")
SortSql="Select * From area order By [area]"
rs1.Open SortSql, conn, 1,3
Do until rs1.EOF
If areana=rs1("areana") Then
response.write "<option value=Create_excel.asp?areana="&rs1("areana")&" Selected>"&rs1("areana")&"</option>"
Else
response.write "<option value=Create_excel.asp?areana="&rs1("areana")&">"&rs1("areana")&"</option>"
End If
rs1.MoveNext
Loop
rs1.close
Response.write "</select></td></tr>"
Select Case areana
Case "樂器演奏"
area=11
Case "古典歌謠"
area=12
Case "情調音樂"
area=13
Case "國語歌曲"
area=21
Case "台語歌曲"
area=22
Case "西洋歌曲"
area=23
Case "日韓歌曲"
area=24
Case "歌劇演唱"
area=25
Case "國語唱將"
area=27
Case "西洋唱將"
area=28
Case "一般視頻"
area=31
Case "高清視頻"
area=32
End Select
response.write "<input type='hidden' name='area' value='"&area&"'>"
response.write "<input type='hidden' name='areana' value='"&areana&"'>"
response.write "<td>音樂目錄</td><td><select name='reareana' size='1'>"
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("song.mdb")
conn.open Provider & DBPath
Set rs=Server.CreateObject("ADODB.Recordset")
IF areana<>"" Then
SortSql="Select * From List where areana='" &areana& "' order By [Recordid]"
Else
SortSql="Select * From List order By [Recordid]"
End If
rs.Open SortSql, conn, 1,3
Do until rs.EOF
response.write "<option value="&rs("reareana")&" selected>"&rs("reareana")&"</option>"
rs.MoveNext
Loop
rs.close
%>
</select></td></tr>
<tr align="center"><td colspan="2"><input type="submit" name="addphoto" value="建立 (<%=areana%>.xls) 檔 - 資料表_歌手名"><input type="reset" value="取消"></td></tr>
</table>
使用server.CreateObject("Excel.Application") 讀取已有Excel文件，並寫入Access
實例示範
復制內容到剪貼板 歌詞或引言或程序代碼
歌詞或引言或程序代碼<HEAD><TITLE>建立一個EXCEL檔並依選取條件將Access檔符合條件的資錄錄匯入EXCEL</TITLE>
<META content="text/html; charset=big5" http-equiv=Content-Type>
<link REL="stylesheet" HREF="style1.css" TYPE="text/css">
</HEAD>
<body bgcolor=ffffff marginheight=0 marginwidth=0 leftmargin=0 topmargin=0>
<%
If request("Request_Method")="POST" Then
areana=request("areana")
strAddr=Server.MapPath("excel/"&areana)
'On Error Resume Next
'建立Excel對象
set objExcelApp=CreateObject("Excel.Application")
'不顯示警告
objExcelApp.DisplayAlerts=false
'不顯示界面
objExcelApp.Application.Visible=false
'讀取已有Excel文件
objExcelApp.WorkBooks.Open strAddr
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
i=2
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("db/song.mdb")
conn.open Provider & DBPath
Set rs = Server.CreateObject("adodb.recordset")
sqlstr="Select Top 1 * From song order By id Desc"
rs.open sqlstr,conn,3,2
Do until i>500
rs.AddNew
'rs("id")=objExcelSheet.Cells(i,1).value
rs("area")=objExcelSheet.Cells(i,2).value
rs("areana")=objExcelSheet.Cells(i,3).value
rs("reareana")=objExcelSheet.Cells(i,4).value
rs("filename")=objExcelSheet.Cells(i,5).value
rs("url")=objExcelSheet.Cells(i,6).value
rs("gd")=objExcelSheet.Cells(i,7).value
rs("hits")=objExcelSheet.Cells(i,8).value
'last_update=objExcelSheet.Cells(i,9).value
'A=Cdate(last_update)
'rs("last_update")=A
rs.Update
i=i+1
If objExcelSheet.Cells(i,1).value="" Then Exit do
loop
rs.close
'退出Excel操作(一定要退出否則檔案會一直處於唯讀)
objExcelApp.Quit
set objExcelApp=Nothing
k=i-2
s=0
Response.write "<Div align=left><font size=5><b>表列剛剛由Excel寫入Access的資料</b></font></Div>"
Response.write "<table border=1><tr><td>編號</td><td>分類編號</td><td>分類名稱</td><td>歌手</td><td>歌曲</td><td>相對位址</td><td>精選</td><td>瀏覽次數</td><td>最後更新</td></tr>"
'輸出剛剛寫入的資料錄
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("db/song.mdb")
conn.open Provider & DBPath
Set rs = Server.CreateObject("adodb.recordset")
sqlstr="Select * From song order By id Desc"
rs.open sqlstr,conn,3,2
do until s=k
s=s+1
response.write "<tr valign=top align=center><td>"&rs("id")&"</td>"
response.write "<td align=center>"&rs("area")&"</td>"
response.write "<td align=left>"&rs("areana")&"</td>"
response.write "<td align=left>"&rs("Reareana")&"</td>"
response.write "<td align=left>"&rs("filename")&"</td>"
response.write "<td align=left>"&rs("url")&"</td>"
GD=rs("gd")
If GD=1 Then
response.write "<td align=center>是</td>"
Else
response.write "<td align=center>否</td>"
End If
response.write "<td align=center>"&rs("hits")&"</td>"
response.write "<td align=left>"&rs("last_update")&"</td></tr>"
rs.movenext
loop
Rs.close
conn.close
Response.write "</table>"
response.end
End If
%>
<CENTER>
<form action=ExcelToAccess.asp method=post>
<table border="1" align="center" width=400>
<tr><td>選一個EXCEL檔案</td>
<td>
<select name="areana" size="1">
<option value="孟庭葦.xls" selected>孟庭葦.xls</option>
<option value="秀蘭瑪雅.xls">秀蘭瑪雅.xls</option>
<option value="林憶蓮.xls">林憶蓮.xls</option>
<option value="張宇.xls">張宇.xls</option>
<option value="鄭秀文.xls">鄭秀文.xls</option>
<option value="黃鶯鶯.xls">黃鶯鶯.xls</option>
</select></td></tr>
<tr align="center"><td colspan="2"><input type="submit" name="ExcelToAccess" value="送出"><input type="reset" value="取消"></td></tr>
</table>
<META content="text/html; charset=big5" http-equiv=Content-Type>
<link REL="stylesheet" HREF="style1.css" TYPE="text/css">
</HEAD>
<body bgcolor=ffffff marginheight=0 marginwidth=0 leftmargin=0 topmargin=0>
<%
If request("Request_Method")="POST" Then
areana=request("areana")
strAddr=Server.MapPath("excel/"&areana)
'On Error Resume Next
'建立Excel對象
set objExcelApp=CreateObject("Excel.Application")
'不顯示警告
objExcelApp.DisplayAlerts=false
'不顯示界面
objExcelApp.Application.Visible=false
'讀取已有Excel文件
objExcelApp.WorkBooks.Open strAddr
set objExcelBook=objExcelApp.ActiveWorkBook
set objExcelSheets=objExcelBook.Worksheets
set objExcelSheet=objExcelBook.Sheets(1)
i=2
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("db/song.mdb")
conn.open Provider & DBPath
Set rs = Server.CreateObject("adodb.recordset")
sqlstr="Select Top 1 * From song order By id Desc"
rs.open sqlstr,conn,3,2
Do until i>500
rs.AddNew
'rs("id")=objExcelSheet.Cells(i,1).value
rs("area")=objExcelSheet.Cells(i,2).value
rs("areana")=objExcelSheet.Cells(i,3).value
rs("reareana")=objExcelSheet.Cells(i,4).value
rs("filename")=objExcelSheet.Cells(i,5).value
rs("url")=objExcelSheet.Cells(i,6).value
rs("gd")=objExcelSheet.Cells(i,7).value
rs("hits")=objExcelSheet.Cells(i,8).value
'last_update=objExcelSheet.Cells(i,9).value
'A=Cdate(last_update)
'rs("last_update")=A
rs.Update
i=i+1
If objExcelSheet.Cells(i,1).value="" Then Exit do
loop
rs.close
'退出Excel操作(一定要退出否則檔案會一直處於唯讀)
objExcelApp.Quit
set objExcelApp=Nothing
k=i-2
s=0
Response.write "<Div align=left><font size=5><b>表列剛剛由Excel寫入Access的資料</b></font></Div>"
Response.write "<table border=1><tr><td>編號</td><td>分類編號</td><td>分類名稱</td><td>歌手</td><td>歌曲</td><td>相對位址</td><td>精選</td><td>瀏覽次數</td><td>最後更新</td></tr>"
'輸出剛剛寫入的資料錄
set conn=Server.CreateObject("ADODB.Connection")
Provider="Provider=MicroSoft.Jet.OLEDB.4.0;"
DBPath="Data Source=" & Server.MapPath("db/song.mdb")
conn.open Provider & DBPath
Set rs = Server.CreateObject("adodb.recordset")
sqlstr="Select * From song order By id Desc"
rs.open sqlstr,conn,3,2
do until s=k
s=s+1
response.write "<tr valign=top align=center><td>"&rs("id")&"</td>"
response.write "<td align=center>"&rs("area")&"</td>"
response.write "<td align=left>"&rs("areana")&"</td>"
response.write "<td align=left>"&rs("Reareana")&"</td>"
response.write "<td align=left>"&rs("filename")&"</td>"
response.write "<td align=left>"&rs("url")&"</td>"
GD=rs("gd")
If GD=1 Then
response.write "<td align=center>是</td>"
Else
response.write "<td align=center>否</td>"
End If
response.write "<td align=center>"&rs("hits")&"</td>"
response.write "<td align=left>"&rs("last_update")&"</td></tr>"
rs.movenext
loop
Rs.close
conn.close
Response.write "</table>"
response.end
End If
%>
<CENTER>
<form action=ExcelToAccess.asp method=post>
<table border="1" align="center" width=400>
<tr><td>選一個EXCEL檔案</td>
<td>
<select name="areana" size="1">
<option value="孟庭葦.xls" selected>孟庭葦.xls</option>
<option value="秀蘭瑪雅.xls">秀蘭瑪雅.xls</option>
<option value="林憶蓮.xls">林憶蓮.xls</option>
<option value="張宇.xls">張宇.xls</option>
<option value="鄭秀文.xls">鄭秀文.xls</option>
<option value="黃鶯鶯.xls">黃鶯鶯.xls</option>
</select></td></tr>
<tr align="center"><td colspan="2"><input type="submit" name="ExcelToAccess" value="送出"><input type="reset" value="取消"></td></tr>
</table>
[本日志由 CHOME 於 2012-05-09 09:12 PM 更新]
上一篇: MS Excel ODBC
下一篇: 失落的一角
文章來自: 本站原創
引用通告: 查看所有引用 | 我要引用此文章
Tags: 
相關日志:
評論: 0 | 引用: 0 | 查看次數: -
發表評論