Python List of Dictionaries to XLS

Question

How may I optimize the following function to write a xls file from a list of dictionaries using the openpyxl library in Python?

The current logic behind feels very very wrong

The dict list:

things = [
    {
        "Fruit": "Orange",
        "Flavour": "Good",
        "Expiration": "21May20"
    },
    {
        "Fruit": "Apple",
        "Flavour": "Good",
        "Expiration": "19May20"
    },
    {
        "Fruit": "Banana",
        "Flavour": "Regular",
        "Expiration": "16May20"
    }
]

The functions that I have:

from openpyxl import Workbook, load_workbook

def create_xls(filepath):
    wb = Workbook()
    wb.save(filepath)

def write_xls(filepath, dictionary):
    wb = load_workbook(filepath)
    sheet = wb.active

    headers = [x for x in dictionary[0]]

    for index, value in enumerate(headers):
        sheet.cell(row=1, column=index+1).value = value

    for i, x in enumerate(dictionary):
        for idx,value in enumerate(x.values()):
            sheet.cell(row=i+2, column=idx+1).value = value

    wb.save(filepath)

Thank you!

Answer 1

From the documentation, you can see that a worksheet object has an .append method that let you write a row from an iterable at the bottom of said sheet. Documentation from the builtin help is reproduced here:

Help on method append in module openpyxl.worksheet.worksheet:

append(iterable) method of openpyxl.worksheet.worksheet.Worksheet instance
    Appends a group of values at the bottom of the current sheet.

    * If it's a list: all values are added in order, starting from the first column
    * If it's a dict: values are assigned to the columns indicated by the keys (numbers or letters)

    :param iterable: list, range or generator, or dict containing values to append
    :type iterable: list|tuple|range|generator or dict

    Usage:

    * append(['This is A1', 'This is B1', 'This is C1'])
    * **or** append({'A' : 'This is A1', 'C' : 'This is C1'})
    * **or** append({1 : 'This is A1', 3 : 'This is C1'})

    :raise: TypeError when iterable is neither a list/tuple nor a dict

This means that your can sheet.append(headers) instead of your ugly loop. Similarly, using .values() on your dictionnaries, you can simplify your write_xls function to:

def write_xls(filepath, dictionary):
    wb = load_workbook(filepath)
    sheet = wb.active

    headers = list(dictionary[0])
    sheet.append(headers)

    for x in dictionary:
        sheet.append(list(x.values()))

    wb.save(filepath)

---

Now, a few more things to consider.

First off, since you are only interested in creating the file and writing in it, you may be interested in the write-only mode provided by openpyxl. This mean you will simplify your code to a single function:

def write_xls(filepath, dictionary):
    wb = Workbook(write_only=True)
    sheet = wb.create_sheet()

    headers = list(dictionary[0])
    sheet.append(headers)

    for x in dictionary:
        sheet.append(list(x.values()))

    wb.save(filepath)

Second, you relly very much on your data being presented well ordered and without flaws. This might bite you at some point. I would:

1. find all possible headers in your dictionnaries and order them;
2. use them to recreate each row using the same ordering each time.

This will allow you to have a coherent output, even with inputs such as:

things = [
    {
        "Fruit": "Orange",
        "Flavour": "Good",
        "Expiration": "21May20",
    },
    {
        "Flavour": "Good",
        "Fruit": "Apple",
        "Expiration": "19May20",
    },
    {
        "Flavour": "Regular",
        "Expiration": "16May20",
        "Fruit": "Banana",
    }
]

or even:

things = [
    {
        "Fruit": "Orange",
        "Flavour": "Good",
        "Expiration": "21May20"
    },
    {
        "Fruit": "Apple",
        "Flavour": "Good",
        "Junk": "Spam",
        "Expiration": "19May20"
    },
    {
        "Fruit": "Banana",
        "Flavour": "Regular",
        "Expiration": "16May20"
    }
]

---

Proposed improvements:

import itertools

from openpyxl import Workbook


def write_xls(filename, data):
    wb = Workbook(write_only=True)
    ws = wb.create_sheet()

    headers = list(set(itertools.chain.from_iterable(data)))
    ws.append(headers)

    for elements in data:
        ws.append([elements.get(h) for h in headers])

    wb.save(filename)