Get Last / Append New Excel Column by openpyxl max_column
By using max_column, you may get the last column in the Excel sheet. In this tutorial, we will show you in a Python program to get the last column number in a sheet.
How to get the maximum column number in an existing Excel sheet by openpyxl
By using max_column attribute, you may get the last column in the Excel sheet.
In this tutorial, we will show you a Python program to get the last column number in a sheet.
We will also show you add/insert a new column at the end of an existing sheet by using max_column.
Sample sheet:
Getting last column number by max_column attribute
In the program below,
- openpyxl library is imported
- Then we loaded our sample sheet (openpyxl_ex.xlsx), by load_workbook
- This is followed by using the “Employees” sheet in the Workbook
- Then we displayed the last column number in the sheet
Program:
1 2 3 4 5 6 7 8 9 10 11 |
from openpyxl import load_workbook wb_col_max = load_workbook('openpyxl_ex.xlsx') #Accessing Employees sheet sheet = wb_col_max["Employees"] #Print last column number print("Last Column Number : ", sheet.max_column) |
Result:
Last Column Number : 2
Note that we used this simple sheet for making things understand easily. Normally, your sheet may contain many columns.
Adding a column at the end of an existing sheet
As we know our last column number, we can access the next column after the current last column to give it a name.
See the program below and the output:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
from openpyxl import load_workbook wb_col_max = load_workbook('openpyxl_ex.xlsx') #Accessing Employees sheet sheet = wb_col_max["Employees"] add_col = sheet.max_column + 1 #Giving new last column a name sheet.cell(row=1, column=add_col).value = 'Join Date' #Save the workbook wb_col_max.save('openpyxl_ex.xlsx') #Print last column number again print("Last Column Number : ", sheet.max_column) |
Output:
Excel sheet:
In Python console:
Last Column Number : 3
How did it work?
- We got the last column number by max_col attribute
- Then we added 1 to that number
- By using sheet’s cell method, we assigned a name to the last column.
- The result is printed again – which is 3.
You may learn about inserting columns using openpyxl in its tutorial.
Next, you might want to learn:
Change background/interior color of cells by opepyxl
Add border colors to cells by openpyxl
Make cell text bold italic by openpyxl