openpyxl Part 6 – Converting Spreadsheet Data into a Class

In the previous part of the openpyxl series we manually created a workbook with some data in it and then converted it into a dictionary. Today we’ll see how to convert it into class, which is quite a common scenario too. Here’s the wb2.xlsx workbook again:

To keep things simple, let’s work in interactive mode again. We’ll just create one class, Company. As this class is going to contain just data, let’s make it a data class. Data classes were introduced in Python 3.7, so if you’re using an older version of Python, the regular Python classes will do as well.

First of all let’s create the class:

>>> from dataclasses import dataclass
>>> @dataclass
... class Company:
...     name: str
...     city: str
...     category: str
...     income: float
...     number_of_employees: int
...

As you can see, we specify the data types in data classes. With the class in place, let’s import the workbook:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")
>>> sheet = workbook.active

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

The Conversion

Now we’re ready to convert the spreadsheet data into instances of the Company class. First, let’s define an empty list where we will append the instances:

>>> companies = []

And now let’s create the instances in a loop. Each row (except for the headers row, naturally) will be represented as an object of the Company class:

>>> for row in sheet.iter_rows(min_row=2, values_only=True):
...     company = Company(name=row[0],
...                       city=row[1],
...                       category=row[2],
...                       income=row[3],
...                       number_of_employees=row[4])
...     companies.append(company)
... 

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

Now we have a list of Company objects. Let’s check out the third company:

>>> print(companies[2])
Company(name='Dogs and Cats', city='Madrid', category='pets', income=458784, number_of_employees=36)

And now let’s check the city where the last company has its headquarters:

>>> print(companies[-1].city)
Vienna

Now that we know how to read data from a workbook and convert it to other data types, like a dictionary or a class, let’s talk about creating spreadsheets and adding all sorts of elements to them in code. In the next part we’ll be adding and updating data in our wb2 workbook.

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.