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...

score 0 · Answer 1 · answered Jul 04 '14 at 09:27

0

I would use the first item of your list to write the titles:

for col,title in enumerate(data[0].keys()):
    ws.write(0,col,title)

And then fill in the values:

row = 0
col = 0
for d in data:
    for value in d.values():
        ws.write(row,col,value)
        col += 1
    col = 0
    row += 1

answered Jul 04 '14 at 09:27

Sanjii

1
3

The issue with this approach, is that there is no guarantee that the values will come out in the same order each time, or in the same order as the headers. (Though they will if all the dictionaries contain exactly the same set of keys, and the keys were all added in the same order every time, and the dictionaries have not been modified at all, see the documentation for [`dict`](https://docs.python.org/2/library/stdtypes.html#dict.items)) – Jamie Cockburn Jul 04 '14 at 09:58

Jamie Cockburn · Accepted Answer · 2014-07-04T13:05:29.660

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.

thanks this is working. But do you have any ideas on how I can put the datetime values in the correct format on the Excel? — salamey, Jul 04 '14 at 11:50

Convert list of dictionaries to EXCEL with Python

2 Answers2