excelscan.rb

require 'win32ole'
require 'delegate'
require 'singleton'

class TheExcel<SimpleDelegator
  include Singleton
  
  def initialize
    @excel = WIN32OLE.new("Excel.Application")
    @excel['Visible'] = TRUE
    super(@excel)
  end
  
  def quit
    @excel.quit
  end
  
  def TheExcel.quit
    TheExcel.instance.quit
  end
  
  def TheExcel.visible=(switch)
    TheExcel.instance['Visible'] = switch
  end
end

class CellArray
  def initialize(cell)
    @cell = cell
  end
  
  def [](n)
    @cell.cells(1, n + 1)['value']
  end
end

class ExcelScan
  def initialize(fname, sname = nil, visible = true)
    @excel = TheExcel.instance
    @excel['Visible'] = visible
    @book = @excel['Workbooks'].open fname
    @sheet = nil
    @sheet_list = {}
    if sname != nil then
      @book.Worksheets.each do |ws|
        if ws.name == sname or @sheet == nil then
          @sheet = ws
        end
        @sheet_list[ws.name] = ws
      end
    else
      @book.Worksheets.each do |ws|
        if @sheet == nil then
          @sheet = ws
        end
        @sheet_list[ws.name] = ws
      end
    end
    
    @cur_col = 1
  end
  
  def set_sheet(sname)
    @sheet = @sheet_list[sname]
  end
  
  def reset
    @cur_col = 1
  end
  
  def next
    @cur_col = @cur_col + 1
  end
  
  def each
    lcnt = @cur_col
    ura = @sheet.UsedRange
    if ura != nil
      rend = ura.Rows.Count
    else
      rend = 0
    end
    
    while lcnt <= rend
      yield CellArray.new(@sheet.rows(lcnt))
      lcnt = lcnt + 1
    end
  end
  
  def each_sheet
    @sheet_list.each do |sn, sh|
      lcnt = @cur_col
      rend = sh.UsedRange.Rows.Count
      while lcnt <= rend
        yield CellArray.new(sh.rows(lcnt))
        lcnt = lcnt + 1
      end
    end
  end
  
  def sheet_list
    @sheet_list
  end
  
  def cur_col
    @cur_col
  end
  
  def close
    @book.close
  end
  
  def close!
    TheExcel.instance['DisplayAlerts'] = false
    @book.Close
  end
end