How to Align Excel Text by openpyxl

How to align Excel text using openpyxl library

The alignment of Excel cells, rows, or columns can be set by using openpyxl.styles.Alignment class.

Following options are available to set alignment:

• horizontal
• indent
• justifyLastLine
• readingOrder
• relativeIndent
• shrinkToFit
• shrink_to_fit
• textRotation
• text_rotation
• vertical
• wrapText
• wrap_text

Each of these options has different values that you may set to align text in an Excel sheet. For example, horizontal has the following values that you may set:

• center
• left
• general
• justify
• centerContinuous
• right
• fill
• distributed

The examples below show using various options.

An example of using horizontal and vertical options

In the first example, we will use two options of the Alignment class of openpyxl:

• horizontal
• vertical

To show you the visual impact of different values, we will set the options of four different cells. These cells are D2 to D5.

For an idea, this is our sample sheet before applying this style:

Following is the Python code and resultant sheet after execution:

# Setting the alignment examples
from openpyxl import load_workbook
from openpyxl.styles import Alignment

#Loading the Workbook
wb_style = load_workbook('test_alignment.xlsx')

#Accessing Product Informaiton Sheet
sheet = wb_style.active

#Specifying alignment for D2 to D5 cells
cell_align_1 = sheet["D2"]
cell_align_1.alignment = Alignment(horizontal='center', vertical='top')


cell_align_2 = sheet["D3"]
cell_align_2.alignment = Alignment(horizontal='right', vertical='bottom')

cell_align_3 = sheet["D4"]
cell_align_3.alignment = Alignment(horizontal='left', vertical='justify')

cell_align_4 = sheet["D5"]
cell_align_4.alignment = Alignment(horizontal='fill', vertical='center')


#Saving the Workbook
wb_style.save("test_alignment.xlsx")

Result:

You can see the difference and also notice D5 “fill” value impact.

Applying alignment style to the range of cells example

This example applies a single alignment style to a range of cells. For that, we are using a range of B4 to C8 cells.

We will apply the following options in this example:

• horizontal=’right’
• vertical=’center’
• shrinkToFit=True
• textRotation=180
• wrapText=True

With all combined, see the sheet before (in the above example) and after the execution of this code:

# Setting the alignment examples
from openpyxl import load_workbook
from openpyxl.styles import Alignment

#Loading the Workbook
wb_style = load_workbook('test_alignment.xlsx')

#Accessing Product Informaiton Sheet
sheet = wb_style.active


#Specifying alignment for B4 to C8 cells range

range=sheet['B4':'C8']
for cell in range:
    for c in cell:
        c.alignment=Alignment(horizontal='right', vertical='center', shrinkToFit=True, textRotation=180, wrapText=True)


#Saving the Workbook
wb_style.save("test_alignment.xlsx")

Result:

The values are in this angle due to textRotation option. You may provide the angle value from 0 to 180.

Testing more options/values in the rows example

Just for diversification of our examples, we are using iter_rows function to apply alignment at row levels.

We will apply the following options and values for this example from row number 4 to the last row.

• horizontal=’justify’
• vertical=’top’
• indent=100.5
• readingOrder=2.5
• shrinkToFit=True

In order to make rows prominent, we also highlighted them by using the background option of openpyxl:

The code:

# Setting the alignment examples
from openpyxl import load_workbook
from openpyxl.styles import Alignment
from openpyxl.styles import PatternFill


#Loading the Workbook
wb_style = load_workbook('test_alignment.xlsx')

#Accessing Product Informaiton Sheet
sheet = wb_style.active

#Get the last row number in the sheet
last_row = sheet.max_row

#Applying Alignment and backgroud to the rows
for rows in sheet.iter_rows(min_row=4, max_row=last_row, min_col=None):
   for cell in rows:
     cell.alignment=Alignment(horizontal='justify', vertical='top', indent=100.5, readingOrder=2.5, shrinkToFit=True)
     cell.fill = PatternFill(start_color="80FF00", end_color="80FF00",fill_type = "solid")


#Saving the Workbook

wb_style.save("test_alignment.xlsx")

Result:

Notice the alignment difference between the first three rows and rows 4 to 11.

 

For reference: https://openpyxl.readthedocs.io/en/latest/api/openpyxl.styles.alignment.html