昨年「RubyでExcelのデータを読み込む」というエントリを立てた。ExcelのデータをRubyで処理すると、オフィスアプリを使うレイヤとシステム開発をするレイヤの橋渡しが簡単になりますよ、という記事だった。

今回はその続編。Rubyの方からExcelに値や書式を設定していく際のTipsをメモっておく。

Tips

Excelのプロセスを残さないようにする

バグなどでRubyスクリプトの実行が止まってしまっても、Excelのプロセスは生き残っている。後でタスクマネージャを見て慌てることになるので、まずは例外をつかまえてプロセス終了するよう仕込みをしよう。

@app = WIN32OLE.new('Excel.Application')
begin
# do_something
ensure
@app.quit
end
定数を読み込む / 使う

書式指定やオプションなどにExcelで定義されている定数が必要になる。WIN32OLE::const_loadで読み込み、使うときはconst_getで入手する。Rubyの定数は先頭大文字というルールがあるので、Excelでつけられたプレフィクスの先頭を大文字にした名前で引っ張る。

# 定数をロードする
WIN32OLE.const_load @app, self.class
# 使うときはこんな感じ。先頭のxlはXlになる。
self.class.const_get("XlFillSeries")
新しく作るWorkbookのワークシート数を設定

新しいワークブックを作成すると、デフォルトでは3枚のワークシートが作られる。個人的には邪魔になることが多いので、最初から1枚に設定してしまう。

@app.SheetsInNewWorkbook = 1 # 新bookのsheetを1枚に設定
@book = @app.Workbooks.add

ちなみにWorkbooks.addに引数としてテンプレートファイルを指定することができる。

ワークシートが空かどうかを判定

仕事のコードからは除去したのだが、ワークシートがブランクかどうか判定する方法も調べたのでメモしておく。

# ワークシートが空っぽかどうか判定
@book.WorkSheets.each do |s|
if @app.WorksheetFunction.CountA(s.Cells).to_i == 
# ワークシートsはブランク
@sheet = s
end
end
テキスト書式を設定する

フォント、サイズ、ボールドを設定する方法。日本語のフォント名の場合はkconvでsjisに変換する必要がある。

# ワークシート全体のフォントとサイズ
@sheet.Cells.Font.Name = 'ＭＳ ゴシック'.tosjis
@sheet.Cells.Font.Size = 9
# 特定のセルをboldに
@sheet.Cells(1, 2).Font.Bold = true
オートフィルを行う

連続データの作成など、オートフィルがあってこそのExcelといえなくもない。手順としては…

  • 起点になるセルを決めて値を設定する
  • そのセルを含むRangeを作成する
  • フィルの種類を決めて対応する定数を取得
  • 起点のセルからAutofillメソッドを呼び出す
root_cell.value = 1
root_cell.Autofill @sheet.Range(root_cell, @sheet.Cells(root_cell.Row + 255, root_cell.Column)), self.class.const_get("XlFillSeries")
カラム幅の自動調整

セルの幅をダブルクリックで調整するアレをRubyから行うには、行を選択してAutoFitメソッドを呼び出す。

column = @sheet.Columns(1)
column.AutoFit
セルの背景色と塗りパターンを指定

セルの塗りや罫線をコントロールするには、Interiorプロパティを操作する。

cell.Interior.ColorIndex = 45     # 微妙なオレンジ
cell.Interior.Pattern = self.class.const_get('XlSolid')
ワークシートをコピーして末尾に追加

テンプレートを使って作ったワークブックでも、普通にWorksheets.Addを呼んでワークシートを追加すると真っ白なものができてしまう。テンプレートのシートをコピーして末尾に追加する操作は以下のように行う。

last_sheet = @book.WorkSheets.Item(@book.WorkSheets.Count)
@book.WorkSheets(TEMPLATE_NAME).Copy({ "After" => last_sheet })
new_sheet  = @book.WorkSheets.Item(@book.WorkSheets.Count)
ワークシートを名前で指定して消す

普通にWorksheet#Deleteを呼んでも消えない。DisplayAlertsプロパティを一時的にfalseにし、確認ダイアログを抑止する必要があるようだ。

display_alerts = @app.DisplayAlerts
@app.DisplayAlerts = false
@book.WorkSheets('sheet_name').Delete
@app.DisplayAlerts = display_alerts
セルから値を取り出す

メソッドがいくつもあるのでメモ。

cell = @sheet.Cells(1,2)
puts cell.Value                 # 値
puts cell.Value2                # 値(通過型/日付型のデータをdoubleで返す)
puts cell.Text                  # 表示されているそのままを返す
puts cell.Formula               # Cellの数式を返す

●MS リファレンス

http://msdn.microsoft.com/ja-jp/library/microsoft.office.tools.excel%28VS.80%29.aspx

●Office TANAKA – Excel VBA Tips[連続データを作成する]

http://officetanaka.net/excel/vba/tips/tips76.htm

●新規ブックの作成 – ブックの作成と保存 – Excel VBA入門

http://www.officepro.jp/excelvba/book_new/index1.html

●ブック作成時のワークシートの作成枚数 – 共通の操作 – Excel VBA入門

http://www.officepro.jp/excelvba/application_other/index5.html

●6.2 セルに背景色を付ける

http://www.happy2-island.com/excelsmile/smile03/capter00602.shtml

●Office TANAKA – Excel VBA Tips[セルのValue2プロパティ]

http://officetanaka.net/excel/vba/tips/tips115.htm

●RubyでExcelのデータを読み込む: Kwappa開発室

http://kwappa.txt-nifty.com/blog/2009/10/rubyexcel-98ca.html