How to read XML data and write to Excel sheet in Python for Selenium Automation

user_15066382 0
Viewed 920

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")
readxml openpyxl python
1 Answers

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)

user_1673020
Related