I need the last row in a particular column that contains data in Excel. In openpyxl sheet.max_row or max_column gets us the maximum row or column in the whole sheet. But what I want is for a particular column.

My scenario is where I have to get some values from database and append it to the end of a particular column in Excel sheet.

In this screenshot, if I want max_column containing data in column 'C', it should return 10:

In the above image if I want last cell containing data of column 'C', it should return 10

------------- Solution 1 --------------------

import pandas as pd

# lt is the dataframe containing the data to be loaded to excel file

for index,i in enumerate(lt):

panda_xl_rd = pd.read_excel('file.xlsx',"sheet_Name") # Panda Dataframe

max = len(panda_xl_rd.iloc[:,(col-1)].dropna())+2 ''' getting the

row_num of

last record in

column

dropna removes

the Nan

values else we

will get

the entire

sheets max

column length .

+2 gets

the next column

right after the

last column to

enter data '''

cellref = sheet.cell(row = max+index, column=col)

cellref.value = i

del panda_xl_rd

------------------------Solution 2 ----------------------

------------------------Solution 3 ----------------------

Maybe solution 3 is a more concise one !!

解决方案Question: i want max_column containing data in Column 'C' it should return 10:

Simple count cell.value not Empty

Documentation Accessing many cells

PSEUDOCODE

for cell in Column('C'):

if not cell.value is empty:

count += 1

Comment: What if we have an empty cell in between?

Count the Rows in sync with the Column Range, and use a maxRowWithData variable. This will also work with no empty cell between.

PSEUDOCODE

for row index, cell in enumerate Column('C'):

if not cell.value is empty:

maxRowWithData = row index

Note: The cell index of openpyxl is 1-based!