Get Last / Append New Excel Column by openpyxl max_column

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:

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:

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