xlwt convenience methods

As I started using xlwt, I found myself wanting some more convenient methods for dumping tabular data into a worksheet, especially when all the data can be treated as strings.  Tabular data in this context is an iterable of iterables, such as a list of tuples.

Here’s what I’ve got so far:

"""Excel utilities.

'Tabular data' in this context is an iterable of iterables.
"""

import xlwt

def to_workbook(tabular_data, workbook=None, sheetname=None):
    """
    Returns the Excel workbook (creating a new workbook
    if necessary) with the tabular data written to a worksheet
    with the name passed in the 'sheetname' parameter (or a
    default value if sheetname is None or empty).
    """
    wb = workbook or xlwt.Workbook()
    ws = wb.add_sheet(sheetname or 'Data')
    to_worksheet(tabular_data, ws)
    return wb

def to_worksheet(tabular_data, worksheet):
    """
    Writes the tabular data to the worksheet (returns None).
    Thanks to John Machin for the tip on using enumerate().
    """
    for row_index, row_data in enumerate(tabular_data):
        worksheet_row = worksheet.row(row_index)
        for col_index, col_data in enumerate(row_data):
            worksheet_row.write(col_index, col_data)

In a Django context, then, you have a very straightforward way turning a query into an Excel file using the values_list() QuerySet method, e.g.:

wb = to_workbook(MyModel.objects.values_list())

Since values_list() outputs the attribute values for each object in the same order in which they’re defined in the model class, you can insert a row of headings to your table:

table = MyModel.objects.values_list()
headings = [f.name for f in MyModel._meta.fields]
table.insert(0, headings)
wb = to_workbook(table)
Advertisements

,