TemplatedWorksheet¶
The TemplatedWorksheet describes a a sheet in an excel file and is the bare bone used for building useful sheet templates such as the TableSheet. A TemplatedWorksheet is defined by following attributes:
- It’s
sheetname
which is used for identifying the sheets in the excel file- It’s
read()
method which when implemented should return the relevant data contained in the sheet- It’s
write(data)
method which when implemented should write the provided data to the sheet and make sure that it is properly formatted
The TemplatedWorksheet will handle managing the openpyxl worksheet so you do not have to worry about whether the sheet is created or not before you start writing.
To create a TemplatedWorksheet you should implement the read and write method. We’ll demonstrate this by creating a DictSheet a TemplatedWorksheet which reads and writes simple key value pairs in a python dict.
class DictSheet(TemplatedWorksheet):
def write(self, data):
worksheet = self.worksheet
for item in data.items():
worksheet.append(list(item))
def read(self):
worksheet = self.worksheet
data = {}
for row in worksheet.rows:
data[row[0].value] = row[1].value
return data
We can now add our DictSheet to a TemplatedWorkbook and use it to create an excel file.
class DictWorkbook(TemplatedWorkbook):
dict_sheet = DictSheet(sheetname="dict_sheet")
workbook = DictWorkbook()
workbook.dict_sheet.write({
"key1": "value1",
"key2": "value2",
"key3": "value3",
})
workbook.save("key_value_pairs.xlsx")
We can use the same TemplatedWorkbook to read the data from the file we just created.
workbook = DictWorkbook(join(dirname(__file__), "key_value_pairs.xlsx"))
print(workbook.dict_sheet.read())
Have a look at the TableSheet for a more advanced example. It includes type handling and plenty of styling.