0

Extract data from python to excel

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()

8th Jun 2020, 10:06 AM
David Chichon Sanchez
David Chichon Sanchez - avatar
3 Answers
+ 4
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. ï»ż
8th Jun 2020, 2:19 PM
Lothar
Lothar - avatar
+ 3
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.
26th Jun 2020, 2:21 PM
Lothar
Lothar - avatar
0
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
26th Jun 2020, 8:58 AM
David Chichon Sanchez
David Chichon Sanchez - avatar