VB.NETからExcelファイルのデータを読み込むには
やりたいこと。
★一覧表の領域と、その他情報領域が存在する。役割ごとに名前付きセルとして定義してあるので、そこから情報を取得する。
※名前付きセルはファイルに一意に存在するよう定義する。
方法案
- マクロで読込できる形式で吐き出し(CSVとか)、そのファイルを分析して、取得する。
- VB.NETからExcelに対してDB接続し、情報を取得する
- COM相互運用を利用し、VB.NETからExcelオブジェクトに対して情報を取得する
- 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の階層構造を意識した順番で行う。
参考:
http://msdn.microsoft.com/ja-jp/library/vstudio/ee317478%28v=vs.100%29.aspx
http://eikunti.blog.fc2.com/blog-entry-19.html
http://shinichiaoyagi.blog25.fc2.com/blog-entry-180.html
http://support.microsoft.com/kb/302094
http://jeanne.wankuma.com/tips/vb.net/excel/
http://igeta.cocolog-nifty.com/blog/2007/07/rcw.html