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('-----------------------------')
Post a Comment for "Is There Any Method To Get The Number Of Rows And Columns Present In .xlsx Sheet Using Openpyxl?"