Ruby で Excel を弄る

趣味です。

VBA の書き方はわからないけど、Ruby の書き方ならわかるんです。
Ruby で書いたって、win32ole 挟んだ状態で使ったら、そんなに VBA 書くのと変わんないコードになるんだけどね。

今回は、日毎に時間帯別の統計情報が記載されたテキストファイルをもとに整形された Excel ファイルを出力するのが目標。
テキストファイルは、こういう感じの内容。

集計対象名1 —–
2010/02/21 0 15
2010/02/21 1 8
(中略)
2010/02/21 22 21
2010/02/21 23 18

集計対象名2 —–
2010/02/22 0 15
2010/02/22 1 8
(中略)
2010/02/22 22 21
2010/02/22 23 18

(以下略)

最初に、

集計対象名(スペース)ハイフンx5

のヘッダが来て、それに続いて0時から23時までの

日時(TAB)時間帯(TAB)件数(TAB)

という行が繰り返されるこのセットを一日分として、数日分の内容が繰り返されて、更にそれが集計対象毎にも繰り返される。

そんなテキストデータをパースして、Excel化するのに用いたスクリプトが以下。

#!/usr/bin/ruby

require 'win32ole'
require 'kconv'

# ----- Configuration ---------------

filename = "stats.txt"
xlsfilename = "C:/Documents and Settings/user/デスクトップ/stats.xlsx"
sheet_title = ["シート1", "シート2", "シート3", "シート4"]

# -----------------------------------

xlsfilename = xlsfilename.tosjis
data = Hash.new
keys = Array.new
File.open(filename) { |f|
	key = ""
	while(l = f.gets)
		l.chomp!
		next if l == ""
		if l.match(/----/)
			key = l.split(" ")[0]
			data[key] = Array.new
			keys.push(key)
			puts key
		else
			data[key].push(l.split("\t"))
		end
	end
}

puts "starting Excel..."

fso = WIN32OLE.new('Scripting.FileSystemObject')
excelapp = WIN32OLE.new('Excel.Application')
book = excelapp.Workbooks.Add()
sheet = book.Worksheets(1)
p fso.GetAbsolutePathName(xlsfilename)

i = 1
data_start = 3
keys.each do |k|
	begin
		sheet = book.Worksheets(i)
	rescue
		sheet = book.Worksheets.Add({ "After" => book.Worksheets(book.Worksheets.Count) })
	end
	puts k
	sheet.Name = sheet_title[i - 1].tosjis
	first_cell = sheet.cells.item(1,1)
	first_cell.value = "集計対象 : ".tosjis + k
	#first_cell.Font.Size = 14
	first_cell.Font.Bold = true

	j = data_start
	sheet.Cells.Item(j, 1).Value = "対象日".tosjis
	sheet.Cells.Item(j, 2).Value = "時間帯".tosjis
	sheet.Cells.Item(j, 3).Value = "件数".tosjis
	header = sheet.Range("A#{data_start}:C#{data_start}")
	header.Font.ColorIndex = 2
	header.Font.Bold = true
	header.Interior.Color = "&HD58D53"
	header.HorizontalAlignment = 3
	j += 1

	date = ""
	data[k].each do |l|
		if date != l[0]
			sheet.Cells.Item(j, 1).Value = l[0].gsub(/^([0-9]{4})([0-9]{2})([0-9]{2})$/, '\1/\2/\3')
			date = l[0]
		end
		hour_cell = sheet.Cells.Item(j, 2)
		hour_cell.Value = l[1]
		hour_cell.NumberFormatLocal = "0 時台".tosjis

		data_cell = sheet.Cells.Item(j, 3)
		data_cell.Value = l[2]
		data_cell.NumberFormatLocal = "0 件".tosjis

		if (j % 2) != 0
			hour_cell.Interior.Color = "&HF2F2F2"
			data_cell.Interior.Color = "&HF2F2F2"
		end

		j += 1
	end

	h = 0
	(data_start + 1).step(j-23, 24) do |k|
		sheet.Range("A#{k}:A#{k + 23}").Merge()
		day_cell = sheet.Cells.Item(k, 1)
		day_cell.NumberFormatLocal = "yyyy年m月d日".tosjis
		if (h % 2) != 0
			day_cell.Interior.Color = "&HF2F2F2"
		end
		h += 1
	end

	bd = sheet.Range("A#{data_start}:C#{j - 1}").Borders
	bd.LineStyle = 1
	bd.Weight = 2
	bd.ColorIndex = -4105

	i += 1
end
# TODO: i が 3以下だったら不要なシートがある筈なので消すべき

book.SaveAs(fso.GetAbsolutePathName(xlsfilename))
excelapp.Quit

Ruby とはいえ一発モノなのでクラス作ったりとかはしなかった。ワンライナーの延長。
win32ole を利用するので、実行環境は Windows が前提。Rumix などの Ruby パッケージと、Excel もインストールされている必要がある。

以下、行毎の処理についての覚書。
8〜10行目までが設定部分。

9行目で、出力ファイル名を指定するのだが、拡張子は、実行する環境に入ってる Excel のデフォルトとなっている形式に変えてやる必要があるはず。Excel 2007 以降なら “xlsx” で、それよりも古いものなら “xls” だろう。保存時に指定ができるのかも知れないが、自分の使っている Office 2010 Beta の環境で xlsx が出力出来たら文句がないので調べる気力が湧かなかった。あと、Windows 環境のパス区切り文字も”/”(スラッシュ)でいいのだと気付くまでに結構ハマった。このあたり、環境に慣れてないのは弱い。

15〜31行目でテキストファイルの内容をメモリに展開。テキストファイルなんてせいぜい100KB前後なので、全く気にせずに全部舐めたが、Ruby でこういう形でデータ持つとどのくらいメモリ消費するんだろう。
データは、変数 data にハッシュの形で持つ。
キーとなるのは集計対象名で、23〜24行目で設定する。また、この時、変数 keys にキーとなる値を配列として保管しておく。Ruby のハッシュは順序を記憶しないのだが、後から処理する際、読み込んだ順に舐めて欲しいのでこういう小細工をしている。
値は二次元配列で、単純に上記サンプルのような3項目がある行を、split して data 格納する。

35〜38行目で Excel を起動し、新しいファイルを初期化する。

43行目からのループが実際のデータ流し込み処理。
44〜48行目の begin 〜 end はまず、最初に3枚用意されるワークシートの利用を試み、それがエラーするようになったら末尾に順次追加するための指定。sheet_title 配列からシートの名前を設定し、A1セル(シートの cells.item(1,1) で示されるセル)には集計対象の値を入れて、フォントを調整する。
56〜64行目では、実データのヘッダにあたる部分を指定。61行目で ColorIndex に指定しているマジックナンバー2は白。64行目でHorizontalAlignmentに指定しているマジックナンバー3はセンタリング。定数読み込むのがめんどくさかった。同様に、102〜104行目でいくつかのマジックナンバーを指定しているのも、黒くて細い実線の指定。

68〜80行目でデータ行相当部分を流し込んでいる。70〜73行目で 20100222 のような形式で入ってくる日付情報を/で区切る。76, 80行目で時間のセルとデータのセルには、それぞれ書式の設定も行なっている。また、82〜85行目で、偶数行には背景色を設定した。

91〜99行目は、同じ値が入る事になる日付の部分のセルを結合する処理。70, 72行目で、日付は複数回繰り返し設定される事のないように処理しているので、23行ずつまとめて結合していく。また、日付の書式設定と、マージ後の各行に対して、偶数行の色付けを行なう。

最後に、101〜104行目でデータ部分全体を格子で囲んで終了!
なのだが、全体的に一文字変数が多過ぎて、後から読んだらちょっとわかりづらかった・・・。

後、Excel を呼ぶ部分の処理はやはり重い。
RubyでExcel三昧 – @jitteの日記」 を見ると、クリップボード経由でデータを流し込む事で高速化出来るようだ。もっとデータが多くなったら検討する価値があるだろう。

以下、書く時に見ていた参考URL

preload preload preload