Automating Excel with Ruby: The Workbook Object

Previously, we looked at the Excel Application object. Today, we'll take it down a level and investigate the Workbooks collection and Workbook objects.

The Excel application object contains a (1-based index) collection of all open workbooks. You could think of the excel.Workbooks() statement as a method which returns the collection of Workbooks (if called with no argument) or a single Workbook (if called with an index or name argument).

To create a new workbook, call the Add method of the Workbooks collection:

workbook = excel.Workbooks.Add

To open an existing workbook, call the Open method of the Workbooks collection and pass it the filename of the workbook:

workbook = excel.Workbooks.Open('c:\temp\MyWorkbook.xls')

You can reference a single workbook (that's already open) by index:

workbook = excel.Workbooks(1)

...or by filename:

workbook = excel.Workbooks('c:\temp\MyWorkbook.xls')

... or you can reference the currently active (selected) Workbook:

workbook = excel.ActiveWorkbook

You can, of course, iterate over the Workbooks collection:

for workbook in excel.Workbooks
    # ...code...
end

To save a new workbook, call its SaveAs method, supplying a filename:

workbook.SaveAs('c:\temp\YourWorkbook.xls')

To save changes to a previously-saved workbook, just call its Save method:

workbook.Save

To close a workbook after saving changes:

workbook.Close

Next, we'll move down to the next logical level and work with the Excel Worksheet object.

Automating Excel with Ruby: The Application Object

Automating Microsoft Excel can involve hundreds of objects, each with its own properties and methods. Let's start by looking at the top-level Application object.

As previously mentioned here, you'll use the win32ole library to create a new instance of the Excel application object:

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

...or to connect to an existing instance of the Excel application object:

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

Note that a new instance of Excel will be not visible by default. To show it, set the application object's Visible property to true:

excel.Visible = true

You may want to first hide Excel until your data input and formatting process is complete, then make it visible. This may speed things up, and prevents the user from interfering with your program (and vice versa).

To make Excel visible, but block user input, set the Application object's Interactive property:

excel.Interactive = false

To turn off screen updating, set the ScreenUpdating object's Interactive property:

excel.ScreenUpdating = false

This can help speed up processes that involve updating a large number of cells. Just don't forget to return ScreenUpdating and Interactive to true when completed.

You may wish to define the number of worksheets included in a new workbook, to ensure you have the exact number of worksheets that you will be using. This can be done by getting or setting the application object's SheetsInNewWorkbook value:

excel.SheetsInNewWorkbook = 3

This value is retained in the application beyond this session. So, if you're including this code in an app to be run on end-users' machines, it may be polite to reset the SheetsInNewWorkbook value to what the user had previously set, after adding your workbook:

number_of_sheets = excel.SheetsInNewWorkbook
excel.SheetsInNewWorkbook = 3
workbook = excel.Workbooks.Add
excel.SheetsInNewWorkbook = number_of_sheets

Actions such as deleting worksheets or overwriting existing files may result in an alert box asking you to confirm the action. To disable such alerts, set the Excel Application object's DisplayAlerts property:

excel.DisplayAlerts = false

To exit Excel, call the application object's Quit method:

excel.Quit

That's all for now, as I try to keep individual posts brief. Next, we'll look at the Workbook object. Feel free to leave a comment or send me email if there are specific items you would like to see discussed here.

Thanks for stopping by!

Ruby, ADO, and SQLServer

Ruby and ActiveX Data Objects (ADO) make working with Microsoft SQL Server databases simple. Here's a simple example of a class that manages the SQL Server database connection and queries:

require 'win32ole'

class SqlServer
    # This class manages database connection and queries
    attr_accessor :connection, :data, :fields

    def initialize
        @connection = nil
        @data = nil
    end

    def open
        # Open ADO connection to the SQL Server database
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "User ID=USER_ID;"
        connection_string << "password=PASSWORD;"
        connection_string << "Initial Catalog=DATABASE;"
        connection_string << "Data Source=IP_ADDRESS;"
        connection_string << "Network Library=dbmssocn"
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        # Create an instance of an ADO Recordset
        recordset = WIN32OLE.new('ADODB.Recordset')
        # Open the recordset, using an SQL statement and the
        # existing ADO connection
        recordset.Open(sql, @connection)
        # Create and populate an array of field names
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            # Move to the first record/row, if any exist
            recordset.MoveFirst
            # Grab all records
            @data = recordset.GetRows
        rescue
            @data = []
        end
        recordset.Close
        # An ADO Recordset's GetRows method returns an array 
        # of columns, so we'll use the transpose method to 
        # convert it to an array of rows
        @data = @data.transpose
    end

    def close
        @connection.Close
    end
end

You can then use this class as follows:

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.close

The above code is, of course, incomplete and can certainly be improved and extended (error handling, etc.). But, hopefully, it provides you with a solid foundation on which to build.

UPDATE: You might like to know that you can automate many of your SQL Server administrative tasks by leveraging Distributed Management Objects (SQL-DMO). I've explained this in a later article here.

Thanks for stopping by!

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.

Developing Desktop Ruby Apps for Windows

It seems that a large portion of the Ruby community is either LINUX or Mac OS-based. And Ruby's 'Killer App' is a web development framework (Ruby on Rails). So you can be forgiven for not realizing that you can use Ruby to develop solid desktop applications for Microsoft Windows, complete with a native Windows look-and-feel GUI.

Every tool has its Pros and Cons, its Fanboys and Detractors. For what it's worth, here's a brief overview of the tools I consider essential...

RUBY: One-Click Ruby Installer
The One-Click Ruby Installer will probably meet your needs for a painless Ruby installation that includes most of the Ruby libraries you'll need starting out. It includes the SciTE Text Editor (my code editor of choice) and the WIN32OLE library, essential for COM automation.

DATABASE: SQLite
SQLite is fast, light, and powerful -- and requires zero-configuration. Download the DLL file, install the sqlite-ruby gem and you're ready to deal with datasets both large and small. You can even embed the small SQLite3.DLL file in your RubyScript2Exe-compiled executable file.

GUI TOOLKIT: wxRuby
I've been using wxRuby (version 0.6) for about a year now, and am pleased with the results. My GUI needs are fairly modest (buttons, list and combo boxes, textboxes, etc.) and my user interfaces are virtually identical to those produced by Microsoft Visual Studio. For best results, be sure to de-couple your GUI code from your back-end code, to ensure optimal GUI responsiveness and the ability to easily replace GUIs in the future. Write your GUI code by hand, or use the (separate but free) wxFormBuilder tool to design your interface and export it to an XML document that wxRuby can load.

COMPILER: RubyScript2Exe
See my earlier post, Distributing Ruby Apps on Windows, for further details on this essential tool.

INSTALLER: Inno Setup
Once you have your application packaged up into an executable, you'll want to provide your users with a simple means for installing it (and perhaps other files) on their PCs, creating Start menu icons, etc. Inno Setup handles all of this, is easily configurable, and gives your Windows application a professional, polished installation package.

I hope you find the information above, and this Ruby on Windows weblog in general, to be helpful.

Thanks for stopping by!

Ruby, Excel, and SQLite (instead of MS Access)

I used to rely on Microsoft Access for my desktop database needs, but have recently converted to SQLite and its associated Ruby library. SQLite is fast, light, and powerful. Combining SQLite with Ruby gives me the ability to quickly handle most data processing tasks.

Example: Let's say you've been given an Excel workbook with multiple worksheets of data, and you need to cross-reference data between worksheets. In many such cases, you'll want to import the data into a real database and use SQL.

Here's a brief, unpolished snippet of code that reads data from an open Excel workbook and creates an SQLite database with a table for each worksheet in the Excel workbook:

require 'win32ole'
require 'sqlite3'

#   Connect to a running instance of Excel
xl = WIN32OLE.connect('Excel.Application')
#   Get the active workbook
wb = xl.ActiveWorkbook
#   Create the SQLite3 database
db = SQLite3::Database.new('excel.db')
#   Create a database table for each worksheet 
#   in the workbook
wb.Worksheets.each do |ws|
    #   Grab all values from worksheet into a 
    #   2-dimensional array
    data = ws.UsedRange.Value
    #   Grab first row of data to use as field names
    field_names = data.shift
    #   Create database table using worksheet name and 
    #   field names
    db.execute("CREATE TABLE [#{ws.Name}] \
        ( #{field_names.join(',')} );")
    #   For each row of data...
    data.each do |row|
        #   ...single-quote all field values...
        row.collect! { |f| f = "'" + f.to_s + "'" }
        #   ...and insert a new record into the 
        #   database table
        db.execute("INSERT INTO [#{ws.Name}] VALUES \
            ( #{row.join(',')} );")
    end
end

Now, you've got your data loaded into a real database, quickly and easily, and can get to work on it... via Ruby or the SQLite command line interface.

I would be happy to provide more info on using SQLite3 with Ruby, but why the lucky stiff gives a pretty good jump-start here.