rbexcelbase.rb

#!/usr/bin/env ruby

=begin
   ruby win32ole common functions

   OLE excel object: Application, Workbook, Worksheet, Range, Cells , ...
   Demo howto find them, get/set value/style
   used other ruby script import it:

require 'rbexcelbase'  #  copy this to C:\ruby2\lib\ruby\2.3.0\rbexcelbase.rb   or
require File.join(File.dirname(__FILE__), 'rbexcelbase.rb')

=end


require 'win32ole'

require 'pry' rescue nil


p [RUBY_VERSION, WIN32OLE::VERSION] if $DEBUG


class ExcelSess
  # same excel constant
  RED = 3
  BRIGHTGREEN = 4
  BLUE = 5
  PALEYELLOW = 19
  BRIGHTCYAN = 28
  LIGHTCYAN = 34
  LIGHTGREEN = 35
  MIDYELLOW = 36
  MAGENTA = 38
  GOLD = 44
end

class WIN32OLE
  # obj.methods.grep /xxx/i,  ole_xx.oms.grep /sheet/i
  def oms
    ole_methods.map { |e| e.name }.sort
  end
end

#  xx = ws.range('A1:B3')  or  ws.Cells(3,5)
def set_ched_style(xx, bgcolor = ExcelSess::RED)
  xx.Interior.ColorIndex = bgcolor
  xx.Font.Size += 1
  #xx.Font.Name = 'NSimSun'
  xx.Font.Bold = true
  #xx.Font.Italic = true
  #xx.Font.Underline = true
  xx.WrapText = true
end

# xx = String or  ws.range('A1:B3') or  ws.Cells(3,5)
def set_ched_value(xx, new_value)
  if WIN32OLE === xx
  elsif String === xx
    xx = xx.Range(xx)
  else
    raise "??? #{xx}.class " 
  end

  old_v = xx.Value
  xx.Value = new_value
  if old_v != value
    set_ched_style(xx)
  end
end

def show_all_ws
  ex = WIN32OLE.connect("excel.application")
  1.upto(ex.workbooks.count) do |ii|
    wb = ex.workbooks.item(ii)
    1.upto(wb.worksheets.count) do |jj|
      ws = wb.worksheets.item(jj)
      puts "#{wb.name}:#{ws.name}"
    end
  end
end

def find_ws(wbname, wsname)
  ex = WIN32OLE.connect("excel.application")
  ws = ex.Workbooks[wbname].Worksheets[wsname]
  ws
end

def awb
  ex = WIN32OLE.connect("excel.application")
  awb = ex.ActiveWorkbook
  awb
end

def aws
  ex = WIN32OLE.connect("excel.application")
  aws = ex.ActiveSheet
  aws
end

def usedrange ws=nil
  ws = aws() unless ws
  ur = ws.UsedRange()
  ur
end

# ws for each line to do_some_thing
def for_it(ws=nil)
  ws = aws() unless ws
  vs = ws.UsedRange.Value              # UsedRange always begin 'A1', e.g. 'A1:XX99'
  if Array === vs
    vs.each_with_index do |varr, i|
      iRow = i+1
      res = yield(ws, varr, iRow)      # proc { |ws, value_1d_array, iRow |  do_some_thing  }
      puts " #{iRow}=>\t#{res}"
    end
  else # nil if empty, or single value
    raise "??? UsedRange "
  end
end

def copy_ws(ws=nil)
  ws = aws() unless ws
  ws.copy
  ws = aws()
  p [ws.parent.name, ws.name]
end

def test_for_it
  # demo: select a range, create table
  for_it do |ws, varr, iRow|
    iCol = varr.size                   # last column, base 1, valur_array is base 0
    iOutCol = iCol + 1                 # alter all used column, first empty column
    cel = ws.Cells(iRow, iOutCol)
    set_ched_style(cel, ExcelSess::RED)
    cel.Value = varr[0..-1].join('-')  # set new value
  end

end

if __FILE__ == $0
  binding.pry if defined? Pry
end