CODEWITHSUNDEEP
pythonexcelopenpyxl
Kulkarni Sachin
Kulkarni Sachin

Reputation: 313

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. Is there any such method in openpyxl ?

Upvotes: 20

Views: 76427

Answers (8)

macikatr
macikatr

Reputation: 21

import openpyxl as ox
from openpyxl import Workbook

path=("test.xlsx") #location of the file
workbook=ox.load_workbook(path) 
sheet=workbook.active     
nrows=sheet.max_row       #openpyxl version of nrows of xlrd
ncols=sheet.max_column    #openpyxl version of ncols of xlrd 
print(nrows,ncols)

Upvotes: 1

Alexander Craggs
Alexander Craggs

Reputation: 8819

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

Version ~= 3.0.10 Syntax

rows = sheet.max_row
columns = sheet.max_column

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

Upvotes: 32

Pierre Puiseux
Pierre Puiseux

Reputation: 130

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.

Upvotes: 0

Merrin K
Merrin K

Reputation: 1790

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)

Upvotes: 1

Cam
Cam

Reputation: 1763

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

Upvotes: 1

AshG
AshG

Reputation: 21

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

Upvotes: 2

Syed Adnan Haider
Syed Adnan Haider

Reputation: 74

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

Upvotes: 1

Andrey Mazur
Andrey Mazur

Reputation: 570

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

Upvotes: 2

Related Questions