I find myself using Python for most of my programming tasks lately, and while I still predominately use Javascript as a web developer, I’ve been taking up any opportunity to use Python. Recently, I wrote a script where I fetched some useful data from an investor directory website, massaged the data, and finally output a nicely organized excel spreadsheet.

The code snippet below is a generic example of how you can create an Excel spreadsheet using Python by turning a list of objects into rows of data with headers.

Snippet

This snippet is also available on github here.

# python 3.10.3
from xlsxwriter import Workbook

def create_xlsx_file(file_path: str, headers: dict, items: list):
    with Workbook(file_path) as workbook:
        worksheet = workbook.add_worksheet()
        worksheet.write_row(row=0, col=0, data=headers.values())
        header_keys = list(headers.keys())
        for index, item in enumerate(items):
            row = map(lambda field_id: item.get(field_id, ''), header_keys)
            worksheet.write_row(row=index + 1, col=0, data=row)
            
xl_headers = {
    'name': 'Full Name',
    'email': 'Email',
    'title': 'Title',
}

list = [
  {'name': 'Ali', 'email': 'ali@email.com', 'title': 'CEO'},
  {'name': 'Richard', 'email': 'rich@email.com', 'title': 'CFO'},
  {'name': 'Karen', 'email': 'karen@email.com', 'title': 'COO'}
]

create_xlsx_file("my-xlsx-file.xlsx", xl_headers, list)