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.