RobustExcelOle

robust_excel_ole helps to automate modifying and reading Excel files. This is done by sending VBA methods via Win32OLE. Moreover, robust_excel_ole implements a management system of Excel files and Excel instances and keeps track of all workbooks.

Goals:

  • support both fully automated operations as well as simultaneous user interactions during the automation

  • be able to cope with all cases of Excel (and user) behaviour, encapsulate errors

  • provide convenient methods for frequent (common) tasks

  • support the use of simultaneously running Excel instances

  • allow the presence of referenced libraries and provide some support for that

  • supports EXCEL 2010, EXCEL 2007

This is work in progress.

Requirements

  • Ruby 1.8.6 or higher

Install

gem install robust_excel_ole

Usage

include RobustExcelOle

Opening a workbook.

Example:

book = Book.open('workbook.xls')

Opening a workbook with a block. The semantics is similar to, e.g., File.open.

Book.open('workbook.xls') do |book|
  # do something
end

Options are the following:

:default_excel

open the workbook in the Excel instance where it was opened before if the book was once open, otherwise in a new or already used Excel instance (default: reuse)

:force_excel

open the workbook in a new or given Excel instance (defaut: :new)

:if_absent

specify behaviour if the workbook with the given file name does not exist if the workbook does not exist (default: create)

:if_unsaved

specify behaviour if the workbook was unsaved (default: new_excel)

:if_obstructed

specidy behaviour if the workbook is blocked by another book (default: new_excel)

:read_only

open in read-only mode (default: false)

:displayalerts

allow display alerts in Excel (default: false)

:visible

make visibe in Excel (default: false)

The option :defaut_excel :

If the workbook was open before, then open it in the Excel instance where it was open before. If the workbook cannot be reopened, then

:reuse

Connect to a running Excel, if it exists, otherwise open a new Excel.

:new

Open in a new Excel.

[instance]

Open in a given Excel instance.

The option +:force_excel :

No matter if the workbook was open before,

:new

Open in a new Excel instance.

[instance]

Open in a given Excel instance.

The option +:if_absent :

If the Excel file does not exists, then

:create

Create a new Excel file

:raise

Raise an exception.

The option :if_unsaved :

If a workbook contains unsaved changes and a new workbook with the same file name shall be opened, then

:raise

Raise an exeption. Don't open the workbook.

:accept

Let the unsaved workbook open.

:forget

Discard any changes and reopen the workbook.

:new_excel

Open the new workbook in a new Excel instance

:alert

Give control to Excel.

The option :if_obstructed :

If a workbook is open and a new workbook with same name and a different path is open, then

:raise

Raise an exception. Don't open the workbook.

:forget

Close the old workbook, open the new workbook.

:save

Save the old workbook, close it, open the new workbook

:close_if_saved

Close the old workbook and open the new workbook, if the old workbook is saved, otherwise raise an exception.

:new_excel

Open the new workbook in a new Excel instance.

The values :displayalerts and :visible are reached to the class Excel that controls opening and closing Excel instances.

Closing a workbook.

Simple close.

book.close

There is one option: :if_unsaved . It can have one of the following values:

:raise (default), :save, :forget, :alert

The option specifies: If the workbook is unsaved, then

:save

Save the workbook before closing it.

:raise

Raise an exception. Don't close the workbook.

:forget

Close the workbook.

:alert

Give control to Excel.

Reopening a workbook.

A special feature of robust_excel_ole is that it allows to reopen workbooks after closing them.

book = Book.open('workbook.xls')
book.close
book.reopen

The closed workbook is now alive again, i.e. is open and responds to Excel methods.

This feature is a result of providing identity transparence and storing the file name.

The Book objects and transperence identity

An Excel file (or workbook) is represented by a Book object. A Book object is defined by the full name of the workbook and the Excel instance in which it is opened. RobustExcelOle aims to ensure identity transperency. Identity transparence means that the same Book objects refer to the same Excel files, and vice versa. In other words, a Book objects is a proxy of an Excel file.

Uplifting a workbook to a Book object

A Book object can be created when giving an Excel workbook.

book = Book.new(win32ole_workbook)

Saving a workbook.

Simple save.

book.save

Saving a workbook with a file name.

book.save_as('another_workbook.xls')

Options are the following:

:if_exists

:raise (default), :overwrite, :alert

:if_obstruced

+:raise (default), :forget, :save, +close_if_saved

The option :if_exists :

If a workbook with the file name already exists, then

:raise

Raise an exeption. Don't write the file.

:overwrite

Delete the existing file and write the file. If the workbook is open in an Excel instance, then raise an exception.

:alert

Give the control to Excel.

Examples:

Saving a workbook and overwriting the file if it exists before.

book.save_as('another_workbook.xls', :if_exists => :overwrite)

If a workbook is blocking the workbook that should be saved, then the former one can be saved and closed before.

book = Book.open('workbook.xls')
book2 = Book.open('another_workbook.xls')
book2.save_as('dir/workbook.xls', :if_exists => :overwrite, :if_obstructed => :save)

Unobtrusively modifying a workbook

The method unobtrusively enables the user to read or modify a workbook, no matter if it is open in some Excel instance, if it is saved or unsaved, and if it is writable or not. When opening a workbook unobtrusively, its status remains unchanged. This status includes, whether the workbook is opened or closed, saved or unsaved, readonly or writable.

One option chooses the Excel instance in which a closed workbook is opened. The options +:reuse (default) indicates that the closed workbook is opened in the Excel instance of the workbook, if it exists, or that another Excel instance is reused. The option :hidden provokes that the closed workbook is opened in a separate Excel instance that is not visible and has no DisplayAlerts. Any following closed workbook would be opened in this Excel instance as well when using this option. Moreover, an Excel instance can be given directly where to open the closed workbook.

Options are the following:

:reuse (default) : open a closed workbook in the Excel instance of the workbook, if it exists, otherwise reuse another Excel :hidden : open a closed workbook in one separate Excel instance that is not visible and has no displayaslerts <excel-instance> : open a closed workbooks in the given Excel instance

:read_only

Open the workbook unobtrusively for reading only (default: false)

:readonly_excel

if the workbook is opened only as ReadOnly and shall be modified, then true: close it and open it as writable in the excel instance where it was open so far false (default) open it as writable in another running excel instance, if it exists,

otherwise open in a new excel instance
:keep_open

let the workbook open after unobtrusively opening (default: false)

Book.unobtrusively('workbook.xls') do |book|
  # some modification
  sheet = book[0]
  sheet[1,1] = "c" 
end

The methods for_reading and for_modifying indicate unobtrusively reading or modifying.

Book.for_modifying('workbook.xls') do |book|
  # some modification
  sheet = book[0]
  sheet[1,1] = "c" 
end

Checking whether the referenced Excel workbook responds to methods.

book.alive?

Setting and getting the contents of a range.

Setting:

book["name"] = "value"

or

book.set_nvalue("name") = "value"

Getting:

book["name"] 
=> "value"

or

book.nvalue("name")
=> "value"

Activating a workbook.

Bring the focus to a workbook. Make it available for keyboard inputs and make the Excel instance visible.

book.activate

Making a workbook visible or invisible

Open a workbook and make the Excel instance visible.

book = Book.open('workbook.xls', :visivble => true)

Make the workbook invisible.

book.visible = false

Make the workbook visible

book.visible = true

Making an Excel visible or invisible, and enable and disable DisplayAlerts.

Make an Excel visible

book.excel.visible = true

Enable DisplayAlerts.

book.excel.displayalerts = true

Options: true -> enable DisplayAlerts , false -> Disable DisplayAlerts

Accessing a sheet.

A sheet object can be accessed with a Book#[] method via an integer number.

sheet = book[0]

Accessing a sheet object with the sheet name.

sheet = book['Sheet1']

Accessing sheet objects using the methods Book#each.

book.each do |sheet|               
  # do something with sheet
end

Accessing a row or a column.

A sheet object is enumerable. Use the methods Sheet#each_column, Sheet#each_row or Sheet#each.

sheet.each do |cell|
  # do something with cell
  # read every row every column
end

sheet.each_row do |row|
  # do something with row
end

sheet.each_column do |column|
  # do something with column
end

Accessing a cell.

Reading a cell from a sheet object.

sheet[1, 1]  => first cell.

Reading a cell from a range object.

row_range[0]  => first cell in row_range
column_range[1] => second cell in column_range

Methods to cell are just delegated as VBA methods. Example:

Reading the value of a cell.

cell = sheet[1,1]
cell.Value  => value of the cell.

Writing a cell

sheet[1,1] = "new_value"

Accessing a range of a row or column.

Accessing a range of a row.

sheet.row_range(1)  => first row
sheet.row_range(1, 1..3 )  => first three cells of the first row

Accessing a range of a column.

sheet.col_range(3)  => third column
sheet.col_range(3, 1..2)  => first two cells of the third column

Naming a cell

Naming or renaming a cell range given its address.

book.add_name(1,1,"name")

Reading and modifying a Value of a named range

Returning the value of a range, e.g. a cell, that has a with a defined name.

book.nvalue(name)

or

book[name]

Setting the value of a range, e.g. a cell, that has a with a defined name.

book.set_nvalue(name,value)

or

book[name] = value

Adding a sheet.

Adding a new sheet.

book.add_sheet

Adding a new sheet with a name.

book.add_sheet(:as => 'new_sheet')

Adding a new sheet with a name before another sheet.

book.add_sheet(:as => 'new_sheet2', :before => another_sheet)

Adding a copy of a sheet with a name after another sheet.

book.add_sheet(sheet, :as => 'sheet_copy', :after => another_sheet)

Value of a named cell or range

Returning the value of a cell or range that has a with a defined name.

sheet.nvalue(name)

Creating and reusing an Excel instance.

Creating a new Excel.

excel1 = Excel.create

Getting a running Excel instance and reusing it.

excel2 = Excel.current

Reusing a running Excel instance, making it visible and turning on displayalerts.

excel2 = Excel.new(:reuse => true, :visible => true, :displayalerts => true).

Uplifting an Excel instance represented as WIN32OLE object to an Excel object

excel = Excel.new(:reuse => win32ole_object)

Making Excel visible or invisible

Making Excel visible.

excel = Excel.create
excel.visible = true

Making Excel invisible

excel.visible = false

Turning on or off Displayalerts.

Turning DisplayAlerts on.

excel = Excel.create
excel.displayalerts = true

Turning DisplayAlerts off.

excel.displayalerts = false

Turning on and off in a block.

excel = Excel.create
excel.with_displayalerts true do
  book = Book.open('workbook.xls')
end

Closing an Excel

excel = Excel.current
excel.close

Closing the Excel instance and terminating the Excel process

excel.close(:hard => true)

Closing all Excel instances.

 Excel.close_all

Closing the Excel instances and terminating the Excel processes

 Excel.close_all(:hard => true)

Terminating all Excel processes

Excel.kill_all

Recreating an Excel instance

Reopening the closed Excel instance. This includes reopening all workbooks that were open in that Excel instance.

excel.close
excel.recreate

Options are :visible and :displayalerts

excel.recreate(:visible => true, :displayalerts => true)

Providing Excel instances

Providing all Excel instances (opened via RobustExcelOle) as objects of the class Excel

Excel.excel_objects

Examples

Including robust_excel_ole.

include RobustExcelOle

Example 1

Opening a workbook.

book = Book.open('workbook.xls')

Accessing a sheet via its name.

sheet = book['Sheet1']

Changing the first cell.

sheet[1,1] = "new"

Saving the workbook.

book.save

Saving the workbook with a different name, and overwrite if a file with this name exists.

book.save_as('different_workbook.xls', :if_exists => :overwrite)

Closing the workbook.

book.close

Example 2

Opening a workbook.

book = Book.open('workbook.xls')

Opening the workbook in a new Excel instance and make it visible.

new_book = Book.open('workbook.xls', :force_excel => :new, :visible => true)

Opening the workbook in a given Excel instance.

another_book = Book.open('workbook.xls', :force_excel => book.excel)

Closing the workbooks.

book.close
new_book.close
another_book.close

Reopening the workbook.

reopened_book = book.reopen

The writable book is being prefered.

reopened_book == book
=> true

Opening another workbook. Since the workbook was not open before, reopening the workbook fails and the :default_excel option applies. According to :default_excel => :new a new Excel is created, and the workbook is opened there.

different_book = Book.open('different.xls', :default_excel => :new)

Example 3

Opening a workbook.

book = Book.open('workbook.xls')

Adding a copy of the first sheet after the second sheet.

book.add_sheet(book[0], :as => 'Sheet1_copy', :after => book[1])

Close the workbook.

Opening a new workbook with the same name in a new Excel. Leave the workbook that contains unsaved changes in the old Excel.

new_book = Book.open('workbook.xls', :if_unsaved => :new_excel)

Accessing a sheet and change a cell.

sheet = new_book[0]
sheet[1,1] = "another"

Opening another workbook with the same name in the running Excel. The workbook that contains unsaved changes will be closed before.

third_book = Book.open('workbook.xls', :if_unsaved => :forget)

Adding a sheet.

third_book.add_sheet

Closing the workbook without saving it.

third_book.close(:if_unsaved => :forget)

Closing the first workbook and saving it before.

book.close(:if_unsaved => :save)

Example 4

Opening a workbook.

book1 = Book.open('workbook.xls')

Opening a workbook with the same name in a different path. Close the old workbook.

book2 = Book.open('more/workbook.xls', :if_obstructed => :forget)

Changing its cell.

sheet = book2[0]
sheet[1,1] = "new"

Opening a workbook with the same name in a different path. The old workbook that was modified will be saved and closed before.

book3 = Book.open('workbook.xls', :if_obstructed => :save)

Opening a workbook with the same name in a different path. The other workbook will be closed, because it does not contain unsaved changes.

book4 = Book.open('more/workbook.xls', :if_obstructed => :close_if_unsaved)

Closing the workbook.

book4.close

Example 5

Opening a workbook.

book = Book.open('workbook.xls')

Printing its first cell.

sheet = book[0]
p "1st cell: #{sheet[1,1].Value}"

Unobtrusively modify the workbook.

Book.unobtrusively('workbook.xls') do |book|
  sheet = book[0]
  sheet[1,1] = 'simple'
end

The workbook is modified, but its status is unchanged.

new_sheet = book[0]
not (new_sheet[1,1].Value == sheet[1,1].Value) 
=> true

book.saved 
=> true

Development

robust_excel_ole started as a simple fork from tomiacannondale's wrap_excel adapted to Ruby 1.8.6. The functionality of wrap_excel is optimised and extended by new features. Most notable extensions include:

  • workbooks can be opened in already running Excel instances (instead of opening a new Excel whenever a workbook is opened)

  • a workbook management system stores all workbooks that have been open. This book store is being used, e.g., for reopening a workbook that has been closed before. It provides transparency identity, i.e., equal Excel workbooks correspond to equal Book objects of RobustExcelOle.

Some features in robust_excel_ole that are not compatible with wrap_excel:

  • open uses by default a running Excel instance instead of creating a new one, and opens a workbook by default in writable mode instead of read_only

  • close closes the workbook instead of closing all workbooks and the Excel instance.

  • save_as instead of save.

Want to do more things

All RobustExcelOle objects include the win32ole instance. If you want to do something that not provide a function, you can use win32ole methods.

Support

This is work in progress. Please contact us and to report issues and feature requests to github Issues. github.com/Thomas008/robust_excel_ole/issues

Collaborate

Please pull request on github.

Author

thomas Thomas.Raths@gmx.net

License

MIT License. For more imformation, please see LICENSE.