Freezing Panes Filters and Column Widths From Code

Open an export and you can tell in two seconds whether someone cared. A frozen header, filters on every column and widths that fit the content cost five lines of code and make the file feel finished. They are also the settings people forget, because the code works without them. This post gives the calls for both libraries, a helper that computes widths, and the print settings that complete the job. It is a spoke of sending database results to Excel.

Freeze the header row

When a sheet scrolls, the header should stay visible. Frozen panes lock rows above and columns to the left of a chosen cell. In openpyxl, you name the first cell that is allowed to scroll:

ws.freeze_panes = "A2"      # freeze row 1
ws.freeze_panes = "B2"      # freeze row 1 and column A
ws.freeze_panes = None      # remove the freeze

A2 means everything above row 2 and left of column A is frozen, which is just row 1. B2 freezes row 1 and column A, useful when the first column is an identifier that should remain visible when a reader scrolls right. In XlsxWriter the call takes a zero-based row and column:

ws.freeze_panes(1, 0)       # freeze the first row
ws.freeze_panes(1, 1)       # first row and first column

If the data has a title above the header, freeze below the header, not at row 1. A frozen area that takes half the screen is worse than none, so keep it to the header row and at most one or two columns. On narrow screens, a wide frozen column pushes everything else out of view.

Add filter buttons

An auto filter puts the drop-down arrows on the header cells so readers can sort and filter. The range should cover the header and all data rows:

ws.auto_filter.ref = f"A1:{ws.cell(row=1, column=ws.max_column).column_letter}{ws.max_row}"

In openpyxl that builds a reference such as A1:E843 from the sheet's current extent. ws.dimensions returns the used range as a string and works when the data starts in A1: ws.auto_filter.ref = ws.dimensions. In XlsxWriter:

ws.autofilter(0, 0, len(df), len(df.columns) - 1)

The arguments are the first row, first column, last row and last column, zero-based, including the header row. A filter that stops short of the last row leaves the remaining rows out of sorting, and a reader who sorts then scrambles the data, with the unsorted rows staying below. Compute the end of the range from the data's length, not from a typed number.

Do not add an auto filter to a range that is also an Excel table: the table already has filter buttons, and Excel reports an error or repairs the file. One or the other.

Set column widths

Widths are measured in characters of the workbook's default font, roughly the number of digit characters that fit. In openpyxl:

ws.column_dimensions["A"].width = 14
ws.column_dimensions["B"].width = 32

In XlsxWriter, set_column takes a column range, the width and an optional format:

ws.set_column("A:A", 14)
ws.set_column("B:B", 32)
ws.set_column(2, 4, 12)         # columns C to E by number

The maximum width is 255 characters. A column too narrow for a number or date shows #### instead of the value, and a column too narrow for text truncates the display when the next cell is not empty. Neither damages the data, and both look broken, which is why widths matter more than they should.

Rather than guessing, compute widths from the data:

def widths(df, minimum=8, maximum=50, padding=2):
    out = {}
    for i, col in enumerate(df.columns):
        longest = max([len(str(col))] + [len(str(v)) for v in df[col].head(1000)])
        out[i] = max(minimum, min(longest + padding, maximum))
    return out

# for the sample frame
print(widths(df))

{0: 10, 1: 9, 2: 10, 3: 8}

The helper measures the header and up to 1,000 values per column, adds a margin and clamps between a minimum and a maximum. Sampling the first 1,000 rows keeps it fast on large frames, at the risk of missing a longer value further down; raise the sample or scan the whole column when that matters. Dates and floats print longer than their text suggests, so measure what Excel will show: a date with yyyy-mm-dd hh:mm needs 16 characters. Then apply:

for i, w in widths(df).items():
    ws.set_column(i, i, w)

XlsxWriter offers ws.autofit(), which estimates widths from the written data, and it is the shortest route when approximate fits are acceptable. It must be called after the data is written, and it works from the cell contents without knowing the fonts, so very long text can still be cut off. openpyxl has no auto-fit; you compute widths as above.

For text columns with long content, set a maximum width and turn on wrapping instead of stretching the column across the screen. In XlsxWriter, create a format with {"text_wrap": True} and apply it to the column; rows then grow in height to show the wrapped text when Excel recalculates row heights on open.

Make printing work

Many reports are printed or turned into PDF. Two settings pay off. Repeat the header on every printed page, and fit the width to one page:

# openpyxl
ws.print_title_rows = "1:1"
ws.page_setup.orientation = "landscape"
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0
ws.sheet_properties.pageSetUpPr.fitToPage = True

# XlsxWriter
ws.repeat_rows(0)
ws.set_landscape()
ws.fit_to_pages(1, 0)

fitToHeight = 0 and fit_to_pages(1, 0) mean "one page wide, as many pages tall as needed". Without them, a wide table prints its last columns on separate pages. Setting the paper size (ws.set_paper(9) for A4 in XlsxWriter) avoids the default of Letter on a machine configured for another region. A header and footer with the page number and the report date are a nice finish: in XlsxWriter, ws.set_footer("&CPage &P of &N").

Other small settings worth a line

Hide gridlines on a presentation sheet with ws.hide_gridlines(2) in XlsxWriter, or ws.sheet_view.showGridLines = False in openpyxl. Keep them on data sheets, where the grid helps reading.

Zoom with ws.set_zoom(90) or ws.sheet_view.zoomScale = 90, if the sheet is wide.

Select the first cell and make the right sheet active with ws.activate(), so the workbook opens where you want. Excel remembers the active sheet that was saved.

Row height for the header row with ws.set_row(0, 24) in XlsxWriter, or ws.row_dimensions[1].height = 24 in openpyxl.

Data validation and protection are separate topics; do not enable sheet protection without a plan, since a protected sheet frustrates the filtering you just enabled unless you allow it explicitly.

Resist applying all of them. Four or five choices that serve the reader beat a dozen decorations.

A complete example

With pandas and XlsxWriter, the whole set for one sheet is:

with pd.ExcelWriter("orders.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer, sheet_name="Orders", index=False)
    ws = writer.sheets["Orders"]
    ws.freeze_panes(1, 0)
    ws.autofilter(0, 0, len(df), len(df.columns) - 1)
    for i, w in widths(df).items():
        ws.set_column(i, i, w)
    ws.repeat_rows(0)
    ws.set_landscape()
    ws.fit_to_pages(1, 0)
    ws.set_paper(9)

Ten lines, and the sheet behaves like something a person made on purpose. Wrap the block in a function and call it for every sheet in a multi-sheet report (building a multi-sheet Excel report from SQL), so the standards live in one place.

When code-based layout settings are not worth the trouble

The consumer is a program. A loader ignores panes, filters and widths. Skip them, and keep the file minimal.

The file is a template with its own layout. Fill it with openpyxl and leave the layout as the designer made it. Overwriting their freeze and widths undoes their work (editing an existing workbook without losing formatting).

The workbook is huge. Computing widths from every value on a million-row sheet is slow. Sample, or set fixed widths per column from the schema.

People will paste the data elsewhere. If the destination is another system, formatting is wasted effort.

The calls are documented in the XlsxWriter pages for worksheet methods and in openpyxl's worksheet properties. To check a small extract as JSON, use the CSV to JSON converter.