How to freeze the top row and the first column using XlsxWriter?

Question

I am exporting a pandas DataFrame to Excel, and since it contains a lot of rows and columns, it would be useful to keep the top row and the first column when browsing its contents.

There is a feature present in Excel that allows for freezing the top row and the first column. Is accessible through XlsxWriter when exporting DataFrames to excel?

Opheliaophelie

Answer 1

You can use worksheet.freeze_panes() to achieve this . There are many options for that method. Read http://xlsxwriter.readthedocs.io/worksheet.html#worksheet-freeze-panes to know how to use the method.

For a quick breakdown, .freeze_panes has two mandatory parameters and two optional ones:
freeze_panes(row, col[, top_row, left_col])

These can be expressed in various ways:

worksheet.freeze_panes(1, 0)  # Freeze the first row.
worksheet.freeze_panes('A2')  # Same using A1 notation.
worksheet.freeze_panes(0, 1)  # Freeze the first column.
worksheet.freeze_panes('B1')  # Same using A1 notation.
worksheet.freeze_panes(1, 2)  # Freeze first row and first 2 columns.
worksheet.freeze_panes('C2')  # Same using A1 notation.

To quote the documentation for the optional parameters:

The parameters top_row and left_col are optional. They are used to specify the top-most or left-most visible row or column in the scrolling region of the panes. For example to freeze the first row and to have the scrolling region begin at row twenty:

worksheet.freeze_panes(1, 0, 20, 0)

Lewin

Thanks! I tried the suggested worksheet.freeze_panes(1, 1) and it worked. – Endowment

Answer 2

For those who would like to freeze the top row and/or column when exporting a pandas DataFrame to Excel, without interfacing with the underlying engine, to_excel() provides a way to do so via freeze_panes keyword argument.

import pandas as pd
df = pd.DataFrame({"Data": [10, 20, 30, 20, 15, 30, 45]})
df.to_excel("pandas_simple.xlsx", freeze_panes=(1, 1))

Mazurka