Skip to content

Instantly share code, notes, and snippets.

@braindevices
Created February 14, 2023 21:47
Show Gist options
  • Save braindevices/f7a5f27bdb8f7513620a1ee48f630b03 to your computer and use it in GitHub Desktop.
Save braindevices/f7a5f27bdb8f7513620a1ee48f630b03 to your computer and use it in GitHub Desktop.
how to autowrap the text in excel when export from pandas dataframe styler

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment