Python calc for c in column rows containing data

[avei]

I am interested in writing an iterative look up using python macros. Essentially the sheet on the spread sheet will have in Column A, a list of values (xxx, yyy, zzz, etc, …) There will be an occasional blank row which will be skipped, but I will not have up front knowledge of the precise length of the list. My plan is to us a for loop to cycle through the data and fetch the outside information and place it in column B adjacent to the item in col A.

A B
xxx 14
yyy 16
zzz 03 and so forth.

code snippet which will allow me to get the data is as follows:

import sys
import uno
import time
import urllib.request

from datetime import date

oDoc = XSCRIPTCONTEXT.getDocument()

    def update():
    
            oSheet = oDoc.CurrentController.ActiveSheet
            oCell1 = oSheet.getCellRangeByName('A1')
            url_stub = 'http://download.inventory.intranet/qty_avail?spartid='
             part = 'ABSR'
            url = url_stub + part + '&ac=qtyonhand'
            r = urllib.request.urlopen(url)
            oCell1.Value = r.read()
            return()

This works. It calls the url with http://download.inventory.intranet/qty_avail?spartid=ABSR&ptyonhand, but obviously it only works once. It returns a number between 0 and n and stores (in this case a number in cell A1 on the current sheet.

What I would like to do is to do an iterative process across rows: (pseudo-code)

def update():
    
            oSheet = oDoc.CurrentController.ActiveSheet
            oCell1 = oSheet.getCellRangeByName('A1')
            oCell2 = oSheet.getCellRangeByName(1,0)
            url_stub = 'http://download.inventory.intranet/qty_avail?spartid='
            for part in oSheet.oCell1. [     some method which ends when there is no more data in rows 1 and below   ]
                 url = url_stub + part + '&ac=qtyonhand'
                 r = urllib.request.urlopen(url)
                 oCell2 = oSheet.getCellRangeByValue(1,row number)
                 oCell2.Value = r.read()
            return()

If someone can point me in the right direction for the best method for detecting no more data below the final row, I’d be grateful. The rest of it, I think I can figure out. As I have struggled with python macros in LibreOffice, and am now pounding the old StarOffice documents, I’m going to try to put what I’ve learned into a consolidated document once I get through this project. All the stuff I’ve seen is mainly based on BASIC, but I think if I can figure out the python methods, it might be a better way to go. Thanks for any help.

[Ratslinger]

The answer by @JohnSUN in this post should give you the needed information.

As an aside note, just a very brief glance at you code. This line:

oCell2 = oSheet.getCellRangeByName(1,0)

is incorrect. To get by position, use getCellByPosition(0,0). Remember 0,0 is A1.

EDIT:

Ironically, I was working using that data for something else. Here is a quick Python routine to get last row:

def detect_last_used_row():
    oDoc = XSCRIPTCONTEXT.getDocument()
    oSheet = oDoc.CurrentController.ActiveSheet
    oCursor = oSheet.createCursor()
    oCursor.gotoEndOfUsedArea(False)
    return oCursor.getRangeAddress().EndRow +1

Please note, this obtains the last row used in the sheet and not for any particular column. It will help limit the rows looked at. You can always do further checking by examining the cell contents.

[avei]

This is exactly what I was looking for. I did not want to place an EOF placeholder at the bottom of the valid data, so I was looking for something like your method. Interestingly, when I was using openpyxl for this task, there is a simple for construct that accomplishes this in 2 lines of code, but alas, openpyxl will not read native .ods LO files, only excel. tnx.

[Ratslinger]

FYI The least number of lines used is not always the best way. The stated code could have been done as such. Instead I presented it this way for clarity.

If this answers your question please click on the (upper left area of answer).

[avei]

Agreed, but the openpyxl solution is this:

for   row in sheet.columns[1]:
   do something

—The openpyxl method used in the for statement is able to determine the last row used, which eliminates the need for the creation of the oCursor, moving it to the end and calculating the endrow range.
(ps. I did try clicking on the but apparently, I’m so new to posting that it decided I need more points. It worked now, though.)