Excel2007 ファイルに ADO.NET でデータを書き込む
検証環境 Fujitsu FMV-W630(Pen4(530)3.4GHz/3GB)
WindowsVista Ultimate(6.0.6002 SP2,v.659 Build 6002)
VisualStudio2008Pro(VB) 9.0.30729.1SP/.NET Framework 3.5SP1
本エントリは、猿頁 » Blog Archive » Excel ファイルに PIA でデータを書き込むの続きです。
さて、今度は別アプローチ。
ADO.NET を使って Excel2007ファイル(.xlsx)にデータを書き込んでみます。
1. プロバイダの指定と接続 → 切断
このへんもセオリーなので、さらっと記述します。
Const EX_PATH = "C:\Users\salvage\Book1.xlsx"
Const ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& EX_PATH & "; Extended Properties=""Excel 12.0; HDR=No;"""
Const ConnectSheetScript = "CREATE TABLE [Sheet1$] " _
& "(c1 Decimal, c2 Decimal, c3 Decimal, c4 Decimal, c5 Decimal" _
& ", c6 Decimal, c7 Decimal, c8 Decimal, c9 Decimal, c10 Decimal)"
Using xlConn = New System.Data.OleDb.OleDbConnection(ConnectionString)
xlConn.Open()
Using xlCmd = New OleDb.OleDbCommand("", xlConn)
xlCmd.CommandText = ConnectSheetScript
xlCmd.ExecuteNonQuery()
'(A)
End Using
xlConn.Close()
End Using
ConnectionString は、Excel へのDB接続に使用するプロバイダを記述します。
Excel2003 までは Jet4.0 プロバイダでよかったんですが、2007 からは ACE プロバイダ(Accessのエンジンです)を使うことになります。
また、今回は書き込みのみの目的ですので Extended Properties のあたりは「Excel」「HDR」しか記述していません。読み込みも行う場合は IMEX とか ReadOnly とかの設定も必要になったりするらしいですが、まあ今回はユルめな感じで。
どんな設定項目があるのかの公式な資料は、ガンバったんですがどうしても見つけることができませんでした。
今回の情報は、主に
を参考にしています。
HDR は 「No」な設定にしてみました。こうしとくと、1 行目のデータをテーブルにおける項目名とみなしません。
でもこの場合、新規の Excel だと空白のセル自体をフィールドとして見なさなくなり例外が発生してしまいますので、CREATE TABLE で書き込む列に項目名を付けてやる必要があります。そうすると、シートの 1 行目にはつけた項目名そのものが格納されてしまいます。
HDR=Yes であらかじめ 1 行目に項目名を埋め込んでいた時とまったく同じ出力結果になってしまうので、正直あまり使い分ける意味がないような気がします。
このように CREATE TABLE で項目名を明示的に付けた場合、実際に書き込むロジックを記述する (A) の部分は、以下のようになります。
For i As Integer = 1 To 10000
xlCmd.CommandText = "INSERT INTO [Sheet1$] (1.000000000,1.1,1.12,1.123," _
& "1.1234,1.12345,1.123456,1.1234567,1.12345678,1.123456789);"
xlCmd.ExecuteNonQuery()
Next
結果は
…うーん、微妙。
ヘタな PIA よりは十分速いですが、上手に組んだ PIAよりは遅いと。
1 行目が常に見出し行になってしまう制限も含め、あまり使い勝手のいいものではなさそうです。
ついでに。
CREATE TABLE も使わず、あらかじめ Excel ファイルの 1 行目に項目名を設定もせずに、直接 INSERT INTO 文だけで書き込むこともできます。
この場合は、INSERT INTO 文そのものに列名を表す「F1, F2, …」を記述します。
Const InsertString = "INSERT INTO [Sheet1$] (F1, F2, F3, F4, F5"
& ", F6, F7, F8, F9, F10) VALUES " _
& "(1.000000000,1.1,1.12,1.123,1.1234,1.12345,1.123456," _
& "1.1234567,1.12345678,1.123456789);"
こんな感じですね。
ただし、あらかじめ使用する Excel ファイル－シートの一番右側の列に何か書き込んでおかないと列そのものが存在しない、というエラーになります。
かといって書き込んでおくと、その次の行から INSERT されることになりますので、どっちにせよ最低 1 行は INSERT には使えないという制限は健在なわけです。
もひとつ。
ADO.NET でデータが書き込まれた列は、書式設定が初期値に戻ります。表示形式を「日付」にしようが、文字色・背景色・罫線を設定しておこうが、データが書き込まれるとすっぴんの「標準」に戻ってしまいます。
ので、装飾等を入れる必要がある場合には、ADO,NET は選択肢にならないということになりそうです。
ということで、調べてはみましたが、あまりメリットが感じられない結果になってしまいました。ACE プロバイダを使った Excel への書き込み。
どう使い分ければいいかなー、と考えてみたんですが、Excel がインストールされたマシンで動作させるなら、制限もなく上手に組めば速度も確保できるという点で、Excel PIA がベストなのではないかと。
Excel がインストールされていない環境で Excel ファイルを生成したい場合には、1 行目制限はあきらめて ADO,NET(ACE プロバイダ)という選択肢もあると思います。
「ACE プロバイダは Access2007 がインストールされていないと使えない」という記述もどこかで読みましたが、それはちょっと調べが浅いような気もします。
確かに Windows 標準では提供されていませんし、Access2007 と同時にインストールされるエンジンではありますが、この ACE エンジン(＋ Office2007 対応 ODBC ドライバのセット)は、単体無償でダウンロード提供されています。
ダウンロードの詳細 : 2007 Office system ドライバ: データ接続コンポーネント
ので、このコンポーネントをインストールすれば、Office なしでも ACE プロバイダを使った Excel ファイルへの書き込みは可能になります、と。
かなりリーズナブルに動作環境を構築できますので、予算と併せ考えた場合は意外とニーズは高いかもしれません。
