Updating Excel Files

Last edited on

Synopsis

Squish does not yet include functionality for writing or updating Excel files, but it is possible to use third party modules for (for example) Python, to implement simple scripts that perform the desired activity, like updating a cell value.

Such helper scripts/tools can then be executed by the Squish test script as external processes, as described in Executing external applications.

Updating *.xls files

#! /usr/bin/env python

import os.path
import sys


# Use xlrd, xlwt, xlutils libraries:
#
# More information:
#  http://www.python-excel.org/
#
# Install on Windows (run as admin/elevated):
#  pip install xlrd xlwt xlutils
#
# Install on Unix:
#  sudo -H pip install xlrd xlwt xlutils
from xlrd import open_workbook
from xlutils.copy import copy


# Also see:
# https://stackoverflow.com/questions/26957831/edit-existing-excel-workbooks-and-sheets-with-xlrd-and-xlwt

def main():
    file_name = sys.argv[1]
    worksheet_index = int(sys.argv[2])
    row = int(sys.argv[3])
    column = int(sys.argv[4])
    new_cell_value = sys.argv[5]

    wb_readonly = open_workbook(file_name)
    wb = copy(wb_readonly)

    ws = wb.get_sheet(worksheet_index)
    ws.write(row, column, new_cell_value)

    wb.save(file_name)


if __name__ == "__main__":
    if len(sys.argv) != 6:
        print "USAGE: %s file_name worksheet_index row column new_cell_value" % sys.argv[0]
        sys.exit()
    main()
update_xls.py

Updating *.xlsx files

#! /usr/bin/env python

import os.path
import sys


# Use openpyxl library:
#
# More information:
#  http://www.python-excel.org/
#
# Install on Windows:
#  pip install openpyxl
#
# Install on Unix:
#  sudo -H pip install openpyxl
from openpyxl import load_workbook


def main():
    file_name = sys.argv[1]
    worksheet_index = int(sys.argv[2])
    cell_coordinate = sys.argv[3]
    new_cell_value = sys.argv[4]

    # Load Excel file:
    wb = load_workbook(file_name)

    # Get worksheet:
    ws = wb.worksheets[worksheet_index]

    ws[cell_coordinate].value = new_cell_value

    wb.save(file_name)
    wb.close()


if __name__ == "__main__":
    if len(sys.argv) != 5:
        print "USAGE: %s file_name worksheet_index cell_coordinate new_cell_value" % sys.argv[0]
        sys.exit()
    main()
update_xlsx.py