[三流君] −−＞ [ＡＳＰで遊ぶ、失敗する] −−＞ [バックナンバー一覧]
−−＞ 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
でテスト可能です。

ＡＳＰ、ＶＢＳｃｒｉｐｔ勉強中の三流プログラマーのＫｅｎ３でした。

ページフッター

ここまで、読んでいただきどうもです。目的の情報が見つかったか？少々心配しつつ、、、

三流君へ メッセージを送る

感想や質問・要望・苦情など 三流君へメッセージを送る。
返信例 XXXXさんへ
下記のフォームからメッセージを送ることができます。

あなたのお名前（ニックネーム）:さん
返信は？:

アドレス:に返事をもらいたい
感想や質問↓:


(感想や質問・要望・苦情はHPで記事に載せることがあります。)

種類別のリンク や 広告など

[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]

Blogとリンク:[三流君のMemo別館]/ [ASP 三流君のソースコード置き場]/ [Ken3Video YouTubeで動画解説]
広告:

気になった ジャンル ↓を選択してください。

まぁ、基本はデータの受け取りかなぁ。
・[Form等を使用したデータのやり取り]・・・ASPと言っても、HTMLの入力フォームからデータを受け取ります。POSTやGETでやりとりを押さえますか。

次は、データの入出力 で ADOを使った(ADOで接続) と SQLの解説を少々
・[ADOでMdbファイルを使う]・・・MDBと接続して、簡単な追加・更新・削除を行った。
・[ADOでExcelと接続してみた]・・・.xlsと接続してSQLを使ってみた。
・[ADOでCSVと接続してみた]・・・.CSV テキストを読み出した。※更新・削除はできません

広告:

DBが使えるので、あまり使用しないけど、普通のテキストファイル処理
・[テキストファイル処理]・・・ファイルを開いて、書き込む。１行読み込みなどを軽く

VBScriptでFormat関数が無いなど、微妙にVBAと違うけど
[VBScript関数関係の説明]・・・少し、処理を書いてみた。
[その他処理サンプル]・・・あまり良いサンプル作れなかったけど。。。
何かの参考となれば幸いです。


[三流君(TOP ken3.org へ戻る)] / [ASPで遊ぶ、失敗する] / [ASP記事 バックナンバー目次]

広告: