python xlwings: Find the cell in the last row

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

reference

xlwings.Range Simple Reference xlwings function to find the last row with data (stackoverrun)

Recommended Posts

python xlwings: Find the cell in the last row
Find the difference in Python
Find the maximum Python
List find in Python
Find the solution of the nth-order equation in python
[Python] Find the transposed matrix in a comprehension
Find the Hermitian matrix and its eigenvalues in Python
What does the last () in a function mean in Python?
Get last month in python
Download the file in Python
Find permutations / combinations in Python
Let's find pi in Python
Find the maximum python (improved)
Find out the apparent width of a string in python
Python --Find out number of groups in the regex expression
Find the eigenvalues of a real symmetric matrix in Python
[Python] Find the second smallest value.
Python in the browser: Brython's recommendation
Save the binary file in Python
Hit the Sesami API in Python
Get the desktop path in Python
Get the script path in Python
In the python command python points to python3.8
Implement the Singleton pattern in Python
Find the Levenshtein Distance with python
Hit the web API in Python
I wrote the queue in Python
Examine the object's class in python
Get the desktop path in Python
Get the host name in Python
Access the Twitter API in Python
I wrote the stack in Python
Master the weakref module in Python
Delete a particular character in Python if it is the last
Find the maximum value python (fixed ver)
Learn the design pattern "Prototype" in Python
Learn the design pattern "Builder" in Python
Try using the Wunderlist API in Python
Check the behavior of destructor in Python
Learn the design pattern "Flyweight" in Python
Try using the Kraken API in Python
Learn the design pattern "Observer" in Python
Learn the design pattern "Memento" in Python
Learn the design pattern "Proxy" in Python
Write the test in a python docstring
Learn the design pattern "Command" in Python
OR the List in Python (zip function)
Display Python 3 in the browser with MAMP
Tweet using the Twitter API in Python
Learn the design pattern "Bridge" in Python
Check if the URL exists in Python
Learn the design pattern "Mediator" in Python
Associate the table set in python models.py
The result of installing python in Anaconda
What is "mahjong" in the Python library? ??
Read the file line by line in Python
Read the file line by line in Python
MongoDB for the first time in Python
Learn the design pattern "Iterator" in Python
The basics of running NoxPlayer in Python
Find and check inverse matrix in Python