After using Ruby’s win32ole library to automate some menial tasks at
work
with MS Office, I started putting some frequently used methods together
in a
library. I’d like to release a gem soon that could possibly be useful to
others, I’ll call it “AutoOffice”, but I wanted to ask a couple of
questions
and post a sample here first.
It has a Base class which inherits from WIN32OLE. The other classes
(Access,
Excel, Word, etc.) inherit from the Base class. It’s intended to used
interactively or in scripts, and requires Ruby and Microsoft Office
(tried
on 2003, XP most likely works, earlier versions and 2007 I have no
idea.)
AutoOffice makes certain assumptions. For example, to hide images in the
active workbook with plain win32ole you would do:
x.activeWorkbook.displayDrawingObjects = XlHide
With AutoOffice it would be:
x.hide_pictures.
Most Excel methods work on the active sheet in the active workbook, and
most
Word methods would work with the active document. I think this makes it
easier to work interactively, but you can use any of the standard OLE
methods at any time (and you will have to in most cases.) Not really a
DSL,
it’s more a nice set of shortcuts to use with win32ole.
A quick example: Opening a spreadsheet and auto filtering the first row.
file_name = ‘c:\work\report.xls’
range_name = ‘1:1’
WIN32OLE:
require ‘win32ole’
x = WIN32OLE.new(‘Excel.Application’)
x.visible = true
x.open(‘file_name’)
x.activesheet.enableautofilter = true
activesheet.range(range_name).autofilter.range = range_name
AutoOffice:
require ‘auto_office’
x = Excel.new(file_name)
x.auto_filter(range_name)
A few questions:
What’s the best way to determine when to add new methods and when to
just
use win32ole? There are literally thousands of methods in those
libraries
and sometimes it’s not much harder to type x.activeworkbook.close than
x.close. I’m also trying to avoid generally non useful methods like
save_daily_report_from_outlook_as_a_zip_file_and_then_extract_it_then_open_it_up_in_excel()
Any problem with using names for classes that might be trademarked?
(I’ll
ask my lawyer if I need an absolute answer, but just wondering.)
I haven’t written any tests, but any tips on writing tests for a project
like this?
Could someone point me to some help with creating gems that require
Windows
and gem_require in general? I’m using newgem and can’t run ‘rake
package’ on
Windows because of tar (the GNU version I installed doesn’t have the
required features), Or I’ll just use Linux to package it.
In:
def get(range = DefaultRange)
range(range).value
end
is it OK to call the argument ‘range’ or should I just call it ‘r’?
A sample of the code is below. Though there’s still much missing
(exception
handling, for example), any criticism is appreciated. Thanks to everyone
for
the all the help.
Nate
require ‘win32ole’
module AutoOffice
class Base < WIN32OLE
# Need to add CONST module
OlFolderInbox = 6
OlMailitem = 0
XlHide = 3
XlCSV = 6
OfficeApps = [‘msaccess’, ‘excel’, ‘frontpg’, ‘outlook’,
‘powerpnt’, ‘mspub’, ‘winword’]
def show
visible = true
end
def hide
visible = false
end
def Base.kill_office
system("taskkill" +
OfficeApps.map {|app| " /im #{app}.exe"}.to_s +
" /f")
end
def Base.kill(app)
system("taskkill /im #{app}.exe /f")
end
end
class Access < AutoOffice::Base
def initialize(file_name = "")
super('access.application')
open(file_name) if(file_name != "")
show
end
def open(file_name)
openCurrentDataBase(file_name)
end
def run_macro(macro_name)
doCmd.runMacro(macro_name)
end
end
class Excel < AutoOffice::Base
DefaultRange = 'a1'
def initialize(file_name = "")
super('excel.application')
if(file_name == "")
workbooks.add
else
open(file_name)
end
show
end
def auto_filter(range = DefaultRange)
activesheet.enableautofilter = true
activesheet.range(range).autofilter.range = range
end
def auto_fit(range = DefaultRange)
range(range).columns.autofit
end
def first_nil(range = DefaultRange)
num_cells = 0
range(range).each do |cell|
num_cells += 1
break if(cell.value == nil)
end
num_cells
end
def close
activeworkbook.close
end
def get(range = DefaultRange)
range(range).value
end
def hide_pictures
activeworkbook.displaydrawingobjects = XlHide
end
def open(file_name)
workbooks.open(file_name)
end
def save_as_csv(file_name = activeworkbook.name.gsub(/\.xls/,
‘’))
activeworkbook.saveas(file_name, XlCSV)
end
def set(range = DefaultRange, value = nil)
range(range).value = value
end
end
end