スポンサーサイト

    上記の広告は1ヶ月以上更新のないブログに表示されています。
    新しい記事を書く事で広告が消せます。

    vb.netからExcelのグラフを作成する&COM参照解放の一工夫

    今回はExcelのグラフをvb.netから作成する基本です.

    VisualStudioとExcelがインストールされている環境で開発を行い,VSTOは使用せずにCOM相互運用で操作をします.

    「これ,苦労したよなぁ」と思ってまとめていたのですが,苦労したのはグラフの作成部分ではなくてそのグラフの整形やら何やらで,プログラムでグラフを作成する場合の特有の問題ではなかったので,その点はまた別の記事にまとめます.結局今回の記事はまとめる必要もあったかなぁ,というレベルの,基本的な話になってしまいました.

    簡単すぎることだけをまとめるのも何なので,最後にCOMオブジェクトの参照を解放するコードについて少し工夫をしてみたのでそれも紹介します.

    1.グラフ作成(基本)
    Excelでシートにグラフを作成する際はChartクラスのインスタンスを作成してこれに対して設定する必要がある.ChartのインスタンスはChartObjectが所有し,ChartObjectのインスタンスはSheetのインスタンスがコレクションとして保持している,入れ子構造になっている.

    手順としてはSheetからChartObjectのコレクションを取得(★1)し,そのコレクションに対してAdd関数でChartObjectを追加(★2).さらにChartObjectのメンバにあるChartを取得(★3)し,ChartのSeriesCollectionやAxsesコレクションを使って系列の追加(★4)やグラフの整形を行う.

    系列に設定するデータは基本的には”ワークシート名!セルの参照”を設定する(★5).

    ※ このサンプルでは★2のChartObject作成でChartObjectの作成位置をセル範囲を使って指定しているが,ここは直接数値で指定ももちろん可能.
    Dim app = New Excel.Application
    app.Visible = True

    Dim books = app.Workbooks
    Dim book = books.Add()

    Dim sheets = book.Worksheets
    Dim sheet = DirectCast(sheets(1), Excel.Worksheet)

    ' プロット用ダミーデータ作成
    Dim data(360, 1) As Object
    For i As Integer = 0 To 360
        data(i, 0) = i
        data(i, 1) = Math.Sin(i / 180 * Math.PI)
    Next
    Dim cells = sheet.Cells
    Dim tl = cells(1, 1)
    Dim br = cells(361, 2)
    Dim source = sheet.Range(tl, br)
    source.Value = data

    ' グラフ作成処理
    Dim chartObjects = DirectCast(sheet.ChartObjects, Excel.ChartObjects) ' ★1チャートコレクション取得
    Dim chartArea = sheet.Range("C1:G20") ' チャートを配置するセル
    Dim chartObject = chartObjects.Add(
        CDbl(chartArea.Left), CDbl(chartArea.Top),
        CDbl(chartArea.Width), CDbl(chartArea.Height)) ' ★2チャートオブジェクト作成
    Dim chart = chartObject.Chart ' ★3チャート取得

    ' ★4系列作成
    Dim seriesCollection = DirectCast(chart.SeriesCollection, Excel.SeriesCollection)
    Dim series = seriesCollection.NewSeries()
    series.Name = "sample"
    ' ★5セルの参照を設定する場合は"シート名!参照アドレス"の形式にする(Book名は省略できる)
    series.Values = sheet.Name & "!" & sheet.Range(sheet.Cells(1, 2), sheet.Cells(361, 2)).Address
    series.XValues = sheet.Name & "!" & sheet.Range(sheet.Cells(1, 1), sheet.Cells(361, 1)).Address
    series.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers ' 散布図



    2.グラフ作成(データを直接設定する)
    Excelのグラフでデータの設定を行うときに,セルの参照を設定する代わりに,”={1, 2, 3}”などと直接データを指定することが可能だが,プログラムから設定する場合も同じようにデータを直接設定することが可能.この場合,わざわざ文字列でデータを組み立てる必要はなく,Objectの配列をSeriesのValuesやXValuesに設定すればよい(★1).
    ' プロット用ダミーデータ作成
    Dim xdata(360) As Object
    Dim ydata(360) As Object
    For i As Integer = 0 To 360
        xdata(i) = i
        ydata(i) = Math.Sin(i / 180 * Math.PI)
    Next

    ' グラフ作成処理
    Dim chartObjects = DirectCast(sheet.ChartObjects, Excel.ChartObjects) ' チャートコレクション取得
    Dim chartArea = sheet.Range("C1:G20") ' チャートを配置するセル
    Dim chartObject = chartObjects.Add(
        CDbl(chartArea.Left), CDbl(chartArea.Top),
        CDbl(chartArea.Width), CDbl(chartArea.Height)) ' チャートオブジェクト作成
    Dim chart = chartObject.Chart ' チャート取得

    ' 系列作成
    Dim seriesCollection = DirectCast(chart.SeriesCollection, Excel.SeriesCollection)
    Dim series = seriesCollection.NewSeries()
    series.Name = "sample"
    ' ★1データを直接設定する場合はObjectの配列を設定する
    series.Values = ydata
    series.XValues = xdata
    series.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers ' 散布図
    この方法だと作成されるExcelのファイルサイズが少し小さくなる.また,設定するデータが多い場合,作成されたグラフのデータを後から手動で変更することができない.

    後から手でデータを変更したいという要求がある場合にはこの方法は使えない状況が生ずるので注意が必要.


    3.参照の解放処理
    上のサンプルでは省略しているが,前の記事で言及したCOMオブジェクトの解放処理はExcelに関連するオブジェクトを取得した分すべてに対してする必要がある.例えば1のサンプルでは以下のコードが必要になる.
    System.Runtime.InteropServices.Marshal.ReleaseComObject(series)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(seriesCollection)

    System.Runtime.InteropServices.Marshal.ReleaseComObject(chart)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(chartObject)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(chartArea)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(chartObjects)

    System.Runtime.InteropServices.Marshal.ReleaseComObject(cells)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(tl)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(br)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(source)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(book)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(books)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(app)

    関数一個だけであれば良いが,さすがにこれがあちこちの関数にあると不格好なので,何か工夫をした方が良いわけだが,正直にラッパーを作ろうとすると,使用するすべてのExcel関連クラスに関してラッパーを作る必要があったりしてあまりよろしくない.次に思いつくのはスマートポインタっぽいものだけど,.netは変数のスコープが終わってもガベージコレクタが働くまではファイナライザは呼ばれないので,できれば明示的に関数終わり,またはどこかの点で解放できる方が都合が良く,とりあえず何かコレクションにオブジェクトを突っ込んで,後でまとめて解放できるような道具があればよさげ.

    ということで以下のような仕組みを考えてみたので紹介.

    リソース解放の定番と言えばIDisposableなのでそれをImplementsしたクラスを作成する.で,そのクラスでCOM参照をコレクトするようにして,Disposeメソッドで解放処理を呼ぶ.設計としてはあまりよくないが,一工夫したのが★1のオブジェクト登録処理で,コレクションにオブジェクトを登録すると同時にそのオブジェクトを返却する関数として処理を実装するところ.
    ''' <summary>
    ''' COMオブジェクト参照管理クラス
    ''' </summary>
    ''' <remarks></remarks>
    Public Class ComObjectReferenceHandler
        Implements IDisposable

        ' COMオブジェクト参照
        Private _references As New List(Of Object)

        ''' <summary>
        ''' 参照追加
        ''' </summary>
        ''' <typeparam name="T">COMオブジェクト型</typeparam>
        ''' <param name="reference">オブジェクト</param>
        ''' <returns>引数で渡されたCOMオブジェクト参照をそのまま返却</returns>
        ''' <remarks></remarks>
        Public Function Add(Of T)(reference As T) As T ' ★1
            Debug.Assert(Not _references.Contains(reference), "参照の多重登録はできません")
            _references.Add(reference)
            Return reference
        End Function

        ''' <summary>
        ''' 参照解放
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub ReleaseComObject()
            _references.ForEach(Sub(reference) System.Runtime.InteropServices.Marshal.ReleaseComObject(reference))
            _references.Clear()
        End Sub

    ' ※自動追加されるIDisposableのメソッド中でReleaseComObjectをコールする

    このようなクラスを作成し(上のサンプルは後半を省略.完全なソースはこの記事の末尾)以下のように使用する.

    まずUsingステートメントで先ほど作成したクラスのインスタンスを作成する(★1).こうすることで最後のEnd Usingで自動でDisposeが呼ばれ(★3),結果として登録したすべての参照が解放されることになる.もちろんUsingを使用せずに普通に変数を宣言してもよいが,その場合はDisposeの呼び忘れに注意.呼び忘れた場合は,変数がスコープを外れた後にガーベージコレクタが仕事をするまで参照は解放されない.

    COMオブジェクトの作成時には★2のように参照管理オブジェクトのAddに参照を渡しつつ,引数へその返値を代入する(オブジェクトを取得している部分すべての箇所をrh.Add()で包む).型はジェネリックで引数で渡した型がそのまま返却されるのでタイプセーフも守られる.設計としては余りきれいではないけど,コードの見た目や行数がほぼ変わらずに楽ができるので自分が責任者であればこのレベルは許容したい(厳しいコード規約を運用しているところだと通らないかもしれない).
    Using rh As New ComObjectReferenceHandler()  '★1参照管理
        Dim app = rh.Add(New Excel.Application) ' ★2参照をコレクションに登録すると共に変数へ代入
        app.Visible = True

        Dim books = rh.Add(app.Workbooks) 
        Dim book = rh.Add(books.Add())

        Dim sheets = rh.Add(book.Worksheets)
        Dim sheet = rh.Add(DirectCast(sheets(1), Excel.Worksheet))

        ' プロット用ダミーデータ作成
        Dim xdata(360) As Object
        Dim ydata(360) As Object
        For i As Integer = 0 To 360
            xdata(i) = i
            ydata(i) = Math.Sin(i / 180 * Math.PI)
        Next

        ' グラフ作成処理
        Dim chartObjects = rh.Add(DirectCast(sheet.ChartObjects, Excel.ChartObjects)) ' チャートコレクション取得
        Dim chartArea = rh.Add(sheet.Range("C1:G20")) ' チャートを配置するセル
        Dim chartObject = rh.Add(chartObjects.Add(
            CDbl(chartArea.Left), CDbl(chartArea.Top),
            CDbl(chartArea.Width), CDbl(chartArea.Height))) ' チャートオブジェクト作成
        Dim chart = rh.Add(chartObject.Chart) ' チャート取得

        ' 系列作成
        Dim seriesCollection = rh.Add(DirectCast(chart.SeriesCollection, Excel.SeriesCollection))
        Dim series = rh.Add(seriesCollection.NewSeries())
        series.Name = "sample"
        ' データを直接設定する場合はObjectの配列を設定する
        series.Values = ydata
        series.XValues = xdata
        series.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers ' 散布図

        book.Saved = True
        app.Quit()
    End Using ' ★3 rh.Dispose()がコールされる

    完全な参照管理クラスのソースは以下の通り.長いように見えるけど,ほとんどがIDisposableをImplementしたときにVisualStudioが自動で追加するソースで,自前で作成したコードはAdd関数およびReleaseComObjectメソッドおよびProtected Overridable Sub Dispose(disposing As Boolean)の中からReleaseComObjectメソッドを呼んでいる部分(10行程度)くらい.
    Public Class ComObjectReferenceHandler
        Implements IDisposable

        ' COMオブジェクト参照
        Private _references As New List(Of Object)

        ''' <summary>
        ''' 参照追加
        ''' </summary>
        ''' <typeparam name="T">COMオブジェクト型</typeparam>
        ''' <param name="reference">オブジェクト</param>
        ''' <returns>引数で渡されたCOMオブジェクト参照をそのまま返却</returns>
        ''' <remarks></remarks>
        Public Function Add(Of T)(reference As T) As T
            Debug.Assert(Not _references.Contains(reference), "参照の多重登録はできません")
            _references.Add(reference)
            Return reference
        End Function

        ''' <summary>
        ''' 参照解放
        ''' </summary>
        ''' <remarks></remarks>
        Public Sub ReleaseComObject()
            _references.ForEach(Sub(reference) System.Runtime.InteropServices.Marshal.ReleaseComObject(reference))
            _references.Clear()
        End Sub

    #Region "IDisposable Support"
        Private disposedValue As Boolean ' 重複する呼び出しを検出するには

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: マネージ状態を破棄します (マネージ オブジェクト)。
                End If

                ReleaseComObject()
            End If
            Me.disposedValue = True
        End Sub

        ' TODO: 上の Dispose(ByVal disposing As Boolean) にアンマネージ リソースを解放するコードがある場合にのみ、Finalize() をオーバーライドします。
        Protected Overrides Sub Finalize()
            ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
            Dispose(False)
            MyBase.Finalize()
        End Sub

        ' このコードは、破棄可能なパターンを正しく実装できるように Visual Basic によって追加されました。
        Public Sub Dispose() Implements IDisposable.Dispose
            ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
    #End Region

    End Class

    このクラスでもAddし忘れれば当然解放漏れになるので,大人数の開発やスキルの低い人を含む開発の場合にはちゃんと工夫しないといけない.このクラスの目的は,記述した処理の目的(グラフの作成など)がわかりにくくならないようにしつつ,必要な後片付けの処理(参照の解放)をコンパクトに記述することである.

    スポンサーサイト

    テーマ : ソフトウェア開発
    ジャンル : コンピュータ

    tag : Excel franework .net プログラム

    コメントの投稿

    非公開コメント

    プロフィール

    eikun

    Author:eikun
    なかなかやるきがでない人です

    えいくんち

    twitter

    pixiv

    最新記事
    最新コメント
    最新トラックバック
    月別アーカイブ
    カテゴリ
    検索フォーム
    RSSリンクの表示
    リンク
    ブロとも申請フォーム

    この人とブロともになる

    QRコード
    QR
    上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。