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

DeepSpace · Accepted Answer · 2020-12-10T19:30:36.380

14

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

edited Dec 10 '20 at 19:30

answered Jun 18 '18 at 11:52

DeepSpace

78,697
11
109
154

looks good! Can you just tell me how to add the index from the pandas Dataframe to the excel, since `index=True` does not work – valenzio Jun 18 '18 at 12:23
1

@valenzio `StyleFrame(df).to_excel('test.xlsx', index=True).save()` does work for me. – DeepSpace Jun 18 '18 at 12:46
So I have a 2500 x 4000 pandas dataframe, if I try to export, it my Ram usages explodes (over 8GB). For small dataframes it works fine. Am I doing something wrong? – valenzio Jun 19 '18 at 07:03
1

@valenzio I suppose some things aren't as efficient as they could be. Thanks for letting me know, I'll have a look at that. – DeepSpace Jun 19 '18 at 15:03
That would be awesome, I will look into your first Option as well, but it is a bit tedeious since I never worked with openpyxl – valenzio Jun 20 '18 at 13:39
I turned my Dataframe to StyleFrame in the way suggested, but it throws AttributeError("'Series' object has no attribute 'style'") error. My simplified code: def save_file(results: DataFrame): StyleFrame(results).to_excel("path_to_file").save() Am I doing something wrong? – Lohi Jul 15 '21 at 14:28

score 1 · Answer 2 · answered Apr 18 '22 at 08:42

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)

score 0 · Answer 3 · answered Nov 15 '22 at 15:10

0

nowadays you may just use set_text_wrap() method using ExcelWriter!

cell_format = workbook.add_format()
cell_format.set_text_wrap()

worksheet.write(0, 0, "Some long text to wrap in a cell", cell_format)

answered Nov 15 '22 at 15:10

mikecarti

3
2

But where do you define where the actual line break is? – valenzio Feb 22 '23 at 13:44

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

3 Answers3

Linked