how to autowrap the text in excel when export from pandas dataframe styler

auto_textwrap_in_pandas_styler.md

The styler gives us a easy way to format the dataframe and maintain the format across different kind of output: html, excel, etc.

However, one of my friends has spot a problem of it, the styler.to_excel() output some how supressed some advanced formating of XlsxWriter. Here is an example:

import pandas as pd
data = pd.DataFrame({"A":list(range(10,12)), "B":["abcdefgafafadf", "afbafafafafadfadfadf"]})
with pd.ExcelWriter("/tmp/test.xlsx") as writer:
    data.style.apply(lambda x: ['background-color: red']*2 if x['A']==11 else [None]*2, axis=1).to_excel(writer, index=False)
    writer.sheets["Sheet1"].write(3, 1, "Some long text to wrap in a cell")
    wrap_format = writer.book.add_format({'text_wrap': True})
    writer.sheets["Sheet1"].set_column('A:B', 5, wrap_format)

After opening the file in excel. We can see the first 2 rows does not have the text_wrap feature. But the rest of the rows has it.

According to XlsxWriter authors, the reason is that In XlsxWriter generated files a cell format overrides a column format. The Pandas styler adds a cell format so the column format has no effect.

So if we are using the pandas styler, we should basically say good-bye to those XlsxWriter tricks. It maybe also good. Because if we do tricks to only excels, we end up non-uniform looks in other output formats. However, the html output is already enabled the text-wrap by default. And I searched into the pandas documentation. I cannot find anything about the text wrap in styler. After digging into the code I found a function _get_is_wrap_text. In there the default is no text wrapping. And the value seems depends on "white-space" key in CSS style. From here, it looks like the white-space: normal; will be the correct props.

The following code works fine:

import pandas as pd
data = pd.DataFrame({"A":list(range(10,12)), "B":["abcdefgafafadf", "afbafafafafadfadfadf"]})
with pd.ExcelWriter("/tmp/test.xlsx") as writer:
    data.style.apply(lambda x: ['background-color: red; white-space: normal;']*2 if x['A']==11 else ['white-space: normal;']*2, axis=1).to_excel(writer, index=False)
    writer.sheets["Sheet1"].set_column('A:B', 5, None)

I try to get rid of the set_column() but failed. I cannot find anything related to the column width in the styler. It looks like the _XlsxStyler class. only contains per cell format. The column width is considered per column as col_info in XlsxWriter. Thus currently there is no way to control the column widths with pandas styler yet.