Automating Excel with Ruby

EXAMPLE: Assuming that data represents a 2-dimensional array that you wish to write to a Microsoft Excel worksheet, here's a code snippet, with comments...

# Require the WIN32OLE library
require 'win32ole'
# Create an instance of the Excel application object
xl = WIN32OLE.new('Excel.Application')
# Make Excel visible
xl.Visible = 1
# Add a new Workbook object
wb = xl.Workbooks.Add
# Get the first Worksheet
ws = wb.Worksheets(1)
# Set the name of the worksheet tab
ws.Name = 'Sample Worksheet'
# For each row in the data set
data.each_with_index do |row, r|
  # For each field in the row
  row.each_with_index do |field, c|
      # Write the data to the Worksheet
      ws.Cells(r+1, c+1).Value = field.to_s
  end
end
# Save the workbook
wb.SaveAs('c:\temp\workbook.xls')
# Close the workbook
wb.Close
# Quit Excel
xl.Quit

A key to automating Excel is understanding the Excel Object Model, which defines the objects, their properties and methods. Here's a few methods and resources that may help...

Record an Excel macro, then view the resulting VBA code to determine the necessary objects, properties, and methods.

Pick up a copy of 'Writing Excel Macros with VBA' by Steven Roman.

Google for 'Excel Object Model' to find info on the Excel Object Model.