Horizontal text alignment in openpyxl

Question

I'm trying to change the text alignment to the center of 2 merged cells. I've found some answers that didn't work for my case:

currentCell = ws.cell('A1')
currentCell.style.alignment.horizontal = 'center' #TypeError: cannot set horizontal attribute
#or
currentCell.style.alignment.vertical = Alignment.HORIZONTAL_CENTER #AttributeError: type object 'Alignment' has no attribute 'HORIZONTAL_CENTER'

both didn't work, is there any other way to do it?

Answer 1

yes, there is a way to do this with openpyxl:

from openpyxl.styles import Alignment

currentCell = ws.cell('A1') #or currentCell = ws['A1']
currentCell.alignment = Alignment(horizontal='center')

hope this will help you

Answer 2

This is what finally worked for me with the latest version from PIP (2.2.5)

    # center all cells
    for col in w_sheet.columns:
        for cell in col:
            # openpyxl styles aren't mutable,
            # so you have to create a copy of the style, modify the copy, then set it back
            alignment_obj = cell.alignment.copy(horizontal='center', vertical='center')
            cell.alignment = alignment_obj

Update:

As of openpyxl version 2.4.0 (~2016) the .copy() method is deprecated for StyleProxy objects.

Try changing the last two lines to:

from copy import copy
alignment_obj = copy(cell.alignment)
alignment_obj.horizontal = 'center'
alignment_obj.vertical = 'center'
cell.alignment = alignment_obj

Answer 3

None of the other solutions worked for me, since my solution requires openpyxl, and at least in 2.1.5 cell.alignment can't be set directly.

from openpyxl.styles import Style, Alignment

cell = ws.cell('A1')
cell.style = cell.style.copy(alignment=Alignment(horizontal='center')) 

The above copies the current style and replaces the alignment. You can also create a whole new style - with any values not specified taking the default values from https://openpyxl.readthedocs.org/en/latest/styles.html

cell.style = Style(alignment=Alignment(horizontal='center'),font=Font(bold=True))

# or - a tidier way

vals = {'alignment':Alignment(horizontal='center'),
        'font':Font(bold=True),
       }
new_style = Style(**vals)
cell.style = new_style

Answer 4

I found the following code to be a pretty simple way to format every cell in your worksheet:

tot_rows = ws.max_row #get max row number
tot_cols = ws.max_column #get max column number

cols = range(1,tot_cols) #turns previous variables into iterables
rows = range(1,tot_rows) 

for c in cols:
    for r in rows:
        ws.cell(row=r, column=c).alignment = Alignment(horizontal='center', vertical='center')

Answer 5

its my first time posting anything here. So i found a way to align text using openpyxl, Alignment

i=3
while i < 253:
    cellc = ws.cell(row=i, column= 3)
    cellc.alignment = Alignment(horizontal="right")
    i+=1

I set i to be the start point then the len of my column

Answer 6

You can achieve this by using Python XlsxWriter library.

import xlsxwriter

workbook = xlsxwriter.Workbook('example.xlsx')
worksheet = workbook.add_worksheet()

cell_format = workbook.add_format({'align': 'center'})

worksheet.merge_range('A1:B1', "")
worksheet.write_rich_string('A1','Example', cell_format)

workbook.close()

Here i have merged cells A1, B1 and added a cell format parameter which includes the align parameter assigned as center.