RobustExcelOle

robust_excel_ole implements methods for accessing Excel files using win32ole.

It provides convenient methods for opening, modifying, saving and closing Excel files. Furthermore all VBA methods can be sent.

robust_excel_ole manages Excel workbooks that have been opened in several Excel instances.

This is work in progress.

Requirements

  • Ruby 1.8.6 or higher

Install

gem install robust_excel_ole

Usage

Opening a workbook.

Example:

include RobustExcelOle
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 :default_excel, :force_excel, :if_absent, :if_unsaved, :if_obstructed, :read_only, :visible, :displayalerts.

Here are a few examples:

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

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

Opening a workbook with the same name in a different path. The option :if_obstructed => :forget causes the old workbook to close such that it does not block the new workbook anymore.

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

Opening another workbook with the same file name in the running Excel. The option :if_unsaved => :accept has the effect that if the workbook contains unsaved changes, then the workbook remains open, but no error is raised, i.e. the program can continue.

book3 = Book.open('workbook.xls', :if_unsaved => :accept)

Closing a workbook.

book.close

The option is : :if_unsaved. Example:

Closing the workbook and saving it before if it has unsaved changes.

book.close(:if_unsaved => :save)

Reopening workbooks.

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

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

The closed book is now alive again, i.e. responds to Excel methods.

book.alive?
 => true

This feature is achieved by providing identity transperence and by storing the file name. Identity transperence 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.

reopened_book === book
=> true

Saving a workbook.

book.save

Saving a workbook with a file name.

book.save_as('another_workbook.xls')

The option is :if_exists. Example:

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

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

Unobtrusively opening 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 book is opened or closed, saved or unsaved, readonly or writable.

Options are :if_closed, :read_only, :use_readonly_excel, +:keep_open, :visible

The option :if_closed => :hidden provokes that a closed book is opened unobtrusively in a separate Excel instance that is not visible and has no DisplayAlerts. Any following closed book would be opened in this Excel instance as well when using this option.

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

Value of a named cell or range

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

book.nvalue(name)

Checking whether the workbook is alive.

This method finds out whether the Excel workbook that is referenced by the Book object responds to methods.

if book.alive? then sheet = book[0] end

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

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[0, 0]  => 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 a cell are just delegated. Example:

Reading the value of a cell.

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

Writing a cell

sheet[0,0] = "new_value"

Accessing a range of a row or column.

Accessing a range of a row.

sheet.row_range(0)  => first row
sheet.row_range(0, 0..2 )  => first three cells of the first row

Accessing a range of a column.

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

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).

Reusing a certain running Excel instance.

excel3 = Excel.new(:excel => excel1)

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.

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

Closing all Excel instances.

Excel.close_all

Examples

Example 1

Opening a book.

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

Accessing a sheet via its name.

sheet = book['Sheet1']

Changing the first cell.

sheet[0,0] = "new"

Saving the book.

book.save

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

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

Closing the book.

book.close

Example 2

Opening a book.

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

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

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

Opening the book in a given Excel instance.

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

Closing the books.

book.close
new_book.close
another_book.close

Reopening the book.

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

The writable book is being prefered.

reopened_book === book
=> true

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

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

Example 3

Opening a book.

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])

Opening a new book with the same name in a new Excel. Leave the book 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 book with the same name in the running Excel. The book 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 book without saving it.

third_book.close(:if_unsaved => :forget)

Closing the first book and saving it before.

book.close(:if_unsaved => :save)

Example 4

Opening a book.

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

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

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

Changing its cell.

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

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

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

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

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

Closing the book.

book4.close

Example 5

Opening a book.

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

Printing its first cell.

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

Unobtrusively modify the book.

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

The book is modified, but its status is unchanged.

new_sheet = book[0]
not (new_sheet[0,0].value == sheet[0,0].value) 
=> true
book.Saved 
=> true

More Details

For more details about usage: see Readme_detail_rdoc.html

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:

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

  • a book management system stores all books that have been open. This book store is being used, e.g., for reopening a book that has been closed before. It provides transperency identity, i.e., equal Excel books 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 book 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

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 [email protected]

License

MIT License. For more imformation, please see LICENSE.