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