使用自动列宽、列自动过滤器等将 CSV/DataFrame 保存到 Excel。

下面的功能允许您自动设置列“自动过滤器”，使列宽适应数据宽度或列名宽度，以及“冻结”顶部行和/或左侧列：

from pathlib import Path
import pandas as pd


def df_to_excel_auto_fmt(
        df,
        fn,
        max_col_width=30,
        autofilter=True,
        freeze_panes=(1, 0),
        fmt_int="#,##0",
        fmt_float="#,##0.00", 
        fmt_date="yyyy-mm-dd",
        fmt_datetime="yyyy-mm-dd hh:mm:ss",
        **kwargs):
    """
    Export / save Pandas.DataFrame to an Excel file with automatically adjusted column's widths.
    It can also add Excel column "autofilters" and freeze panes (top rows and left columns).
    Cell values will be formatted according to "fmt_*" parameters.

    :param df: DataFrame to be exported to Excel
    :param fn: output Excelfile name. NOTE: the extension will be changed to ".xlsx"
    :param max_col_width: maximum column width in Excel. Default: 30
    :param autofilter: boolean - whether add Excel autofilter or not. Default: True
    :param freeze_panes: tuple of int (length 2).
                         Specifies the one-based bottommost row and rightmost column
                         that is to be frozen.
    :param fmt_int: Excel format for integer numbers
    :param fmt_float: Excel format for float numbers
    :param fmt_date: Excel format for dates
    :param fmt_datetime: Excel format for datetime's
    :param kwargs: additional arguments to pass to df.to_excel(filename, **kwargs)
    :return:  None
    """
    file = Path(fn).with_suffix(".xlsx")
    # get default parameters
    first_col = int(kwargs.get("index", True))
    sheet_name = kwargs.get("sheet_name", "Sheet1")
    if "freeze_panes" not in kwargs:
        kwargs["freeze_panes"] = freeze_panes
    writer = pd.ExcelWriter(
        file.with_suffix(".xlsx"), 
        engine="xlsxwriter",
        date_format=fmt_date,
        datetime_format=fmt_datetime)
    df.to_excel(writer, sheet_name=sheet_name, **kwargs)
    workbook = writer.book
    worksheet = writer.sheets[sheet_name]
    int_fmt = workbook.add_format({'num_format': fmt_int})
    float_fmt = workbook.add_format({'num_format': fmt_float})
    for xl_col_no, dtyp in enumerate(df.dtypes, first_col):
        col_no = xl_col_no - first_col
        width = max(df.iloc[:, col_no].astype(str).str.len().max(), 
                    len(df.columns[col_no]) + 6)
        width = min(max_col_width, width)
        # print(f"column: [{df.columns[col_no]}]\twidth:\t[{width}]")
        if np.issubdtype(dtyp, np.integer):
            worksheet.set_column(xl_col_no, xl_col_no, width, int_fmt)
        elif np.issubdtype(dtyp, np.floating):
            worksheet.set_column(xl_col_no, xl_col_no, width, float_fmt)
        else:
            worksheet.set_column(xl_col_no, xl_col_no, width)
    if autofilter:
        worksheet.autofilter(0, 0, 0, df.shape[1] + first_col)
    writer.save()
    writer.close()

使用示例：

fn = r"c:\download\file.csv"
df = pd.read_csv(fn, sep=";")
df_to_excel_auto_fmt(
    df,
    fn,
    max_col_width=30,
    fmt_datetime="dd.mm.yy hh:mm",
    index=False)

下次更新模块后，基于的版本XlsxWriter停止工作，所以我决定使用以下方法重写相同的函数openpyxl：

def df_to_excel_auto_fmt(
        df,
        fn,
        max_col_width=30,
        autofilter=True,
        freeze_panes=(1, 0),
        fmt_int="#,##0",
        fmt_float="#,##0.00",
        fmt_date="yyyy-mm-dd",
        fmt_datetime="yyyy-mm-dd hh:mm:ss",
        **kwargs):
    """
    Export / save Pandas.DataFrame to an Excel file with automatically adjusted column's widths.
    It can also add Excel column "autofilters" and freeze panes (top rows and left columns).
    Cell values will be formatted according to "fmt_*" parameters.

    :param df: DataFrame to be exported to Excel
    :param fn: output Excelfile name. NOTE: the extension will be changed to ".xlsx"
    :param max_col_width: maximum column width in Excel. Default: 30
    :param autofilter: boolean - whether add Excel autofilter or not. Default: True
    :param freeze_panes: tuple of int (length 2).
                         Specifies the one-based bottommost row and rightmost column
                         that is to be frozen.
    :param fmt_int: Excel format for integer numbers
    :param fmt_float: Excel format for float numbers
    :param fmt_date: Excel format for dates
    :param fmt_datetime: Excel format for datetime's
    :param kwargs: additional arguments to pass to df.to_excel(filename, **kwargs)
    :return:  None

    (c) https://ru.stackoverflow.com/users/211923/maxu?tab=profile
    """
    from openpyxl.utils import get_column_letter

    def set_column_format(ws, column_letter, fmt):
        for cell in ws[column_letter]:
            cell.number_format = fmt
    file = Path(fn).with_suffix(".xlsx")
    # get default parameters
    first_col = int(kwargs.get("index", True)) + 1
    sheet_name = kwargs.get("sheet_name", "Sheet1")
    if "freeze_panes" not in kwargs:
        kwargs["freeze_panes"] = freeze_panes
    writer = pd.ExcelWriter(
        file.with_suffix(".xlsx"),
        engine="openpyxl",
        date_format=fmt_date,
        datetime_format=fmt_datetime)
    df.to_excel(writer, sheet_name=sheet_name, **kwargs)
    # workbook = writer.book
    worksheet = writer.sheets[sheet_name]
    for xl_col_no, dtyp in enumerate(df.dtypes, first_col):
        col_no = xl_col_no - first_col
        width = max(df.iloc[:, col_no].astype(str).str.len().max(),
                    len(df.columns[col_no]) + 6)
        width = min(max_col_width, width)
        # print(f"column: [{df.columns[col_no]} ({dtyp.name})]\twidth:\t[{width}]")
        column_letter = get_column_letter(xl_col_no)
        worksheet.column_dimensions[column_letter].width = width
        if np.issubdtype(dtyp, np.integer):
            set_column_format(worksheet, column_letter, fmt_int)
        if np.issubdtype(dtyp, np.floating):
            set_column_format(worksheet, column_letter, fmt_float)
    if autofilter:
        worksheet.auto_filter.ref = worksheet.dimensions
    writer.save()
    writer.close()

使用示例：

fn = r"c:\download\file.csv"
df = pd.read_csv(fn, sep=";")
df_to_excel_auto_fmt(
    df,
    fn,
    max_col_width=30,
    fmt_datetime="dd.mm.yy hh:mm",
    index=False)