Pandas/Excel: Any way to encode the ALT-ENTER / CHAR(10) line break into data when calling DataFrame.to_excel()?

Question

I have a pandas DataFrame that I would like to write to Excel. For one column, I have data values that are comma-delimited strings, like "val1,val2" or "val1,val2,val3". When I write that column, I would like to replace the commas with the equivalent of pressing ALT-ENTER in Excel, so that there are line breaks between the values. So, my first example would display as val1, then a break within the cell, then val2, and so forth. You can also do this in Excel by making the cell a formula and putting &"char(10)"& between each value.

I see how I could do this by coding up formulas via XLSXWriter and writing cells individually. However I'm hopefully (or lazily) wondering whether there's a way to encode the breaks right into the data so that they would all come out via a simple call to to_excel() on the DataFrame.

Answer 1

In XlsxWriter you can use newlines in the string and the text_wrap format property to wrap it onto separate lines. See this section of the Format docs.

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

worksheet.write(0, 0, "Val1\nval2", wrap_format)

To do it from Pandas you could convert the commas in the strings to \n and then apply the text_wrap format to the column in the target spreadsheet.

Answer 2

I still had to do a bit of research after reading the answers here and therefore I wanted to post here a fully working example

df = pd.DataFrame({'Data': ['bla\nbla', 'blubb\nblubb']})
with pd.ExcelWriter('test.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='Sheet1')
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']
    cell_format = workbook.add_format({'text_wrap': True})
    worksheet.set_column('A:Z', cell_format=cell_format)

In particular, I had to figure out that after creating the format object I still need to set it on the respective cells.

Note: You need to pip install xlsxwriter before doing it.

Here, again, the link to the Format Class documenation