Save a list of Pandas DataFrames to a multi-sheet Excel workbook.

save_excel.py

import pandas as pd

def save_excel(
    data: list[pd.DataFrame],
    filename: str,
    sheet_names: list[str],
    *,
    freeze_top_row: bool,
    auto_fit_columns: bool,
    sort_by: Optional[Union[str, list[str]]] = None,
) -> None:
    """Save a list of Pandas DataFrames to a multi-sheet Excel workbook.

    Args:
        data: List of Pandas DataFrames to save.
        filename: Full path and file name to save spreadsheet to.
        sheet_names: List of sheet names for each element in ``data`` list.
            Must be the same length as ``data``.
        freeze_top_row: Whether to freeze the top row of each sheet.
        auto_fit_columns: Whether to fit column widths to contents.
        sort_by: One or more names of columns to sort each DataFrame by.

    Returns:
        None.

    Raises:
        ImportError: If required module ``xlsxwriter`` is not installed.
    """
    try:
        import xlsxwriter
    except ImportError:
        raise ImportError("Required module 'xlsxwriter' is not installed.")
    
    with pd.ExcelWriter(filename, engine="xlsxwriter") as writer:
        for index, df in enumerate(data):

            sheet_name = sheet_names[index]
            params = {'index': False, 'sheet_name': sheet_name}

            if freeze_top_row:
                params["freeze_panes"] = (1, 0)

            if sort_by:
                df.sort_values(by=sort_by, inplace=True)

            df.to_excel(writer, **params)

            # https://stackoverflow.com/a/61617835/21865432
            if auto_fit_columns:
                for column in df:
                    column_length = max(df[column].astype(str).map(len).max(), len(column))
                    col_idx = df.columns.get_loc(column)
                    writer.sheets[sheet_name].set_column(col_idx, col_idx, column_length)