VB.NET-TIPS などプログラミングについて

Excel ファイルを VB.NET から操作することはいろんなシステムで要求されます。 例えば、検索されたデータ一覧を Excel ファイルに出力することや、 印刷用のテンプレート Excel ファイルを読込んで、そこにデータを設定し、出力する等です。

それで、今回は Microsoft.Office.Interop.Excel COM参照による方法について示します。 Microsoft.Office.Interop.Excel を利用するには対象となるパソコンに Excel がインストールされている必要があります。

先ずはプロジェクトに Microsoft.Office.Interop.Excel の参照設定を行う必要があります。
以下は、メニューの中の「プロジェクト(P)」⇒「参照の追加(R)...」を開いて Microsoft Excel 15.0 Object Library を選択したところです。 15.0 の部分は Excel のバージョンにより変わります。

参照設定が終わると、左図の様にソリューションエクスプローラの中の「参照設定」に Microsoft.Office.Interop.Excel が現れます。
これで、プログラムの中でエクセルを利用する準備が終わりました。

早速簡単なエクセルファイルを上書きする例を示します。
今回の例はフォームにボタンを1個設置しそのクリックイベントで処理を行います。
ファイル名を「test.xlsx」とし、そのファイルの中のシートの先頭セルに "TEST123" の文字列を設定し、登録します。 （「test.xlsx」は前もって作成しておきます。）

エクセルを使う手順は以下の様に行います。

• Excel オブジェクトを生成しアプリケーション起動
• Excel の Workbook のコレクションである Workbooks の取得
• Workbooks オブジェクトにより既存の Excel ファイルを Workbook に開く
• Excel の Worksheet のコレクションである Worksheets 取得
• Excel の Worksheets から先頭のシート Worksheet 取得
• シート内の処理を行う
• Workbook オブジェクトによりファイルに保存
• Worksheet オブジェクトの解放
• Worksheets オブジェクトの解放
• Workbook オブジェクトを閉じてから解放
• Workbooks オブジェクトの解放
• Excel オブジェクトを閉じる
• Excel アプリケーションの解放

以下の様にオブジェクトを順を追って参照を取得し、対象となるワークシートに処理を行った後で 逆順でオブジェクトの解放を行います。
Excel.Application ⇒ Workbooks ⇒ Workbook ⇒ Worksheets ⇒ Worksheets

オブジェクトの解放用の関数は、Marshalクラスの COM オブジェクトの解放用関数 System.Runtime.InteropServices.Marshal.ReleaseComObject を使います。 （エクセルオブジェクトは COM オブジェクトとして動作するためです）

簡単なエクセル処理の例１

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'Excel のアプリケーション参照用オブジェクト
        Dim xlsApplication As Excel.Application = Nothing
        'Excel の Workbooks 参照用オブジェクト (Workbook の Collection)
        Dim xlsWorkbooks As Excel.Workbooks = Nothing
        'Excel の Workbooks 内の1個の Workbook 参照用オブジェクト
        Dim xlsWorkbook As Excel.Workbook = Nothing
        'Excel の Workbook 内の Worksheets 参照用オブジェクト (Worksheet の Collection)
        Dim xlsWorkSheets As Excel.Sheets = Nothing
        'Excel の Sheets 内の1個の Worksheet 参照用オブジェクト
        Dim xlsWorkSheet As Excel.Worksheet = Nothing
        'Excel の Sheet 内の1個のセル Range 参照用オブジェクト
        Dim xlsRange As Excel.Range = Nothing
        'フルパスでのファイル名 (ファイル名のみだと Excel 側のデフォルトのフォルダになるはず)
        Dim strFileName As String = My.Application.Info.DirectoryPath & "\test.xlsx"

        'Excel アプリケーション起動 
        xlsApplication = New Excel.Application
        'Excel の Workbooks 取得
        xlsWorkbooks = xlsApplication.Workbooks
        'Excel非表示
        xlsApplication.Visible = False
        xlsApplication.DisplayAlerts = False
        '既存 Excel ファイルを開く
        xlsWorkbook = xlsWorkbooks.Open(strFileName)
        'Excel の Worksheets 取得
        xlsWorkSheets = xlsWorkbook.Worksheets
        'Excel の Worksheet 取得
        xlsWorkSheet = CType(xlsWorkSheets.Item(1), Excel.Worksheet)
        'シート名称
        xlsWorkSheet.Name = "シート名test"

        'セル選択
        xlsRange = xlsWorkSheet.Range("A1")
        'セルに値設定
        xlsRange.Value = "TEST123"
        MRComObject(xlsRange)

        '保存時の問合せダイアログを非表示に設定
        xlsApplication.DisplayAlerts = False
        'ファイルに保存 (Excel 2007～ブック形式)
        xlsWorkbook.SaveAs(Filename:=strFileName, FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook)
        '保存時の問合せダイアログを表示に戻す
        xlsApplication.DisplayAlerts = True

        '終了処理
        'xlsWorkSheet の解放
        MRComObject(xlsWorkSheet)
        'xlsWorkSheets の解放
        MRComObject(xlsWorkSheets)
        'xlsWorkbookを閉じる
        xlsWorkbook.Close(False)
        'xlsWorkbook の解放
        MRComObject(xlsWorkbook)
        'xlsWorkbooks の解放
        MRComObject(xlsWorkbooks)
        'Excelを閉じる 
        xlsApplication.Quit()
        'xlsApplication を解放
        MRComObject(xlsApplication)
    End Sub

    'COM オブジェクトへの参照を解放
    Private Sub MRComObject(ByRef objCom As Object)
        If Not objCom Is Nothing Then
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom)
            Catch
                '
            Finally
                '参照を解除する
                objCom = Nothing
            End Try
        End If
    End Sub

End Class

各処理の途中でエラーが発生した場合には COM オブジェクトの解放が行われない場合がありますので、 変数の宣言以降を Try … Catch … End で括って更に終了処理を Finally にまとめて以下の様にすれば解消できるはずです。

簡単なエクセル処理の例２

Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        'Excel のアプリケーション参照用オブジェクト
        Dim xlsApplication As Excel.Application = Nothing
        'Excel の Workbooks 参照用オブジェクト (Workbook の Collection)
        Dim xlsWorkbooks As Excel.Workbooks = Nothing
        'Excel の Workbooks 内の1個の Workbook 参照用オブジェクト
        Dim xlsWorkbook As Excel.Workbook = Nothing
        'Excel の Workbook 内の Worksheets 参照用オブジェクト (Worksheet の Collection)
        Dim xlsWorkSheets As Excel.Sheets = Nothing
        'Excel の Sheets 内の1個の Worksheet 参照用オブジェクト
        Dim xlsWorkSheet As Excel.Worksheet = Nothing
        'Excel の Sheet 内の1個のセル Range 参照用オブジェクト
        Dim xlsRange As Excel.Range = Nothing
        'フルパスでのファイル名 (ファイル名のみだと Excel 側のデフォルトのフォルダになるはず)
        Dim strFileName As String = My.Application.Info.DirectoryPath & "\test.xlsx"

        Try
            'Excel アプリケーション起動 
            xlsApplication = New Excel.Application
            'Excel の Workbooks 取得
            xlsWorkbooks = xlsApplication.Workbooks
            'Excel非表示
            xlsApplication.Visible = False
            xlsApplication.DisplayAlerts = False
            '既存 Excel ファイルを開く
            xlsWorkbook = xlsWorkbooks.Open(strFileName)
            'Excel の Worksheets 取得
            xlsWorkSheets = xlsWorkbook.Worksheets
            'Excel の Worksheet 取得
            xlsWorkSheet = CType(xlsWorkSheets.Item(1), Excel.Worksheet)
            'シート名称
            xlsWorkSheet.Name = "シート名test"

            'セル選択
            xlsRange = xlsWorkSheet.Range("A1")
            'セルに値設定
            xlsRange.Value = "TEST123ABC"
            MRComObject(xlsRange)

            '保存時の問合せダイアログを非表示に設定
            xlsApplication.DisplayAlerts = False
            'ファイルに保存 (Excel 2007～ブック形式)
            xlsWorkbook.SaveAs(Filename:=strFileName, FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook)
            '保存時の問合せダイアログを表示に戻す
            xlsApplication.DisplayAlerts = True

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            '終了処理
            'xlsWorkSheet の解放
            MRComObject(xlsWorkSheet)
            'xlsWorkSheets の解放
            MRComObject(xlsWorkSheets)
            'xlsWorkbook のオブジェクトが存在すれば閉じる
            If Not xlsWorkbook Is Nothing Then
                xlsApplication.DisplayAlerts = False
                xlsWorkbook.Close()
                xlsApplication.DisplayAlerts = True
            End If
            'xlsWorkbook の解放
            MRComObject(xlsWorkbook)
            'xlsWorkbooks の解放
            MRComObject(xlsWorkbooks)

            'xlsApplication のオブジェクトが存在すれば閉じる
            If Not xlsApplication Is Nothing Then
                xlsApplication.DisplayAlerts = False
                xlsApplication.Quit()
                xlsApplication.DisplayAlerts = True
            End If
            'xlsApplication を解放
            MRComObject(xlsApplication)
        End Try
    End Sub

    'COM オブジェクトへの参照を解放
    Private Sub MRComObject(ByRef objCom As Object)
        If Not objCom Is Nothing Then
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom)
            Catch
                '
            Finally
                '参照を解除する
                objCom = Nothing
            End Try
        End If
    End Sub

End Class

関連する記事