Testing in offline mode using Xray and Excel

Simple non-intrusive offer, pillar, blog, or a guide

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.


#!/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
parser.on("-p", "--pass PASSWORD", "Jira password") do |v|
options[:password] = v
parser.on("-j", "--jira JIRA_BASE_URL", "Jira base URL") do |v|
options[:jira_url] = v
parser.on("-c", "--csv CSV_FILE", "CSV file") do |v|
options[:csv] = v
parser.on("-s", "--summary SUMMARY", "Test Execution summary") do |v|
options[:summary] = v
parser.on("-d", "--description DESCRIPTION", "Test Execution description") do |v|
options[:description] = v
parser.on("-v", "--version VERSION", "Version") do |v|
options[:version] = v
parser.on("-r", "--revision REVISION", "Revision") do |v|
options[:revision] = v
parser.on("-t", "--testplan TEST_PLAN", "Test Plan issue key") do |v|
options[:plan] = v
parser.on("-e", "--environment TEST_ENVIRONMENT", "Test Environment") do |v|
options[:environment] = v

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 = {}

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



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

last_test_key = row[test_key_col]


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

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.

Now, you can go to Xray, analyse them further and decide whether you want to create bugs or not for the failed Tests.

Hopefully, the reactor is working as expected and you never need to go back.

Happy online —and offline— testing!

Comments (0)