Testing in offline mode using Xray and Excel

You may think that users always have access to internet and intranet resources but unfortunately, that is not the case.

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.