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 pdwriter= pd.ExcelWriter("some_excel.xlsx", engine='xlsxwriter')

workbook  = writer.bookworksheet= 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 = 0whileTrue:
        if sheet_obj.cell(number_of_rows, 3).value != None:
            last_row_index_with_data = number_of_rows
            breakelse:
            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 itnrows=ws.max_rowifnrows>1000:nrows=1000lastrow=0while True:ifws.cell(nrows,3).value!=None:lastrow=nrowsbreakelse: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 sheetnamesfor 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('-----------------------------')