Generate index cards, wiki pages and pdf files from your product backlog spreadsheet – Part 2

Agile, Media wiki

In this article we’ll extract information from an MS Excel spreadsheet using python’s xlrd library.

Prerequisites include python-setuptools (aka easy_install – also available on Mac, Linux, Windows ) and the pypi xlutils and mwclient libraries.  To install these on Ubuntu do:

sudo apt-get install python-setuptools

sudo easy_install xlutils

 sudo easy_install mwclient

Now it's time to get coding.
First of all, we'll write a function read the items from the spreadsheet into a per-row list of (colname, colvalue) pairs and construct a list of lists:
import xlrd

def read_backlog_items_from_workbook(excelfilename):
    wb = xlrd.open_workbook(excelfilename)
    sh = wb.sheet_by_name(u'BACKLOG')
    # Get the names of each column from the zeroth row:
    colnames = sh.row_values(0)
    rows = []
    for rownum in range(1, sh.nrows):
        # Create a list of (colname, value) for each item in the row
        item = zip(colnames, sh.row_values(rownum))
        # and append this row to our list of rows.
        rows.append(item)
    return rows

Now we have a python list of lists of column name-value pairs, we need to convert the data we just read into mediawiki markup:

def second_level_section(heading, contents):
    return ("\n==%s==\n%s\n") % (heading, contents)

def first_level_section(headingprefix, headingval):
    return ("\n=%s: %s=\n\n") % (headingprefix, headingval)

# returns ((colname, colval), row_minus_named_column)
def remove_column(colname, row):
    row = copy.deepcopy(row)
    named_col = [(col_name, col_val) for (col_name, col_val) in row if col_name==colname]
    row.remove(named_col[0])
    row_minus_named_column = row
    return (named_col, row_minus_named_column)

def rows_to_mediawiki(rows):
    markup = ""
    for item in rows:
        # Special case the backlog story "Name" field - we want to seperate it from the other columns
        # so that it can be the title of this subsection
        (title,item) = remove_column("Name", item)
        markup = markup + first_level_section(title[0][0], title[0][1])
        # Every other column gets it's own sub column
        for col in item:
            markup = markup + second_level_section(col[0], col[1])
    return markup


Bringing it all together:

#! /usr/bin/env python
# Dependencies on ubuntu:
# easy_install:
#   sudo apt-get install python-setuptools
# xlutils pypi package:
#   sudo easy_install xlutils
# mwclient:
#   sudo easy_install mwclient
import sys
import xlrd
import copy

def read_backlog_items_from_workbook(excelfilename):
    wb = xlrd.open_workbook(excelfilename)
    sh = wb.sheet_by_name(u'BACKLOG')
    # Get the names of each column from the zeroth row:
    colnames = sh.row_values(0)
    rows = []
    for rownum in range(1, sh.nrows):
        # Create a list of (colname, value) for each item in the row
        item = zip(colnames, sh.row_values(rownum))
        # and append this row to our list of rows.
        rows.append(item)
    return rows

def second_level_section(heading, contents):
    return ("\n==%s==\n%s\n") % (heading, contents)

def first_level_section(headingprefix, headingval):
    return ("\n=%s: %s=\n\n") % (headingprefix, headingval)

# returns ((colname, colval), row_minus_named_column)
def remove_column(colname, row):
    row = copy.deepcopy(row)
    named_col = [(col_name, col_val) for (col_name, col_val) in row if col_name==colname]
    row.remove(named_col[0])
    row_minus_named_column = row
    return (named_col, row_minus_named_column)

def rows_to_mediawiki(rows):
    markup = ""
    for item in rows:
        # Special case the backlog story "Name" field - we want to seperate it from the other columns
        # so that it can be the title of this subsection
        (title,item) = remove_column("Name", item)
        markup = markup + first_level_section(title[0][0], title[0][1])
        # Every other column gets it's own sub column
        for col in item:
            markup = markup + second_level_section(col[0], col[1])
    return markup

def main(_args):
    filename = "/home/me/myproject/ProductBacklog.xls"
    rows = read_backlog_items_from_workbook(filename)
    markup = rows_to_mediawiki(rows)
    # Output wiki markup to stdout
    print markup

if __name__ == '__main__':
    main(sys.argv)

So, we now have a simple tool that can take an MS Excel spreadsheet  (on Linux, Mac or Windows) and convert it to mediawiki markup.  Once in mediawiki markup you can  can share it via a mediawiki wiki so that people with any http capable device on any platform are capable of viewing it  (no need to resort to file servers, sharepoint etc. etc.).  It's possible to automate the process and use this with the paste-to-wiki script we covered in an earlier post.

In Part 3, we'll see how to generate a pdf file from the mediawiki page  (useful for e-mailing to people so they can read it offline on any pdf-viewing device such as a laptop, a tablet, a smart phone or a kindle etc. etc.)

Later we'll see how to generate index cards.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s