Loop to repeat action after passing each line (xlrd)?

Question

Python

Help, please, to understand. I am working with an xls file, which contains line by line data that needs to be entered in the form of xml, followed by sending a post request to api. I don’t program myself - I’m just a teapot, so for now I’m trying to figure it out intuitively and at the same time I’m learning how to write code directly in practice.
I need to create a loop in such a way that after passing through the line, the procedure for writing to xml is performed and the xml content is sent post to the server, and then it goes to the next line and performs the same procedure.

I use the xlrd library, I threw in a couple of lines, but unfortunately, at the end of my cycle, the data from the last line is written to xml.
Can you please suggest what other options are available?
Thank you!

import requests
import xlrd

book = xlrd.open_workbook(r"C:\Users\Admin\Documents\Pytest\data.xls")
sh = book.sheet_by_index(0)

for i in range(1,sh.nrows):
    print(f'# Итерация номер {i} из {sh.nrows}.')
    oldname  = sh.cell_value(rowx=i,colx=0)
    citype = sh.cell_value(rowx=i,colx=1)
    newname = sh.cell_value(rowx=i,colx=2)

with open('test.xml',"w",encoding="utf-8") as f:

url = "http://..." + содержимое xml

Answer 1

Something like this should be:

Such...

import requests
import xlrd
from pathlib import Path


def my_xml(citype, oldname, newname):
    return f'''<?xml version='1.0' encoding='UTF-8'?>
    <API version='1.0' >
        <citype>
            <name>{citype}</name>
                <criterias>
                    <criteria>
                        <parameter>
                            <name compOperator="IS">CI Name</name>
                            <value>{oldname}</value>
                        </parameter>
                    </criteria>
                </criterias>
            <newvalue>
                <record>
                    <parameter>
                        <name>CI Name</name>
                        <value>{newname}</value>
                    </parameter>
                </record>
            </newvalue>
        </citype>
    </API>'''


if __name__ == '__main__':
    datafilename = Path('data.xls')
    datafilepath = Path.home() / Path('Documents/Pytest/')
    datafile = datafilepath / datafilename
    book = xlrd.open_workbook(datafile)
    sh = book.sheet_by_index(0)
    url_prefix = "http://qwerty.com/api/ci?OPERATION_NAME=update&INPUT_DATA="
    for i in range(0, sh.nrows):
        print(f'## Итерация номер {i} из {sh.nrows}.')
        oldname = str(sh.cell_value(rowx=i, colx=0))
        citype = str(sh.cell_value(rowx=i, colx=1))
        newname = str(sh.cell_value(rowx=i, colx=2))
        url = url_prefix + my_xml(oldname, citype, newname)
        requests.post(url)