Is There Any Method To Get The Number Of Rows And Columns Present In .xlsx Sheet Using Openpyxl?

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.

Solution 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

Solution 2:

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

Solution 3:

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

Solution 4:

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

Solution 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('-----------------------------')