How to Get Total Columns and Rows in an Excel Sheet by openpyxl

Getting the total number of rows and columns in Excel by openpyxl

Python openpyxl library has two functions that can be used to get the number of rows and columns in the specified Excel sheet.

These functions are:

• max_row
• max_column

See the example below for using each of these functions.

An example of max_row function to get row count

For our examples, we are using the following sample sheet:

You can see, it’s a Product Information sheet that contains a few columns and rows. First, let us get the number of rows by using openpyxl:

The code:

# Find total number of rows

from openpyxl import load_workbook

#Loading the Workbook

wb_access = load_workbook('test_Excel.xlsx')

#Accessing Active Sheet
sheet = wb_access.active

print("Total Number of Rows in Product Information Sheet = ", sheet.max_row )

Result:

Total Number of Rows in Product Information Sheet =  11

Compare it with the sheet above and you can see we have total of 11 rows including the header row.

Getting total number of columns in the sheet

Now let us have a look at getting the total count of columns in our Product information sheet.

The code:

# Find total number of columns

from openpyxl import load_workbook




#Loading the Workbook

wb_access = load_workbook('test_Excel.xlsx')




#Accessing Active Sheet

sheet = wb_access.active




print("Total Number of Columns in Product Information Sheet = ", sheet.max_column )

Result:

Total Number of Columns in Product Information Sheet =  5

What if rows are empty?

So, what max_row function returns if certain rows are empty in the sheet? Have a look at the sample sheet below where we deleted four rows of data:

While running the same code as in the first example, see what we get the output:

The code:

# Find total number of rows - if rows are empty

from openpyxl import load_workbook

#Loading the Workbook
wb_access = load_workbook('test_Excel.xlsx')

#Accessing Active Sheet that has empty rows
sheet = wb_access.active


print("Total Number of Rows in Product Information Sheet = ", sheet.max_row )

Result:

Total Number of Rows in Product Information Sheet =  11

So, we get the same result i.e. 11 because the maximum row index of the file is eleven.

What about if you want to get the result without empty rows?

The Solution

The following simple solution can be used for getting the total data rows only. We have a small function that gets the data rows only.

In the Python program below, we will display both – including empty rows and excluding empty rows. Have a look:

Python program:

# Get Data rows onlys example
from openpyxl import load_workbook

#Loading Workbook
workbook = load_workbook('test_Excel.xlsx')

#Active sheet - Product Information
sheet = workbook.active

#Function to get data rows only
def data_rows_only(*, sheet):
    rows = 0
    for max_row, row in enumerate(sheet, 1):
        if not all(col.value is None for col in row):
            rows += 1
    return rows


#Calling the function
data_rows = data_rows_only(sheet=sheet)

print("Total Number of Rows Incl. Empty = ", sheet.max_row )
print ("Total Data Rows - Excl. Empty = ", data_rows)

Result:

Total Number of Rows Incl. Empty =  11

Total Data Rows - Excl. Empty =  7