Extract data from python to excel

Question

Hello, I need to solve a problem.
I am trying to extract xml data using elementTree, and the value is showing in the console, but I can't write to Excel as I want.
So I use the Xlwings library but the error is that I need to write a value in a cell and then write another value, however the code repeats the same value and saves the Excel over and over until all the values (in the range that I specify) ​​coming out of console they finish reading.

def ExtraerPXConciliar():
with open('./Autofactura1.xml', 'rt') as f:
tree = ElementTree.parse(f)
#root = tree.getroot()
#print(root)
for node in tree.iter('GeneralData'):
name = node.attrib.get('Ref')
if name != None:
print(' %s' % name)
#Extraemos el PX
#Exportamos a EXCEL
wb = xw.Book('./Hoja.xlsx') #Workbook location
sht = wb.sheets['Hoja1'] #Sheet Name
sht.range('A1:A5').value = name

wb.save()

Answer 1

If you write the code:

sht.range('A1:A5').value = name

it fills each cell in the defined range with the content of the variable 'name'. To get the individual cells filled you have to use this:

import xlwings as xlw

wb = xlw.Book('test.xlsx')
sh = wb.sheets['s1']
for i in sh.range('F1:F5'):
sh.range(i).value = name
wb.save()

This code can write to each individual cell in the defined range. But to get real individual values to store in the spreadsheet, variable 'name' has to get new content for each iteration in writing process. This is the task you have to solve now.


Answer 2

with the code i already gave you, you can write to single cells in a sheet. With this code, you can use a list or other iterable, and write it to range of cells as you can see in the sample.

import xlwings as xlw

lst = [17,5,2,11,0,7,1,6,2]
wb = xlw.Book('test.xlsx')
sh = wb.sheets['s1']
#for ind, i in enumerate(sh.range('H1:H5'),1):
#sh.range(i).value = ind
sh.range('C1').options(transpose=True).value = lst
wb.save()

# documentation: https://docs.xlwings.org/en/stable/datastructures.html

To describe how exactly your XML data must be read and written, I lack more detailed insights. This is the program logic, you have to implement.

Answer 3

I understand, but I was blocked.
How could I pass to the variable name, an argument for me to iterate the data and thus allow me to write it to the excel sheet?
Thanks