[三流君] −−＞ [ＡＳＰで遊ぶ、失敗する] −−＞ [バックナンバー一覧]
−−＞ No.054 ADOでSQL Select * INTO文発行でExcel(*.xls)を作成する

ADOでSQL Select * INTO文発行でExcel(*.xls)を作成する

本文(発行内容)

＜ADOでSQL Select * INTO文発行でExcel(*.xls)を作成する＞

こんにちは、三流プログラマーのＫｅｎ３です。

今回は、
ADOに目覚めたKen3の続きで(目覚めてないけど(笑))
SQLの Select * INTO文を発行して、Excel(*.xls)を作成してみたいと思います。

これも、意外と簡単に進みました、、、

拾い読みして、何かの参考となれば幸いです。


/*
 * １．今回のキッカケ
*/


前回、ADOでExcelと接続して、データを取得しました。

調べごとのついでの知識じゃないけど、
Accessで、「Docmd」を使用せずImportする方法が数種類載っていて、
INSERT INTO TABLE FROM [Excel 8.0;Database=Drive:\Path\File].[Sheet]
なんて、SQL文を発行すると、出来るみたいです。

ADOでExcel-->Accessの逆、
MDBのテーブルをシートにできないかなぁとフト思い、
いろいろと試行錯誤してみました。



/*
 * ２．[Excel 8.0;Database=Drive:\Path\File].[Sheet]は使えるかも
*/


SQL文でテーブルからテーブルのデータのインポート(追加は)
SELECT * INTO テーブル FROM テーブル Where 条件
って感じでもOKです。

この方法をテストしてみます。

  '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("db026.mdb")

  'やっとデータベースを開ける
  db.open

ここまでは、お約束?じゃないけど、MDBファイルへの接続を作りました。

次が問題の、
~~~~~~~~~~~
  '送り元のテーブル情報 TESTテーブルからIDの降順
  strFROM = " FROM TEST ORDER BY ID DESC"
まずは、元テーブルの条件をセットして、

次に、出力先のExcelファイルを指定します。
  'Excelファイルの位置とシート名(送り先)
  strXLS = "[Excel 8.0;database=" & Server.MapPath("a.xls") & "].[DATA]"
たんに、
Server.MapPathで今実行中の位置＋a.xlsのパスを作成、
シート名は固定でDATAとしました。

普通につなげてSQL文を作成します。
  'SQL文の作成
  strSQL = "SELECT * INTO " & strXLS & strFROM

で、作成したSQL文を発行。

  set RET = db.Execute(strSQL) 'SQL文の発行

あれれ、思ったより、すんなり出来た(笑)
a.xlsの中身を見ると、DATAシートも出来てるし。

よし、データを追加・変更して再度実行しよう。
おっと、
Microsoft JET Database Engine エラー '80040e14' 
テーブル 'DATA' は既に存在しています。 
/cgi-bin/test/test054-1.asp, 行 32 
あらら、同じファイル、同じシート名には、上書きしてくれないのか。。。

a.xlsを消すか、ダブらないシート名にしないと行けないのか。

ファイル名は日付の頭にaをつけて、a20030520.xls 
シート名は時刻で９時10分15秒ならs091015にします。

これなら同じ秒数にテストしなければ大丈夫かな。
後日削除しやすいようにTEMP\a20030520.xlsとフォルダーも指定して作成します。

ASP,VBScriptでFormat関数使えないんだよなぁ。。。
日付関係の関数を駆使して、文字列を作成するか。

えっと、現在時刻はNow関数でわかるから、
あとはこれを変換すればいいのか。

年 Year()
月 Month()
日 Day()
時 Hour()
分 Minute()
秒 Second()
関数に日付の値を渡すと変換できます。

あとは一桁の時、頭０付けかぁ、
Right("0" & Day(T), 2)
と小細工で、０と＋してから、右端から２文字取るかな。

T = Now()

strMM = Right("0" & Month(T)), 2)
strDD = Right("0" & Day(T)), 2)
YYYYMMDD = Year(T) & strMM & strDD

strH = Right("0" & Hour(T)), 2)
strM = Right("0" & Minute(T)), 2)
strS = Right("0" & Second(T)), 2)

で、ファイル名とシート名を作成しますか。

strFNAME = Server.MapPath("temp\a" & YYYYMMDD & ".xls")
strSNAME = "s" & strH & strM & strS

こんな感じかな。


/*
 * ３．調査、テスト結果を合わせて、１つのプログラムを作成
*/


ここまでくれば、見通しが明るいので、１つにつなげてみた。
たんに、文字列作成しただけですね（笑）

<%@LANGUAGE=VBScript%>
<html>
<head>
<title>ADO Select * INTO [Excel 8.0;Database=Drive:\Path\File].[Sheet]</title>
</head>
<body>
<h1>ADO Select * INTO [Excel 8.0;Database=Drive:\Path\File].[Sheet]</h1>
test054-1.asp<br>
[Excel 8.0;database=ファイル名.xls].[シート名]<br>
を指定して、<br>
SELECT * INTO文を発行し、Excelファイルを作成する<br>
<hr>
<%
  '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("db026.mdb")

  'やっとデータベースを開ける
  db.open

  '送り元のテーブル情報 TESTテーブルからIDの降順
  strFROM = " FROM TEST ORDER BY ID DESC"

  'Excelファイルの位置とシート名(送り先)を作成する
  T = Now()  '現在時刻を保存

  strMM = Right("0" & Month(T), 2)   '年月日を作る
  strDD = Right("0" & Day(T), 2)
  YYYYMMDD = Year(T) & strMM & strDD

  strH = Right("0" & Hour(T), 2)     '時分秒を作る
  strM = Right("0" & Minute(T), 2)
  strS = Right("0" & Second(T), 2)

  strFNAME = Server.MapPath("temp\a" & YYYYMMDD & ".xls") 'ファイル名
  strSNAME = "s" & strH & strM & strS   'シート名
     '↓接続用の文字列をファイル名、シート名を使って作成
  strXLS = "[Excel 8.0;database=" & strFNAME & "].[" & strSNAME & "]"

  'SQL文の作成
  strSQL = "SELECT * INTO " & strXLS & strFROM

  'テストで作成文字列の表示
  Response.Write "作成FILEは<b>" & strFNAME & "</b><br>"
  Response.Write "シート名は<b>" & strSNAME & "</b><br>"
  Response.Write "接続文字列<b>" & strXLS & "</b><br>"
  Response.Write "発行ＳＱＬ<b>" & strSQL & "</b><br>"

  set RET = db.Execute(strSQL) 'SQL文の発行

  'データベースも閉じようよ
  db.Close

  'お行儀よくオブジェクトも開放しましょう（通常は自動的に解放されるけど）
  Set db = Nothing

%>
<hr>
作成終了です。<br>
作成された<A href="temp/a<%=YYYYMMDD%>.xls" Target="_Blank">
a<%=YYYYMMDD%>.xls</a>の確認（保存は右ボタンで、クリックで開きます）<br>
<A href="test028-2.asp" Target="_Blank">db026.mdbのデータを見る</a><br>
<A href="test027-2.asp" Target="_Blank">db026.mdbへデータを登録する</a><br>

</body>
</html>

http://www.ken3.org/cgi-bin/test/test054-1.asp
でテストできてるので、確認してみてください。

うわ、

Microsoft JET Database Engine エラー '80004005' 
パス 'd:\users\ken3_org\cgi-bin\test\temp\a20030520.xls' は正しくありません。
パス名に間違いがないことと、ファイルが置かれたサーバーに接続していることを
確認してください。 
/cgi-bin/test/test054-1.asp, 行 55 
えっ、なんで？
あっ、ｔｅｍｐフォルダ作るの忘れた（笑）

保存先のフォルダも作ったし、ＯＫ、なんとか出来ました。


/*
 * ４．終わりの挨拶　＜／ＨＴＭＬ＞
*/


今回は、
ADOでSQL Select * INTO を使って、Excelファイルを作成してみました。

感覚的には、ADOで接続さえクリアすれば、
あとは、ＳＱＬやＡＤＯのいつもの感覚で処理可能なので、楽かなぁ。

http://www.ken3.org/cgi-bin/test/test054-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記事 バックナンバー目次]

広告: