News¶ ↑

New Interface of #[]¶ ↑

Worksheet#[] now returns a value instead of a range, e.g.

worksheet[1,1]
# => "foo"

Transformation: To yield the range, write range() instead of (). For example,

 worksheet.range(1,1)

or

 worksheet.range([1,1])

This returns the range of cell with address 1,1.

With this new interface, we want to be compatible with the standard syntax of other known Excel libraries, e.g. spreadsheet or axlsx.

RobustExcelOle¶ ↑

RobustExcelOle helps controlling Excel. This obviously includes standard tasks like reading and writing Excel workbooks. The gem is designed to manage simultaneously running Excel instances, even with simultanously happening user interactions.

RobustExcelOle deals with various cases of Excel (and user) behaviour, and supplies workarounds for some Excel and JRuby bugs.

It supports handling workbooks across Excel instances by keeping track of workbooks and instances. Conflicts are managed before Excel notices.

Library references are supported.

RobustExcelOle works by sending VBA methods via WIN32OlE. Therefore, it runs on Windows only.

Features¶ ↑

RobustExcelOle supports

opening and processing workbooks across various Excel instances
standard tasks like opening, reading, writing, saving, closing workbooks
dealing with simultanously happening user interactions
dealing with various cases of Excel and user behaviour
managing conflicts when opening workbooks (e.g. blocking or unsaved workbooks)
workarounds for Excel bugs and JRuby bugs
availability of all VBA methods
availability of the Excel constants (in form if Ruby constants: Excel constant.capitalize)
all standard Excel file formats (.xlsx, .xls, .xlsm)
reopening workbooks after closing them
unobtrusively opening workbooks, i.e. opening and processing workbooks while preserving their status, e.g. saved, readonly
reading and writing workbooks, worksheets, list objects, ranges, rows, columns, cells
naming, adding, and copying ranges and worksheets
references to other Excel libraries
console for interactive mode

Requirements¶ ↑

Ruby 2.1 or higher.

Installation¶ ↑

You can install the gem by running the command

gem install robust_excel_ole

or by using bundler and putting the following line in your Gemfile.

gem 'robust_excel_ole'

Usage¶ ↑

RobustExcelOle can be used either for scripts

require 'robust_excel_ole'
include RobustExcelOle

or as a console. You can start the console via the command

reo

If you want to start the console under jruby, and if you don’t want to use a version manager to switch between ruby and jruby, then use the command

jreo

The call of the console will include RobustExcelOle for you. The consoles require the ruby gem ‘pry’ and ‘pry-bond’ to enable the ruby shell ‘pry’ with filename and string completion.

The following examples can be used for both scripts and console. If you have started the console in the gem path, you can just put these examples.

Description ¶ ↑

In the following example, we want to open a workbook, modify a cell, save and close the workbook.

Let’s open a workbook.

workbook = Workbook.open 'spec/data/workbook.xls'

Now we have a Workbook object that wraps a WIN32OLE object. That is, we can send any WIN32OLE (VBA) method to it. See docs.microsoft.com/en-us/office/vba/api/excel.workbook#methods.

For example, we can determine the name of the workbook.

workbook.Name 
# => "workbook.xls"

For some common tasks and for considering various complex cases of Excel and user behaviour, more convenient methods are implemented. For example, RobustExcelOle provides methods for reading and writing the contents of ranges, for opening, saving, closing, reopening and unobtrusively opening workbooks, and for setting options.

First we want to make the workbook visible.

workbook.visible = true

This method makes both the Excel instance and the window of the Workbook WIN32OLE object visible.

Let’s read the value of a named cell.

value = workbook['firstcell']
# => "foo"

Now we want to write a new value into this cell.

workbook['firstcell'] = "new"

Then we’ll save the workbook.

workbook.save

More features when opening, modifying, creating, saving and closing workbooks¶ ↑

RobustExcelOle allows unobtrusively reading and modifying workbooks, i.e. accessing workbooks without changing their “status”. The status comprises whether the workbook is open or closed, saved or unsaved, read-only or writable, visible or invisible, whether the calculation mode is manual or automatic, and checking compatibility is done or not done.

Workbook.unobtrusively('spec/data/workbook.xls') do |workbook|
  # do something
end

Workbook.for_modifying('spec/data/workbook.xls') do |workbook|
  # do something
end

Workbook.for_reading('spec/data/workbook.xls') do |workbook|
  # do something
end

We can also create a new, empty workbook.

Workbook.create('spec/data/new_workbook.xls', visible: true)

Moreover, we can open the workbook using a block, similar to, e.g., File.open.

Workbook.open('spec/data/workbook.xls') do |workbook|
  # do something
end

Once we have a workbook, we can set some options, e.g.

workbook.for_this_workbook(visible: true, read_only: false)

We can also open the workbook and provide the options in one step, e.g.

workbook = Workbook.open('spec/data/workbook.xls', visible: true)

or, using abbreviations,

workbook = Workbook.open('spec/data/workbook.xls', v: true)

Note that Workbook.open can also connect to workbooks which were not opened via RobustExcelOle (but had been opened before by some user).

For more details about opening workbooks see README_open

We can do a simple save

workbook.save

or save the workbook under a different name.

workbook.save_as('spec/data/new_workbook.xls')

Finally we can close the workbook

workbook.save

or save and close the workbook in one step.

workbook.close(if_unsaved: :save)

For more details about saving and closing workbooks see README_save_close

Using Excel instances¶ ↑

We can start a new Excel instance with

excel1 = Excel.create

excel1 = Excel.new(reuse: false)

We can also obtain an Excel object by connecting to the already running Excel instance.

excel2 = Excel.current

excel2 = Excel.new(reuse: true)

We close the Excel instance using

excel1.close

Closed Excel instances can be reopened.

excel1.recreate(reopen_workbooks: true, visible: true)

Closing all Excel instances is done by

Excel.close_all(if_unsaved: :forget)

For hard terminating all Excel processes we can use

Excel.kill_all

For more details about opening Excel instances see README_excel

Opening workbooks in several Excel instances¶ ↑

RobustExcelOle enables opening and processing workbooks in several Excel instances. Using more than one Excel process allows, e.g., running a script that operates in one Excel instance, while a user (or another script) modifies workbooks in another Excel instance.

For example, suppose we want to open a workbook.

workbook1 = Workbook.open('spec/data/workbook.xls')

Now we want to open another workbook in a different Excel instance.

workbook2 = Workbook.open('spec/data/different_workbook.xls', excel: :new)

We can also create a third Excel instance and open another workbook in this instance.

excel1 = Excel.create
workbook3 = Workbook.open('spec/data/another_workbook.xls', excel: excel1)

A workbook is opened by default in the Excel instance where it was open before most recently.

workbook1.close
workbook1 = Workbook.open('spec/data/workbook.xls')

If this Excel instance is damaged or closed, then options control whether the workbook shall be opened in the current (active), a new or a given Excel instance.

workbook1 = Workbook.open('spec/data/workbook.xls', default: {excel: new})

Without the option :default, the workbook can be forced to be opened in the current, new or given Excel instance, no matter if and where it was opened before, e.g.

workbook2 = Workbook.open('spec/data/workbook.xls', excel: excel1)

Managing conflicts with unsaved and blocking workbooks¶ ↑

RobustExcelOle enables processing workbooks, while still supporting user’s interactions: The commands enable to open, close, reopen, read, modify, write and save Excel workbooks, without the need of the user’s interaction, and even without the user noticing. Thus, while running a script containing RobustExcelOle commands, the user can open and process workbooks in any Excel instances at any time. RobustExcelOle manages the complex cases of conflicts that might occur such that the user does not need to interfere and the script can continue.

For example, suppose we want to process a list of workbooks. RobustExcelOle allows to rapidly open, manipulate, close and save these workbooks. Now assume, the workbook “workbook.xls” is being processed, while the user has already opened this workbook, has modified but not saved it yet. Excel would prompt a message and ask the user what to do. RobustExcelOle solves this conflict by using an option that states whether the changes of the user should be saved (accepted) or discarded (forgotten) before opening the workbook, e.g.

workbook = Workbook.open('workbook.xls', if_unsaved: :accept)

Similarly, if the user has opened a workbook that has the same name but a different path, the conflict can be solved via an option.

workbook1 = Workbook.open('spec/data/workbook.xls')
# do something
workbook2 = Workbook.open('spec/data/more/workbook.xls', if_blocked: :forget)

For more details about opening and closing workbooks in Excel instances see README_open

Operating on worksheets¶ ↑

Assume we have opened a workbook

workbook = Workbook.open('spec/data/workbook.xls')

We access the first worksheet by

worksheet = workbook.sheet(1)

worksheet = workbook.sheet('Sheet1')

worksheet = workbook.first_sheet

We can read and change the worksheet name.

worksheet.name
# => "Sheet1"

worksheet.name = "new_sheet"

We can copy the first worksheet, name it and add it before the third worksheet.

workbook.add_or_copy_sheet(sheet, as: "copied_name, before: workbook.last_sheet)

For more details about processing worksheets see README_sheet

Reading and writing ranges in worksheets¶ ↑

We can define a rectangular range by providing the top left and the bottum down cell.

worksheet.range([1..3,1..4])

We can read the first three cells of the first row

worksheet.row_range(1, 1..3).values   # => ["foo","workbook","sheet1"]

and the third column

worksheet.col_range(3).values   # => ["sheet1", 2.0, 4.0]

We can read the first cell, using

worksheet[1,1].    # => "foo"

worksheet.row_range(1)[0].value    # => "foo"

Then we modify it.

worksheet[1,1] = "hello"

We get the value of a named range

worksheet["firstcell"]    # => "hello"

and set another value to that range.

worksheet["firstcell"] = "new_value"

For more details about reading and writing contents of cells and ranges see README_ranges

List Objects (Tables)¶ ↑

We can define a list object (or table) from scratch

table = ListObject.new(worksheet, "table 1", [1,1], 3, ["Person","AmountSales"])

or get a given list object in a worksheet given its table number or name.

table = worksheet.table(1)

table = worksheet.table("table1")

A list row can be accessed by providing either the row number or a key, i.e. hash of column name - value pairs, e.g.

row1 = table[1]

row1 = table[{"Number": 1, "Person": "John"}]

You can get all or a maximal number of list rows matching the key.

rows = table[{"Number": 1}, limit: 2]
rows = table[{"Number": 1}, limit: nil]

Now we can set value of a cell of the table with help of methods that are equal to or are underscored variants of the column names, e.g.

row1.person = "John"

row1["Person"] = "John"

Similarly you can get the values. We can also read all values in a row.

table[1].to_a
# => ["John", 40]

table[1].to_h
# => {"Person": "John", "AmountSales": 40}

We can set the values in a whole row.

table[1].values = ["Herber", 80]

Similarly, we can read and set the values in a whole column, e.g.

table.column_values("Person")
# => ["John", "Peter"]

and

table.set_column_values(1, ["Herbert","Paul"])

We can add and delete rows and columns, supplying optionally their name, the position and contents.

 table.add_column("column_name", 3, ["John", "Paul"])
 table.add_row(3, ["John", 40, 2, 2004])

 table.delete_column_values("column_name")
 table[2].delete_values

Finally we can delete empty rows and columns.

 table.delete_empty_rows
 table.delete_empty_columns

For more details about using and processing list objects see README_listobjects

More features¶ ↑

The method General.change_current_binding allows to change the value of self within the current binding, while preserving the local variables, without starting another repl. Assume, object shall be the self, then you would put

General.change_current_binding(object)

Without this method, the ruby shell ‘pry’ allows to change the value of ‘self’ in the console as well, e.g.

object.pry

cd object

However, this command also starts another pry repl (with another binding). Moreover, local variables in the previous binding are forgotten.

The class Win32Ole is being extended such that RobustExcelOle methods can be applied to WIN32OLE objects. As mentioned above, the RobustExcelOle objects are wrapper of corresponding WIN32OLE objects. So the RobustExcelOle objects and their wrapped WIN32OLE objects are interchangeable. One example would be

range.ole_range.copy([4,3])

Likewise it is possible to convert (“type-lift”) Win32Ole objects into the corresponding RobustExcelOle object, using the method to_reo. It is a refinement of the class WIN32OLE. So you can write

using ToReoRefinement

range = sheet.Names.Item("firstcell").to_reo

Design issues¶ ↑

We don’t consider active worksheets or workbooks in our implementation, because this has been proven to be very error-prone.

Examples¶ ↑

You can run the examples included in the directory examples, e.g.

ruby examples\open_save_close\example_unobtrusively.rb

Development¶ ↑

This project RobustExcelOle is work in progress. We are happy to implement further features. So we invite you to send your pull requests. We then strive to realize them as soon as possible. If you have any feedback, or you find use cases that RobustExcelOle does not satisfy, please let us know.

RobustExcelOle is being tested for Excel 2010 and Excel 2013. It can be used for any recent Excel Office version. Most functions should run on Excel 2007 as well.

RobustExcelOle has been optimised with help of the rubocop and the rcov tool.

Support¶ ↑

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.

sunito/robust_excel_ole