2

I have a scenario where I need to get the data from XML file and write the same to Excel sheet and use the same sheet for data processing. I am able to read the data from XML, but not able to insert the same data (records) to an excel file I am using OpenPyExcel for this, please suggest any alternative and help me here. I am not seeing any error though, but nothing is being written to excel sheet

import xml.etree.ElementTree as ET
import openpyexcel

tree = ET.parse("Test_Cust.xml")
root = tree.getroot()
workbook = openpyexcel.load_workbook("xml_excel.xlsx")
sheet = workbook["Sheet1"]

for items in root.iter():
    if items.tag == "Email":
        cust_email = items.text
    elif items.tag == "CompanyName":
        cust_cn = items.text
    elif items.tag == "FirstName":
        cust_fn = items.text
    elif items.tag == "LastName":
        cust_ln = items.text

        rownum = (sheet.max_row)
        print(rownum)
        colnum = (sheet.max_column)
        print(colnum)

        for r in range(2, rownum+1):
            for c in range(1, colnum+1):

                sheet.cell(row = r, column = c).value = cust_email
                sheet.cell(row=r, column=c).value = cust_email
                sheet.cell(row=r, column=c).value = cust_email
                sheet.cell(row=r, column=c).value = cust_email
                workbook.save("xml_excel.xlsx")
print("Done")
JeffC
  • 22,180
  • 5
  • 32
  • 55
Ram0803 Chigari
  • 27
  • 5
  • Why are you writing the same value to the same cell 4 times in your loop(s)? I'm assuming that has something to do with your issue. – JeffC Feb 17 '21 at 16:30
  • 2
    Is your indentation different in your actual source? As you have it above, none of your logic to write to the Excel sheet will happen unless `items.tag == "LastName"` is true, and then `cust_email` will only be set if an earlier iteration happened to set it. If the indentation is wrong, please fix it. If the indentation is right, I could see that being your problem. – CryptoFool Feb 17 '21 at 16:37
  • @JeffC, I am just writing the same value for testing purpose, – Ram0803 Chigari Feb 17 '21 at 16:52
  • @CryptFool, I think the indentation is right, because with the same code (values), I was successfully able to insert the XML records to MySql database table – Ram0803 Chigari Feb 17 '21 at 16:52
  • Hi, Somehow I am able to get some records into excel sheet. But all the time ONLY the last item of the XML file is getting written to ALL the cells of Excel Sheet – Ram0803 Chigari Feb 17 '21 at 17:33

1 Answers1

1

Have you tried writing to the cell with this syntax?:

 ws.cell(column=colnum,
         row=rownum, value='mydata')

Note: you are saving the sheet in the inner loop. Slow!

Apart from that, this is a valid question: how can you easily read XML formatted data into OpenPYXL? But the question is more about parsing the incoming XML. That done, writing to OpenPYXL seems trivial.

(Oops, my mistake: my answer is for OpenPYXL, and may not work with openpyexcel)

rleir
  • 791
  • 1
  • 7
  • 19