Testing in offline mode using Xray and Excel

There are scenarios where testers go out to the field and execute their testing in places without or with restricted connectivity. They may not have access to the enterprise Jira server due to lack of VPN access, for example.

So, how can you test, record the results and comments, and then later on synch them up to Xray?

Let's say you need to do some testing inside a nuclear reactor (and somehow survive).

Step 1: Export the Tests

Start by using an Excel sheet with the Test cases you want to run, with all the necessary information, including the steps.

If you want to run just one or two Test cases, you could just write the Excel sheet manually. Otherwise, you can automate this using Xporter.

Start by selecting the Test issues you want based on some criteria (e.g., the ones from a Test Set or a Test Plan or just some handpicked ones). Then, choose the "Xray Offline Test Report" Xporter template and voilá! You have an Excel sheet that you can take with you and use for offline testing. The report is available in Xporter's Template Store and can be installed from within Jira, in Xporter's Template Store settings table.

Step 2: Record the results

Using Excel or a similar application (e.g., LibreOffice), execute your tests and record the respective results in the Excel sheet. You may pass/fail individual steps and leave comments where and if appropriate. Don't forget to save it.

Step 3: Import the results

When you get back to the office, it's time to submit the results to Jira and Xray.

Since Xray provides a complete REST API, use it to submit results using simple Xray JSON format.

With little effort, you can build an application to parse the Excel sheet and submit its results. Export the sheet to CSV and use that format instead.

As a proof-of-concept, the example below uses a Ruby script to parse the CSV and then submits it to the Xray JSON endpoint. Please note that the script below is an unoffical and unsupported script, but you can certainly use and adapt it your needs.

submit_results.rb

#!/usr/bin/env ruby

require 'optparse'
require 'rest-client'
require 'csv'
require 'json'

# This will hold the options we parse
options = {}

OptionParser.new do |parser|
parser.on("-u", "--user USERNAME", "Jira username") do |v|
options[:username] = v
end
parser.on("-p", "--pass PASSWORD", "Jira password") do |v|
options[:password] = v
end
parser.on("-j", "--jira JIRA_BASE_URL", "Jira base URL") do |v|
options[:jira_url] = v
end
parser.on("-c", "--csv CSV_FILE", "CSV file") do |v|
options[:csv] = v
end
parser.on("-s", "--summary SUMMARY", "Test Execution summary") do |v|
options[:summary] = v
end
parser.on("-d", "--description DESCRIPTION", "Test Execution description") do |v|
options[:description] = v
end
parser.on("-v", "--version VERSION", "Version") do |v|
options[:version] = v
end
parser.on("-r", "--revision REVISION", "Revision") do |v|
options[:revision] = v
end
parser.on("-t", "--testplan TEST_PLAN", "Test Plan issue key") do |v|
options[:plan] = v
end
parser.on("-e", "--environment TEST_ENVIRONMENT", "Test Environment") do |v|
options[:environment] = v
end
end.parse!

endpoint_url = "#{options[:jira_url]}/rest/raven/1.0/import/execution"
headers = { "Content-Type" => "application/json"}

rows = CSV.read(options[:csv],{:col_sep => ";"})
description = ""

json = { "info" => { "summary" => options[:summary], "version" => options[:version], "description" => (options[:description] || "")}}
json["info"]["revision"] = options[:revision] if !options[:revision].nil? && !options[:revision].empty?
json["info"]["version"] = options[:version] if !options[:version].nil? && !options[:version].empty?
json["info"]["testPlanKey"] = options[:plan] if !options[:plan].nil? && !options[:plan].empty?
json["info"]["testEnvironments"] = [ options[:environment] ] if !options[:environment].nil? && !options[:environment].empty?
json["tests"] = []

first_row_with_tests = 1
test_key_col = 1
status_col = 9
comment_col = 10
step_number_col = 5
step_status_col = 9
step_comment_col = 10

last_test_key = nil
test_info = {}

rows.each_with_index do |row,idx|
if idx >= first_row_with_tests

# inside some test related info?
if !row[test_key_col].nil? && (row[test_key_col] =~ /^w+-d+$/) # !row[test_key_col].empty?

# changed test key and has test related info to commit? then add it to the json object
if (row[test_key_col] != last_test_key) && !test_info.empty?
json["tests"] << test_info
test_info = {}
end

# is it step info or global test run info
if !row[step_number_col].nil? && !row[step_number_col].empty?
step_info = { "status" => row[step_status_col]}
step_info["comment"] = row[step_comment_col] if !row[step_comment_col].nil? && !row[step_comment_col].empty?
test_info["steps"] = [] if test_info["steps"].nil?
test_info["steps"] << step_info
else
# test run global row
test_info = { "testKey" => row[test_key_col], "status" => row[status_col]}
test_info["comment"] = row[comment_col] if !row[comment_col].nil? && !row[comment_col].empty?
#puts test_info

end

else

# has test related info to commit? then add it to the json object
if !test_info.empty?
json["tests"] << test_info
end
test_info = {}
end

last_test_key = row[test_key_col]
end

end

puts json.to_json
RestClient::Request.execute method: :post, url: endpoint_url, user: options[:username] , password: options[:password], headers: headers, payload: json.to_json

The script syntax:

/submit_results.rb --help Usage: submit_results [options] -u, --user USERNAME Jira username -p, --pass PASSWORD Jira password -j, --jira JIRA_BASE_URL Jira base URL -c, --csv CSV_FILE CSV file -s, --summary SUMMARY Test Execution summary -d, --description DESCRIPTION Test Execution description -v, --version VERSION Version -r, --revision REVISION Revision -t, --testplan TEST_PLAN Test Plan issue key -e TEST_ENVIRONMENT, Test Environment --environment

To execute it, you must have a recent Ruby version with the "rest-client" gem.

Along with the Jira credentials and the CSV filename, you can also pass some parameters related with the Test Execution issue that is going to be created.

submit_results.rb -j http://jiraserver.example.com -u admin -p admin -v v3.0 -r 123 -d "results reported in the field" -s "execution made inside the nuclear reactor" -c detailed_offline_results.csv

Voilá! You got your offline recorded results in Jira.