python-3.x - 将 pandas 数据框导出到 Excel 并设置列宽和文本换行

Question

我必须将此数据框导出到 Excel

    import pandas as pd
    import xlsxwriter
    df1 = pd.DataFrame([['a', 'b'], ['c', 'd']],
                    index=['row 1', 'row 2'],
                    columns=['col 1', 'col 2'])

    df1.to_excel(os.path.join('tmp', "output1.xlsx"))  # doctest: +SKIP
    df2 = df1.copy()
    with pd.ExcelWriter(os.path.join('tmp', "output2.xlsx"), engine='xlsxwriter') as writer:  # doctest: +SKIP
        df1.to_excel(writer, sheet_name='Sheet_name_1')
        df2.to_excel(writer, sheet_name='Sheet_name_2')

我知道“xlsxwriter”允许多种自定义。如何设置列宽和文字换行，以上面的代码为草稿？

标签： python-3.xpandasxlsxwriter

Answer 1

正如@jmcnamara 建议的那样，我在官方文档中找到了答案：

    import pandas as pd

    # Create a Pandas dataframe from some data.
    df = pd.DataFrame({'Numbers':    [1010, 2020, 3030, 2020, 1515, 3030, 4545],
                    'Percentage': [.1,   .2,   .33,  .25,  .5,   .75,  .45 ],
    })

    # Create a Pandas Excel writer using XlsxWriter as the engine.
    writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')

    # Convert the dataframe to an XlsxWriter Excel object.
    df.to_excel(writer, sheet_name='Sheet1')

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

    # Add some cell formats.
    format1 = workbook.add_format({'num_format': '#,##0.00'})
    format2 = workbook.add_format({'num_format': '0%'})

    # Note: It isn't possible to format any cells that already have a format such
    # as the index or headers or any cells that contain dates or datetimes.

    # Set the column width and format.
    worksheet.set_column('B:B', 18, format1)

    # Set the format but not the column width.
    worksheet.set_column('C:C', None, format2)

上面的代码取自https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html#ex-pandas-column-formats。