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.
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
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)
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:
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)
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: