Here is the task I need to write tests for: we have a basic Excel spreadsheet with a handful of cells in it.
The row cells with yellow background are the column headers. Column cells starting from A2 are captions. "Category1" has three child elements and "Category2" has only two. The category rows are calculated by the sum of their children. Value Total is the sum of Value1 and Value2 for each row. The totals in the bottom are calculated by adding up Category1 and Category2 values.
First I'd like to verify that the column headers and captions are in place. I started writing my feature this way:
Feature: Modify values in the Excel sheet
In order to show my power
As a user
I want to interact with Excel
Scenario: Display column headers and captions
Given I have 2 categories
And I have 3 child elements under the first category
When I open the Excel workbook
Then I should see "Category" in the "A1" cell
And I should see "Value Total" in the "B1" cell
And I should see "Value1" in the "C1" cell
And I should see "Value2" in the "D1" cell
And I should see "Category1" in the "A2" cell
And I should see "Child1" in the "A3" cell
And I should see "Child2" in the "A4" cell
I created the following folder structure:
/features
|
|-- step_definitions
| |-- excel_handler.rb
| |-- excel_steps.rb
|-- support
| |-- env.rb
| - excel.feature
The feature from above was saved in the excel.feature file.
I am not particularly concerned with the "Given" part of the scenario. The data can be loaded into Excel either from a CSV, XML or a remote data store. I'll ignore this part to keep my examples clear and concise.
My previous blog post described how I can interact with Excel through the great WIN32OLE object. I created the ExcelHandler class which does that:
class ExcelHandler
include Singleton
# set this to your Excel file path
@@excel_file_path = 'C:\Temp\TestWorkbook.xlsx'
def open_excel
begin
@excel = WIN32OLE.connect('excel.application')
@wb = @excel.ActiveWorkbook
rescue
@excel = WIN32OLE::new('excel.application')
@excel.visible =true
@wb = @excel.Workbooks.Open(@@excel_file_path )
end
end
end
I might not have the most elegant code in the open_excel method, but this allows me to attach to a running instance of the Excel workbook which is a big thing for me. In case the workbook has not been launched yet, I take care of it here. Launching and closing Excel takes time and resources, I'd like to reuse running instances of Excel whenever I can.
I take advantage of the singleton pattern in this class. Starting up Excel is an expensive operation, I want to make sure that one and only instance is handling the workbook. My tests are single threaded, I think I should be safe there.
The env.rb file has the require statements:
require 'spec/expectations' require 'win32ole' require 'singleton'
All the magic happens in the excel_steps.rb file:
When /^I open the Excel workbook$/ do
ExcelHandler.instance.open_excel
@worksheet = ExcelHandler.instance.worksheet
@worksheet.extend CellValueGetter
end
Then /^I should see "([^\"]*)" in the "([^\"]*)" cell$/ do |value, cell|
@worksheet.get_cell_value(cell).strip.should == value
end
module CellValueGetter
def get_cell_value(cell)
get_cell(cell).value
end
def set_cell_value(cell, value)
get_cell(cell).value = value
end
def get_cell(cell)
cell_values = cell.split('')
cell_values.length.should == 2
cells(cell_values[1].to_i, cell_values[0])
end
end
Look at how I add methods to the @worksheet object. You gotta love Ruby for that!
The methods in this module are responsible for getting and setting a value based on the provided cell.
I left out the Given parts that I ignore anyway. You can look at the entire source code after you pulled it from my github account.
When I execute "cucumber features" in the command prompt I get this:
1 scenario (1 passed)
10 steps (10 passed)
0m0.027s
Hey, the first scenario is passing!!
All right, let's verify in the second scenario that the data was loaded correctly:
Scenario: Display loaded values
Given I have 2 categories
And I have 3 child elements under the first category
When I open the Excel workbook
Then I should see 111 in the "C3" cell
And I should see 353 in the "C2" cell
And I should see 458 in the "B3" cell
And I should see 1523 in the "B2" cell
I had to add one step definition to the excel_step.rb file:
Then /^I should see (\d+) in the "([^\"]*)" cell$/ do |value, cell| @worksheet.get_cell_value(cell).should == value.to_i end
When I execute "cucumber features" in the command prompt I see this:
1 scenario (1 passed)
7 steps (7 passed)
0m0.024s
I know I am not using the "Given" part of the scenarios, however, I do repeat code there. I used the background feature of Cucumber and DRY-ed up my scenarios a little bit.
Background:
Given I have 2 categories
And I have 3 child elements under the first category
I use scenario outline in my third scenario. I set the "Value1" cell for the "Child1" row to 211. Take a look at the result in Excel:
I also try to set the same cell to 51, I got these numbers then:
I am verifying numbers in red in the last scenario:
Scenario Outline: Change values
Given the default values were loaded
When I open the Excel workbook
And I put <child1_value> in the "C3" cell
Then I should see <category_value1> in the "C2" cell
And I should see <child1_sum> in the "B3" cell
And I should see <category_total> in the "B2" cell
And I should see <value1_total> in the "C9" cell
Examples:
| child1_value | category_value1 | child1_sum | category_total | value1_total |
| 211 | 453 | 558 | 1623 | 1281 |
| 51 | 293 | 398 | 1463 | 1121 |
I added the following step to the excel_steps.rb file:
When /^I put (\d+) in the "([^\"]*)" cell$/ do |value, cell| @worksheet.set_cell_value(cell, value) end
Let's see what we get now:
2 scenarios (2 passed)
18 steps (18 passed)
0m0.038s
All the scenarios passed individually; let's see how we do when we execute them all at once:
4 scenarios (4 passed)
37 steps (37 passed)
0m3.718s
Everything is rock solid so far. Am I testing the right file? I change the first scenario's "Then I should see "Category" in the "A1" cell" line to this: "Then I should see "CategoryABC" in the "A1" cell".
When I execute "cucumber features" I get the following output:
4 scenarios (1 failed, 3 passed)
37 steps (1 failed, 6 skipped, 30 passed)
0m3.710s
I see that the scenario I just changed is now failing:
Then I should see "CategoryABC" in the "A1" cell # features/step_definitions/excel_steps.rb:23
expected: "CategoryABC",
got: "Category" (using ==)
Diff:
@@ -1,2 +1,2 @@
-CategoryABC
+Category
(Spec::Expectations::ExpectationNotMetError)
./features/step_definitions/excel_steps.rb:24:in `/^I should see "([^\"]*)" in the "([^\"]*)" cell$/'
features\excel.feature:13:in `Then I should see "CategoryABC" in the "A1" cell'
I change it back to the original one and everything is OK again.
One more thing I need to do: when the feature is complete, I'd like to close Excel.
I use the cucumber's at_exit hook to accomplish that:
at_exit do ExcelHandler.instance.close_excel end
When I execute the cucumber feature I see Excel popping up and closing down.
What if I wanted to run the feature in headless mode? It's simple, I just have to change the @excel.visible = true value to false in the excel_handler.rb file.
I think cucumber is a great way to automate testing on an Excel spreadsheet. The cucumber features can serve both as requirement documents and test scripts, hence providing executable documentation. They are easy to read and understand for the entire team.
You can get the demo's source code from my github account.
I developed the code on Windows 7 using ruby version 1.9.1. You also need cucumber and the rspec gems installed to run the code.
Happy coding!
