とあるプログラマーの覚書

Visual Basic .NET編:Excelオートメーションのパフォーマンスアップ方法

スポンサーリンク

本日は、Excelオートメーションについてのトピックです。
Excelオートメーションとは、VB.NET、C#.NET等のプログラムから、Excelの機能を呼び出してExcelファイルの生成等を行う方法です。
Excelだけでなく、Word、PowerPoint、Access等も同じような機能が用意されており、これらを総称して「Officeオートメーション」と呼びます。

WordやPower Pointであれば、Officeオートメーションを使ってやる事といえば、Webページへの変換とかその程度ではないでしょうか。実際に値を放り込んだりという事は、プログラム側で自動化すると言ってもなかなか難しそうな気がします。

Excelの場合は、それだけでなく、実際に値を放り込んだり、罫線を引いたり、セルの書式設定を施したりと、結構色々な事が出来ます。Excelは縦x横の構造なので、プログラム側で自動的に値を設定したり、DBから取得したデータを出力させたりと、利用用途はWordやPower Pointと比較すると多いと思います。

そんなExcelオートメーションなのですが、少量のデータを扱うのであれば特に気になりませんが、数百、数千、数万のレコードを出力させるような場合、気を付けないと相当パフォーマンスが遅いです。

例えば、データベースから取得したデータを、Excelに出力するような処理を想像してみてください。
VB.NETのコードですが、以下のようなイメージです。(色々端折っていますので、そのままではコンパイル通りません。)



oExcel = New Application
oBooks = oExcel.Workbooks

oBook = oBooks.Open(excelfile)
oSheets = oBook.Worksheets
oSheet = CType(oSheets.Item(1), Worksheet)
‘以下のdrは、SqlDataReader型オブジェクト(DBからのデータ取得は省略)
rowidx = 0
While (dr.Read())
  rowidx += 1
  oSheet.Cells(rowidx, 1).NumberFormatLocal = “@”
  oSheet.Cells(rowidx, 1).Value = dr(“ID”)
  oSheet.Cells(rowidx, 1).HorizontalAlignment = XlHAlign.xlHAlignLeft
  oSheet.Cells(rowidx, 1).Borders(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
  oSheet.Cells(rowidx, 1).Borders(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
  oSheet.Cells(rowidx, 1).Borders(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
  oSheet.Cells(rowidx, 1).Borders(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlMedium
  oSheet.Cells(rowidx, 1).Borders(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin
  oSheet.Cells(rowidx, 1).Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin

  oSheet.Cells(rowidx, 2).NumberFormatLocal = “@”
  oSheet.Cells(rowidx, 2).Value = dr(“NAME”)
  oSheet.Cells(rowidx, 2).HorizontalAlignment = XlHAlign.xlHAlignLeft
  oSheet.Cells(rowidx, 2).Borders(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
  oSheet.Cells(rowidx, 2).Borders(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
  oSheet.Cells(rowidx, 2).Borders(XlBordersIndex.xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
  oSheet.Cells(rowidx, 2).Borders(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlThin
  oSheet.Cells(rowidx, 2).Borders(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin
  oSheet.Cells(rowidx, 2).Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin
End While


まぁざっと上記のようなイメージとなります。
これ、データ件数が多くなると、かなり処理速度が遅くなります。
Excelオートメーションを利用しているアプリと、Excelの間に発生するアプリ間通信が発生すればするほど重たくなってしまいます。
その為、Excelオートメーションの関数やプロパティに値を設定するコードは、必要最小限に留めるような工夫をする事で、全然変わってきます。

上記の処理では、1セルずつ値を設定した後、書式設定、罫線を引くという作業を行っていますが、これを纏めて行うような工夫をします。
ループの中で直接Excelオートメーションを使う事は極力避けます。
二次元配列を一つ用意し、DBから取得したデータによるループ処理では、この二次元配列に値をセットしていきます。
書式設定や罫線もループの外で行います。
先程のコードを書き換えると、以下のようなイメージです。(あくまでイメージなので、一部省略しています。)



oExcel = New Application
oBooks = oExcel.Workbooks

oBook = oBooks.Open(excelfile)
oSheets = oBook.Worksheets
oSheet = CType(oSheets.Item(1), Worksheet)
‘以下のdrは、SqlDataReader型オブジェクト(DBからのデータ取得は省略)
rowidx = 0
While (dr.Read())
  rowidx += 1
  arr(rowidx, 0) = dr(“ID”)
  arr(rowidx, 1) = dr(“NAME”)
End While

Dim range As Range = oSheet.Range(oSheet.Cells(1, 2), oSheet.Cells(rowidx, 2))

range.NumberFormatLocal = “@”
range.HorizontalAlignment = XlHAlign.xlHAlignLeft
range.Borders.LineStyle = XlLineStyle.xlContinuous
range.Borders(XlBordersIndex.xlEdgeLeft).Weight = XlBorderWeight.xlMedium
range.Borders(XlBordersIndex.xlEdgeRight).Weight = XlBorderWeight.xlThin
range.Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin

range.Value = arr


上記のように、Rangeオブジェクトを使って、1セルずつ設定するのではなく、一気に値を設定する事で、かなり速くなります。

あと工夫が出来る事といえば、無駄な設定はしない事です。
例えば、罫線の太さは、XlBorderWeight.xlThinが初期値です。初期値なのであれば、敢えて設定する必要は無いので、デフォルト値を設定するようなコードは削除しましょう。

他には、Excelのセルの値を元に何らかの判定をする場合でも、実際のExcelのセルにアクセスしない方がいいです。
例えば、先程の処理の後、空文字のセルには「-」を設定する、というような処理をするとします。
その場合、以下のようなコードはNGです。



For i = 1 To rowidx
  If oSheet.Cells(i, 2).Value = “” Then
    oSheet.Cells(i, 2).Value = “-”
  End If
Next


今回の場合は、Excel出力元となる二次元配列のデータにアクセスして、判定しましょう。
もしくは、二次元配列に設定するときに変換しておいてもよいですね。

とにかく、Excelのセルにアクセスする回数を減らせば減らす程パフォーマンスは上がります。
なるべく頭を使って、アクセスを減らしてみてください。
私が作成したアプリの場合、13時間掛かっていた処理が、これらの処理を変える事で20分になりました。

他にも参考となりそうなページがありましたので、リンクを貼らせて頂きます。
VB.NET:「Excelのセルに高速に値を出力する方法」 – kzWeb備忘録
Office TANAKA – Excel VBA講座:セルの操作[罫線の設定]

今回は以上です。


スポンサーリンク

URL :
TRACKBACK URL :

Leave a Reply

*
* (公開されません)

*

*

Return Top