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?

See the docs on Working with Python Pandas and XlsxWriter and in particular the section on Formatting of the Dataframe headers — jmcnamara
– jmcnamara, Commented May 16, 2019 at 11:46

Alexander Borochkin · Accepted Answer · 2019-05-16 17:11:46Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related