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:

Answer 2

I have spent hours troubleshooting this same problem. I have tried the following:

header_format = workbook.add_format({'bold': True, 'text_wrap': True, 'valign': 'top'})

And setting it through its own statement:

header_format.set_text_wrap()

And adding a boolean to the statement:

header_format.set_text_wrap(True)

I thought maybe it was because I was using conditional formatting:

worksheet.conditional_format(0, 0, 0, 21, {'type': 'no_blanks', 'format': header_format})

So I tried it without:

worksheet.set_row(0, 45, testing_format)

I thought that maybe because it was coming before the statement setting column widths below it that perhaps it needed to be at the very end (even though no other format settings exhibit this behavior). That didn't work.

I tried to use one formatting with only text_wrap set and no other formatting and used in no other places. That didn't work.

I tried it with it being the only formatting set for the entire worksheet. That didn't work

I tried updating XlsxWririter to 1.3.7. That didn't work.

At this point I can say with 100% confidence that text wrapping does not work in XlsxWriter 1.3.7 with Python 3.7.4, and Excel for Microsoft 365 MSO 64-bit.

Answer 3

The reason this doesn't work is that Excel doesn't support text wrapping in a conditional format. From the XlsxWriter docs:

Note: In Excel, a conditional format is superimposed over the existing cell format and not all cell format properties can be modified. Properties that cannot be modified in a conditional format are font name, font size, superscript and subscript, diagonal borders, all alignment properties and all protection properties.

Outside of conditional formatting it should work as expected:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

my_format = workbook.add_format({'text_wrap': True})

worksheet.write(0, 0, "hello world, how are you", my_format)

workbook.close()

Output:

Answer 4

jmcnamara's example did work, however it will not work for data coming from a pandas dataframe. You can see it does the bold and the valign, but the wrap doesn't work. I also didn't have problem with conditional formatting (as long as it wasn't from a dataframe).

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, "Sheet1", index=False)
worksheet = writer.sheets['Sheet1']

workbook = writer.book

header_format = workbook.add_format({'bold': True, 'text_wrap': True, 'valign': 'top'})


worksheet.set_column(0, 0, 10)
worksheet.write(0, 3, "hello world, how are you")
worksheet.set_row(0, 70, header_format)

workbook.close()

Answer 5

When I changed from workbook.add_format().set_text_wrap()to workbook.add_format({'text_wrap': True})it worked as expected

Answer 6

can not modify the type due to the format of dataframe format prohibition, you can clean the dataframe format in advance, then apply the cell format by set column or set row.

pandas.io.formats.excel.ExcelFormatter.header_style = None