[三流君] −−＞
[ＡＳＰで遊ぶ、失敗する] −−＞
[バックナンバー一覧]
−−＞ No.057 Excelグラフ付の雛形シートにASPからADOを使用してデータセット
Excelグラフ付の雛形シートにASPからADOを使用してデータセット
本文(発行内容)
＜Excelグラフ付の雛形シートにASPからADOを使用してデータセット＞
こんにちは、三流プログラマーのＫｅｎ３です。
今回は、
グラフ付のExcelファイルとADOで接続後、
データをセット（更新）
そのＥｘｃｅｌファイルをダウンロードさせます。
拾い読みして、何かの参考となれば幸いです。
/*
* １．今回のキッカケ
*/
No.53 ADOでExcelファイル(*.xls)とやっと接続できた（ほっ）
http://www.ken3.org/backno/backno_asp11.html#53
で、
サーバーに置いた、*.xlsファイルに接続でき、
No.56 Excel(*.xls)とADOで接続、.Addnewしてみた
http://www.ken3.org/backno/backno_asp12.html#56
で、
Excelの表にデータを追加することが出来ました。
そしたら、今度は更新でしょう
ってことで、一週間分のログを日別に集計して、
Excelの表を更新してみます。
.AddNewの時、.Updateも使っているので、
普通の表だと面白みに欠けるので、
グラフ付のＥｘｃｅｌファイルに書き込んでみます。
/*
* ２．まずは、雛形ファイルの用意
*/
A列 B列 C D列
日付 訪問者数
2001/1/1 10
2001/1/2 20
2001/1/3 30 横棒グラフを
2001/1/4 40 シートとリンクさせて作成
2001/1/5 50 雛形を作成。
2001/1/6 60
2001/1/7 70
2001/1/8 80
作成イメージは
↑みたいに作成しました。
今回は、名前の定義は作らないで、
DATA!A2:B9 みたいに指定してみたいと思います。
/*
* ３．訪問者をＳＱＬ文で集計する
*/
ログファイルから日付別に集計するのは、
http://www.ken3.org/cgi-bin/test/test040-1.asp
でやったＳＱＬ文を流用して、
Select Format(WriteTime, 'YYYYMMDD') AS YYYYMMDD, Count(*) AS CNT
From log
GROUP BY Format(WriteTime, 'YYYYMMDD')
を使用して、日付別に集計します。
集計値を配列に入れて、それをエクセルにセットする方法を取ります。
いきなり動作したソース載せちゃうけど、
http://www.ken3.org/cgi-bin/test/test057-1.asp
でテスト可能です。ポイントの解説は下のほう見てください。
<%@LANGUAGE=VBScript%>
<html>
<head>
<title>Excel(*.xls)グラフ付雛形にADOで接続しデータ更新</title>
</head>
<body>
<h2>Excel(*.xls)グラフ付雛形にADOで接続しデータ更新</h2>
<!-- Ken3 --><!-- #include file="info.inc" -->
test057-1.asp<br>
ASPからADOを使用してExcel(*.xls)と接続して、<br>
.Updateでレコードを更新する。<br>
<hr>
<%
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3
'配列変数に集計値をまず入れます。
Dim PageCNT(10) '８個でいいんだけど
Dim dHIZUKE(10)
'ADO DB Connection オブジェクトを作成する、英文そのままじゃん
Set db=Server.CreateObject("ADODB.Connection")
'データアクセスにはJet.OLEDB.4.0を使うことを設定
db.Provider = "Microsoft.Jet.OLEDB.4.0"
'接続ＤＢの位置は、Server.MapPathで変換して渡す
db.ConnectionString = Server.MapPath("cnt.mdb")
db.open 'やっとデータベースを開ける
'FORMAT関数を使用して、テーブル名logからデータを集計する
'ＳＱＬ文を発行する
strSQL = "Select Format(WriteTime, 'YYYY/MM/DD') AS YYYYMMDD, Count(*) AS CNT"
strSQL = strSQL & " From log"
strSQL = strSQL & " Where WriteTime >= #" & DateAdd("d", -7, Date) & "#"
strSQL = strSQL & " GROUP BY Format(WriteTime, 'YYYY/MM/DD')"
'オマケで画面にデータ表示
Response.Write "<hr>発行する（した）ＳＱＬ文は<br><B><font color='green'>"
Response.Write strSQL & "</font></B><br>です<hr>"
Set rs = db.Execute(strSQL)
'データの表示をテーブルで行う
Response.Write "<TABLE Border='1'>"
'見出しをバカっぽく、そのまま書き込む
Response.Write "<TR>"
Response.Write "<TD>NO.</TD>"
Response.Write "<TD>日付</TD>"
Response.Write "<TD>閲覧ページ数</TD>"
Response.Write "</TR>"
'ＥＯＦまでループ
nCNT = 1
Do While rs.EOF = False '.EOFがFalseの間
Response.Write "<TR>" '内容を表示する
Response.Write "<TD>" & nCNT & "</TD>"
Response.Write "<TD>" & rs.Fields("YYYYMMDD") & "</TD>"
Response.Write "<TD ALIGN='RIGHT'>" & rs.Fields("CNT") & "</TD>"
Response.Write "</TR>" & Chr(13) & Chr(10)
'配列変数に日付、カウント値を代入
dHIZUKE(nCNT) = rs.Fields("YYYYMMDD")
PageCNT(nCNT) = rs.Fields("CNT")
'次のレコードにポインタを移動する
rs.MoveNext 'これを忘れると悲惨なことに、、、
'カウンタを増やす
nCNT = nCNT + 1
Loop
Response.Write "</TABLE><HR>" 'テーブルは終わりです
'お行儀よくオブジェクトも開放しましょう
'今回同じ名前を使いまわしてみます、開放すれば大丈夫でしょう
rs.Close '開いていたレコードセットを閉じる
Set rs = Nothing
db.Close 'データベースも閉じようよ
Set db = Nothing
'---------------------
'ここからExcel側の処理
'---------------------
'ADO DB Connection オブジェクトを作成する、英文そのままじゃん
Set db=Server.CreateObject("ADODB.Connection")
'接続文字列作成、test057.xlsを指定する
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("test057.xls") & ";" & _
"Extended Properties=Excel 8.0"
Response.Write "ExcelとのADO接続文字列は<br><b>"
Response.Write strCon & "</b><br>です<br>"
'データベース(xls)を開く
db.open strCon
Response.Write "その次はレコードセット<br>"
'ADO DB Recordset オブジェクトを作成する、英文そのままじゃん
Set rs=Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseServer 'カーソルはサーバーの指定
Response.Write "Select * from [DATA$A1:B9], db, 〜<br>"
rs.Open "Select * from [DATA$A1:B9]", db, adOpenStatic, adLockOptimistic
Response.Write "開いたレコードセットに対して.MoveFirst<br>"
rs.MoveFirst '先頭へ(A2:B2の行)
Response.Write "ループで配列からフィールド（セル）へセット<br>"
'ループ
For n = 1 To 8 '当日＋一週間の７日で８回まわる
rs.Fields(0).Value = dHIZUKE(n)
rs.Fields(1).Value = PageCNT(n)
rs.Update '更新
rs.MoveNext '次のレコードへ（次の行へ）
Next
Response.Write "rs.Close でレコードセットを閉じる<br>"
rs.Close
Set rs = Nothing 'オブジェクト変数を開放する
Response.Write "db.Close でxlsとの接続を閉じる<br>"
db.Close
Set db = Nothing 'オブジェクト変数も開放する
%>
<hr>
集計値をtest057.xlsへ書き込みました<br>
[<a Href="test057.xls" TARGET="_blank">書き込んだtest057.xlsを開く</a>]<br>
↑書き込まれたデータの確認、グラフ付はＯＫか確認する<br>
</body>
</html>
ポイント解説
~~~~~~~~~~~~
あまりポイントは、無いんだけど、
'接続文字列作成、test057.xlsを指定する
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("test057.xls") & ";" & _
"Extended Properties=Excel 8.0"
接続までは、スムーズでした。
で
いつものようにハマったのは、データエリアの指定です。
^^^^^^^^^^^^^^^^^^^^^^^^^^^
回答を先に出してしまうけど、
Response.Write "Select * from [DATA$A1:B9], db, 〜<br>"
rs.Open "Select * from [DATA$A1:B9]", db, adOpenStatic, adLockOptimistic
と
from [DATA$A1:B9]で指定すると、
シートの範囲をレコードセットにできます。
※名前の範囲無くても使用できるので、意外と便利ですね
初め書いた書き方は(失敗例その１は)
rs.Open "Select * from DATA!A2:B9", db, adOpenStatic, adLockOptimistic
この書き方だと、
Microsoft JET Database Engine エラー '80040e14'
FROM 句の構文エラーです。
/cgi-bin/test/test057-1.asp, 行 121
ちっ、また調べるのかよ（笑）
http://www.microsoft.com/japan/msdn/columns/office/office10052000.asp
がＭＳのページです。※よかった日本語だよ。
Excel Link
Excel ワークブックへのリンク
オブジェクト 構文
ワークシート sheetname$
名前付き範囲 rangename
名前付きではない範囲 sheetname$rangeaddress
えっと、この３種類が使えるなら、
DATA$A2:B9でいいのか、この位置に＄は違和感あるけど、仕様なんでしょう。
！はエクセルのセル上の慣れてる書き方だったか。
rs.Open "Select * from [DATA$A2:B9]", db, adOpenStatic, adLockOptimistic
で勝負。(失敗例その２)
ADODB.Field エラー '800a0bcd'
~~~~~~~~~~~
BOF と EOF のいずれかが True になっているか、
または現在のレコードが削除されています。
要求された操作には、現在のレコードが必要です。
オイオイ、
あらら、まだダメなの、、、
ADODB.Field エラー？
~~~~~~~~~~~
あっ、フィールドかぁ、
A2:B9これってデータエリアで、見出しの行含んでないや。
これで、フィールド名が取れないのかな、もしかして。
rs.Open "Select * from [DATA$A1:B9]", db, adOpenStatic, adLockOptimistic
と見出しの行を含ませたら、やっと動作しました。
※意外と単純なミス？でした。。。
でも、エラーメッセージ、フィールド名が不明とか、
わかりやすく出してほしいよね......
えっ、そんなミスするのは三流君だけ？？？
あとは、ループで頭から配列変数からフィールドにデータをセット、
その後、.Updateで更新しました。
rs.MoveFirst '先頭へ(A2:B2の行)
Response.Write "ループで配列からフィールド（セル）へセット<br>"
'ループ
For n = 1 To 8 '当日＋一週間の７日で８回まわる
rs.Fields(0).Value = dHIZUKE(n)
rs.Fields(1).Value = PageCNT(n)
rs.Update '更新
rs.MoveNext '次のレコードへ（次の行へ）
Next
余計な話なので、聞き流してほしいけど、
ADOって
.Edit メソッドで編集状態に
フィールド代入
フィールド代入
.Update メソッドでレコード確定
の流れじゃないのね。
DAOのクセで.Edit使ってたけど、
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Microsoft VBScript 実行時エラー エラー '800a01b6'
オブジェクトでサポートされていないプロパティまたはメソッドです。: 'Edit'
/cgi-bin/test/test057-1.asp, 行 128
.Editってメソッドは無いです。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
/*
* ４．終わりの挨拶 ＜／ＨＴＭＬ＞
*/
今回は、
・Excelの雛形シートにASPからデータをセット
・Select Form [シート$範囲]でExcelの指定範囲をレコードセットに指定
・指定範囲の先頭行にはフィールド名を指定するんだよ
・ＡＤＯには、．ＥＤＩＴメソッドは無いんだよ
って話でした
サクサクと、作成が進むと思ったのですが、
簡単な処理でもハマりました。
私の失敗談が、
ASPからADOを使用して、Excelファイルに書き込む
そんな処理の参考となれば幸いです。
今回のグラフ付雛形シートにデータセット、
http://www.ken3.org/cgi-bin/test/test057-1.asp
でテスト可能です。
素朴な疑問やリクエスト、今回みたいなクレームなどがあったら、
掲示板 : http://www.ken3.org/cgi-bin/bbs/asp/wforum.cgi
に気軽に書き込んでください。
ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。
フィードバック
ASP系の→[掲示板]←を覗く、質問を書き込む
三流君の主なリンク先
[アクセスランキング]
[サイトマップ]
[リンク先・相互リンク先など]
その他 宣伝広告