プログラマ・アゲイン blog

還暦を過ぎたけどプログラマ復帰を目指してブログ始めました

トップ > 言語 > win32oleを使用したExcel操作

win32oleを使用したExcel操作

言語 言語-Ruby プログラム

RubyプログラムからExcelファイルを操作する方法については、以下のような沢山のWebページがあり参考になりました。

magazine.rubyist.net

objectclub.jp

今回は、それらを基に自分でコーディングした、win32oleライブラリーを使用したExcel操作について、説明したいと思います。

 

コーディング・サンプル

先ず、どんなコーディングをしたかを整理するために、いろいろなところで作成したものを集めてみました。

各モジュールに共通しているのは Excelファイルのオープン/作成/クローズで、個別の処理の中ではExcelセルへの操作がいろいろありました。

require 'win32ole'
# Excel VBA定数のロード
module ExlConst; end
excel = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, ExlConst)
begin
  # EXCELへの操作
  excel.WindowState = ExlConst::XlMaximized
  excel.Visible = true
  # EXCELファイルのオープン
  workbook = excel.Workbooks.Open(template_filename, true)
  # EXCELワークシートの操作
　worksheet = workbook.Worksheets.Item('SHEET1')
  worksheet.Activate
  excel.ActiveWindow.ScrollRow = sheet_row
  # EXCELセルへの値の挿入
  worksheet.Cells.Item(sheet_row, column_id).Value = members[0]
  worksheet.Cells.Item(last_row + 1, 'J').Formula = "=AVERAGE(J2:J#{last_row})"
  worksheet.Cells.Item(last_row + 2, 'J').Formula = "=MAX(J2:J#{last_row})"
  worksheet.Cells.Item(last_row + 3, 'J').Formula = "=MIN(J2:J#{last_row})"
  # Excelセルの色付け
  sheet.Cells.Item(row, col).Interior.ColorIndex = 6 # Yellow
  # 列の幅の調整
  sheet.Columns('A:H').AutoFit
  # 表のヘッダー部の色付けなどの装飾
  range = sheet.range('A1:H1')
  range.interior.themeColor = ExlConst::XlThemeColorAccent1
  range.font.themeColor = ExlConst::XlThemeColorDark1
  range.font.bold = true
  # 罫線の色付けなどの装飾
  border = worksheet.Range(worksheet.cells(sheet_row + 1, 5), worksheet.Cells(sheet_row + 1, 6)).Borders(ExlConst::XlEdgeTop) # 罫線上端
  border.LineStyle = ExlConst::XlLineStyleNone   # 線なし
  border.LineStyle = ExlConst::XlDot             # 点線
  border.Weight = ExlConst::XlThin               # 太さ細
  border.ColorIndex = 1                          # 色コード 1 黒
  # Excel worksheetに一行追加し一部罫線を削除
  def insert_rowone(sheet, row)
    sheet.Rows(row.to_s + ":" + row.to_s).copy
    sheet.Rows((row + 1).to_s).Insert
    sheet.Range(sheet.cells(row + 1, 9), sheet.cells(row + 1, 14)).ClearContents
    sheet.Range(sheet.cells(row + 1, 16), sheet.cells(row + 1, 21)).ClearContents
    sheet.Cells.Item(row + 2, 'A').Formula = "=A#{row + 1}+1"
  end
  # 最終編集行の取得
  xllastrow = sheet.Columns(col).SpecialCells(ExlConst::XlLastCell).Row
  lastrow = sheet.Cells(xllastrow, col).End(ExlConst::XlUp).Row
rescue => exception
  # 障害時処理
ensure
  # 終了処理
  excel.DisplayAlerts = false
  begin
    if workbook
      if output_filename == template_filename
        workbook.Save
      else
        workbook.SaveAs(output_filename)
      end
    end
  rescue => exception
    # 障害時処理
  end
  excel.DisplayAlerts = true
  # Excelファイルのクローズ
  excel.Workbooks.Close
  # Excelアプリケーションの終了
  excel.Quit
end

それぞれの内容について、説明していきます。

 

実行の準備

クラスの読み込み

require 'win32ole'

前回のブログにも書きましたが、WIN32OLEクラスはExcelに特化したクラスではなく、汎用的なクラスです。ただ、使用するためには上記のように requireメソッドで読み込んでおきます。

 

Excel定数の設定

Excel定数とは、Office アプリケーションでさまざまな用途で使われている定数のことで、COM コンポーネントで定義された定数の内、Excelで使われているものの事とします。

module ExlConst; end
excel = WIN32OLE.new('Excel.Application')
WIN32OLE.const_load(excel, ExlConst)

この COMコンポーネントで定義された定数を、Ruby のクラスや モジュールにロードするために WIN32OLEクラスの const_loadメソッドを使います。

1行目では、ExlConstという名前でモジュールを宣言しています。

2行目では、Excel Applicationオブジェクトを作成しています。

3行目で、ExlConstモジュールに excelという Applicationオブジェクト(COMオブジェクト)で使われている定数がロードされます。WIN32OLEクラスの const_loadメソッドの第二引数は、モジュール/クラス になります。Rubyは動的なプログラミング言語で、実行中にモジュールに定数の定義を追加することもできるという特徴を利用しています。

なお、Rubyで定義される定数は Rubyの文法上 大文字から始まるという制約がある為、Excel定数名の最初の文字は大文字になります。

これで、例えば「ExlConst::XlEdgLeft」という形で定数を使うことができます。(XlEdgLeftは、左の境界線の事で、値は 7です)

サンプル・ソースの中でも、ExlConst::XlMaximized、ExlConst::XlLastCell、ExlConst::XlUp、ExlConst::XlThemeColorAccent1、ExlConst::XlThemeColorDark1、ExlConst::XlEdgeTop、ExlConst::XlLineStyleNone、ExlConst::XlDot、ExlConst::XlThinを使用しています。それぞれの意味については後述します。

 

Applicationオブジェクトの操作

Excel のオブジェクトには、以下のようなものがあります。

  • Applicationオブジェクト：実行しているアプリケーションに関する情報を提供
  • Workbookオブジェクト ：Excel アプリケーションで開かれている 特定のブック
  • Worksheetオブジェクト：ブックの中の一枚のシート
  • Rangeオブジェクト　　：セル、行、列、１つ以上のセルのブロックを含む範囲

Applicationオブジェクトは、前述で作成しました。

要は、Excelアプリケーションの事ですが、以下のように稼働時の動作を指定することもできます。

  excel.WindowState = ExlConst::XlMaximized
  excel.Visible = true
 
  worksheet.Activate
  excel.ActiveWindow.ScrollRow = sheet_row

1行目で、Excelアプリケーション画面を最大化しています。

2行目で、それを画面に表示して見えるようにしています。

わざわざ見えるようにしたのは、プログラムが動いていることが判るようにと思っての事ですが、Excel表が大きい場合、3行目のワークシート切り替えや 4行目の行切り替えをしないと画面自体が変わらないので、あまり意味はありませんでした。

 

Excelファイルへの操作

オープン

前述の Workbookオブジェクトを、作成します。ここでは、既存のExcelファイルをオープンすることによって作成しています。なお、新規に Excelファイルを作成する場合は、 Workbooksオブジェクト の Addメソッドを使います。

workbook = excel.Workbooks.Open(template_filename, true)

Workbooksオブジェクトの Openメソッドで、Excelファイル(ブック)を開きます。

引数の template_filenameには、Excelファイルの絶対パスが入っています。2番目の trueは、ブックを読み取り専用モードで開くことを指定しています。

詳しくは、以下のページに書かれています。

Workbooks.Open メソッド (Excel) | Microsoft Docs

 

保存

サンプルでは、既存Excelファイルを更新した後、それが新規保存の場合は SaveAsメソッドで、上書き保存の場合には Saveメソッドで保存しています。

      if output_filename == template_filename
        workbook.Save
      else
        workbook.SaveAs(output_filename)
      end

因みに、新規保存か上書き保存かは、読み込んだファイルと書き込むファイルのファイル名が違うかどうかで判断しています。

 

クローズ

Workbooksオブジェクトの Close メソッドで、開いている全てのブックを閉じます。

excel.Workbooks.Close

該当のブックだけを閉じたい場合は、以下のようにもコーディングできます。

workbook.Close

 

Excelの終了

最後に、Applicationオブジェクトの Quitメソッドで、Excelアプリケーションそのものを終了させます。

excel.Quit

これは、Rubyスクリプトが終了したのに Excelアプリケーションが立ち上がったままにならないように、必ずコーディングします。

 

Worksheetの操作

ワークシート (worksheet)

ワークシートの作成は、Workbookオブジェクトのプロパティメソッド Worksheetsを用いています。

worksheet = workbook.Worksheets.Item('SHEET1')

Worksheets.Itemプロパティで、コレクションから単一のオブジェクトを取得しています。その時、「SHEET1」を指定することで、そのワークシートに名前を付けています。

因みにコレクションという言葉ですが、COMオブジェクトには名前付け規則があり、「〜s」というプロパティは「s」をとった COMオブジェクトのコレクションオブジェクトとなることが多いそうです。例えば、Worksheetsプロパティは、Worksheetオブジェクトのコレクションオブジェクトという事のようです。なので、コレクションというのは、Win32OLEで eachメソッドが使えるようなオブジェクト(簡単に言うと複数まとまっているオブジェクト)の事と考えれば良さそうです。

 

セル (cell)プロパティ

通常 Excelアプリケーションを直接使用して表などを作成している時のセルと、Rubyプログラムから WIN32OLEで操作する時の Cellsとは、若干イメージが違います。(こう思うのは私だけかもしれませんが)

直接 Excelアプリケーションで操作している時には、あたかもセル 1つ1つがオブジェクトのイメージで、このオブジェクトの中に、値や式や属性などが紐づけられて持たれていると思っていました。

しかし、Cellは実際は Worksheetオブジェクトのプロパティであり、Rangeオブジェクトの代替のようなイメージです。

なお、セルの指定は、Cellsプロパティだと、R1C1形式の指定になっています。(行、列)の順で指定するので、Excelを直接操作している時のセルの指定('A1')=(列行)の指定とは違っているので注意が必要です。

また、R1C1形式は、行列ともに数字となる表示形式のことです。つまり、列の指定は Excelではアルファベット(A～)ですが、Item(,列)の中では数字でも可能となります。数字だと、今の列の 3列右とかは、「couurnt_col + 3」で指定できるので便利です。

具体的に使用している部分について説明します。

値の挿入
  worksheet.Cells.Item(sheet_row, column_id).Value = members[0]
  worksheet.Cells.Item(last_row + 1, 'J').Formula = "=AVERAGE(J2:J#{last_row})"
  worksheet.Cells.Item(last_row + 2, 'J').Formula = "=MAX(J2:J#{last_row})"
  worksheet.Cells.Item(last_row + 3, 'J').Formula = "=MIN(J2:J#{last_row})"('SHEET1')

1行目では、セルを Item(sheet_row, column_id)で指定し、Valueプロパティに値を代入しています。Valueプロパティに値を代入することで、セルに値が挿入されます。

詳しくは、以下に書かれています。

Range.Value プロパティ (Excel) | Microsoft Docs

 

2行目から4行目は、計算式(AVERAGE、MAX、MIN)を代入した場合です。この場合は、Valueプロパティではなく、Formulaプロパティに代入します。

これらの計算式では、"J2"セルから "J#{last_row}"セルまでの平均値、最大値、最小値を求めています。#{last_row}は、変数last_rowの値が埋め込まれる式展開です。

詳しくは、以下に書かれています。

Range.Formula プロパティ (Excel) | Microsoft Docs

色づけ
  sheet.Cells.Item(row, col).Interior.ColorIndex = 6 # Yellow

このサンプルでは、Item(row, col)セルの背景色(Interior)を黄色に設定しています。

文字の色を変えたい場合には、Interiorの部分をフォント(Font)に置き換えます。

色の指定方法ですが、簡単な方法として、Excelが持っている 56色のパレットを使用しています。6番が黄色に設定されています。

パレットの色など詳細については、以下の書かれています。

【VBA入門】ColorIndexの使い方と色見本一覧(色番号、RGB) | 侍エンジニアブログ

列の幅調整
  sheet.Columns('A:H').AutoFit

このサンプルでは、A列からH列の列の幅を、AutoFitメソッドを使用して内容に合わせて調整しています。

詳しくは、以下のページに書かれています。

Range.AutoFit メソッド (Excel) | Microsoft Docs

 

Rangeオブジェクト

Rangeオブジェクトとは、セル、行、列、連続した1つ以上のセルブロックを含むセルの選択、または3D範囲を表しています。

ただ、Cellsプロパティと違って、範囲アドレスに文字列引数を使用する場合、アドレスは A1スタイルの形式で指定する必要があります。つまり、必ず"列行"や"列行:列行"の文字で指定します。Excelアプリケーションを使う場合はデフォルトが A1形式なので、そういう意味では違和感はないのですが、プログラミングすることを考えると大変な面はあります。

詳しくは、以下のページに書かれています。

Range オブジェクト (Excel) | Microsoft Docs

色づけ
  range = sheet.range('A1:H1')
  range.interior.themeColor = ExlConst::XlThemeColorAccent1
  range.font.themeColor = ExlConst::XlThemeColorDark1
  range.font.bold = true

1行目は、シートの1行目の、A列からH列のセル範囲をrangeオブジェクトとしています。

2行目では、そのセル範囲の内部の色を、アクセント1（青）に設定しています。設定は、Rangeオブジェクトの interiorプロパティから取得したインテリア・オブジェクトのthemeColorプロパティに、定数の XlThemeColorAccent1 (値は5)を代入することで行います。

3行目では、そのセル範囲の文字の色を、背景1（白）に設定しています。設定は、Rangeオブジェクトの fontプロパティから取得したフォント・オブジェクトのthemeColorプロパティに、定数の XlThemeColorDark1 (値は1)を代入することで行います。

4行目では、そのセル範囲の文字を、太文字にしています。設定は、Rangeオブジェクトの fontプロパティから取得したフォント・オブジェクトの boldプロパティに、trueを代入することで行います。

ここで使用している ThemeColor(テーマカラー)ですが、カラーパレットにある「テーマの色」の1行目に表示されている、基本色の事です。

今使用している Excel 365では、以下のようなテーマカラーになっています。

f:id:hanasakag:20210824165449p:plain

テーマカラーは、OFFICEのバージョンでも変わるし、ページレイアウト・タブの配色で、以下のような他のテーマカラーに変えることもできます。

f:id:hanasakag:20210824170301p:plain

詳しくは、以下のページに書かれています。

Excel VBA セルにテーマの色を設定する ThemeColorプロパティ

罫線

罫線は、上・下・左・右の境界線のことです。使用する Bordersオブジェクトは、Rangeオブジェクトの 4つの罫線を表す 4つの Borderオブジェクトのコレクションです。

  border = worksheet.Range(worksheet.cells(sheet_row + 1, 5), worksheet.Cells(sheet_row + 1, 6)).Borders(ExlConst::XlEdgeTop) # 罫線上端
  border.LineStyle = ExlConst::XlLineStyleNone   # 線なし
  border.LineStyle = ExlConst::XlDot             # 点線
  border.Weight = ExlConst::XlThin               # 太さ細
  border.ColorIndex = 1                          # 色コード 1 黒

1行目で、Rangeオブジェクトの Bordersプロパティで、上の境界線の Bordersコレクションを取得しています。

2行目～3行目は、Bordersオブジェクトの LineStyleプロパティに定数を代入することで、罫線の種類を設定しています。

4行目は、Bordersオブジェクトの Weightプロパティに定数を代入することで、罫線の太さを設定しています。

5行目は、Bordersオブジェクトの ColorIndexプロパティに定数を代入することで、罫線の色を設定しています。

罫線の種類や太さについては、以下のページに書かれています。

罫線の種類の設定 - セルの罫線の設定 - Excel VBA入門

 

その他

今まで説明してきたもの以外にも、いろいろなオブジェクトやメソッドがあります。それらのうち、以下のようなものも使っています。

  # Excel worksheetに一行追加し一部罫線を削除
  def insert_rowone(sheet, row)
    sheet.Rows(row.to_s + ":" + row.to_s).copy
    sheet.Rows((row + 1).to_s).Insert
    sheet.Range(sheet.cells(row + 1, 9), sheet.cells(row + 1, 14)).ClearContents
    sheet.Range(sheet.cells(row + 1, 16), sheet.cells(row + 1, 21)).ClearContents
    sheet.Cells.Item(row + 2, 'A').Formula = "=A#{row + 1}+1"
  end
  # 最終編集行の取得
  xllastrow = sheet.Columns(col).SpecialCells(ExlConst::XlLastCell).Row
  lastrow = sheet.Cells(xllastrow, col).End(ExlConst::XlUp).Row
Rowsプロパティ

3行目～4行目は、Rowsプロパティを使用して、copyメソッドで該当の行をコピーし、Insertメソッドで指定した行に挿入しています。

因みに、今までセルに対する捜査は Cellsや Rangeで行いましたが、行の操作は Rowsプロパティ、列の操作は Columnsプロパティで行うことができます。

詳しくは、以下のページに書かれています。

【ExcelVBA入門】Rowsプロパティを使って行操作する方法を徹底解説！ | 侍エンジニアブログ

ClearContentsメソッド

5行目～6行目は、Rangeオブジェクトの ClearContentsメソッドを使って、範囲内の値と数式をクリアしています。

最終行

10行目～11行目は、最終行の行番号を取得しています。

先ず10行目は、Worksheetオブジェクトの Columnsプロパティで、指定したワークシートの列を表す Rangeオブジェクトを取得します。次に、その Rangeオブジェクトの SpecialCellsメソッドで、指定した型と値に一致するすべてのセルを表す Rangeオブジェクトを取得します。ここで指定した SpecialCells(ExlConst::XlLastCell)は、Excelシート上で 先頭を選択して「Ctrl + Shift + End」キーを押した時と同じなので、指定した列の最終行の Rangeオブジェクトが返ります。そこから Rowプロパティで、行番号を取得します。

通常ならこれで最終行が取得できるのですが、Excelを使っていて(理由はよく理解できていません)最終行が全然違ったところになる場合があります。

そこで念のために、11行目では、EndプロパティにXlUpを指定(Excelの「End + 上方向キー」で直前のデータセルまで飛ぶ)し、Rowプロパティで行番号を再度取得しています。

それぞれで取得した xllastrowと lastrowの値が違っていた場合には、lastrowの値が最終行の番号という事にします。

最終行の取得方法については、以下のページに書かれています。

VBAで最終行を取得するには？｜End(xlUp)、SpecialCells(xlLastCell) | 侍エンジニアブログ

 

このように、win32oleを使用したExcel操作では、Excelアプリケーションで行っていることを同じように実行することができます。次回は、rubyXLについてまとめてみたいと思います。