How to wrap text of an entire column in xlsxwriter

Question

Hi i would like to wrap a text of an entire column specifically as it is too long. How do i achieve that.

This is the output that I get:

This is the code which i have wrote:

df2 = pd.DataFrame([config[s] for s in config.sections()])
df2 = df2[["control_name", "rationale"]]

print(df2)
# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header.
df2.to_excel(writer, sheet_name='For PDF', startrow=0, index=False, header=False)

df2.columns = ["Control Name", "Rationale"]

# Create xlsxwriter workbook object .
workbook = writer.book
# Create xlsxwriter worksheet object
worksheet = writer.sheets['For PDF']

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

# Add a header format.
header_format = workbook.add_format({
    'bold': True,
    'text_wrap': True,
    'valign': 'center',
    'fg_color': '#D7E4BC',
    'border': 1})

# Write the column headers with the defined spacing format.
for col_num, value in enumerate(df2.columns.values):
     worksheet.write(0, col_num, value, header_format)

for column in df2:
    column_width = max(df2[column].astype(str).map(len).max(), len(column)+1)
    col_idx = df2.columns.get_loc(column)
    writer.sheets['For PDF'].set_column(col_idx, col_idx, column_width)

 writer.save()

This is the output that i want to achieve:

Please help to achieve both the desired columns as shown above

Answer 1

To set the text wrap format for the entire column you need to add the format object to a set_column() call. Something like this:

writer.sheets['For PDF'].set_column(col_idx, col_idx, column_width, text_format)

Set the column_width to None if you don't want to specify the column width. Also, your current code seems to be auto fitting the column widths. Having very wide columns, to the width of the string, will mean that the text won't/won't need to wrap.

Update: added example.

import pandas as pd


# Create a Pandas dataframe from some data.
df2 = pd.DataFrame({'Control name': ['foo', 'bar', 'baz'],
                    'Rationale': [
                        'some long sentence ' * 4,
                        'some longer sentence ' * 6,
                        'hello ' * 10]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_wrap.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df2.to_excel(writer, sheet_name='For PDF')


# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['For PDF']

# Add a format.
text_format = workbook.add_format({'text_wrap' : True})

# Resize columns for clarity and add formatting to column C.
worksheet.set_column(1, 1, 20)
worksheet.set_column(2, 2, 20, text_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output: