xlsxwriter: text_wrap() not working

Question

I'm creating and formatting excel files and want to wrap the text in column. I found the method in xlsxwriter which is text_wrap() but when I use this method, Its not working for me. I tried everything but failed.

What I'm doing in my script.

• reading a csv using pandas
• creating and saving dataframe into excel file
• applying text wrap over it.

Script

text_format = workbook.add_format({'text_wrap': True})
worksheet.conditional_format('A1:W{}'.format(len(df)), {'type': 'no_errors',
                                      'format': text_format})

Any help will be appreciated.

Thanks

Answer 1

As noted in the XlsxWriter docs, DataFrame formatting cannot be overwritten using set_row(). The easiest way to change the formatting of a DataFrame header is to overwrite the header with individual cell entries, overwriting the value and the format of each cell:

import pandas as pd
import xlsxwriter

df = pd.DataFrame({'this is a very long header that should wrap': ['a2', 'a3', 'a4'],
                   'header2': [1, 2, 3],
                   'header3': [4, 5, 6]})

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

workbook  = writer.book
worksheet = writer.sheets['Sheet1']
header_format = workbook.add_format({'bold': True, 'text_wrap': True, 'valign': 'top'})

# Overwrite both the value and the format of each header cell
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value, header_format)

workbook.close()

The code above will produce the following: