Export pandas dataframe to Excel and setting columns width and text wrapping

Question

I have to export this dataframe to 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')

I know that 'xlsxwriter' allows multiple customizations. How to set column width and text wrapping, taking the above code as the draft?

Answer 1

I found the answer in official documentation as @jmcnamara suggested:

    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)

The code above is taken from https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html#ex-pandas-column-formats.