ワークシート内をなめて情報を得る

Excelシート内をなめて、シート内に書かれている情報を集めてみる。 たいていは、1行に1つの情報が設定されている。

こういうときは、RubydeExcelで提供しているRangeCursolクラスを利用するといい。 ある行の範囲を指すRangeCursolをつくり、情報を得ながら、下に下げていくとワークシートをなめることができる。

まずは、なめる対象となるExcelファイルを用意しよう。

名簿のサンプル

この名簿の特徴は、

  • B/C/D列はマージされていて、それぞれの区分が行方向にやはりマージされている

この名簿の困った点は、

  • 苗字と名前の区切りが半角スペースだったり全角スペースだったりする。
  • 区分名に適当にスペースが入ってて、サーチしにくい
  • おいおい、半角カタカナなんぞ使うなよ
  • 内線を全角数字で書くのはなんとかしてくれ

このへんを解消していこう。

まずは単純になめるサンプル

まずは、ワークシートをなめるサンプルを示す。 基本は、RangeCursolオブジェクトをつくり、データがなくなるまで下に下げていくという方法だ。

  require 'excel_lib'
  excel = Excel.new
  book = excel.copy_book("./meibo.xls")
  sheet = book.sheets[1]
  range = sheet.range("B3:G3").get_cursol  # (A)
  loop do
    break if range.text[0] == nil          # (B)
    p range.text                           # (C)
    range.v_move(1)                        # (D)
  end
  

実行結果はこんな感じになる。このテクニックを使えば、Ruby上でデータを集計し、好きなように利用できる。

  ["県 議 会 議 員", "山内 静香", "県議会環境厚生委員会委員長", nil]
  ["市 町 村 長", "関 順次", "市長会会長", nil]
  ["市 町 村 長", "橋本 幸子", "町村会会長", nil]
  ["市 町 村 長", "橋本 かず", "町長", "温泉部会委員"]
  ["関係行政機関職員", "森田 和夫", "森林管理局事務所長", nil]
  ["関係行政機関職員", "平田 志津子", "自然保護事務所長", nil]
  

解説しよう。

  • (A)でRangeCursolを作っている。RangeCursolはセルを指すRangeとほぼ同じだが、移動させることができる。
  • (B)でループの終了条件を決めている。RangeCursol#textはオブジェクトが複数のセルを指している場合は、値のArrayを返す。ここでは、 1列目がnilだったら、終わりとしている
  • (C)で1行分のデータを取り出している。最初、rangeは"B3:G3"を指しているので、中身は6個のセルをさしているように見える。しかし、 いくつかのセルは連結されており、RubydeExcelが連結されている部分は1つのセルのように扱うため、range.textは4つの値しか返さない
  • (D)でカーソルを一つ下に下げている。"B3:G3"に対して、v_move(1)を行うと、"C3:G3"を指すようになる。

データの規格化を行うサンプル

では、データの規格化をおこなってみよう。

要は、読み込んだら、Rubyの強力な文字列操作でデータを規格化して、元にかきもどしてやればいい。

  require 'excel_lib'
  require 'nkf'          # (A)
  
  OFF_GROUP = 0
  OFF_NAME = 1
  OFF_DEP = 2
  OFF_TEL = 3
  excel = Excel.new
  book = excel.copy_book("./meibo.xls")
  sheet = book.sheets[1]
  range = sheet.range("B3:G3").get_cursol
  loop do
    break if range.text[OFF_GROUP] == nil
    mod_group = range.text[OFF_GROUP].gsub(/ |\s/, "")   # (B)
    mod_name = range.text[OFF_NAME].gsub(/ |\s+/, " ")   # (C)
    mod_dep = NKF.nkf('-XSs', range.text[OFF_DEP])        # (D)
    mod_tel = NKF.nkf('-Z -m0', range.text[OFF_TEL])      # (E)
    range.text = [mod_group, mod_name, mod_dep, mod_tel]  # (F)
    range.v_move(1)
  end
  

実行結果は、以下のようになる。

規格化された名簿

解説しよう。

  • (A)半角/全角変換にはNKFモジュールを使うので、requireしておく
  • (B)では、区分に使われているスペースを駆除
  • (C)では、苗字と名前の区切りに使われているスペースを半角スペース1個に規格化している
  • (D)では、役職に使われている半角カタカナを全角に直している
  • (E)では、全角数字を半角数字に変換している。全角アルファベットもこれで半角化できる
  • (F)で書きもどしている。セルのマージを気にしなくていいのはありがたい*1


戻る


*1手前ミソになりますが、マージされたセルがあっても代入が簡単なのは、 RubydeExcelで苦労して実現しているんですよ。