GUI(Tcl/Tk) ← : Excel操作 : → Excel操作(OLE)

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経由よりは高速です。 大量のデータを処理する必要がある場合には速度の効果は大きいです。 経験的には使い所を選べば有効に使えました。

Ruby: spreadsheet インストール

gem install spreadsheet

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

Ruby: Excelファイルの作成

まずは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クラスのオブジェクトを入れてもOKです。

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

Ruby: 既存の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に変換して 表示しています。

Ruby/spreadsheet: シートのデザイン

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

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

spreadsheetの書式はformatオブジェクトとして管理されています。 これを設定し直せば書式を変更することが出来ます。 書式設定を間違えるとExcelで開けないファイルとなりますので注意して下さい。 特にformatオブジェクトの生成に注意が必要です。

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

これでsheet[1,0]のセルを青にすることが出来ます。 patternは1でsolid(塗り潰し)です。 興味があれば他の値を試してみてください。

row(n)はn行目の指定、format関係はsheet[y,x]の形式ではサポートされていません。 format(i)はiカラム目のformat(書式)オブジェクトを返します。

ruby で p 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>

フォントオブジェクトと幾つかの属性値が定義されています。まあ大体名前から予想がつくと思います。 なので、必要な値を変更して、変更したformatオブジェクトをset_formatで指定してあげれば 書式が変更されるということになります。

ここで注意が必要なのはrubyは全てオブジェクトなので取り出したフォーマットオブジェクトは 現在使用中の物であることです。変数に代入しても新しい別物にはなりません。 その為、変更したいときには例題の様に dup を指定して複製を作って変更する必要があります。 更に注意が必要なのは上記の様にフォーマットオブジェクトの中のfontオブジェクトは 更にネスとされた構成となっていますので、dupでは新しくなりません。 なので、フォントの色とか変えたいときにはfontもdupで新しくしてあげる必要があります。

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'




GUI(Tcl/Tk) ← : Excel操作 : → Excel操作(OLE)


お勧めのRuby開発環境

Trail4You 仮想マシンバザール : Ruby統合開発環境

仮想マシン上にruby統合開発環境をインストールしてあります。 rvm, git もインストール済みで各種rubyを切替ながら試せます。