Data-Driven Tests
How do I use Excel with Watir for data-driven tests?
require 'win32ole'
excel = WIN32OLE::new("excel.Application")
workbook = excel.Workbooks.Open("c:\\examples\\example_sheet.xls")
worksheet = workbook.WorkSheets(1) # get first workbook
worksheet.Select # just to make sure macros are executed, if your sheet doesn't have macros you can skip this step.
value = worksheet.Range("a12").Value # get the value at cell a12 in worksheet.
data = worksheet.Range("a1:c12").Value # returns 2D array with values starting from cell a1 to cell c12
See also Scripting Excel
and roo.
There is also Excel interface class library available which simplifies reading and writing Excel data.
How do I use OpenOffice.org Calc with Watir for data-driven tests?
Similar to Excel and the rest of Microsoft Office, OpenOffice.org is enabled for automation using a COM/OLE-like interface called UNO. So here's a brief example of how to access your Excel or OpenOffice.org spreadsheet using UNO, the Ruby Windows OLE interface, and OpenOffice.org.
require "win32ole"
noArgs = []
file_uri = "file:
serviceManager = WIN32OLE.new("com.sun.star.ServiceManager")
coreReflection = serviceManager.createInstance("com.sun.star.reflection.CoreReflection")
desktop = serviceManager.creatInstance("com.sun.star.frame.Desktop")
spreadsheet = desktop.loadComponentFromURL (file_uri, "_blank", 0, noArgs)
sheetsCollection = spreadsheet.Sheets
sheet1 = sheetsCollection.getByIndex(0)
cellA1Formula = sheet1.getCellByPosition(0, 0).Formula # Gets text or number or whatever is in the cell
cellA1NumericValue = sheet1.getCellByPosition(0, 0).Value # Gets numerical value of the cell
cell1A = sheet1.getCellByPosition(0, 0) # Gets cell 1A
cell1A.Formula = "Nathan Lane" # Sets the formula for cell 1A to "Nathan Lane"
(Deprecated)Alternatively I have put together a library that simplifies some of the tasks of retrieving and storing data in a spreadsheet using OpenOffice.org. Currently this library is downloadable at my website, at http://nathandelane.awardspace.com/programming.html. The library is called oohelper.rb.
(New)Oohelper.rb has been replaced by Ufooar (Uno For OpenOffice.org and Ruby), which is downloadable here at its own project page http://rubyforge.org/projects/ufooar/.
See also roo.
