スキマハコ

わすれがちなスキマな事を詰め込んでいます。ITの他に暮らしについても書いています。

VB.NETからExcelファイルのデータを読み込むには

 やりたいこと。
 ★一覧表の領域と、その他情報領域が存在する。役割ごとに名前付きセルとして定義してあるので、そこから情報を取得する。
 ※名前付きセルはファイルに一意に存在するよう定義する。

開発環境

 ※但し、2007以降でも動くもの

方法案

  1. マクロで読込できる形式で吐き出し(CSVとか)、そのファイルを分析して、取得する。
  2. VB.NETからExcelに対してDB接続し、情報を取得する
  3. COM相互運用を利用し、VB.NETからExcelオブジェクトに対して情報を取得する
  4. VSTOを使う

 メリット・デメリット
 1.一番原始的な気がする。
  メリット: 確実。
  デメリット:ファイル監視が絡む。
 2.インポートと言えばこれ。
  メリット: 無駄なく取れそう(したことない)
  デメリット:帳票形式のデータインポートには向いていない。
 
 4はクライアントにもVSTOをインストールする必要があるようなので、今回は見送り。
 3については経験がなかったので、実験してみた。

やってみる

 初めに書いたソース

' ファイルパスはOpenFileDialogを利用し、確実に存在する前提。
        Private Sub Read(ByVal ustrFilePath As String)

            ' Excelを操作クラス
            Dim excelApp As Excel.Application = Nothing
            Dim wkbk As Excel.Workbook = Nothing
            Dim sheets As Excel.Sheets = Nothing
            Dim wksheet As Excel.Worksheet = Nothing

            ' データ取得クラス
            Dim rangeDataList As Excel.Range = Nothing
            Dim rangeYear As Excel.Range = Nothing
            Dim nmsNames As Excel.Names = Nothing

            ' 結果格納
            Dim data(,) As Object
            Dim strYear As String

            Try
                ' Excelアプリケーションの開始
                excelApp = New Excel.Application
                ' ファイルオープン
                wkbk = excelApp.Workbooks.Open(ustrFilePath)
                sheets = wkbk.Worksheets
                wksheet = sheets(1)

                ' 取得
                nmsNames = wkbk.Names
                For i = 0 To nmsNames.Count - 1
                    ' 一致する名前定義クラスから、参照するRangeを取得
                    If nmsNames(i).Name = "DataList" Then
                        rangeDataList = nmName.RefersToRange

                    ElseIf nmsNames(i).Name = "Year" Then
                        rangeYear = nmName.RefersToRange

                    End If
                Next

                If rangeDataList Is Nothing OrElse rangeYear Is Nothing Then
                    exit sub
                End If

                ' 格納
                data = DirectCast(rangeDataList.Value, Object(,))
                strYear = rangeYear.Value

            Catch
                Throw
            Finally

                ' 解放
                nmsNames = Nothing
                rangeYear = Nothing
                rangeDataList = Nothing
                wksheet = Nothing
                sheets = Nothing
                wkbk = Nothing
                wkbks = Nothing

                ' Close Excel.
                excelApp.Quit()
                excelApp = Nothing
            End Try
        End Sub

 わあ!ひどいありさまだ!
 このソースだと、このメソッドを呼ぶたびにExcelプロセスが残っていき、元プロセスが終了しない限り(または終了しても残るかも)、見た目的にデーモンなプロセスがどんどん増えていきます。
 上手く解放が行われていないため、Excel終了を読んでもプロセスが死なないのですね。

修正しよう

  • 解放を適切に行おう。

 COM相互運用というのは.NET ⇔ ランタイム呼び出し可能ラッパー (RCW: Runtime Callable Wrapper) ⇔ COMの関係で動きます。
 RCWが適切に解放されないと、Excelプロセスは終了出来ないのです。
 解放には ReleaseComObjectメソッド、あるいはFinalReleaseComObjectを利用します。
 解放の記述を修正です。

    ' 解放を修正
            Finally
                ' 解放(全ての解放を行うため、FinalReleaseComObjectを利用します)
                If Not nmsNames Is Nothing Then
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(nmsNames)
                    nmsNames = Nothing
                End If
                If Not rangeYear Is Nothing Then
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(rangeYear)
                    rangeYear = Nothing
                End If
                If Not rangeDataList Is Nothing Then
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(rangeDataList)
                    rangeDataList = Nothing
                End If

                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wksheet)
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(sheets)
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wkbk)
                wksheet = Nothing
                sheets = Nothing
                wkbk = Nothing

                ' Close Excel.
                excelApp.Quit()
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp)
                excelApp = Nothing
            End Try

 Nothingの代入は不要かもしれません。むだかなーと思いつつもやってます。
 解放の順番も重要です。
 COMは次のような階層構造になっています。

Application
Workbooks
Workbook
Worksheets
Worksheet
Range

 ですので、子供から先に解放すべきです。

 ここまで来ても、まだ解放されません…。

  • 暗黙的にキャストされている箇所はRCWが生まれている。

 なんと恐ろしいことでしょう。
 次のような箇所に該当します。

                ' ファイルオープン
                wkbk = excelApp.Workbooks.Open(ustrFilePath) ' ← Workbooksを暗黙的に変換している!
                sheets = wkbk.Worksheets
                wksheet = sheets(1)

 このように.が2つ以上ある場合は暗黙的にキャストが働いていると考えて構いません。
 こうして発生したRCWの参照は解放されず、どうしようもなくなります。
 全て、一度変数で参照させて最後に必ず解放するようにしましょう。

            ' Excelを操作クラス
            Dim excelApp As Excel.Application = Nothing
            Dim wkbks As Excel.Workbooks = Nothing
            Dim wkbk As Excel.Workbook = Nothing
            Dim sheets As Excel.Sheets = Nothing
            Dim wksheet As Excel.Worksheet = Nothing

            ' Excelアプリケーションの開始
            excelApp = New Excel.Application
            wkbks = excelApp.Workbooks			' ← 暗黙的変換を回避
            ' ファイルオープン
            wkbk = wkbks.Open(ustrFilePath)
            sheets = wkbk.Worksheets
            wksheet = sheets(1)
  • 繰り返しの中で代入した場合は毎回解放する

 ループ文で回している場合は、利用後に毎回解放しましょう。
 ※なんか怖いので。

                ' 取得
                nmsNames = wkbk.Names
                For i = 0 To nmsNames.Count - 1
                    nmName = nmsNames(i)
                    ' 一致する名前定義クラスから、参照するRangeを取得
                    If nmName.Name = "DataList" Then
                        rangeDataList = nmName.RefersToRange

                    ElseIf nmName.Name = "Year" Then
                        rangeYear = nmName.RefersToRange

                    End If

                    ' 解放(参照が切り替わるので、毎回解放しないとゴミとして残るのでは…)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(nmName)
                    nmName = Nothing
                Next
  • DirectCastはつかっちゃだめ

 キャストが走るので、解放できないRCWが出来てしまうのです。
 ふつーに代入しちゃいましょう。

                data = rangeDataList.Value

まとめ

  • RCWは必ず解放する
  • 暗黙的なキャストはしないで、必ず変数に格納する
  • 解放するときはCOMの階層構造を意識した順番で行う。