38

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?

user1514631
  • 1,183
  • 1
  • 9
  • 14

4 Answers4

76

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")
DowntownDev
  • 842
  • 10
  • 15
Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
  • 2
    Yes, the api has been changed and your code works correctly. Thank you! – alones Jan 12 '19 at 19:56
  • For those who prefer snake case: `wrap_text` is also valid as per the [docs](https://openpyxl.readthedocs.io/en/latest/api/openpyxl.styles.alignment.html#openpyxl.styles.alignment.Alignment.wrap_text) – johnson Apr 11 '23 at 10:15
  • The snake case stuff may be removed over time. – Charlie Clark Apr 11 '23 at 14:30
43

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

enter image description here

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()
Eldamir
  • 9,888
  • 6
  • 52
  • 73
jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • thanks for your help. Do you know by any chance a good documentation for python openpyxl with examples? – user1514631 Mar 14 '13 at 10:10
  • 2
    The latest OpenPyXL docs are [here](https://openpyxl.readthedocs.org/en/latest/index.html). However, I figured out the syntax above by reading the code (and by having a general idea about what I was looking for). XlsxWriter has detailed [docs](https://xlsxwriter.readthedocs.org/en/latest/contents.html) and [examples](https://xlsxwriter.readthedocs.org/en/latest/examples.html). – jmcnamara Mar 14 '13 at 11:56
  • I get duplication on both XlsxWriter and openpyxl modules. (E.g. - When I write "line1 \n line2" I get "line1 \n line2 line1 \n line2 " – unresolved_external Aug 01 '16 at 15:09
  • unresolved_external: The screenshot shows that the example generates the correct output. If you have a different or new issue start a new StackOverflow question with a small working example that demonstrates it. – jmcnamara Aug 01 '16 at 15:16
  • I do this but the output in the cell is still just a single string with "\n" in it instead of a string split across multiple lines. Any idea why? – Elliptica Sep 27 '19 at 23:08
  • 1
    This answer is outdated. Won't work in recent versions. See other answer – Eldamir Nov 29 '19 at 07:22
5

Just an additional option, you can use text blocking """ my cell info here """ along with the text wrap Boolean in alignment and get the desired result as well.

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb= Workbook()
sheet= wb.active
sheet.title = "Sheet1"

sheet['A1'] = """Line 1
Line 2
Line 3"""

sheet['A1'].alignment = Alignment(wrapText=True)

wb.save('wrap_text1.xlsx')
Manuel
  • 534
  • 3
  • 9
Steven Barnard
  • 514
  • 7
  • 12
2

Just in case anyone is looking for an example where we iterate over all cells to apply wrapping:

Small working example:

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Alignment
from openpyxl.utils.dataframe import dataframe_to_rows

# create a toy dataframe. Our goal is to replace commas (',') with line breaks and have Excel rendering \n as line breaks.
df = pd.DataFrame(data=[["Mark", "Student,26 y.o"],
                        ["Simon", "Student,31 y.o"]], 
                  columns=['Name', 'Description'])

# replace comma "," with '\n' in all cells
df = df.applymap(lambda v: v.replace(',', '\n') if isinstance(v, str) else v)

# Create an empty openpyxl Workbook. We will populate it by iteratively adding the dataframe's rows.
wb = Workbook()
ws = wb.active # to get the actual Worksheet object

# dataframe_to_rows allows to iterate over a dataframe with an interface
# compatible with openpyxl. Each df row will be added to the worksheet.
for r in dataframe_to_rows(df3, index=True, header=True):
    ws.append(r)

# iterate over each row and row's cells and apply text wrapping.
for row in ws:
  for cell in row:
    cell.alignment = Alignment(wrapText=True)

# export the workbook as an excel file.
wb.save("wrap.xlsx")