トップ «前の日記(2007-07-07) 最新 次の日記(2007-07-09)» 編集

日々の破片

著作一覧

2007-07-08

_ ExcelをDSLとしてプリティプリンタを作る

最近、仕事でレガシーなものを相手にしてたんで、ExcelをDSLとして使いまくっていろいろツールを作ったわけだが。

こういう方法って知ってる人は当たり前に知ってるわけだけど、もしかしたら知らないで無駄なことをしてたり、面倒なことをしてたりする人もいるかも知れないから、ちょっと書いておこうかなぁとか。

というわけで、下のようなExcelブックがあると仮定する。本物はもっといろいろなカラムがあったり、行と行の間に隙間があったりするかも知れないけど、本質的には名前と長さとタイプの3つのフィールドがある。

たとえば、これは固定長レコードのファイルのフィールド仕様だったりするわけだ。

つまりは、こういうレコードのフォーマットのファイルがそこら中にあったり、あるいはログの中にこのフォーマットのレコードのダンプが書き込まれていたりする。そして、上図のような(多分、もっと書式はきれいだろうとは思うが)Excelのレイアウト定義書みたいなものが存在するという状況を想定する。

たとえば、

10:15:23 レコードフォーマットエラー:"820070712ABCDEFGH                                48 "

みたいなやつ。まあ、この例はフィールド数が少ないから、見れば「ああ、形態が48ってことは……」のようにすぐわかるわけだけど、2バイトや1バイトのフィールドが200個くらい定義された620バイトのレコードとかだと、492バイト目のなんちゃらフラグの正当性をチェック、とか言われるとそれなりに面倒だ。

でも、それが

処理区分: 8
日付    : 20070712
名称    : ABCDEFGH             
形態    : 48                   
予約領域:  

のようになっていれば、これは簡単だ。

で、そういうプリティプリントをするプログラムを作れ、と言うと、Excelのレイアウト定義書を見ながらプログラムをおもむろに作り始めたり。

そうではなく、Excelそのものを利用すれば話はぐっと簡単になる。

たとえば、次のプログラムは、このシートを読み取って、タイプに応じたクラスのインスタンスを作成するコードを吐きまくるRubyのコードだ。

#!/usr/local/bin/ruby -Ks
require 'win32ole'
  
def convert(v)
  #数値はデフォルトでフロートになって厄介なので整数化する
  if Numeric === v
    v.to_i
  else
    v
  end
end
 
def print_code(sheet)
  offset = 0
  2.upto(sheet.usedRange.Rows.Count) do |rc|
    name = convert(sheet.cells(rc, 1).value)
    next unless name
    len = convert(sheet.cells(rc, 2).value)
    puts "  Type#{convert(sheet.cells(rc, 3).value).to_s}.new('#{name}', #{len}, #{offset}),"
    offset += len
  end
end
 
def read_sheets(file)
  begin
    excel = WIN32OLE.new('Excel.Application')
    excel.displayAlerts = false
    excel.visible = true if $DEBUG
    book = excel.Workbooks.Open(file)
 
    excel.ScreenUpdating = false
    1.upto(excel.ActiveWorkbook.Sheets.Count) do |num|
      print_code(excel.Sheets.item(num))
    end
    excel.ScreenUpdating = true
    book.close(false)
    excel.Quit
  rescue RuntimeError => e
    p e.backtrace
    STDERR.puts e.message
  end
end
 
read_sheets(File.expand_path('dsl.xls'))

マジックナンバー使いまくりだが、それで良いのだ。というのは、このプログラム自身は基本的にExcelシートデペンデントだからだ。(もちろん、パラメータ化したり、あるいは値が「項目名」ならスキップするというような作りにしても良い)

実行すると標準出力に以下が出力される。

C:\home\test>ruby excelconvert.rb
  Type9.new('処理区分', 1, 0),
  Type9.new('日付', 8, 1),
  TypeX.new('名称', 40, 9),
  Type9.new('形態', 2, 49),
  TypeX.new('予約領域', 1, 51),
 
C:\home\test>

実はこの時点では、Type9とかTypeXなんてクラスはどこにも存在しない。

これから始めるのだ。

最初に作るのは、Type9とかTypeX。そしてレコードを与えられたら上記の並びに適用するコードだ。それを上記の出力の周りに作っていく。

module FooBar
  class TypeX
    def initialize(name, l, o) #出力時の順番を間違えたらこっちで調整。どう考えても、オフセットが長さより先だなぁ
      @name = name
      @offset = o
      @length = l
    end
    attr_reader :name
    def format(record, out)
      # 名前と値の区切りをすべてのフィールドで揃えたい場合にはもう一工夫必要。
      out.puts "#{@name}: #{record[@offset, @length]}"
    end
  end
  class Type9 < TypeX
    def initialize(n, l, o)
      super(n, l, o)
    end
    def format(record, out)
      # タイプ9は数値なので、リーディングゼロを削除
      out.puts "#{@name}: #{record[@offset, @length].to_i}"
    end
  end
  Fields = [
  # Excelから生成したコードを貼り付ける
  Type9.new('処理区分', 1, 0),
  Type9.new('日付', 8, 1),
  TypeX.new('名称', 40, 9),
  Type9.new('形態', 2, 49),
  TypeX.new('予約領域', 1, 51),
  ]
  # レコードにフィールド定義を適用してフォーマット出力させる
  def self.pprint(record, out)
    Fields.each do |x|
      x.format(record, out)
    end
  end
end

あとは、FooBar.pprintにレコードと出力先を与えるコードを書けばおしまい。レコードはファイルから直接読むのもあれば、ログファイルから正規表現を使って抜き出したものを与えるとか、いろいろあるので、必要に応じて考えれば良い。

ここでのミソとなる考え方は、せっかくExcelに項目名や長さ、タイプといった文字列がたくさん書かれているのだから、それをそのまま利用する(自分でコードしない)という点だ。

ここでは生成したコードもRubyだが、別にそこは必要に応じて、JavaやC#にすればよい。たとえば

    puts "  Type#{convert(sheet.cells(rc, 1).value).to_s}.new('#{name}', #{len}, #{offset}),"

の行を

    puts <<EOD
    new Type#{convert(sheet.cells(rc, 1).value).to_s}("#{name}", #{len}, #{offset}),
EOD

とすれば、JavaでもC#でも利用できる。

_ ばか……

なんで、こんなの書いてるんだ? 他に書かなきゃならん似たようなの書いてるじゃん。

本日のツッコミ(全10件) [ツッコミを入れる]
_ nido (2007-07-10 03:56)

データタイプが'X'と'9'ってCOBOLですか。<br>Javaの案件でも、Excelのデータ定義からdtoを生成するrubyスクリプトを書くことがありますが、<br>Excelの定義ファイルより、テーブル定義のSQL文の方が信用できるのでSQL文をパースして生成することの方が多いです。

_ arton (2007-07-10 11:23)

多分、そうでしょうね。>COBOL<br>SQLをパースするより、java.sql.DatabaseMetaDataを読むほうがもっと確実ではないですか? (create tableの生成は無理だとは思うけど。卵と鶏)<br>逆に、Excelの定義からVBAでcreate table文を生成してADOで実行するというようなシステムだと、SQL文のほうが信用できないですね。<br>というように、そのシステムで一番信用できるものを元にすれば良いんじゃないでしょうか。

_ 桑島 (2007-07-10 15:19)

ところでその元ネタのExcelってバージョン管理します?

_ arton (2007-07-10 16:09)

文書管理規約にしたがって管理します。というか、オリジナルのある団体が管理している紙資料に合わせてるというほうが正確かな。<br>元ネタがあってのことだから。

_ NyaRuRu (2007-07-12 05:39)

「データは Excel で送っておいたから」という日常ですな.なんとなくこの記事を思い出したり.<br>http://d.hatena.ne.jp/p-nix/20070526

_ arton (2007-07-12 12:53)

リソースに対して表現はたくさんあるってことでレスト。

_ NyaRuRu (2007-07-13 22:59)

もう 1 つ変なモノ発見.<br>http://msdn.microsoft.com/msdnmag/issues/07/08/Excel/default.aspx?loc=jp<br>Office SharePoint Server なんて使われると個人には手も足も出ないのですが,Cloud な時代にはこういうのが天から降ってくる可能性もあるので気長に待ってます.<br>特定のセルに広告が入ってるとかあったりして.

_ arton (2007-07-14 00:05)

へー、OBAとかの盛んに売り込んでるやつかと思ったら、これExcel2003なんですね。ちょっとびっくり。使いこなしてるところってどれくらいあるんだろう? BizTalkもそうですけど、SharePointとかのサーバー製品って、僕がISV側だからというのもあるけど、実際に動かしているところを見ないんですよね。<br>それにしても、ExcelServiceFacadeなんて名前を見ると、EUCの人(ExcelEngineってそういう発想だと思うけど)もデザパタ勉強してる前提になるのかなぁとか、本末転倒というような感じがしたり。

_ p-nix (2007-07-15 18:15)

初めまして、「データは Excel で送っておいたから」のエントリ主です。このページからのアクセスが増えたので何事かと思い。<br>わー、artonさんにNyaRuRuさんだー。お二方とも雲の上の存在です。というわけで自分にとっては記念カキコ。

_ arton (2007-07-15 20:05)

こんにちは。あのエントリーいいですね。参考になります。


2003|06|07|08|09|10|11|12|
2004|01|02|03|04|05|06|07|08|09|10|11|12|
2005|01|02|03|04|05|06|07|08|09|10|11|12|
2006|01|02|03|04|05|06|07|08|09|10|11|12|
2007|01|02|03|04|05|06|07|08|09|10|11|12|
2008|01|02|03|04|05|06|07|08|09|10|11|12|
2009|01|02|03|04|05|06|07|08|09|10|11|12|
2010|01|02|03|04|05|06|07|08|09|10|11|12|
2011|01|02|03|04|05|06|07|08|09|10|11|12|
2012|01|02|03|04|05|06|07|08|09|10|11|12|
2013|01|02|03|04|05|06|07|08|09|10|11|12|
2014|01|02|03|04|05|06|07|08|09|10|11|12|
2015|01|02|03|04|05|06|07|08|09|10|11|12|
2016|01|02|03|04|05|06|07|08|09|10|11|12|
2017|01|02|03|04|05|06|07|08|09|10|11|12|
2018|01|02|03|04|05|06|07|08|09|10|11|12|
2019|01|02|03|04|05|06|07|08|09|10|11|12|
2020|01|02|03|04|05|06|07|08|09|10|11|12|
2021|01|02|03|04|05|06|07|08|09|10|11|12|
2022|01|02|03|04|05|06|07|08|09|10|11|12|
2023|01|02|03|04|05|06|07|08|09|10|11|12|
2024|01|02|03|04|05|06|07|08|09|10|

ジェズイットを見習え