スポンサーサイト

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

    vb.netからEXCELを操作する

    たまにプログラムからEXCELを操作する仕事をするといつもあれこれ忘れてしまっているので,備忘録もかねてメモを.

    以下はvb.net(.net framework4.0)でExcel2007を操作するためのコードですが,部分部分であれば.net frameworkのバージョンやofficeのバージョンはいくつでも使えるし,VBAでも応用は利く物もあります.

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

    1.設計方針
    プログラムからExcelを操作するのは重い処理になる.ページ設定やセルなどの装飾等,固定で済む物はあらかじめExcelのファイルで作成しておいて,それをテンプレートとして使用するのが良い.テンプレートのExcelファイルを開く→用意してあるテンプレートのシートをコピー→コピーしたシートに対して必要な処理を行う,のような感じ.例えば,あるセルに値を設定し,他のセルにその値から計算できる値を設定して・・・のようにするのではなく,あらかじめテンプレート用のシートに計算で求まるセルは必要な値を格納するセルを参照するように作っておき,プログラムからは最小限必要なセルだけを書き込む,のような感じなど.セルへの値の設定はやり方によってパフォーマンスは段違いなのでこの例の通りにする必要はないけど,Excelの操作はコストがかかるので,できる限り少ない操作で必要な処理が完了するように心がけること.

    お客さんから「こんな感じでつくって」というようなイメージをもらって,必要なパーツをテンプレートにし,それを利用するのもいいかもしれない.図形とか注釈とかのオブジェクトもパーツ用のシートに集めておいてそこからコピーとかするのが楽.

    この場合,テンプレート用のファイルを不意に移動されたり内容を編集されると困ったことになるので,ファイルの置き場所を人からわかりにくいところにしたり,そのファイルをいじらないよう通知する等して対策をすること.実行ファイルやDLLにリソースとして埋め込んでもいいかも.



    2.基本
    プロジェクトからMicrosoft Excel 12.0 Object Libraryへ参照を追加する

    以下のように名前空間をインポートしておくとコードを書きやすい.
    Imports Microsoft.Office.Interop

    Excelの起動と終了は以下のような感じ.デフォルトだと画面が見えないが,画面が見える状態だとプログラムからExcelを操作している最中でも人がExcelを操作可能なので,リリースするときはVisibleをFalseにする.Excelを終了させるときはQuitを呼ぶ.Excelを終了させようがさせまいが,Excelのオブジェクトが不要になったらReleaseComObjectを忘れずにすること.
    Dim app = New Excel.Application
    ' この間でExcelをプログラムから操作する
    app.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(app)

    ReleaseComObjectによるCOMオブジェクト解放は,Excelを操作する過程で使用するすべてのCOMオブジェクトについて必要で,注意点としては
    app.Workbooks.Add()
    のようにコレクション系のプロパティの操作を直接呼び出すと,コレクションのCOMオブジェクトが裏で作成されて解放できなくなるので,必ず以下のように
    Dim books = app.Workbooks
    Dim book = books.Add()
    のように一端コレクションオブジェクトを取得してから処理を行い,
    System.Runtime.InteropServices.Marshal.ReleaseComObject(book)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(books)
    と解放を行う必要がある.うっかりしやすいのがWorkSheet.CellsやChart.Axesのように複数の引数を取ることができるコレクションなど.
    Dim cells = sheet.Cells
    Dim tl = cells(3, 1)
    Dim br = cells(4, 3)
    Dim range = sheet.Range(tl, br) ' 範囲取得
    Dim data(,) As Object = DirectCast(range.Value, Object(,))
    System.Runtime.InteropServices.Marshal.ReleaseComObject(range)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(br)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(tl)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(cells)
    のようにする必要がある.基本的なところはラッパーをつくって解放処理を自動化するのがいいかもしれないが,素直にVSTOを使うのが良いか.

    手作業でできる操作はほぼプログラムでも可能.分厚い本がいろいろと刊行されているけど,Excelでマクロの記録をON状態で欲しい操作を行い,記録を終了させてから作成されたVBAマクロ(Alt+F11)を参考にすればたいてい必要な情報が得られるので本は不要だと思う.エディタからF1でマイクロソフトのヘルプのWebページも表示してくれる.

    コレクションの要素を取得するとObject型で帰ってくるものが多数あり,適切にキャストする必要があってちょっと面倒(WorkSheet.CellsとかChart.Axesなど).コーディング規約とかで縛りがなければOption Strict Offでよい(Excelを操作するモジュールだけOption Strict Offにする規約とするのもいいかも).VisualStudioのコードアシストで候補として表示されないメンバがあったりする(Borderメンバなど).これは慣れるしかない.



    3.セルへのアクセス(基本)
    セルの参照はWorkSheetクラスのCellsメソッドかRangeメソッドを使う.主に使うのは以下の★1,★2の指定.Cellsメソッドだと参照がObject型で帰ってくるのがちょっと面倒.キャストしてもいいけど,★3のようにRangeメソッドに参照を放り込んでもオッケー.コレクション全般にも言えることだけど,最初の要素の添え字は0ではなく1から開始する.
    Dim books = app.Workbooks
    Dim book = books.Add()
    Dim sheets = book.Worksheets
    Dim sheet = DirectCast(seets(1), Excel.Worksheet)
    Dim cells = sheet.Cells
    Dim cell1 As Excel.Range = DirectCast(cells(1, 1), Excel.Range)
    cell1.Value = "行列指定" ' ★1
    sheet.Range("A2").Value = "参照形式" ' ★2
    Dim cell2 = sheet.Cells(3, 1) ' ★3
    sheet.Range(cell2, cell2).Value = "行列指定+Rangeメソッド"

    範囲を指定したセルへのアクセスは以下のような感じ.Rangeメソッドに"A3:C5"と参照形式のアドレスを入れても同じように範囲を取得できるけど,プログラム的にはCellsを使ったやり方の方が使いやすい.
    Dim cells = sheet.Cells
    Dim tl = cells(3, 1)
    Dim br = cells(5, 3)
    Dim range = sheet.Range(tl, br)
    Dim localCells = range.Cells
    Dim localCell = DirectCast(localCells(1), Excel.Range)
    localCell.Value = "AAAAA"
    range.Range("B2").Value = "BBBB"

    上記は一つ一つのセルに対してデータを設定したり取得したりする場合だが,大量のセルに対して処理を行う場合にはパフォーマンスが出ない.Excel.ApplicationのScreenUpdating等のオプションをいじって多少早くはなるが,気持ち程度の改善にしかならないので,大量のセルに対する読み書きの操作が必要な場合は配列を用いてデータの出し入れを行う.

    4.セルへのアクセス(大量のセルの操作を行う場合)
    大量のセルにデータを設定する場合,例えばM×Nの表をデータで埋めるような場合には以下のようにしてデータを一度にセットすることが可能.
    Dim cells = sheet.Cells
    Dim tl = cells(3, 1)
    Dim br = cells(4, 3)
    Dim range = sheet.Range(tl, br) ' 範囲取得
    Dim data(1, 2) As Object ' データ設定用配列
    data(0, 0) = "hoge" ' 配列にデータ設定
    data(1, 2) = "huga"
    range.Value = data ' セルにデータ設定

    設定したいセルの範囲と同じサイズ(かそれ以上)のObject型の二次元配列を用意し,まずはその配列にデータを設定してからセル範囲に配列を設定する.用意する配列のサイズが小さい場合,セルに"N/A"と設定されてしまうので注意する.また,VB.NETでは配列のインデックスは0から始まるのでその点も注意.

    1行N列やN行1列のような場合でも同様に二次元配列を用意する.
    ' 縦長
    Dim cells = sheet.Cells
    Dim tl = cells(1, 1)
    Dim br = cells(4, 1)
    Dim rangeTate = sheet.Range(tl, br) ' 範囲取得
    Dim dataTate(3, 0) As Object ' データ設定用配列
    dataTate(0, 0) = "hoge" ' 配列にデータ設定
    dataTate(3, 0) = "huga"
    rangeTate.Value = dataTate ' セルにデータ設定

    ' 横長
    Dim cells = sheet.Cells
    Dim tl = cells(1, 1)
    Dim br = cells(1, 4)
    Dim rangeYoko = sheet.Range(tl, br) ' 範囲取得
    Dim dataYoko(0, 3) As Object ' データ設定用配列
    dataYoko(0, 0) = "hoge" ' 配列にデータ設定
    dataYoko(0, 3) = "huga"
    rangeYoko.Value = dataYoko ' セルにデータ設定

    データを取得する場合は単純にセル範囲のValueから取得する
    Dim cells = sheet.Cells
    Dim tl = cells(3, 1)
    Dim br = cells(4, 3)
    Dim range = sheet.Range(tl, br) ' 範囲取得
    Dim data(,) As Object = DirectCast(range.Value, Object(,))

    これでセル範囲に応じた二次元配列(上記の例の場合2×3)が取得できる.



    5.参照アドレス文字列
    例えば100行100列目のセルを示す参照アドレスの文字列を作る処理は結構めんどくさかったりする.ボトルネックにならないような場所であれば,以下で知りたいセルのアドレスを取得することが可能.
    Dim cells = sheet.Cells
    Dim cell = cells(100, 100)
    DirectCast(cell, Excel.Range).Address

    単独のセルではなく,範囲についても同様にアドレスを取得可能.
    Dim cells = sheet.Cells
    Dim tl = cells(1, 1)
    Dim br = cells(1, 4)
    sheet.Range(tl, br).Address

    取得できる文字列は"$A$1"のように絶対参照形式なので,必要に応じて文字列の編集を行う.

    ただし,COM経由でExcelにアクセスするためにパフォーマンスは猛烈に悪い(セルにデータを出し入れするのと同じくらいコストがかかると思って良い).大量のループ中でアドレスを作成する必要がある場合には素直に関数を作成するしかない(こちらに列番号から列名を作成する関数を作ってみました).でも,たいていの場合は上記の方法で事足りると思う.


    シートやブックなどに関する操作は基本的にごり押ししかない?苦労しそうな点を思いついたらこの記事をアップデートしていきます.グラフを作成しようとするといろいろと制限があってイライラゲームになりますが,これについては別記事でまとめる予定です.
    スポンサーサイト

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

    tag : プログラム .net franework Excel

    コメントの投稿

    非公開コメント

    プロフィール

    eikun

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

    えいくんち

    twitter

    pixiv

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

    この人とブロともになる

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