RobustExcelOle
This ruby gem automates reading, modifying, and writing Excel files. It is designed to support several kinds of concurrency of both simultaneously running Excel instances and simultanously happening user interactions. RobustExcelOle deals with various cases of Excel and user behaviour, and implements workarounds for some Excel bugs. The gem provides convenient methods for common tasks, and facilitates referenced libraries. It can be used for any recent Excel Office version, and is tested for Excel 2010.
RobustExcelOle works by sending VBA methods via Win32OLE. It implements a management system and keeps track of Excel files and Excel instances.
Requirements
-
Ruby 1.8.6 or higher
Install
gem install robust_excel_ole
Usage
RobustExcelOle can be used either for scripts
require 'robust_excel_ole'
include RobustExcelOle
or as a console
reo
The call of the console will include RobustExcelOle for you.
The following examples can be used for both scripts and console. You can try them directly.
Description
RobustExcelOle enables opening and processing Excel files (or workbooks). In the following example, we want to open a workbook, modify a cell, save and close the workbook.
First we open a workbook.
workbook = Workbook.open 'spec/data/workbook.xls'
We can also open the workbook using a block, similar to, e.g., File.open
.
Workbook.open('spec/data/workbook.xls') do |workbook|
# do something
end
Now we have a Workbook object that wraps a win32ole object. That is, you can send any win32ole (VBA) method to it. For some common tasks and for considering various cases of Excel and user behaviour, more convenient methods are implemented.
For example, we can make the workbook visible, using
workbook.visible = true
or
workbook.for_this_workbook(:visible => true)
You can also open a workbook and make it visible in one step.
workbook = Workbook.open('spec/data/workbook.xls', :visible => true)
or
workbook = Workbook.open('spec/data/workbook.xls', :v => true)
Then we read the value of a cell.
value = workbook['firstcell']
# => "foo"
Now we write a new value in this cell.
workbook['firstcell'] = "new"
For more details about reading and writing contents of cells and ranges see README_ranges
Then we save the workbook.
workbook.save
We can also save the workbook under a different name.
workbook.save_as('spec/data/new_workbook.xls')
Finally we close the workbook.
workbook.close
We could also have saved and closed the workbook in one step.
workbook.close(:if_unsaved => :save)
For more details about saving and closing workbooks see README_save_close
One special feature of RobustExcelOle is that it enables reopening workbooks.
workbook.reopen
Technically, this feature is implemented in such way, that a Workbook object is a proxy of an Excel workbook. A Workbook object is defined by the full workbook name and the Excel instance in which it is opened. RobustExcelOle ensures identity transparency which means that the same Workbook objects refer to the same workbooks, and vice versa.
Using Excel instances
You can start a new Excel instance with
excel1 = Excel.create
or
excel1 = Excel.new(:reuse => false)
You can also have an Excel object by connecting to the already running Excel instance.
excel2 = Excel.current
or
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 you can use
Excel.kill_all
For more details about creating 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 you 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
book3 = 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.
book1.close
book1 = 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 is 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)
As a further feature, RobustExcelOle allows processing workbooks, while still supporting user’s interactions: The commands enable to open, close, reopen, read, modify, write and save Excel files, 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 Excel files 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 you 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 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 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 is solved via an option.
workbook1 = Workbook.open('spec/data/workbook.xls')
# do something
workbook2 = Workbook.open('spec/data/more/workbook.xls', :if_obstructed => :forget)
Finally, 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.('workbook.xls') do |book|
# do something
end
Workbook.for_reading('workbook.xls') do |book|
# do something
end
For more details about opening and closing workbooks in Excel instances see README_open
Processing worksheets
Assime you have opened a workbook
workbook = Workbook.open('spec/data/workbook.xls')
You access the first worksheet by
sheet = workbook.sheet(1)
or
sheet = workbook.sheet('Sheet1')
or
sheet = workbook.first_sheet
You can read and change the worksheet name.
sheet.name
# => "Sheet1"
sheet.name = "new_sheet"
You can read the first three cells of the first row
sheet.row_range(1, 1..3).values # => ["foo","workbook","sheet1"]
and the third column
sheet.col_range(3).values # => ["sheet1", 2.0, 4.0]
You can read the first cell, using
sheet[1,1].value # => "foo"
or
sheet.row_range(1)[0].value # => "foo"
Then we modify it
sheet[1,1] = "hello"
You can get the value of a named range
sheet["firstcell"] # => "hello"
and set another value to that range.
sheet["firstcell"] = "new_value"
You 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
Development
For some details about developing RobustExcelOle see README_development
Want to do more things
If you want to do something that not provide a function, you can use win32ole methods.
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 [email protected]
License
MIT License. For more imformation, please see LICENSE.