CODEWITHSUNDEEP
pythonexcelxlsxwriter
Hassan Mehmood
Hassan Mehmood

Reputation: 1402

xlsxwriter: text_wrap() not working

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.

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

Upvotes: 2

Views: 10946

Answers (6)

Fane Zeng
Fane Zeng

Reputation: 1

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

Upvotes: 0

rhoerbe
rhoerbe

Reputation: 593

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

Upvotes: 1

James Apple
James Apple

Reputation: 236

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:

enter image description here

Upvotes: 6

Jason
Jason

Reputation: 85

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()

enter image description here

Upvotes: 1

jmcnamara
jmcnamara

Reputation: 41524

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: enter image description here

Upvotes: 1

Jason
Jason

Reputation: 85

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.

Upvotes: 1

Related Questions