Apply 'wrap_text' to all cells using openpyxl

Question

I have a Pandas dataframe that I am writing out to an XLSX using openpyxl. Many of the cells in the spreadsheet contain long sentences, and i want to set 'wrap_text' on all the contents of the sheet (i.e. every cell).

Is there a way to do this? I have seen openpyxl has an 'Alignment' option for 'wrap_text', but I cannot see how to apply this to all cells.

Edit:

Thanks to feedback, the following does the trick. Note - copy due to styles being immutable.

for row in ws.iter_rows():
    for cell in row:      
        cell.alignment =  cell.alignment.copy(wrapText=True)

Answer 1

I have been using openpyxl>=2.5.6. Let us say we want to wrap text for cell A1, then we can use the below code.

from openpyxl.styles import Alignment

ws['A1'].alignment = Alignment(wrap_text=True)

Answer 2

Presumably, when you iterate through your cells, the idea would be to apply the format at that.

for row in ws.iter_rows():
    for cell in row:
        cell.style.alignment.wrap_text=True

There is also a fair amount more detail into how to use the wrap text style here Writing multi-line strings into cells using openpyxl

Hope this helps.

Answer 3

import os
import openpyxl
from openpyxl.styles import Alignment, Font
from openpyxl.cell import Cell
#format cells with word wrap and top alignment    
for row in ws2.iter_rows():  
    for cell in row:      
        cell.alignment = Alignment(wrap_text=True,vertical='top') 

Answer 4

Update alignment in openpyxl v3

Many of the answers set wrapText=True but clobber existing alignment options. This is no good.

Using openpyxl v3.0.4, I did the following:

import copy

for row in ws.iter_rows():
    for cell in row:      
        alignment = copy.copy(cell.alignment)
        alignment.wrapText=True
        cell.alignment = alignment

The original poster's solution uses:

cell.alignment =  cell.alignment.copy(wrapText=True)

But this produced the following warning:

DeprecationWarning: Call to deprecated function copy (Use copy(obj) or cell.obj = cell.obj + other).
  cell.alignment =  cell.alignment.copy(wrapText=True)

Answer 5

Iterates all cells

  for rows in ws.iter_rows(min_row=1, min_col=1):
    for cell in rows:
      print('cell %s %s' % (cell.coordinate,cell.value))

* Tested with Python:3.4.2 - openpyxl:2.4.1 *