Data-entry template

Build a spreadsheet meant to be filled in and returned

When the spreadsheet is going out to be completed by someone else, validation does the work that an email of instructions otherwise would.

import polars as pl
import jetxl as jet

# Column headers plus one example row
template = pl.DataFrame({
    "Employee":   ["Jane Doe"],
    "Department": ["Sales"],
    "Hours":      [37.5],
    "Status":     ["Active"],
})

jet.write_sheet_arrow(
    template.to_arrow(),
    "timesheet.xlsx",
    sheet_name="Timesheet",

    header_content=[
        (1, 0, "Q1 Timesheet"),
        (2, 0, "Complete one row per employee and return by 31 March."),
    ],
    data_start_row=4,
    merge_cells=[(1, 0, 1, 3), (2, 0, 2, 3)],

    styled_headers=True,
    freeze_rows=4,
    auto_width=True,
    column_formats={"Hours": "decimal2"},

    data_validations=[
        {
            "start_row": 5, "start_col": 1,
            "end_row": 500, "end_col": 1,
            "type": "list",
            "items": ["Sales", "Engineering", "Support", "Operations"],
            "show_dropdown": True,
            "error_title": "Unknown department",
            "error_message": "Pick one of the four listed departments.",
        },
        {
            "start_row": 5, "start_col": 2,
            "end_row": 500, "end_col": 2,
            "type": "decimal",
            "min": 0.0, "max": 80.0,
            "error_title": "Hours out of range",
            "error_message": "Enter between 0 and 80 hours for the week.",
        },
        {
            "start_row": 5, "start_col": 0,
            "end_row": 500, "end_col": 0,
            "type": "text_length",
            "min": 2, "max": 60,
            "error_title": "Check the name",
            "error_message": "Enter the employee's full name, 2 to 60 characters.",
        },
    ],

    conditional_formats=[{
        "start_row": 5, "start_col": 2,
        "end_row": 500, "end_col": 2,
        "rule_type": "cell_value",
        "operator": "greater_than",
        "value": "40",
        "style": {
            "font": {"bold": True, "color": "FF0000"},
            "fill": {"pattern": "solid", "fg_color": "FFF2CC"},
        },
    }],
)

Verified against jetxl 0.3.1: the resulting workbook carries all three validation rules.

Design notes

Validate far past the data

Rules run to row 500 though the template ships with one row. They need to hold for every row the recipient adds.

Ship an example row

One filled row shows the expected format better than instructions. Say in the header whether to keep or overwrite it.

Flag rather than forbid

Overtime above 40 hours is highlighted, not blocked, because it’s legitimate but worth a second look. Reserve hard limits for genuinely invalid input.

Instructions in the file

The deadline sits in row 2, so it travels with the spreadsheet after the covering email is forgotten.

Validation guides typing and doesn’t enforce it. Pasted values can bypass it and any recipient can delete the rules, so re-validate on the way back in.