Cucumber Ruby and Excel

While 95% of my automation work is web service based, I did put together a bit of Excel validation.

I imported the Ruby gem "roo" which enables Excel, csv and OpenOffice support.  Once in, I could create a test like this to verify the headers of a excel file:
 Scenario Outline: Verify Column headers exist
    Given the financial document is loaded
    Then the <headers> should be at line <row> and column <column>

    Examples:
    |row|column|headers|
    |11 |A     |State  |
    |11 |B     |Government Function|
    |10 |C     |Full-time          |
    |11 |C     |employees          |
    |9  |D     |Full-time          |
    |10 |D     |Pay                |
    |11 |D     |(whole dollars)    |
    |10 |E     |Part-time          |
    |11 |E     |employees          |
    |9  |F     |Part-time          |
    |10 |F     |pay                |
    |11 |F     |(whole dollars)    |
    |10 |G     |Part-time          |
    |11 |G     |hours              |
    |9  |H     |Full-time          |
    |10 |H     |Equivalent         |
    |11 |H     |Employment         |
    |10 |I     |Total              |
    |11 |I     |employees          |
    |8  |J     |Total              |
    |9  |J     |March              |
    |10 |J     |Pay                |
    |11 |J     |(whole dollars)    |

And the step definition is like:
Given /^the financial document is loaded$/ do
  @loadFinancial = Excel.new("data/financial.xls")
end

Then /^the (.+) should be at line (.*) and column (.*)$/ do |headers, row, column|
  @loadFinancial.cell(row.to_i,"#{column}") == headers
end

Some things to note... since I'm passing a row # that's an integer and not a string, I need to do a .to_i to it.  This isn't the fastest solution. But if you have small data files that you want to manipulate or validate, this can work.  This test against 46 fields takes my laptop about 16seconds to complete.  That's pretty slow.  But it works.

Data and File testing

I put up a few small tests to cover data validation within a file.  I'll be building off this project in Github with examples:
https://github.com/wbwarnerb/ts/tree/master/features

So far I've just got a few tests.  The first is a test that verifies Data Files have been delivered and just makes sure they exist.  It's look at the root of the drive for a few files.  This can be modified easily.  Here's the Scenario File:

  Scenario Outline: Check that file exists

    Given a file has come in

    Then the <datafile> is verified as being there

    Examples:

    |datafile|

    |setup.log|

    |hamlet.txt |

 

 

Here's the Step Definition code:

Then /^the (.*) is verified as being there$/ do |datafile|

  assert File.exists?("/#{datafile}")



end

 

The second test does a count on the lines in each file.  It then assumes the user knows this count and validates that each file is the correct line count.  Here's the code to accomplish it:

Here's the Scenario:

  Scenario Outline: Line Count Validation

    Given a file has come in

    Then the <datafile> and it's <linecount> are verified

  Examples:

    |datafile|linecount|

    |setup.log|10      |

    |hamlet.txt |4463  |

Here's the Step Definition Code:

Then /^the (.*) and it's (.*) are verified$/    do |datafile, linecount|

  count = 0

  File.open("/#{datafile}") {|f| count = f.read.count("\n")   }  == "#{linecount}"

  puts "The line count for '#{datafile}' is:"

  puts count



end