Writing multi-line strings into cells using openpyxl

Question

I'm trying to write data into a cell, which has multiple line breaks (I believe \n), the resulting .xlsx has line breaks removed. Is there a way to keep these line breaks?

Answer 1

The API for styles changed for openpyxl >= 2. The following code demonstrates the modern API.

from openpyxl import Workbook from openpyxl.styles import Alignment  wb = Workbook() ws = wb.active # wb.active returns a Worksheet object ws['A1'] = "Line 1\nLine 2\nLine 3" ws['A1'].alignment = Alignment(wrapText=True) wb.save("wrap.xlsx") 

Answer 2

Disclaimer: This won't work in recent versions of Openpyxl. See other answers.

In openpyxl you can set the wrap_text alignment property to wrap multi-line strings:

from openpyxl import Workbook  workbook = Workbook() worksheet = workbook.worksheets[0] worksheet.title = "Sheet1"  worksheet.cell('A1').style.alignment.wrap_text = True worksheet.cell('A1').value = "Line 1\nLine 2\nLine 3"  workbook.save('wrap_text1.xlsx') 

This is also possible with the XlsxWriter module.

Here is a small working example:

from xlsxwriter.workbook import Workbook  # Create an new Excel file and add a worksheet. workbook = Workbook('wrap_text2.xlsx') worksheet = workbook.add_worksheet()  # Widen the first column to make the text clearer. worksheet.set_column('A:A', 20)  # Add a cell format with text wrap on. cell_format = workbook.add_format({'text_wrap': True})  # Write a wrapped string to a cell. worksheet.write('A1', "Line 1\nLine 2\nLine 3", cell_format)  workbook.close()