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)