Python: How to create multi line cells in excel when exporting a pandas dataframe

Question

I have the following pandas Dataframe

df = pd.DataFrame([
    [['First Line', 'Second line']],
    [['First line', 'second line', 'third line']],
    [['first line']]
])

I am trying to export it into an Excel file. However I would like that between each list-element a line break is entered, similar to ALT-ENTER in Excel. So at the end, the excel would look like this:

Thank you

Answer 1

First you'll need to make sure to have a single string with '\n' as a separator instead of the list:

df = pd.DataFrame([
        ['First Line\nSecond line'],
        ['First line\nsecond line\nthird line'],
        ['first line']
    ])

You can then call to_excel like you normally do, then open the file with openpyxl and set the cells' .style.alignment.wrap_text property to True like the answer for this question suggests.

Alternatively you can wrap df with a StyleFrame object (full disclosure: I'm the author of this module, pip install styleframe to get it) that does it for you (wrap_text=True is used as default):

from styleframe import StyleFrame

df = pd.DataFrame([
        ['First Line\nSecond line'],
        ['First line\nsecond line\nthird line'],
        ['first line']
    ])

StyleFrame(df).to_excel('test.xlsx').save()

Answer 2

Here is a solution using Format.set_text_wrap() from xlsxwriter:

df = pd.DataFrame([
    ['First Line\nSecond line'],
    ['First line\nsecond line\nthird line'],
    ['first line']
])

with pd.ExcelWriter('file.xlsx', engine="xlsxwriter") as writer:
    writer.book.formats[0].set_text_wrap()  # update global format with text wrap
    df.to_excel(writer)