Using Spreadsheets

In order to parse the data on a worksheet I needed to make some basic
assumption about how the data is formatted. The following list shows
what makes up a spreadsheet page.

Headers [Style=Bold]

These cells correspond to the test fixture’s attribute or method.
You can optionally follow methods with () if you would like to differentiate
them more from attributes.

Data [Style=None]

The data should comprise the bulk of information on the spreadsheet
and represents the parameters used for the function or testcase (which we
get from the header cells).

Ignore Cell [Style=Italic]

Any text where the font style is italic will be ignored.

Comment [optional header]

If you include a header called ‘comment’, Rasta will ignore the column by default.
This is useful to have a high-level description of what each test case is doing.

Pending [optional header]

If you include a header called ‘pending’ AND the cell has data for a given record,
then the test will be marked by Rspec as pending using the description for a cell.
A good use of this is you create a test case and it finds a bug. You don’t want to
run tests you know fail, so put the bug number in the pending row/column and RSpec
will skip the test and note that it was skipped.

Spreadsheet Values

When parsing a spreadsheet, the cells are evaluated to try to provide a datatype
that matches the value of the cell. The following patterns are matched and
the appropriate datatype is returned.

ARRAY  = /\A\s*\[.+\]\s*\Z/ms       value bounded by []
HASH   = /\A\s*\{.+\}\s*\Z/ms       value bounded by {}
BOOL   = /\A\s*(true|false)\s*\Z/i  value true, false, TRUE, FALSE
NUMBER = /\A\s*-?\d+\.??\d*?\s*\Z/  value number or decimal
REGEXP = /\A\s*(\/.+\/)\s*\Z/ms     value bounded by //

Note that ‘nil’ is still passed as a string because the api passes nil for the
value of an empty cell so it would be difficult for your code to distinguish between them.
So in this case we just pass it along as a string and allow the application to
handle the value appropriately.

Valid Data Configurations

This library has a few requirements about the format of the
worksheet data so it can be properly parsed. In order to define the boundaries
of a table I presume that there will be a header column/row where the cells
are bold.

The library will inspect the worksheet and determine the first
data cell (upper left) by looking for the first bold
cell. The last datacell (lower right) will be used with that first
data cell to form a rectangle representing the boundaries of the
test data. Then, the cells to the left and top of the first data
cell will be looked at to see if the text in the cell is bolded.
If so, it’s assumed to be the header row.

Comments

You can add comments to your spreadsheet outside of the data table
by italicizing the text. If you don’t change the font style, then
it could be interpreted as test data and cause unexpected results.

Skipping Data

The following items will be skipped and not parsed. This gives an
easy way to disable data

Note that you CAN have comments in a tab and the comment portion
will be stripped before it’s used. This is actually useful because
it works around Excel’s requirement that every tab name is unique.
So for example you could have MyTab#a, MyTab#b as separate tabs in
the spreadsheet and MyTab will be returned by Book.each. (though
you do need to specify the full tabname with the comment when using
the :continue option)

Cells with a large number of characters

If a cell has an abnormally large amount of data then the method used
by the rasta library to get the cell’s contents can result in clipping
the data. Most of the time you’ll never hit this but there are cases
where you’re passing in a large array or comparing a block of text.
If you encounter this, change the cell’s number format to ‘Custom’
and select ‘@’. This should correct any problems with truncation.

Parsing the Spreadsheet

If you would like to iterate through the spreadsheet values you can use
something like the following.

require 'rasta/spreadsheet'
begin
  book = Rasta::Spreadsheet::Book.new(ARGV[0])
  book.each do |sheet|
    puts '---' + sheet.name + '---'
    puts sheet.to_s
    sheet.each do |record|
      record.each do |cell|
         puts "\t#{cell.header}=#{cell.value} [#{cell.value.class}]"
      end
    end
  end

ensure
  Rasta::Spreadsheet::Excel.instance.cleanup
end