Convert list of dictionaries to EXCEL with Python

Question

I have a list of dictionaries:

data= [{'week': 'null', 'releasename': 'r2.0', 'tssubmiton': None, 'outstate': None,  'substate': 'null', 'recomputedon': datetime.datetime(2014, 6, 10, 0, 0), 'submittedon': datetime.datetime(2013, 6, 26, 11, 30, 9), u'state': 'new', u'month': '1306', u'item': 'null', 'engineeringaction': 'tofix'}, 
{'week': 'null', 'releasename': 'r2.0', 'tssubmiton': None, 'outstate': None, 'substate': 'null', 'recomputedon': datetime.datetime(2014, 6, 10, 0, 0), 'submittedon': datetime.datetime(2012, 10, 24, 8, 37, 48), 'state': 'new', 'month': '1301', 'item': 'null', u'engineeringaction': 'null'}]

It's much longer but this is just an example. How can I convert this list of dicts to an Excel sheet. I don't want to convert it to CSV but directly to Excel.

I use xlwt library.

I've tried this :

wb = xlwt.Workbook()
# Creating new worksheet with the name specified
ws = wb.add_sheet(sheetName)
# Use dictionary keys as first row values(e.g. headers)
for colIdx, headerCaption in enumerate(data):
    ws.write(0, colIdx, headerCaption)
    # Use dict values as row values for corresponding columns
    for rowIdx, itemVal in enumerate(data[headerCaption]):
        ws.write(rowIdx + 1, colIdx, itemVal)
wb.save(fileName)

But I don't know how to loop on the list of dicts and put each dict in a new row...

Answer 1

This should do the trick:

date_format = xlwt.easyxf(num_format_str='dd/mm/yyyy')
time_format = xlwt.easyxf(num_format_str='hh:mm')
datetime_format = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm')

def format(value):
    if value is None:
        return ("null",)
    if isinstance(value, datetime.datetime):
        return value, datetime_format
    if isinstance(value, datetime.date):
        return value, date_format
    if isinstance(value, datetime.time):
        return value, time_format
    return (value,)

# Create workbook
wb = xlwt.Workbook()
ws = wb.add_sheet("Sheet 1")

# Write header
headers = data[0].keys()
for column, header in enumerate(headers):
    ws.write(0, column, header)

# Write data
for row, row_data in enumerate(data, start=1):
    for column, key in enumerate(headers):
        ws.write(row, column, *format(row_data[key]))

# Save file
wb.save("test.xls")

You can augment the format(...) function to handle any other formats you require.