Is there any method to get the number of rows and columns present in .xlsx sheet using openpyxl?

Question

Is there any method to get the number of rows and columns present in .xlsx sheet using openpyxl ? In xlrd,

     sheet.ncols 
     sheet.nrows

would give the column and row count. Is there any such method in openpyxl ?

Answer 1

Given a variable sheet, determining the number of rows and columns can be done in one of the following ways:

Version ~= 3.0.5 Syntax

rows = sheet.max_rows
columns = sheet.max_column

Version 1.x.x Syntax

rows = sheet.nrows
columns = sheet.ncols

Version 0.x.x Syntax

rows = sheet.max_row
columns = sheet.max_column

Answer 2

Building upon Dani's solution and not having enough reputation to comment in there. I edited the code by adding a manual piece of control to reduce the time consumed on searching

## iteration to find the last row with values in it
nrows = ws.max_row
if nrows > 1000:
    nrows = 1000

lastrow = 0
while True:
    if ws.cell(nrows, 3).value != None:
        lastrow = nrows
        break
    else:
        nrows -= 1

Answer 3

Worksheet has these methods: 'dim_colmax', 'dim_colmin', 'dim_rowmax', 'dim_rowmin'

Below is a small example:

import pandas as pd

writer = pd.ExcelWriter("some_excel.xlsx", engine='xlsxwriter')

workbook  = writer.book
worksheet = writer.sheets[RESULTS_SHEET_NAME]

last_row = worksheet.dim_rowmax

Answer 4

this is the logic

    number_of_rows = sheet_obj.max_row
    last_row_index_with_data = 0
    
    while True:
        if sheet_obj.cell(number_of_rows, 3).value != None:
            last_row_index_with_data = number_of_rows
            break
        else:
            number_of_rows -= 1

Answer 5

A solution using Pandas to get all sheets row and column counts. It uses df.shape to get the counts.

import pandas as pd
xl = pd.ExcelFile('file.xlsx')
sheetnames = xl.sheet_names  # get sheetnames
for sheet in sheetnames:
    df = xl.parse(sheet)
    dimensions = df.shape
    print('sheetname', ' --> ', sheet)
    print(f'row count on "{sheet}" is {dimensions[0]}')
    print(f'column count on "{sheet}" is {dimensions[1]}')
    print('-----------------------------')

Answer 6

Try

import xlrd

location = ("Filelocation\filename.xlsx")
wb = xlrd.open_workbook(location)
s1 = wb.sheet_by_index(0)
s1.cell_value(0,0)                       #initializing cell from the cell position  

print(" No. of rows: ", s1.nrows)             
print(" No. of columns: ", s1.ncols)

Answer 7

When I need the number of non-empty cols, the more efficient I've found is Take care it gives the number of NON-EMPTY columns, not the total number of columns. When I say the more efficient, I mean the easiest way to achieve the goal, but not the fastest (I did not test execution speed). in the following, sheet is an instance of openpyxl.worksheet.worksheet.Worksheet:

values = list(sheet.values) #values is a list of tuple of same len
nb_cols = len(values[0])

if I need the number of non-empty lines, I do this:

nb_lines = len([v for v in sheet.values if any(v)])

Notice this last instruction can fail : if a line has only 0, it is considered as empty.