Quick start¶
Creating your template¶
The first we create our TemplatedWorkbook, which describes the structure of our file using TemplatedWorksheets. This template can then be used for both creating new files or reading existing ones. Below is an example using the TableSheet (a TemplatedWorksheet) to describe a excel file of people and their favorite fruits.
from datetime import date
from enum import Enum
from openpyxl_templates import TemplatedWorkbook
from openpyxl_templates.table_sheet import TableSheet
from openpyxl_templates.table_sheet.columns import CharColumn, ChoiceColumn, DateColumn
class Fruits(Enum):
apple = 1
banana = 2
orange = 3
class PersonSheet(TableSheet):
first_name = CharColumn()
last_name = CharColumn()
date_of_birth = DateColumn()
favorite_fruit = ChoiceColumn(choices=(
(Fruits.apple, "Apple"),
(Fruits.banana, "Banana"),
(Fruits.orange, "Orange"),
))
class PersonsWorkbook(TemplatedWorkbook):
persons = PersonSheet()
Writing¶
To write create an instance of your templated workbook, supply data to the sheets and save to a file.
wb = PersonsWorkbook()
wb.persons.write(
title="List of fruit lovers",
objects=(
("John", "Doe", date(year=1992, month=7, day=17), Fruits.banana),
("Jane", "Doe", date(year=1986, month=3, day=2), Fruits.apple),
)
)
The TableSheet in this case will handle all formatting and produce the following sheet.
Reading¶
To utilize the openpyxl-templates to read from an existing excel file, initialize your TemplatedWorkbook with a file (or a path to a file). Using the read method or simply itterating over a sheet will give you access to the data as namedtupels.
wb = PersonsWorkbook("fruit_lovers.xlsx")
for person in wb.persons:
print(person.first_name, person.last_name, person.favorite_fruit)