3 Examples to Align Excel Text by openpyxl
The alignment of Excel cells, rows, or columns can be set by using openpyxl.styles.Alignment class.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
# 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
# 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
