rubyXLを使用したExcel操作

今回は、rubyXLライブラリーを使用した Excel操作について、説明したいと思います。

前にも書きましたが、rubyXLは、Excel用の gemライブラリーです。したがって、gemコマンドで導入しておく必要があります。今導入しているバージョンは、3.4.15 です。

rubyXLとは、バージョン 3.4.18 の readmeでは、以下のように書かれています。

このgemは、xlsxファイル（Open XML形式）での操作をサポートします。 OOXML構造全体を適切に解析できますが、現在の主な重点は、MS Excelによって生成されたファイルの読み取り、ファイルへの小さな変更、および可能な限り多くの構造を保持しながらファイルを保存して再度開くことです。

独自のバイナリxls形式はこのgemではサポートされていないことに注意してください。 これらのファイルを解析する必要がある場合は、スプレッドシートgemを試してください。

尚、Excelを操作する gemには、rubyXL以外にも Roo(読み込みのみ)や AXSLX(新規作成のみ)というものがあるようです。

Excelで佐々木希を描く with Ruby

Rubyの勉強したての頃、以下のページを見つけました。

wmetawdata.blogspot.com

これは面白そうだと、先ずコーディングをコピーして動かしてみました。取り敢えずは、ページで書かれているように動きました。

それだけでは勉強にならないので、佐々木希の .pngファイルではなく、持っている .jpgファイルで試したり、パフォーマンスを上げる方法はないかと改造したりしました。

結局パフォーマンスは上げられませんでしたが、実行状況が分かるようにはしました。

作成したソース

# Draw Picture with Excel Version 4
# c:\ruby26-x64\user>ruby draw_excel_v4.rb xxxx (xxxx : png or jpg file name)

require 'rubyXL'
require 'rubyXL/convenience_methods/cell'
require 'rubyXL/convenience_methods/color'
require 'rubyXL/convenience_methods/font'
require 'rubyXL/convenience_methods/workbook'
require 'rubyXL/convenience_methods/worksheet'
require 'RMagick'
require './lib/img_info'

# 画像情報の取得
img = Magick::ImageList.new(ARGV[0])
width = img.columns
height = img.rows

# 画像情報の出力
info_print(img)

# Excelを作成し、最初のシートを選択
book = RubyXL::Workbook.new
sheet = book[0]

start_time = Time.now
progress_time = 0

img.each_pixel do |pixel, y, x|

  # ピクセル色を16進数で取得
  color = pixel.to_color(Magick::AllCompliance, false, img.depth, true)
  color.delete!('#')

  # 塗りつぶし
  sheet.add_cell(x, y, '')
  sheet.sheet_data[x][y].change_fill(color)

  # 進捗率と経過時間の表示
  progress_rate = (x * 100) / height
  progress_time = Time.now - start_time
  printf ("Excel export in progress ... %3s percent %6.1f seconds, Height = %3s Width = %3s\r" % [progress_rate, progress_time, x, y])
end

# 列と行のサイズ変更
i = 0; j = 0
for i in 0..width do
  sheet.change_column_width(i, 0.01)
end
for j in 0..height do
  sheet.change_row_height(j, 5)
end

file_name = File.basename("#{ARGV[0]}", '.*')
if File.exist?("#{file_name}.xlsx")
  File.delete("#{file_name}.xlsx")
end
book.write("#{file_name}.xlsx")

printf ("Excel export completed   ... 100 percent %6.1f seconds, Height = %3s Width = %3s\n" % [progress_time, height, width])

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

尚、rubyXLの使い方については、以下のページが参考になります。

qiita.com

実行の準備

RMagick

上記のプログラムの中で、RMagickライブラリーを使用しています。

これも gemライブラリーなので事前に導入しておくのですが、導入するのに苦労します。使用していたパソコンが変わったので改めて導入し直したのですが、そのことについては別の機会に説明します。

クラスの読み込み

以前のブログにも書きましたが、RubyXLを使用するためには以下のように requireメソッドで読み込んでおきます。また、requireは、rubyXLだけでなく、いくつかのconvenience_methodsも含める必要があります。

require 'rubyXL'
require 'rubyXL/convenience_methods/cell'
require 'rubyXL/convenience_methods/color'
require 'rubyXL/convenience_methods/font'
require 'rubyXL/convenience_methods/workbook'
require 'rubyXL/convenience_methods/worksheet'

Excelファイルへの操作

Excelワークブックの作成

Excelワークブックは、Workbookオブジェクトを newメソッドで作成します。

ワークシートは、最初のシートを選択するので、book[0]を指定しています。2番目のシートなら [1]というように、0から始まります。

book = RubyXL::Workbook.new
sheet = book[0]

保存

Excelファイルへの保存は、saveメソッドを使用しますが、writeメソッドも同じものです。

Class: RubyXL::Workbook — Documentation for rubyXL (3.4.18)

file_name = File.basename("#{ARGV[0]}", '.*')
if File.exist?("#{file_name}.xlsx")
  File.delete("#{file_name}.xlsx")
end
book.write("#{file_name}.xlsx")

1行目では、Fileクラスの basenameメソッドを使用して、ファイル名の拡張子の部分を取り除いたものを取得しています。

2行目～4行目では、既に同じ名前の Excelファイルが存在していた時には削除しています。

5行目で、Excelファイル (拡張子が .xlsx) を書き出しています。

Worksheetの操作

セル (cell)の操作

セルの指定は、win32oleと同じで、R1C1形式の指定になっています。(行、列)の順で指定するので、Excelを直接操作している時のセルの指定('A1')=(列行)の指定とは違っているので注意が必要です。

  sheet.add_cell(x, y, '')
  sheet.sheet_data[x][y].change_fill(color)

1行目は、add_cellメソッドでセルを選択します。セルが無かった場合には、作成されます。add_cellの使い方は、以下のようになっており、(行、列、データなし)を指定しています。

Module: RubyXL::LegacyWorksheet — Documentation for rubyXL (3.4.18)

#add_cell(row_index = 0, column_index = 0, data = '', formula = nil, overwrite = true) ⇒ Object<<

2行目は、sheet_data[行][列]でセルを特定し、CellConvenienceMethodsの change_fillメソッドでセルの背景の色を RGBで指定しています。指定する色は、画像から取り出したピクセルの色です。change_fillの使い方は、以下のようになっています。

Module: RubyXL::CellConvenienceMethods — Documentation for rubyXL (3.4.18)

#change_fill(rgb = 'ffffff') ⇒ Object<<

上記の操作で Excelのワークシートに画像データが書き込めたのですが、このままだと 1つ1つのセルが大きくて画像が見た目どおりになりません。そこで、列の幅と行の高さを小さくします。参考にしたサンプルではセルに画像データを書き込むと同時にサイズも変更していたのですが、パフォーマンスが上がるかなと別に行うことにしました。でも、結局パフォーマンスは変わりませんでした。(T T)

列の幅変更

for i in 0..width do
  sheet.change_column_width(i, 0.01)
end

このサンプルでは、change_column_widthメソッドを使用して、0から width列までの幅を 0.01に設定しています。change_column_widthメソッドの使い方は、列番号と幅サイズを指定すればよく、以下のようになっています。

Method: RubyXL::LegacyWorksheet#change_column_width — Documentation for rubyXL (3.3.11)

#change_column_width(column_index, width_in_chars = RubyXL::ColumnRange::DEFAULT_WIDTH) ⇒ Object

行の高さ変更

for j in 0..height do
  sheet.change_row_height(j, 5)
end

行の高さの変更も、列の幅と同じように、0から height行までの高さを 5に設定しています。使用するメソッドは change_row_heightで、行番号と高さサイズを指定すればよく、以下のようになっています。

Method: RubyXL::WorksheetConvenienceMethods#change_row_height — Documentation for rubyXL (3.4.18)

#change_row_height(row = 0, height = 10) ⇒ Object

その他

rubyXLとは関係ないのですが、この画像データを Excelに取り込むプログラムはすごく時間がかかります。高さ 192px、幅 256pxの画像データの処理の場合、以下のように 1時間以上かかりました。何も表示しないと、プログラムが動いているのか、止まっているのか、分かりません。

そこで「Excel export completed  ・・・」の行で、1セルづつの進捗を表示し、プログラムが動いていることが判るようにしています。また、100 percentまでの進捗状況から、どれぐらいで終了するかの予測もたちます。

コーディングは以下の部分ですが、printfメソッドは、フォーマットに従って文字列を生成して表示してくれます。

  # 進捗率と経過時間の表示
  progress_rate = (x * 100) / height
  progress_time = Time.now - start_time
  printf ("Excel export in progress ... %3s percent %6.1f seconds, Height = %3s Width = %3s\r" % [progress_rate, progress_time, x, y])

その他のrubyXLコーディング

他のプログラムでも、rubyXLを使用しているのですが、以下のようなコーディングを行っています。

worksheetの作成

  workbook.worksheets.delete_at(0)
  worksheet = workbook.add_worksheet
  worksheet.sheet_name = 'ruby'

フォントの変更

  worksheet[0][0].change_font_name('ＭＳゴシック')
  worksheet[0][0].change_font_size(16)
  worksheet[0][0].change_font_bold(true)

罫線の変更

  worksheet[0][0].change_border(:bottom, "medium")

win32oleの場合

試しに、同じことを win32oleを使用した形でコーディングしてみました。

作成したソース

# Draw Picture with Excel Version win32ole
# c:\ruby26-x64\user>ruby draw_excel_win32ole.rb xxxx (xxxx : png or jpg file name)

require 'win32ole'
require 'RMagick'
require './lib/img_info'

# 画像情報の取得
img = Magick::ImageList.new(ARGV[0])
width = img.columns
height = img.rows

# 画像情報の出力
info_print(img)

# Excelオブジェクトの取得
excel = WIN32OLE.new('Excel.Application')

# Excelを作成し、最初のシートを選択
book = excel.Workbooks.add()
sheet = book.Worksheets(1)
sheet.Activate

start_time = Time.now
progress_time = 0

begin
  img.each_pixel do |pixel, y, x|

    # ピクセル色を16進数で取得
    color = pixel.to_color(Magick::AllCompliance, false, img.depth, true)
    color.delete!('#')
    rgb = color[0,2].to_i(16) + color[2,2].to_i(16) * 256 + color[4,2].to_i(16) * 256 * 256

    # 塗りつぶし
    sheet.Cells.Item(x + 1, y + 1).Value = ''
    sheet.Cells.Item(x + 1, y + 1).Interior.Color = rgb

    # 進捗率と経過時間の表示
    progress_rate = (x * 100) / height
    progress_time = Time.now - start_time
    printf ("Excel export in progress ... %3s percent %6.1f seconds, Height = %3s Width = %3s\r" % [progress_rate, progress_time, x + 1, y + 1])
  end

  # 列の幅変更
  i = 0; j = 0
  for i in 0..width do
    sheet.Columns(i + 1).ColumnWidth = 0.32
  end
  for j in 0..height do
    sheet.Rows(j + 1).RowHeight = 3
  end

  file_name = File.basename("#{ARGV[0]}", '.*')
  if File.exist?("#{file_name}_win32ole.xlsx")
    File.delete("#{file_name}_win32ole.xlsx")
  end
  fso = WIN32OLE.new('Scripting.FileSystemObject')
  output_filename = fso.GetAbsolutePathName("#{file_name}_win32ole.xlsx")
  book.SaveAs(FileName:output_filename)

  printf ("Excel export completed   ... 100 percent %6.1f seconds, Height = %3s Width = %3s\n" % [progress_time, height, width])
ensure
  # 終了処理
  excel.Workbooks.Close
  # Excelの終了
  excel.Quit
end

win32oleでの Excel使用に書き換える時に、以下の点で困りました。

WIN32OLERuntimeError

最初の実行で、いきなり見たことのない WIN32OLERuntimeErrorになりました。

draw_excel_win32ole.rb:34:in `block in

': (in OLE method `Item': ) (WIN32OLERuntimeError) OLE error code:800A03EC in HRESULT error code:0x80020009 Exception occurred.

最初は何故発生するのかわからず、Webをいろいろググって該当しそうなものを見つけようとしたのですが、見つかりません。そこで、地道にデバックすることにします。

一番最初のコーディングは、以下のようになっていました。

    sheet.Cells.Item(x, y).Value = ''
    sheet.Cells.Item(x, y).Interior.Color = color

セルの指定

まず、メッセージから、1行目の Itemでエラーになっていることが判ります。

今までの win32oleでのコーディングと違わないのに、エラーになっています。そこで、行の xと列の yの値を確認してみました。

すると、rubyXLでは 0～始まって処理されているのに、win32ole(Excel)では 1～始まっています。つまり、最初の (0,0)=(0行、0列)を Excelが処理できなくてエラーになっていることが判りました。

結果、行の xと列の yに +1して、1から始まるようにします。

ピクセル色の指定

次に、セルの指定を修正しても、2行目で同じような WIN32OLERuntimeErrorになりました。

写真のピクセル色を使うので、今まで使っていた ColorIndexプロパティが使えません。そこで、Colorプロパティを使ったのですが、Colorプロパティは、RGB関数によって作成された値に応じた色を設定します。rubyXLでは、to_colorメソッドで取得したストリング・オブジェクトを直接使用していたのですが、Colorプロパティではそれは使えずエラーとなっていました。

修正したコーディングが、以下のものです。

    sheet.Cells.Item(x + 1, y + 1).Value = ''
    sheet.Cells.Item(x + 1, y + 1).Interior.Color = rgb

Colorプロパティ

Colorプロパティに設定する RGB関数の値ですが、次の 1行目のコーディングで計算しています。

rgb = color[0,2].to_i(16) + color[2,2].to_i(16) * 256 + color[4,2].to_i(16) * 256 * 256 ・・・ sheet.Cells.Item(x + 1, y + 1).Interior.Color = rgb

この時注意しないといけなかったのが、16進数の問題でした。元々ストリングの colorには、16進数の文字が R(2桁) G(2桁) B(2桁)の順で並んでいます。普通に to_iだけのメソッド指定だと 10進数として計算してしまうので正しい色になりません。16進数として数値型に変換するために、to_i(16)とします。

Colorプロパティと RGB関数については、以下のページに詳しく書かれています。

Office TANAKA - グラフ[ColorプロパティとRGB関数について]

to_iメソッドについては、以下に詳しく書かれています。

Rubyで文字列と数値を相互変換する(2進、8進、10進、16進数) -- ぺけみさお

それで計算した値を、3行目の Colorプロパティに設定します。

幅と高さの指定

セルの幅や高さの指定も、rubyXLと違っていました。

そこで、Excelを実際に使って、幅 6ピクセル、高さ 6ピクセルの時の値を調べました。

結果、幅は 0.32で、高さは 3になりましたので、それぞれ以下のようにその値を設定しています。

列幅の指定

    sheet.Columns(i + 1).ColumnWidth = 0.32

行高の指定

sheet.Rows(j + 1).RowHeight = 3

実行結果

最後に、実行時間としては、以下のように rubyXLより 5.6倍ぐらい早くなりました。

という事で、大きな Excel表を扱う時は、rubyXLよりコーディングは少し大変ですが、もっぱら win32oleでコーディングしています。