8

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.

sparc_spread
  • 10,643
  • 11
  • 45
  • 59
  • 2
    In XlsxWriter you can use newlines in the string and the `text_wrap` format property to wrap it onto separate lines. [Like this](http://xlsxwriter.readthedocs.io/format.html#set_text_wrap). 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. – jmcnamara Dec 19 '16 at 22:56
  • That absolutely worked! Care to post as an answer so I can upvote and accept? – sparc_spread Dec 19 '16 at 23:08

3 Answers3

5

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.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • Would it be possible to post a complete working code. From the Dataframe with long strings to the export of the file. I am not very familiar with XlsxWriter. Thank you very much. – valenzio Jun 18 '18 at 08:24
  • You will need to open a new question with some sample working code. – jmcnamara Jun 18 '18 at 10:32
  • 1
    Done. Here is the link https://stackoverflow.com/questions/50908676/python-how-to-create-multi-line-cells-in-excel-when-exporting-a-pandas-datafram – valenzio Jun 18 '18 at 11:39
3

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

Konstantin
  • 2,451
  • 1
  • 24
  • 26
0

for me this sort of code worked perfectly:

 liste=['adsf','asdfas','dasdsas','asdfsdaf']
 text=''

 for elem in liste:              
     text=text+elem+'\n'
 ws.Cells(1,1).Value=text
marcin
  • 1