Ruby: spreadsheet gemでの Excelの操作

Excellの表をOfficeソフト無しで操作する方法を紹介します。 これは ruby の gem(spreadsheet) だけが有れば利用可能ですので Linuxなどでも利用することが可能です。

簡単なテストでは上手く行っていますが、 本家のドキュメントにはRuby 1.8でIconv-supportが有る事を 想定していると有りますので、 Ruby 1.9系では日本語処理に問題が出るかも知れません。

spreadsheetの内部データは UTF-8 ですのでスクリプトもUTF-8に統一するのが 楽だと思われます。ただし、その場合、表示したりするときにシステムのコード系に 変換する必要が出ます。その為、場合によってどちらが良いかは変わります。

あいにく私はExcel 2003までしか触っていませんので、 最新のExcelとの関係については確認できませんが、 '.xls'の形式のファイルであれば操作は可能と思われます。

このgemは問題が発生する可能性は高いと思われますので、 利用時は原本のファイルを直接使用せず、 コピーしたものを操作して、 問題が無かったときだけ原本を更新するようにして下さい。

ExcelがインストールされているWindows環境ではWin32OLEを使って 実際のExcelを通して操作することが可能です。その方法は別途紹介します。

spreadsheetでは操作できないExcelシートの属性が色々とあります。 それらは、やはりWin32OLEで実際のExcelを通して処理するしかありません。 また、元のシートへ書戻すと問題が出ることが有るようです。 実務で使用するときには充分に確認して下さい。

ただし、このspreadsheetはExcelを呼び出す事無く直接ファイル操作を 実施するため、OLE経由よりは高速です。 大量のデータを処理する必要がある場合には速度の効果は大きいです。 経験的には使い所を選べば有効に使えました。

gem install spreadsheet

準備は gemとして spreadsheet をインストールするだけです。 spreadsheetの 本家ページはここ のようです。ドキュメントはページの右のリストに列記されています。 Getting Started with Spreadsheet に簡単な使い方の例が出ています。

まずはExcelシートを作ってみます。

#!ruby -Ku
# -*- mode:ruby; coding:utf-8 -*-

require 'rubygems'
require 'spreadsheet'

book = Spreadsheet::Workbook.new
sheet = book.create_worksheet
sheet.name = 'Sheet1'
10.times do |n|
  sheet[n,0] = n
  sheet[n,1] = n*n
  sheet[n,2] = "日本語表示"
end
book.write('sp_test.xls')

require 'rubygems' は gemで入れたものを利用する時には先に指定しておく必要があります。 次に require 'spreadsheet' で spreadsheetを読み込みます。

Spreadsheet::Workbook.new で、ブックオブジェクトが生成されます。 ここではまだファイル名 無しのブックです。 ファイル名は書込む時に指定します。

次にシートを生成します。生成後にシート名を付けています。

sheet = book.create_worksheet( :name => 'Sheet1' )

上記のように生成時の引数として名前を渡すことも出来ます。

後は必要なセルに値を入れて行くだけです。 セルの位置指定はゼロ始まりですので注意して下さい。 注意点は文字列の場合必ずUTF-8に変更して設定する事です。 スクリプトのエンコーディングがUTF-8であればそのままで構いません。 1.8系では require 'kconv' して 文字列(str)を str.toutf8 の様にUTF-8に変換します。 1.9系では str.encode('UTF-8') で変換します。1.9系のencodeは標準関数です。

新規に作ったシートのセルに日付を入れる時には文字列にして入れて置くのが 良いのではないかと思います。セルの書式の設定はspreadsheetでは鬼門です。 既存の書式付のExcelファイルのセルに入れるなら そのままDateやTimeクラスのオブジェクトを入れてもＯＫです。

最後に出来上がったものに名前を付けて書込めば、Excelファイルの出来上がりです。

前記で作ったExcelファイルを読み出して表示するスクリプトは以下の様になります。

#!ruby -Ku
# -*- mode:ruby; coding:utf-8 -*-

require 'rubygems'
require 'spreadsheet'

book = Spreadsheet.open('sp_test.xls')
sheet = book.worksheet('Sheet1')
10.times do |n|
  printf "%3d %3d %s\n",sheet[n,0], sheet[n,1], sheet[n,2].encode('SHIFT_JIS')
end

Spreadsheet.open でファイルを読出しブックオブジェクトが生成されます。 次に、ブックの中のシートを選択します。 後は必要なセルを読み出して表示するだけです。 このスクリプトはWindows上のruby1.9.2で確認したので文字列はSHIFT_JISに変換して 表示しています。

spreadsheetはシートのデザインを操作するのは得意ではありません。 ですから、多少凝ったシートを作る場合には、Excelでテンプレートのブックを作って置き それを読み込んでデータを設定し、別名で保存するようにするのが良いでしょう。 必ず別ファイルに書き出す事でspreadsheetによるシート破壊の問題点を避ける事も出来ます。

Ruby/spreadsheet: 鬼門だけれどフォーマット

f = sheet.row(0).format(0).dup
f.pattern = 1
f.pattern_fg_color = :blue
sheet.row(1).set_format(0,f)

#<Spreadsheet::Format:0x27db630
  @font=#<Spreadsheet::Font:0x27db480 
    @name="Arial", 
    @color=:white, 
    @family=:swiss>, 
  @number_format="GENERAL", 
  @rotation=0, 
  @pattern=1, 
  @bottom_color=:builtin_black, 
  @top_color=:builtin_black, 
  @left_color=:builtin_black, 
  @right_color=:builtin_black, 
  @diagonal_color=:builtin_black, 
  @pattern_fg_color=:blue, 
  @pattern_bg_color=:pattern_bg, 
  @used_merge=0>

f = sheet.row(0).format(0).dup    ← formatの複製
f.pattern = 1                        コピーした物を書き換える
f.pattern_fg_color = :blue
f.font = f.font.dup               ← fontの複製
f.font.color = :white                コピーした物を書き換える
sheet.row(1).set_format(0,f)

'black' 'blue'    'brown'  'cyan'
'gray'  'green'   'lime'   'magenta'
'navy'  'orange'  'pink'   'purple'
'red'   'silver'  'white'  'yellow'