Tuesday, March 30, 2010

Planning Meeting

We started to work on our new application over the weekend. No specifics at this time, let's just call it "Project X". It'll be a web app, most likely developed in Rails using mySQL back-end. But it doesn't really matter just yet.

We got together Saturday around 10 AM. After establishing the agenda we started to work on the details.
Here is what our agenda was:

1) Check competitors
2) Look at HTML Templates
3) Define system user types
4) Work out the workflows for our different user types
5) Create wire frames
6) Create mock-ups
7) Enter story cards in Pivotal tracker

The real work started with item 3 on our list. It is like the 10,000 feet view of the app. No specifics, discussing what happens when. Our technique was similar to a UML activity diagram, focusing on what triggers the application to move from one state to the other.

Once we had a general idea of the different workflows that different user types had, we started to "zoom into" the areas by creating wire frames. We wanted to focus on what pages we needed and how you could get from one page to the other.

We estimated around 12 – 15 pages we need to get the application off the ground. Our mock up task focused on what elements or fields we'd have on those pages. What describes a user? Name, email, company name, etc. Do we need categories to help searches? Sure! So we added that making it easy for the different user types to connect.

One of us was working on the white board, while the other entered stories into the excellent Pivotal Tracker.
We finished around 3:30 PM. I was tired, but we did accomplish what we wanted. Create stories and start to move forward with the project.

What's next? We'll pick a free web template and just build up HTML prototypes. Our idea is vague right now and we want to make changes freely and easily. Having a DB back-end would slow us down when we want to switch things around. We'll learn about the application by doing this and asking ourselves: As User Type "A" when I go to my dashboard, should I see historical items to do rating?
Development will start when we are OK with our HTML pages.

Do you follow the same process with your customers?

Sunday, March 14, 2010

Testing Excel with Cucumber

Testing an Excel application with Ruby's unit testing tool is doable, however, the tests are not easily readable. Why not using Given/When/Then structured Cucumber tests instead? Cucumber is an active open source project enjoying great popularity in the Rails universe. I won’t be describing the tool in this blog post, if you’re new to Cucumber, please visit the official website or read the excellent Cucumber book to learn more about it.

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:
  |-- 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
      @excel = WIN32OLE.connect('excel.application')
      @wb = @excel.ActiveWorkbook
      @excel = WIN32OLE::new('excel.application')
      @excel.visible =true
      @wb = @excel.Workbooks.Open(@@excel_file_path )

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
  @worksheet = ExcelHandler.instance.worksheet
  @worksheet.extend CellValueGetter

Then /^I should see "([^\"]*)" in the "([^\"]*)" cell$/ do |value, cell|
  @worksheet.get_cell_value(cell).strip.should == value

module CellValueGetter
  def get_cell_value(cell)

  def set_cell_value(cell, value)
    get_cell(cell).value = value

  def get_cell(cell)
    cell_values = cell.split('')
    cell_values.length.should == 2
    cells(cell_values[1].to_i, cell_values[0])

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)

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

When I execute "cucumber features" in the command prompt I see this:

1 scenario (1 passed)
7 steps (7 passed)

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.

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

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

Let's see what we get now:

2 scenarios (2 passed)
18 steps (18 passed)

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)

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)

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 ==)
  @@ -1,2 +1,2 @@
  ./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

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!

Saturday, March 6, 2010

Automated Testing of an OBA App with Ruby


You could call a me a late adopter, or a busy dad, but what matters is that it's finally here. I am writing my first blog post!!
I was thinking what the best topic could be for The First one? How I got into programming? Or what I worked on yesterday? Maybe a little bit of both.

I've been doing .NET development as a day job but I am hacking out Ruby/Rails code in the evenings.
I am test infected, I can't live without tests and it really bothers me when I see code written without supporting tests around it. I've seen far too many projects starting out with everything running smooth: features are being developed with the speed of light, everybody is happy. And then, maybe 4-6 months into the project development speed slows down, defect rates go up and the code is just plain fragile. Nobody dares to touch it, not knowing what could go wrong. Changing anything is close to impossible.

I admire the testing enthusiasm I see in the Ruby community. The way they practice TDD, BDD, the way they won't write a single line of code without a failing test first. And while Ruby is really flexible, it can be very dangerous. I'd say not having sufficient test coverage in a dynamic language is like you sitting in car running towards a cliff.

I work on an OBA (Office Business Application) project at work. We have decent unit test coverage, however, not having automated functional testing is a risk. Sure we have QA staff, they can just go through the test scripts manually over and over. I think what could automated should be automated.
I bumped into the win 32 OLE automation object in ruby (http://ruby-doc.org/core/classes/WIN32OLE.html) a couple of months ago but I never had the time to fully investigate it. I was able to open Excel, read and write a value in the cell, save the worksheet and close it. This was all cool, but not enough.

require 'win32ole'
require 'test/unit'
ExcelTest < Test::Unit::TestCase

 def setup
  @excel = WIN32OLE::new("Excel.Application")
  @excel.visible = true
  @workbook = @excel.Workbooks.Open("C:\\path_to_your_excel_file\some_file.xls")
  @sheet1 = @workbook.worksheets(1)
  @sheet2 = @workbook.worksheets(2)

 def test_should_verify_captions
  assert_equal('Category 1', @sheet1.cells(2, "A").value)
  assert_equal('Child 1', @sheet1.cells(3, "A").value)
  assert_equal('Child 2', @sheet1.cells(4, "A").value)
  assert_equal('Child 3', @sheet1.cells(5, "A").value)
  assert_equal('Child 4', @sheet1.cells(6, "A").value)

I am trying to use Ruby unit testing. It's easy and simple for now. In the setup method I create an Excel object with the tested workbook loaded into it. I create two instance variables for the worksheets to keep my code simple.

Then in the first test, I verify caption cells. Nothing magical here, I just make sure that in the 2nd row's "A" column the cell should have a value of "Category1".

To be continued...