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:
company | city | category | income | number_of_employees |
Roses and Pansies | Toronto | gardening | 456845.00 | 26 |
Reader’s Paradise | Sydney | publisher | 854782.00 | 44 |
Dogs and Cats | Madrid | pets | 458784.00 | 36 |
Enjoy Your Meal | New Orleans | restaurant | 447884.00 | 18 |
Bloom Store | London | gardening | 1254654.00 | 84 |
Tastes of the South | Miami | restaurant | 587454.00 | 42 |
Pet For You | Athens | pets | 987887.00 | 21 |
Eat ‘Em Up | Helsinki | restaurant | 1254477.00 | 53 |
Mr. Bunny | Vienna | pets | 254478.00 | 12 |
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)
...
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.