日々の破片

_ rubyでアドホックにxlsxを読む（1:モチベーション）

マスター登録系のWebアプリケーションで、一括登録させたい場合、CSVが比較的利用されていると思う。思う理由は、非プログラマーにとって、デスクトップでの入力アプリケーションといえば、Excelと相場が決まっているからだ。

Excelに入力させた情報をWebにアップロードするなら、ネイティブな形式（xlsかxlsx）が本来は望ましいのだが、ネイティブな形式はバイナリーなので伝統的にCSVが選ばれるのだろう。

CSVは文字列内の,の扱いなどそれなりに厄介なので、タブ区切りのtsvというのが後から出てきたが、後発の悲しさでそれほど主流ではない。というか、よくわからないが、移出形式のファイルといえばcsvというのが不文律みたいだ。

で、これが本格的に厄介になったのは文字コードがからむからだ。

ビジネスの文脈で絵文字が出てくることはそれほどはないかも知れないが、人名地名に広大な文字空間が提供されたので、ふつうにCP932に収まらない文字が入力されまくる。

それをcsvに移出しようとすると、文句が垂れられるのは良いが、押し切ってしまう無頼派（文字化けする）と言われるままにutf-8 CSVというBOM付きCSVを作る順応派の2種類がいて、結局、サーバー側では文字コード判定が必要となる。といっても、先頭3バイト読めば良いので（BOM判定するだけなので）まあ、許容範囲だ。

が、5月のWindows10アップデートから、UTF-8デフォルトという愚かな機能が実装された。まだβ提供なので愚かじゃんという声が大きければ仕様が変わる可能性がないわけでもないが、おそらく突っ走ることになるだろう。

というのは、より良い選択枝がないからだ。

デフォルトUTF-8となると何が起きるかと言うと、コードページがCP932からUTF-8に変わる。

このこと自体は全然望ましいのだが、過去の互換性は最悪となる。

まずメモ帳は、BOM判定はするが、BOMがなければ既定コードページとしてテキストファイルを扱う。つまり、これまで蓄積されたCP932のテキストがメモ帳での読み込みに全滅する。

Excelは過去との互換性は重視するように仕込まれているらしく、BOM無しCSVはすべてCP932として処理する（Excel自体が既定の文字コードを持っているようだ）。

ということは、過去作ったCSV（CP932、読める）、過去作ったBOM付きUTF-8（読める、書ける）、新規に作ったCSV（UTF-8、読めない、書ける）というたすき掛けになる。

これはこれで厄介な状況で、設定変えて、注記なしCSVで保存してもらうと、次はExcelで読めなくなるということだ。

というか、そもそもCSVを選択して出力してくれと依頼した場合に、無印CSVではなくBOM付きUTF-8のCSVが選ばれる可能性があるのだから、厄介事が増えただけのことだった。

というわけで、素直に考えれば、Excelネイティブ形式一択にすれば話が単純となる。文字コードはもともとUTF-8だから考えるまでもない。

あとは、xlsではなくxlsxにしてもらうことだけ気をつければ良い。

というわけで、xlsxが送信されてくることを想定する。

_ rubyでアドホックにxlsxを読む（補足）

ということは、サーバーがCSVを作ってクライアントに対して吐き出す場合は、BOM付きUTF-8が実は最も安全（確実に読んでもらえる）ということになる。

_ rubyでアドホックにxlsxを読む（方法）

xlsxは、zip（pkzip方式）を使った内部にディレクトリ構造を持つ圧縮ファイルで、仕様は[MS-XLSX]: Excel (.xlsx) Extensions to the Office Open XML SpreadsheetML File Formatで公開されている。

ここでは単純にCSVの代替としてxlsxを使うということで、1シート、マトリクス入力のシンプルなxlsxを単に読み取ることを前提とする。書き出しは考えない。

と、決めた瞬間に話がいっきに単純となる。

Rubyでxlsxを扱うには、rooのようなGEMがあるのだが、こいつらはでか過ぎて使う気にまったくなれない。実際のところ、REXMLで十分なので、厄介なのはzipの処理だけだ。

zipは本気で厄介なので、rubyzipを使う。

と決めた時点でXMLパーサとしては、SAX2を使うことが自動で決まる。

xlsxはシートの実体となるxl/worksheets/sheet1.xmlと、文字列の実体となるxl/sharedStrings.xmlの2つのXMLのみ考えれば良い。sheet1.xmlにはすべての1枚目のワークシートの全セルの情報がある。ただし、文字列に関しては、sharedStrings.xml上のインデックスが持たれる。sheet1.xmlという名前から明らかなように、2枚目のワークシートはsheet2.xmlとなる。

読み方は以下の戦略を取る。

zipの全エントリーを列挙し、sheet1.xmlが見つかったら、セルの情報をハッシュに作成する。ハッシュのキーはA1とかAA32といったセル名とする。ハッシュの値はセル内のデータを入れる（当たり前だ）。sheet1.xmlでセルを示す要素名はc、セル名を示す属性はr、型名を示す属性はtだ。c要素に値を示すv要素がネストされる。したがって、SAX2でcを見つけたら、ハッシュにrをキーとして、t属性値とv要素のペアを保持すれば良い。Rubyの場合、タプルの代替としてここでは配列を使う。

zipのエントリーからsharedStrings.xmlを見つけたら、文字列配列を作成する。上でも書いたが、文字列の場合、sheet1.xmlのv要素に入るのは該当配列上の0開始のインデックス番号となる。sharedStrings.xmlは、IME変換情報（カナ入力）や文字修飾情報などが入るので多少厄介だ。基本は1文字列単位にsi要素が作られる。最初に出現するt要素が値としての文字列となる。ただし、文字修飾がある場合、si要素の下に修飾単位に区切ったr要素が作られて、その中の最初のt要素が文字列の実体（部分文字列）となるので、それを拾うことになる。

次のコードは70行で、与えられたパス名またはIO（当然StringIOも可）から、値配列を作る。A1が[0][0]、B1が[0][1]、A2が[1][0]となる。

require 'rexml/parsers/sax2parser'
require 'rexml/sax2listener'
require 'zip'
require 'pp'  # for debugging
def xlsx_to_array_of_array(xlsx)
  dic = []      # 文字列辞書
  table = {}    # シート（セル名をキー、値は[型, 値]
  is = Zip::InputStream.new(xlsx)
  loop do
    ent = is.get_next_entry
    break unless ent          # zipを最後まで読んだ
    if ent.name == 'xl/worksheets/sheet1.xml'
      current = nil           # ハッシュの値用配列入れ
      parser = REXML::Parsers::SAX2Parser.new(ent.get_input_stream.read)
      parser.listen(:start_element, ['c']) {|url, localname, qname, attrs|
        table[attrs['r']] = current = [attrs['t']]
      }
      parser.listen(:characters, ['v']) {|c|
        if current
          current << c
          current = nil
        end
      }
      parser.parse
      pp table if $DEBUG
    elsif ent.name == 'xl/sharedStrings.xml'
      valid = 0       # 本当はFSMを使うほうが良いけど、単純だから状態変数でいいや
      parser = REXML::Parsers::SAX2Parser.new(ent.get_input_stream.read)
      parser.listen(:start_element) {|url, localname, qname, attrs|
        if qname == 'si'  # 文字列エントリーの開始
          valid = 1
        elsif valid == 3  # si内で文字属性を切り替える場合は複数のr内にtが分割される
          #
        elsif valid == 1 && qname == 't' # si直後のtは有効（それ以外はIME変換情報）
          valid = 2
        elsif valid == 1 && qname == 'r' # rを使う場合はr内のすべてのtを利用する
          valid = 3
          dic << ''
        else
          valid = 0
        end
      }
      parser.listen(:characters, ['t']) {|c|
        if valid == 3
          dic.last << c  # si/r/t の場合は分割されているので結合する
        elsif valid == 2
          dic << c       # si/t の場合はエントリー全体を入れる（3の場合の処理を共通で利用しても良いが、バグがあれば上書きされるので後から調べやすい）
        end
      }
      parser.parse
      pp dic if $DEBUG
    end
  end
  csv = []       # 結果の配列の配列用
  table.each do |key, val|
    key =~ /\A([A-Z]+)(\d+)\z/
    col = $1.getbyte(0) - 'A'.getbyte(0)  # 面倒なのでカラムはA～Zまでに収まることにする。AAとかサポートするなら26進数として処理する
    row = $2.to_i - 1
    csv[row] ||= []
    if val[0] == 's'   # 文字列の場合、型はs
      csv[row][col] = dic[val[1].to_i]
    else
      csv[row][col] = val[1]
    end
  end
  pp csv if $DEBUG
  csv
end