猿頁

検証環境　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
Microsoft Office  Excel 2007(12.0.6504.5001) SP2 MSO(12.0.6425.1000)

VB.NET から Excel2007ファイル(.xlsx) にデータを書き込むには、大きく 2 つの方法があります。

1. PIA で書き込む
2. ADO.NET(OLE DB) で書き込む

DB プロバイダ系の書き込み方法としてはもう一つ、ODBC があるんですが、以前やった仕事で 64KB 制限の壁に泣かされたことがあるので、まともに調べる気になりませんでした。のでパス。

---

PIA は、昔の VB / VBA の時代に、Object library として提供されていた OLE インターフェイスを、.NET 用にラップしたものです。
Object Library は Office 最新の 2007 でも提供されていますので、未だ現役の技術ではあります。.NET でも、Object Library を参照設定して使うことはできます。
.NET から Object Library を直接参照設定かけた場合は、型の制限等が違いますので、自動的に .NET の型に合わせたラッパーが生成されます。これを IA と呼ぶんですが、どうも一部うまく機能しないような生成になってしまうらしいです。
で、そのへんを正常に動作するように手動で調整した IA が提供されているわけです。これが PIA 。

Object Library にしても PIA にしても、本質は「OLE による Office ソフトウェアの外部からの制御」です。ので、制御される対象の Office ソフトウェアそのものがインストールされている必要があります。
また、外部から Office インターフェイス ( VBA と Office ソフトウェアのやり取りなどに使われています) をいじるという動作ですので、多機能低性能。ざっくばらんに言うと、「VBA にできることは全部できるが超ノロい」ということですね。

使い方は割と簡単。

1. PIA の参照設定とコード記述の準備。

ソリューションエクスプローラから VB プロジェクトを右クリック → [参照の設定]で「参照の追加」ダイアログを表示させます。
で、[.NET]タブ → 「Microsoft.Office.Interop.Excel(12.0.0.0)」を選択 → [OK]。

適当に Button コントロールを Form に載せ、以下 Click イベントへいろいろ記述していきます。

2. 常套句を記述。

基本は、「使ったオブジェクトは全部変数に取っておいて、後できちんと解放する」こと。
このへんは決まり文句なので、あまり深く考える必要もないような気がします。
今回は既存ファイルへの上書きを例にしたいので、Workbook は Open で生成しています。(新規作成時には Add を使います。)

Const EX_PATH = "C:\Users\salvage\Book1.xlsx"

Dim xlApp = New Microsoft.Office.Interop.Excel.Application
Dim xlBooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlBooks.Open(EX_PATH)
Dim xlSheets As Microsoft.Office.Interop.Excel.Worksheets = xlBook.Worksheets
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = xlSheets("Sheet1")
Dim xlRange As Microsoft.Office.Interop.Excel.Range

'(A)

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets)
xlBook.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
xlBooks.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks)
xlApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

と一気に書いといて、あとは実装したいコードを (A) の部分に記述すれば OKOK 。

3. ダミーデータを 10 列 × 1 万行書き込むロジックを記述。

まあとりあえずこんな感じで。

'(A)
For i = 1 To 10000
    xlRange = xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 10))
    xlRange(1, 1).value = i
    xlRange(1, 2).value = "1.1"
    xlRange(1, 3).value = "1.12"
    xlRange(1, 4).value = "1.123"
    xlRange(1, 5).value = "1.1234"
    xlRange(1, 6).value = "1.12345"
    xlRange(1, 7).value = "1.123456"
    xlRange(1, 8).value = "1.1234567"
    xlRange(1, 9).value = "1.12345678"
    xlRange(1, 10).value = "1.123456789"
Next
xlBook.Save()

4. 上書きするための Excel ファイルを用意。

とりあえず書式設定が保持されることを確認したいので、A ～ J 列に「数値(小数点以下 9 桁)」を設定しておきます。
ビジュアルに確認したいのであれば、文字色や罫線などを設定しておいた方がわかりやすいかもしれません。

5. 時間計測用のロジックを追加。

あとで ADO.NET と動作時間を比較したいので、イベントの最初と最後に時間計測用のコードをちょろりと書いておきます。

Dim startTime = Now
　　…
MsgBox((Now - startTime).ToString)

まあ簡単にこんな感じで。

5. 実行 → 確認。

2. で常套句を記述した際に、「きちんと解放すること」と書きました。これは、解放を忘れると Excel が終了せずに残ってしまうからです。
ので、ほんとに解放されるかどうか、Windwows タスクマネージャを起動しておいて確認できるようにしておきます。タスクマネージャは、[Shift]＋[Ctrl]＋[Esc]一発で起動できます。

実行結果。

10 列 × 1 万行 ＝ 10 万セル分のデータ挿入で 7 分 35 秒ほどでした。
仕事場のマシンで同様の確認をした時には 3 分程でしたので…自宅マシンのスペックが低いんだよなーやっぱり(；－；)。

いやいや、今回のテーマは「私のマシンのスペックの低さを嘆く」ではないので。

6. まとめて挿入する方法

3. の方法だと、1 セルずつ 10 万回のデータ挿入を行うわけですから、オーバーヘッド(この場合は Excel ～ VB 間でデータ挿入以外の制御のために使う時間)も大変なものになります。
1 回の通信で 10 万データを一気に挿入できたら、このオーバーヘッドにかかる時間は 10 万分の 1 になるんではないでしょうか。

ということで、(A)を書き直してみました。

'(A)'
Dim xlArray(9999, 9) As Object
For i = 0 To 9999
    xlArray(i, 0) = New Object : xlArray(i, 0) = i + 1
    xlArray(i, 1) = New Object : xlArray(i, 1) = "1.1"
    xlArray(i, 2) = New Object : xlArray(i, 2) = "1.12"
    xlArray(i, 3) = New Object : xlArray(i, 3) = "1.123"
    xlArray(i, 4) = New Object : xlArray(i, 4) = "1.1234"
    xlArray(i, 5) = New Object : xlArray(i, 5) = "1.12345"
    xlArray(i, 6) = New Object : xlArray(i, 6) = "1.123456"
    xlArray(i, 7) = New Object : xlArray(i, 7) = "1.1234567"
    xlArray(i, 8) = New Object : xlArray(i, 8) = "1.12345678"
    xlArray(i, 9) = New Object : xlArray(i, 9) = "1.123456789"
Next
xlRange = xlSheet.Range("A1").Resize(10000, 10)
xlRange.Value = xlArray
xlBook.Save()

わずか 3 秒。ってなんですかこの速さは。
Object 型の 2 次元配列を用意しとかなきゃならないメモリの圧迫っぷりがいまいちですが、十分実用的な速度は確保できますね。

---

次、ADO.NET でのデータ挿入。
長くなったので、続きます。

---

参考資料

[HOWTO] Visual Basic または VBA から ADO を Excel データで使用する
Visual Basic .NET を使用してデータを Excel ブックに転送する方法
Visual Basic .NET を使用して Microsoft Excel を自動化する方法
Visual Basic: リフレクションを使用して COM オブジェクトを検査する