openpyxml win

I'm a software tester. I write and run a lot of test scripts. These scripts are usually written in Excel.

Excel feels much more appropriate than a dedicated test case management environment. It is a tester's tool, not a management tool. Its Achilles heel, however, is the fragility of inter-workbook linking. It just doesn't seem possible to create and maintain relative links for multiple users.

Using a separate summary/reporting workbook to show test progress requires quite a lot of link plumbing. This plumbing is a maintenance nightmare.

My current project is in its infancy. The shit and the fan aren't even in the same room yet. I have time to put things in place. Time to do something about the plumbing. I can't fix Excel, but I can rip out most of the troublesome plumbing.

I've had some success with Apache POI in the past (Java library for reading/writing Excel files). My Eclipse and Java skills are rusty, but my Python is not.

A little Googling gave me a few options. The first I tried was openpyxl. It worked, so I saw no reason to look beyond it.

The Test Scripts are all based on a template devised and maintained by the testers. This template has some rows at the top containing a live view of the state of the sheet (number of steps, number of passes, number of failures).

I've now reduced the plumbing to a few cells in a workbook that link to a few cells in a CSV. This CSV is written by a Python script that scans a folder and its subfolder for Excel files and uses openpyxl to scrape values from the results cells of test scripts.

If new scripts are created somewhere in the folder structure, these will show up in the CSV.

It's also possible to generate a summary at each level of folder hierarchy. That means we can create a meaningful hierarchy - say reflecting the requirements hierarchy - and easily get a summary at level.

Another benefit is that we can split long test scripts into smaller individual test cases. The Python that scrapes the results will simply find them all without any additional work.