Windows OLE Automation in Ruby

Examples

Simple Spreadsheet 1

#!/usr/bin/env ruby
#
#  Purpose:  Exercise a few simple operations...
#
#--------------------------------------------------------------------------

require 'win32ole'

# You can, of course, iterate over the Workbooks collection:
# 
#    for workbook in excel.Workbooks
#       # ...code...
#    end

excel = WIN32OLE.new('Excel.Application')

excel.visible = TRUE

book = excel.Workbooks.Add();
# book = excel.ActiveWorkbook

sheet = book.Worksheets(1);

sheet.Name = "Directions"

sheet.Range('A1:D1').value = ['North','South','East','West'];

book.SaveAs('C:\temp\Workbook.xlsx')

book.Close

book = excel.Workbooks.Open('C:\temp\Workbook.xlsx')

sheet = book.Worksheets(1);

sheet.setproperty('Cells', 3, 2, 10) # => The B1 cell value is 10.

book.Save

book.Close

excel.quit

# lose the reference so that excel can be garbage collected
excel = nil

# ...and collect it.
GC.start

Simple Spreadsheet 2

Drop some data into Sheet11 and delete the other sheets.

#!/usr/bin/env ruby
#
#  Purpose:  Exercise a few simple operations...
#
#--------------------------------------------------------------------------

require 'win32ole'

excel = WIN32OLE.new('Excel.Application')

excel.visible = TRUE

book  = excel.Workbooks.Add();

sheet = book.Worksheets('Sheet1');

sheet.Name = "Directions"

sheet.Range('A1:D2').value = [['North','South','East','West'],[1,2,3,4]];

book.Worksheets('Sheet2').delete
book.Worksheets('Sheet3').delete

book.SaveAs('C:\temp\Workbook.xlsx')

book.Close

excel.quit

Simple Chart 1

#!/usr/bin/env ruby
#
#  Purpose:  Exercise a few simple chart operations...
#
#--------------------------------------------------------------------------

require 'win32ole'

excel = WIN32OLE.new('Excel.Application')

excel.visible = TRUE

book  = excel.Workbooks.Add();

sheet = book.Worksheets('Sheet1');

sheet.Name = "Directions"

sheet.Range('A1:D2').value = [['North','South','East','West'],[1,2,3,4]];

book.Worksheets('Sheet2').delete
book.Worksheets('Sheet3').delete

chart = book.Charts.add

chart.name = '3D Pie Graph'

puts chart.Type

chart.Type = -4102

#     1  Area
#     2  Horizontal Bar
#     3  Vertical Bar
#     4  Line with markers
#     5  2D Pie
#     6  2D Ring
# -4102  xl3DPie

chart.SeriesCollection(1).Name = 'XYZZY'

book.SaveAs('C:\temp\Workbook.xlsx')

book.Close

excel.quit