in openpyxl, how to set the font to 'centred'?

in openpyxl, how to set the font to 'centred'? - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: in openpyxl, how to set the font to 'centred'? (/thread-12853.html)

in openpyxl, how to set the font to 'centred'? - Pedroski55 - Sep-16-2018

I have the following code to set the font in my excel table using openpyxl:

ft1 = Font(name='Arial', size=20)

for sheet in targetFileSheetNames:
    targetFileActiveSheet = targetFile[sheet]
    maxRow = targetFileActiveSheet.max_row
    maxCol = targetFileActiveSheet.max_column
    for colNum in range(2, maxCol + 1, 1):
        for rowNum in range(1, maxRow + 1):
            targetFileActiveSheet.cell(row=rowNum, column=colNum).font = ft1

I would also like to have the text in the middle of the cell. I can't see how to set that.

Could someone please give me a tip? (I know that centre in American is 'center' Big Grin

RE: in openpyxl, how to set the font to 'centred'? - pcsailor - Sep-16-2018

Try this:

rows = range(1, 44)
columns = range(1, 10)
for row in rows:
    for col in columns:
        sheet.cell(row, col).alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

Phil

RE: in openpyxl, how to set the font to 'centred'? - Pedroski55 - Sep-16-2018

Thanks for that, almost there, but something is not right:

Quote:Traceback (most recent call last):
File "./setTheFontv1.py", line 52, in <module>
targetFileActiveSheet.cell(row=rowNum, column=colNum).alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
NameError: name 'Alignment' is not defined

Maybe I need to load some other module? I have:

import os, openpyxl
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

Got it! I needed

from openpyxl.styles import Alignment

then

alignment = Alignment(horizontal='center',
                          vertical='bottom',
                          text_rotation=0,
                          wrap_text=False,
                          shrink_to_fit=True,
                          indent=0)

and in the for-loop

targetFileActiveSheet.cell(row=rowNum, column=colNum).alignment = alignment

RE: in openpyxl, how to set the font to 'centred'? - pcsailor - Sep-17-2018

awesome!