TableSheet

The TableSheet is a TemplatedWorksheet making it easy for reading and write sheets with excel Data Tables. It is made up of an ordered set of typed columns which support when converting to and from Excel. Read more about what the columns do here.

Elements of the TableSheet

The TableSheet recognizes the following elements:

  • Title (optional) - A bold header for the Data Table
  • Description (optional) - A smaller description intended for simple instructions
  • Columns - The Columns in the datatable, which in turn is made up of headers and rows
from itertools import repeat
from openpyxl_templates import TemplatedWorkbook
from openpyxl_templates.table_sheet import TableSheet
from openpyxl_templates.table_sheet.columns import CharColumn


class TableSheetElements(TableSheet):
    column1 = CharColumn(header="Header 1")
    column2 = CharColumn(header="Header 2")
    column3 = CharColumn(header="Header 3")
    column4 = CharColumn(header="Header 4")


class TableSheetElementsWorkook(TemplatedWorkbook):
    table_sheet_elements = TableSheetElements()


wb = TableSheetElementsWorkook()
wb.table_sheet_elements.write(
    title="Title",
    description="This is the description, it can be a couple of sentences long.",
    objects=(tuple(repeat("Row %d" % i, times=4)) for i in range(1, 4))
)
wb.save("table_sheet_elements.xlsx")
../_images/table_sheet_elements.png

The TableSheet does not support reading the title or description.

Creating the TableSheet

A TableSheet is created by extending the TableSheet class, declaring columns and optionally changing styling and other settings. Once the TableSheet class has been created an instance of this class is be supplied to the TemplatedWorkbook.

Declaring columns

The columns are declared as class variables on a TableSheet which will identify and register the columns (in order of declaration). The columns are avaliable under the columns attribute.

A TableSheet must always have atleast one TableColumn.

from openpyxl_templates.table_sheet import TableSheet
from openpyxl_templates.table_sheet.columns import CharColumn, IntColumn


class DemoTableSheet(TableSheet):
    column1 = CharColumn()
    column2 = IntColumn()

The column declaration supports inheritance, the following declaration is perfectly legal.

class ExtendedDemoTableSheet(DemoTableSheet):
    column3 = CharColumn()

Note that the columns of the parent class are always considered to have been declared before the columns of the child.

All columns must have a header and there must not be any duplicated headers within the same sheet. The TableSheet will automatically use the attribute name used when declaring the column as header.

class DemoTableSheet(TableSheet):
    column1 = CharColumn(header="Header 1")
    column2 = IntColumn()  # The header of column2 will be set automatically to "column2"

An instance of a column should never be used on multiple sheets.

TODO: Dynamic columns

TODO: Describe the ability to pass additional columns to __init__ via columns=[...]

Writing

Simple usage

Writing is done by an iterable of objects to the write function and optionally a title and/or description. The write function will then:

  • Prepare the workbook by registering all required styles, data validation etc.
  • Write title, and description if they are supplied
  • Create the headers and rows
  • Apply sheet level formatting such as creating the Data Table and setting the freeze pane

Writing will always recreate the entire sheet from scratch, so any preexisting data will be lost. If you want to preserve your data you could read existing rows and combine them with the new data.

class DemoTableSheet(TableSheet):
    column1 = CharColumn()
    column2 = IntColumn()


class DemoTemplatedWorksheet(TemplatedWorkbook):
    demo_sheet1 = DemoTableSheet()
    demo_sheet2 = DemoTableSheet()

wb = DemoTemplatedWorksheet()

wb.demo_sheet1.write(
    objects=(
        ("Row 1", 1),
        ("Row 2", 2),
        ("Row 3", 3),
    ),
    title="The first sheet"
)
wb.demo_sheet2.write(
    objects=(
        ("Row 1", 1),
        ("Row 2", 2),
        ("Row 3", 3),
    ),
    title="The second sheet",
    description="Lorem ipsum dolor sit amet, consectetur adipiscing elit. In euismod, sem eu."
)
wb.save("read_write.xlsx")

Using objects

The write accepts rows iterable containing tuples or list as in the example above. If an other type is encountered the columns will try to get the attribute directly from the object using getattr(object, column.object_attribute). The object_attribute can be defined explicitly and will default to the attribute name used when adding the column to the sheet.

class DemoObject():
    def __init__(self, column1, column2):
        self.column1 = column1
        self.column2 = column2


wb = DemoTemplatedWorksheet()
wb.demo_sheet1.write(
    objects=(
        DemoObject("Row 1", 1),
        DemoObject("Row 2", 2),
        DemoObject("Row 3", 3),
    )
)

Styling

The TableSheet has two style attributes:

  • tile_style - Name of the style to be used for the title, defaults to “Title”
  • description_style - Name of the style to be used for the description, defaults to “Description”
class PrettyDemoSheet(TableSheet):
    def __init__(self):
        super().__init__(
            title_style="Bold & red, title",
            description_style="Extra tiny, description"
        )

    column1 = CharColumn()
    column2 = IntColumn()

Styling of columns done on the columns themselves.

Make sure that the styles referenced are available either in the workbook or in the StyleSet of the TemplatedWorkbook. Read more about styling styling.

Additional settings

The write behaviour of the TableSheet can be modified with the following settings:
  • format_as_table - Controlling whether the TableSheet will format the output as a DataTable, defaults to True
  • freeze_pane - Controlling whether the TableSheet will utilize the freeze pane feature, defaults to True
  • hide_excess_columns - When enabled the TableSheet will hide all columns not used by columns, defaults to True

Reading

Simple usage

The read method does two things. First it will verify the format of the file by looking for the header row. If the headers cannot be found a en exception will be raised. Once the headers has been found all subsequent rows in the excel will be treated as data and parsed to namedtuples automatically after the columns has transformed the data from excel to python.

wb = DemoTemplatedWorksheet("read_write.xlsx")
for row in wb.demo_sheet1.read():
    print(row)

Iterate directly

The TableSheet can also be used as an iterator directly

for row in wb.demo_sheet2:
    print(row)

Exception handling

The way the TableSheet handles exceptions can be configured by setting the exception_policy. It can be set on the TableSheet class or passed as an argument to the read function. The following policies are avaliable:
  • RaiseCellException (default) - All exceptions will be raised when encountered
  • RaiseRowException - Cell level exceptions such as type errors, in the same row will be collected and raised as a RowException
  • RaiseSheetException - All row exceptions will be collected and raised once reading has finished. So that all valid rows will be read, and all exceptions will be recorded.
  • IgnoreRow - Invalid rows will be ignored

The policy only applies to exceptions occuring when reading rows. Exceptions such as HeadersNotFound will be raised irregardless.

Reading without looking for headers

Looking for headers can be disabled by setting look_for_headers to False or passing it as a named argument directly to the read function. When this is done the TableSheet will start looking for valid rows at once. This will most likely cause an exception if the title, description or header row is present since they will be treated as rows.

Customization

The TableSheet is built with customization in mind. If you want your table to yield something else then a namedtuple for each row. It is easy to achieve by overriding the create_object method.

class IceCream:
    def __init__(self, name, description, flavour, color, price):
        self.name = name
        self.description = description
        self.flavour = flavour
        self.color = color
        self.price = price


class IceCreamSheet(TableSheet):
    name = CharColumn()
    description = TextColumn(width=32)
    flavour = CharColumn()
    color = CharColumn()
    price = FloatColumn()

    def create_object(self, row_number, **data):
        return IceCream(**data)

Feel free to explore the source code for additional possibilities. If you are missing hook or add a feature useful for others, feel free to submit a push request.