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 ----------------------
https://stackoverflow.com/a/52816289/10003981
------------------------Solution 3 ----------------------
https://stackoverflow.com/a/52817637/10003981
Maybe solution 3 is a more concise one !!
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…