ruby では roo という Excel や Open Office 等を呼び出すためのライブラリが提供されています．
CSV でなく， Excel データの解析をしたいという要望があったため roo を使おうとしたのだけれど，ハマったハマった．
その備忘録をのこしときます．
環境
症状
Excel シート中に グラフ専用シートがあると，通常のシートが読めなくなることがあります．
#!/usr/bin/env ruby require 'rubygems' require 'roo' xlsx = Excelx.new('hoge.xlsx') xlsx.cell(8,2,"sheet1")
こういうので
$ ./xlsx.rb
/usr/local/ruby-1.9.3/lib/ruby/gems/1.9.1/gems/roo-1.9.5/lib/roo/excelx.rb:390:in `read_cells': undefined method `xpath' for nil:NilClass (NoMethodError)
from /usr/local/ruby-1.9.3/lib/ruby/gems/1.9.1/gems/roo-1.9.5/lib/roo/excelx.rb:162:in `cell'
from ./xlsx.rb:10:in `<main>'
原因
roo 1.9.5 のバグです．
Excelx シートは，実は，基本的に各シートの情報が XML で記述され，これを zip したものなのです．
Excelx シートを unzip すると，workbook.xml 等のシートの構成や編集状況を管理しているメタデータや，実際にシートの中身に対応するシート情報として sheet[n].xml (たとえば，シートが三つあれば，sheet1.xml, sheet2.xml, sheet3.xml)が出てきます．
roo では，この sheet[n].xml の順序と workbook.xml に格納されたシート名との順序が一致しているという前提で，各シート情報の抽出＆そのシートの名前のマップをとっています．
roo の大きなバグは2つ．グラフ専用シートがあるとき，
roo の Excelx 読み込み
たとえば，3枚のシートの Excelx (xlsx) シートの構成は概ねこんな感じになっています(詳しくは知らない)．
+--- [_rels] --- ...
+--- [docProps] --- ...
+--- [Content_Types].xml --- ...
+--- [xl] ---+--- [_rels] ------------ workbook.xml.rels
+--- [theme] --- ...
+--- [worksheets] ---+--- sheet1.xml
| +--- sheet2.xml
| +--- sheet3.xml
|
+--- sharedStrings.xml
+--- styles.xml
+--- workbook.xmlsheet1.xml など出ているけど，これはシート名ではなくて，シートの1枚目，という意味らしい(シート名は workbook.xml で定義されています．)
roo では， worksheets ディレクトリの下にある，sheet[n].xml から，通常のシートが何枚あってどの順序なのか，そのデータはどれなのかを把握しているようです．
そして，workbook.xml の中身は下記のようなシート名が含まれた部分があるのだけど，上記の順序とこのシート名の順序が同じという性質を利用して，シートの順序とシート名をマッチさせているわけです．
roo では，下記のようにして workbook.xml 取得と sheet[n].xml の数＆順序の把握＆データ取得しようとしています．
def process_zipfile(zipfilename, zip, path='') @sheet_files = [] Zip::ZipFile.open(zipfilename) {|zf| zf.entries.each {|entry| #entry.extract if entry.to_s.end_with?('workbook.xml') open(@tmpdir+'/'+@file_nr.to_s+'_roo_workbook.xml','wb') {|f| f << zip.read(entry) } end if entry.to_s.end_with?('sharedStrings.xml') open(@tmpdir+'/'+@file_nr.to_s+'_roo_sharedStrings.xml','wb') {|f| f << zip.read(entry) } end if entry.to_s.end_with?('styles.xml') open(@tmpdir+'/'+@file_nr.to_s+'_roo_styles.xml','wb') {|f| f << zip.read(entry) } end if entry.to_s =~ /sheet([0-9]+).xml$/ nr = $1 open(@tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}",'wb') {|f| f << zip.read(entry) } @sheet_files[nr.to_i-1] = @tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}" end } } return end
やっていることは，unzip したファイルのパスをなめて，マッチするものをひっかけるというもの．
ポイントはここ．
if entry.to_s =~ /sheet([0-9]+).xml$/ nr = $1 open(@tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}",'wb') {|f| f << zip.read(entry) } @sheet_files[nr.to_i-1] = @tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}" end
末尾が sheet[n].xml となっているファイルを抜き出し，その[n]で順序とそのシートのセルデータを取り出そうとしているわけです．
ところが，こいつが思わぬバグに繋がっています．(もう一つ致命的なバグがあるんですが，それは後述)
シートXML抽出時のバグ(問題点1)
冒頭のとおりなのですが，シートにグラフ専用のシートが混ざっていると，バグります．
グラフ専用シートが1つ混ざった Excelx ファイルの構造がどうなっているかというと，下記のようになっています．
+--- [_rels] --- ...
+--- [docProps] --- ...
+--- [Content_Types].xml --- ...
+--- [xl] ---+--- [_rels] ------------- workbook.xml.rels
+--- [theme] --- ...
+--- [worksheets] ----+--- sheet1.xml
| +--- sheet2.xml
| +--- sheet3.xml
|
+--- [charts] --------+--- [_rels] --- ...
| +--- chart1.xml
|
+--- [chartsheets] ---+--- [_rels] --- ...
| +--- sheet1.xml
|
+--- [drawings] ------+--- [_rels] --- ...
| +--- drawings1.xml
|
+--- sharedStrings.xml
+--- styles.xml
+--- workbook.xmlおわかりいただけただろうか？
sheet[n].xml が重複することがあるんです．
今回はグラフ一枚だから，sheet1.xml しか重複していないけれど，グラフ専用シートが増えると，charts, chartsheets,drawings 以下の XML はどんどん増えていきます．
if entry.to_s =~ /sheet([0-9]+).xml$/ nr = $1 open(@tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}",'wb') {|f| f << zip.read(entry) } @sheet_files[nr.to_i-1] = @tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}" end
再掲．
このやり方だと，パスの末尾が sheet[n].xml になっているものを重複カウントしてしまう恐れがあります．たとえば，上記の例だと，実際にほしい worksheets/sheet1.xml を抽出した後に chartsheets/sheet1.xml を拾ってしまうと，上書きされて肝心の sheet1 の情報が飛んでしまいます．
シート名と実際のシートのマップミス(問題点2)
そして，もう一つ問題点があります．
roo から cell(row,col,sheet='fuge') とやって，シート fuge の row行，col列のデータを呼ぶことを考えます．このとき roo は read_cells(sheet='fuge')内で sheets メソッドを呼びます．この sheets メソッドにバグがあります．
def sheets return_sheets = [] #TODO: @workbook_doc.find("//*[local-name()='sheet']").each do |sheet| @workbook_doc.xpath("//*[local-name()='sheet']").each do |sheet| #TODO: return_sheets << sheet.attributes.to_h['name'] return_sheets << sheet['name'] end return_sheets end 略 def read_cells(sheet=nil) sheet = @default_sheet unless sheet sheet_found = false raise ArgumentError, "Error: sheet '#{sheet||'nil'}' not valid" if @default\ _sheet == nil and sheet==nil raise RangeError unless self.sheets.include? sheet n = self.sheets.index(sheet) #TODO: @sheet_doc[n].find("//*[local-name()='c']").each do |c| @sheet_doc[n].xpath("//*[local-name()='c']").each do |c| 略(ここからシートのセルを見ている)
read_cells() では，
n = self.sheets.index(sheet)
とやって，ほしいシート('fuge')が worksheets/sheet[n].xml のどれに対応しているかを調べています．つまり， sheets() では，シート名→sheet[n].xml のどれに対応しているのか，その対応を配列で返す動きをします．
たとえば，シート名とシートの関係が下表のとおりだとします．
| シート名 | シートのXML |
| hoge | sheet1.xml |
| fuge | sheet2.xml |
| mage | sheet3.xml |
このとき，sheets() では
['hoge', 'fuge', 'mage']
という返値が期待されていて，read_cells() では，指定されたシート名 'fuge' がこの配列が何番目(答えは2番目)にあるかを見て， sheet2.xml が fuge シートに対応するXMLデータだと識別します．
sheets() はworkbook.xml を見てこの対応付けをしています．ところが，グラフ専用シートのシート名もこの中に書かれてしまうんですね．そして，対応が崩れてしまう，というのが最大のバグ．
単純に，@workbook_doc は workbook.xml を格納したもので，たとえば中身は下記のようになっています．
<?xml version="1.0" encoding="UTF-8" standalone="true"?> <workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <fileVersion rupBuild="9303" lowestEdited="4" lastEdited="5" appName="xl"/> <workbookPr defaultThemeVersion="124226" filterPrivacy="1"/> <bookViews> <workbookView activeTab="2" windowHeight="8010" windowWidth="14805" yWindow="105" xWindow="240"/> </bookViews> <sheets> <sheet r:id="rId1" sheetId="2" name="hoge"/> <sheet r:id="rId2" sheetId="5" name="Graph1"/> <sheet r:id="rId3" sheetId="1" name="fuge"/> <sheet r:id="rId4" sheetId="3" name="mage"/> </sheets><calcPr calcId="122211"/> </workbook>
sheets() では，
['hoge', 'Graph1', 'fuge', 'mage']
となり，read_cells()で
n = self.sheets.index(sheet) # sheet = 'fuge'
とすると，n は 1 (sheet2相当) ではなく， 2(sheet3相当) を指していることになってしまいます．つまり，sheet2.xml ではなく，sheet3.xml を読みに行こうとしてしまいます．この xlsx シートではちゃんと sheet3 があるので，おそらくエラーはでませんが，違うシートを引いているのは間違いありませんし，sheet3 がない xlsx だったらエラーになっています．このエラーこそが，最初に示したエラーというわけです．
修正
問題点1への対応
def process_zipfile(zipfilename, zip, path='') @sheet_files = [] @drawing_files = [] Zip::ZipFile.open(zipfilename) {|zf| zf.entries.each {|entry| #entry.extract if entry.to_s.end_with?('workbook.xml') open(@tmpdir+'/'+@file_nr.to_s+'_roo_workbook.xml','wb') {|f| f << zip.read(entry) } end if entry.to_s.end_with?('workbook.xml.rels') open(@tmpdir+'/'+@file_nr.to_s+'_roo_workbook.rels.xml','wb') {|f| f << zip.read(entry) } end if entry.to_s.end_with?('sharedStrings.xml') open(@tmpdir+'/'+@file_nr.to_s+'_roo_sharedStrings.xml','wb') {|f| f << zip.read(entry) } end if entry.to_s.end_with?('styles.xml') open(@tmpdir+'/'+@file_nr.to_s+'_roo_styles.xml','wb') {|f| f << zip.read(entry) } end if entry.to_s =~ /drawings\/drawing([0-9]+).xml$/ nr = $1 open(@tmpdir+'/'+@file_nr.to_s+"_roo_drawing#{nr}",'wb') {|f| f << zip.read(entry) } @drawing_files[nr.to_i-1] = @tmpdir+'/'+@file_nr.to_s+"_roo_drawing#{\ nr}" end if entry.to_s =~ /worksheets\/sheet([0-9]+).xml$/ nr = $1 open(@tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}",'wb') {|f| f << zip.read(entry) } @sheet_files[nr.to_i-1] = @tmpdir+'/'+@file_nr.to_s+"_roo_sheet#{nr}" end } } return end
とりあえず，sheet[n].xml を探す正規表現のルールを少し厳格化(上のディレクトリ構成まで見るように)しました．あまり意味はないのですが，sheet XML と グラフ用のシートを sheet と drawing で一応区別しています．(drawing は使ってませんが)
また，workbook.xml.rels という XML データを追加で読むようにしました．これは問題点2のためのものです．
問題点2への対応
やっつけですが，下記のように sheets() を変更しました．
def sheets return_sheets = [] sheet_detail = {} @workbook_rels_doc.xpath("//*[local-name()='Relationship']").each do |rel| case rel['Target'] when /worksheets/ # sheets sheet_detail[rel['Id']] = 'sheet' when /chartsheets/ # graphs sheet_detail[rel['Id']] = 'drawing' end end #TODO: @workbook_doc.find("//*[local-name()='sheet']").each do |sheet| @workbook_doc.xpath("//*[local-name()='sheet']").each do |sheet| #TODO: return_sheets << sheet.attributes.to_h['name'] if sheet_detail[sheet['r:id']] == 'sheet' return_sheets << sheet['name'] end end return_sheets end
問題点1の改修コードのほうに書いてしまいましたが，workbook.xml.rels を使います．これこそが，そのシートが本当にシートなのか・グラフ専用シートなのかを定義しているのです．中身はこうなっています．
<?xml version="1.0" encoding="UTF-8" standalone="true"?> <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"> <Relationship Target="worksheets/sheet2.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId3"/> <Relationship Target="chartsheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartsheet" Id="rId2"/> <Relationship Target="worksheets/sheet1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId1"/> <Relationship Target="styles.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Id="rId6"/> <Relationship Target="theme/theme1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Id="rId5"/> <Relationship Target="worksheets/sheet3.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Id="rId4"/> </Relationships>
- worksheets/sheet2.xml ⇔ シートで Id="rId3"
- chartsheets/sheet1.xml ⇔ グラフ専用シートで Id="rId2"
- worksheets/sheet1.xml ⇔ シートで Id="rId1"
- worksheets/sheet3.xml ⇔ シートで Id="rId4"
このマップをハッシュとしてもっておき，workbook.xml の Id とマッチさせ，workbook.xml 内の各シートが本当にシート(sheet)かグラフ専用シート(drawing)かを見分けています．
で，sheet のものだけを返すという動きをしているわけです．
まとめ
ちゃんと rels XML 読んでマップしろよってことですな．でも，roo はべんり．
patch
その他
In brief, roo 1.9.5 has critical 2 bugs for calling sheets method and unzip process in his excelx.rb. The bugs are as follows:
- When target excelx file has graph-sheet(s), some work-sheet(s) may be confused with the chart-sheet of the graph-sheet(s),
- And then the order mapping from work-sheet(s)' name to its each work-sheet will be failed because obtained sheet(s)' name certainly has also graph-sheet(s)' name.
I've patched its source code. (this)
- In the modified code, the work-sheet(s) and the chart-sheet(s) are clearly realized, so above confusing won't occur.
- And, by checking relationship XML to map the name(s) to its sheet(s) in the xlsx file, bug 2 had been modified.
Actually, the patched code works well on ruby 1.9.3 in ubuntu 12.04 or Vine Linux 5.2.
