Skip to content
Home » openpyxl Part 6 – Converting Spreadsheet Data into a Class

openpyxl Part 6 – Converting Spreadsheet Data into a Class

Spread the love

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:

companycitycategoryincomenumber_of_employees
Roses and PansiesTorontogardening456845.0026
Reader’s ParadiseSydneypublisher854782.0044
Dogs and CatsMadridpets458784.0036
Enjoy Your MealNew Orleansrestaurant447884.0018
Bloom StoreLondongardening1254654.0084
Tastes of the SouthMiamirestaurant587454.0042
Pet For YouAthenspets987887.0021
Eat ‘Em UpHelsinkirestaurant1254477.0053
Mr. BunnyViennapets254478.0012

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

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.


Spread the love

Leave a Reply