Pandas to_excel with a table

to_excel_table.py

# from https://xlsxwriter.readthedocs.io/example_pandas_table.html

df.index.name = "org_id"
criteria = df["Include"]=="y"
sheets = {
    "Included - active": (criteria & df["active"]),
    "Included - inactive": (criteria & ~df["active"]),
    "Excluded": ~criteria,
}
column_widths = df.apply(lambda x: x.astype(str).str.len().max(), axis=0).apply(lambda x: min(x, 50)).to_dict()

with pd.ExcelWriter("output.xlsx") as writer:
    for sheet_name, mask in sheets.items():
        to_add = df[mask].reset_index()
        to_add.to_excel(writer, sheet_name=sheet_name, startrow=1, header=False, index=False)

        # Get the xlsxwriter workbook and worksheet objects.
        workbook = writer.book
        worksheet = writer.sheets[sheet_name]

        # Get the dimensions of the dataframe.
        (max_row, max_col) = to_add.shape

        # Create a list of column headers, to use in add_table().
        column_settings = [{'header': column} for column in to_add.columns]

        # Add the Excel table structure. Pandas will add the data.
        worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

        # Make the columns wider for clarity.
        for col_index, column in enumerate(to_add.columns):
            worksheet.set_column(col_index, col_index, column_widths.get(column, 12))

to_excel_table_function.py

def to_excel_table(df, writer, sheet_name, column_widths=None, max_col_width=50, startrow=0, do_column_widths=True, **kwargs):
    df.to_excel(writer, sheet_name=sheet_name, startrow=startrow+1, header=False, index=False, **kwargs)

    if column_widths is None:
        column_widths = (
            df.apply(lambda x: x.astype(str).str.len().max(), axis=0)
            .apply(lambda x: min(x, max_col_width))
            .to_dict()
        )

    # Get the xlsxwriter workbook and worksheet objects.
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]

    # Get the dimensions of the dataframe.
    (max_row, max_col) = df.shape
    max_row = startrow + max_row

    # Create a list of column headers, to use in add_table().
    column_settings = [{"header": column} for column in df.columns]

    # Add the Excel table structure. Pandas will add the data.
    worksheet.add_table(startrow, 0, max_row, max_col - 1, {"columns": column_settings})

    # Make the columns wider for clarity.
    if do_column_widths:
        for col_index, column in enumerate(df.columns):
            worksheet.set_column(
                col_index, col_index, min(max(column_widths.get(column, 12), len(column)), max_col_width)
            )