How to Get Total Columns and Rows in an Excel Sheet 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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
# 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:
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# 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:
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
# 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:
|
1 2 3 |
Total Number of Rows Incl. Empty = 11 Total Data Rows - Excl. Empty = 7 |
