Python openpyxl: Column Width, Row Height, and Freeze Panes

Python openpyxl column width row height and freeze panes are the three layout controls you need to make generated Excel files readable. When you build a workbook programmatically, the default dimensions often truncate content or leave too much whitespace. This article shows how to set each property, explains the units involved, and covers the practical pitfalls that appear when you open the file in Excel.

Setting Column Widths in openpyxl

In openpyxl, column width is controlled via the column_dimensions collection on the worksheet. Each column is identified by its letter, and you set the width attribute. The width is measured in characters of the default font size, not pixels. For example, to set column A to 20 characters:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.column_dimensions['A'].width = 20

You can also set multiple columns in a loop. The width value must be a number between 0 and 255, as per Excel's limit. Setting it to None restores the default width. If you need to hide a column, you can set its width to 0, though this is rarely needed in generated reports.

Setting Row Heights in openpyxl

Row heights are managed through row_dimensions. Each row is identified by its integer index. The height attribute is measured in points. To set row 1 to 30 points:

ws.row_dimensions[1].height = 30

If you leave the height unset, Excel uses the default height based on the font. openpyxl does not automatically calculate the height needed for wrapped text; you must set it manually if you want extra space. Setting height to None resets the row to the default height.

Freezing Panes in openpyxl

Freeze panes keep certain rows and columns visible while scrolling. The freeze_panes attribute takes a string that specifies the cell above and to the left of which the split occurs. For example, "A2" freezes row 1, "B1" freezes column A, and "B2" freezes both row 1 and column A.

ws.freeze_panes = "B2"

This is useful for header rows and label columns. The split is applied when the file is opened in Excel; some viewers may ignore it. To unfreeze, set freeze_panes to "A1" or None.

Units and Measurement: How openpyxl Interprets Width and Height

Understanding the units prevents surprises. Column width is in characters of the default font (usually Calibri 11). Row height is in points (1/72 inch). Excel's default column width is 8.43 characters, and default row height is 15 points. These values are stored in the worksheet XML and openpyxl reads and writes them directly.

If you need to convert pixels to points or characters, you must account for the font size and DPI. There is no built-in conversion in openpyxl. For most reports, you can estimate widths based on the longest string and a character width factor.

Combining Layout Settings for a Readable Report

For a typical report, you might set the title row height, freeze the header, and adjust column widths to fit content. Here's a practical example:

ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 25
ws.row_dimensions[1].height = 25
ws.freeze_panes = "A2"

This sets a wider column for descriptions, a taller header row, and freezes the header row. You can iterate over columns and rows to apply consistent settings:

for col in ['A', 'B', 'C']:
    ws.column_dimensions[col].width = 15
for row in range(1, 5):
    ws.row_dimensions[row].height = 20

When you need to apply the same width to many columns, a loop is cleaner than repeating assignments. Remember that openpyxl does not auto-fit columns or rows; you must determine the appropriate values based on your content.

Performance and Memory Considerations

Setting column widths and row heights individually is efficient for small worksheets. For large sheets, accessing column_dimensions and row_dimensions repeatedly can incur overhead because each access creates a ColumnDimension or RowDimension object if it doesn't exist. To improve performance, you can batch updates using a loop that sets multiple dimensions at once, but the overhead is generally acceptable unless you are processing thousands of rows.

Freeze panes is a single attribute assignment and has no performance impact. The main cost is in the XML serialization when saving, which is unavoidable. If you are generating many worksheets, consider reusing a template or applying layout settings only to sheets that need them.

Common Pitfalls and Compatibility Notes

One common mistake is setting row height to a value that Excel overrides with auto-fit. If the row's customHeight attribute is not set, Excel may recalculate height based on content. openpyxl does not set customHeight automatically; you must set it to True if you want your height to persist. For example:

ws.row_dimensions[1].customHeight = True
ws.row_dimensions[1].height = 30

Similarly, column widths are respected unless the column is set to auto-fit by Excel. openpyxl does not support auto-fit calculations, so you must manually determine widths.

Freeze panes can be tricky with merged cells. If you freeze at a cell that is part of a merged range, Excel may behave unpredictably. Also, freeze panes only work in the worksheet view; they do not affect the data itself.

Compatibility: The freeze_panes attribute is supported in openpyxl 2.4 and later. Older versions may not have it. Always test the generated file in your target Excel version. When you set customHeight, ensure you also set the height; otherwise Excel may ignore the flag. For column widths, there is no equivalent customWidth flag; the width is always used as specified.