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