It is a method to get / assign the value of the last row with an element from a specific column with xlwings of python. It is also calculated even if a line without elements is inserted in the middle.
Book1.xlsx
| line/Column | A | B | C |
|---|---|---|---|
| 1 | Product name | number | |
| 2 | Apple | 2 | |
| 3 | |||
| 4 | Mandarin orange | 0 | |
| 5 | Strawberry | 5 |
fun.py
def lastExcelRow(sheet, col):
lwr_r_cell = sheet.cells.last_cell # lower right cell
lwr_row = lwr_r_cell.row # row of the lower right cell
lwr_cell = sheet.range((lwr_row, col)) # change to your specified column
if lwr_cell.value is None:
lwr_cell = lwr_cell.end('up') # go up untill you hit a non-empty cell
return lwr_cell.row
def lastExcelCell(sheet, col):#col can be either alphabetic or numerical
return sheet.range(( lastExcelRow(sheet, col), col ))
main.py
import xlwings as xw
import fun
bk = xw.Book("Book1.xlsx")
targetSheet = bk.sheets("Sheet1")
#Find the value of the last row with the elements in column A
print( fun.lastExcelCell(targetSheet , "A").value ) # range("A5") ->Strawberry
#1st row(=Column B)Find the last line with the element of
print( fun.lastExcelCell(targetSheet , 2).value ) # range("B5") -> 5
#Substitute one row below the last row with elements in column A
fun.lastExcelCell(targetSheet, "A").offset(1, 0).value = "Melon" # ->To the descending of strawberries"Melon"Substitute
xlwings.Range Simple Reference xlwings function to find the last row with data (stackoverrun)
Recommended Posts