Excel 操作

たいしたプログラムではないのだけど、個人的なファイル生成と解析に役立ったのでのっけときます。

使い方

begin
  # Excel オブジェクトを生成
  excel = EXCEL::Excel.new()
  # Sheet1 を取得
  sheet = excel.sheet("Sheet1")
  # 1行目から5行目までをぐるぐる
  sheet.each(:row => 5){|row|
    # 1列目に行番号をセット
    row.set_value(1 , row.index)
    # 1列目の文字色を赤に
    row.set_color(1,"#FF0000")
    # 2列目に行番号をセット
    row.set_value("B" , row.index)
    # 2列名の背景色を緑に
    row.set_background_color(2,"#00FF00")
  } 
  # シートを追加
  excel.add_seet("AAAAA")
  # デスクトップに保存
  excel.save("C:/Documents and Settings/Administrator/デスクトップ/Book1.xls")
ensure
  excel.close
end

読み込む時は

  excel = EXCEL::Excel.new("C:/Documents and Settings/Administrator/デスクトップ/Book1.xls")

win32ole を使っているのだけど、gem にある「parseexcel」「spreadsheet-excel」は使ってないみたい。すごいねぇ。

ソース

#!ruby
require 'win32ole'

module EXCEL
  class Excel
    def initialize(path=nil)
      begin
        @excel = WIN32OLE.new('Excel.Application')
        @book = path ? @excel.Workbooks.open(path) : @excel.Workbooks.Add
      rescue => e
        close
        raise e
      end
    end
    def add_sheet(name)
      @book.Worksheets.Add
      @book.Worksheets(1).Name = name
      @book.Worksheets(1).extend Sheet
    end
    def sheet(name)
      @book.Worksheets.Item(name).extend Sheet
    end
    def sheet_at(index)
      @book.Worksheets(index).extend Sheet
    end
    # save file
    # path : absolute path
    def save(path)
      @book.SaveAs(path)
    end
    def close
      @book.Close if @book
      @excel.Quit if @excel
    end
  end
end
module EXCEL
  module Sheet
    def set_value(x , y , value)
      x_t = convert_numeric_to_s(x)
      if value.kind_of?(Array)
        for i in 0...value.length
          self.Cells.Item(y,x_t).Value = value[i]
          x_t.next!
        end
      else
        self.Cells.Item(y,x_t).Value = value
      end
    end
    # get cell's value
    def get(x , y)
      self.Cells.Item(y,x).Value
    end
    # set cell's format
    # @ : string
    def set_format(x , y , format)
      self.Cells.Item(y,x).NumberFormatLocal = format
    end
    # set width
    # example)
    # excel.set_width("A1" , "B1" , 100)
    def set_width(from , to , width)
      from = convert_numeric_to_s(from) + "1"
      to   = convert_numeric_to_s(to)   + "1"
      self.Range(from , to).ColumnWidth = width
    end
    # set cell's font color
    # color : red   #FF0000
    #         green #00FF00
    #         bulue #0000FF
    def set_color(x , y , color)
      x = convert_numeric_to_s(x)
      self.Cells.Item(y,x).Font.Color = convert_color_tohex(color)
    end
    # change background color
    # color : red   #FF0000
    #         green #00FF00
    #         bulue #0000FF
    def set_background_color(x , y , color)
      x = convert_numeric_to_s(x)
      self.Cells.Item(y,x).Interior.Color = convert_color_tohex(color)
    end
    # call each rows
    # options:
    #   :max      => 10 ... until max rows
    #   :hasvalue => 10 ... until the column's value is not blank 
    def each(options = {})
      if max = options[:row]
        1.upto(max) {|i| yield(Row.new(self , i))}
      elsif c = options[:hasvalue]
        i = 1
        while true
          v = get(c , i)
          break unless v
          yield(Row.new(self , i))
          i += 1
        end
      else
        1.upto(100) {|i| yield(Row.new(self , i))}
      end
    end

    private
    def convert_color_tohex(color)
      color = color.slice(1 , color.length) if color =~ /^#/
      return 0 if color.length != 6
      return (color[4,2] + color[2,2] + color[0,2]).hex
    end
    def convert_numeric_to_s(x)
      raise StandardError.new("can not use over 256") if x.kind_of?(Numeric) && x > 256
      if x.kind_of? Numeric
        tmp = "A"
        for i in 1...x
          tmp.next!
        end
        x = tmp
      end
      x
    end
  end
end
module EXCEL
  class Row
    attr_reader :index
    def initialize(sheet , index=1)
      raise StandardError.new("index must grater than 1 -> " + index.to_s) if index < 1
      @sheet = sheet
      @index = index
    end
    def method_missing(method , *arg)
      arg.insert(1,@index)
      @sheet.send(method , *arg)
    end
  end
end