Python Write to XLSX

We have seen how to write a CSV file with Python after parsing an SQLite database. Now let’s cut out the middle man (CSV) and directly generate a .xlsx spreadsheet. Sadly, but understandably, Python doesn’t support reading/writing Excel spreadsheet files natively. We are going to have to rely on external libraries.

To name a few external libraries that support Excel spreadsheets:

• openpyxl
• XlsxWriter
• pandas
• pylightxl

This post is going to focus on the openpyxl library.

Basics to OpenPyxl

First we need to install the library. Open a terminal to your project folder and run:

pip install openpyxl

And like always, let’s jump straight into the code:

import datetime
from openpyxl import Workbook

def main():
    wb = Workbook()
    # grab the active worksheet
    ws = wb.active
    # Data can be assigned directly to cells
    ws['A1'] = 42
    ws['A2'] = datetime.datetime.now()
    # Rows can also be appended with a list
    ws.append([1, 2, 3])
    # Save the file
    wb.save("Exported Data.xlsx")

if __name__ == "__main__":
    main()

But we don’t want to be manually adding values one by one, so let’s bulk input data.

Lists to Xlsx

As we’ve seen in previous posts, you would typically parse out a large list of data, and want to save it into a readable format, so let’s see how that would look with the following sample code:

import random
import string
from openpyxl import Workbook


def main():

    # Populate generic data list.
    # list of [index, random number, random string of text] elements.
    data = []
    for i in range(1, 100):
        data.append([
            i,
            random.randint(1024, 65_536),
            ''.join(random.choice(string.ascii_lowercase) for _ in range(random.randint(6, 12)))
        ])

    wb = Workbook()
    ws = wb.active

    # Add a header
    ws.append(['#', 'Free Memory (MB)', 'Node ID'])

    # Add our generated data.
    for entry in data:
        ws.append(entry)

    # Save the file.
    wb.save("Data output.xlsx")


if __name__ == "__main__":
    main()

Don’t get too tied up in the data generation part, it’s purely an example. But I do encourage to try this out on your own machine. It will generate the following file:

Remarks

If you plan on installing external libraries, consider setting up a Python virtual environment to manage the dependency versions. This is out of the scope of this post, but to get you started, check out Python’s VEnv Tutorial.