Unable to create XML using data from Excel sheet with Python LXML library

Question

Below is the complete code:

import lxml.etree
import lxml.builder
import openpyxl

wb = openpyxl.load_workbook('C:\Users\powell.mittra\Excel.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
x = sheet.cell(row=12, column=1).value

E = lxml.builder.ElementMaker()
ROOT = E.x
DOC = E.doc
FIELD1 = E.field1
FIELD2 = E.field2

the_doc = ROOT(
        DOC(
            FIELD1('some value1', name='blah'),
            FIELD2('some value2', name='asdfasd'),
            )   
        )   

print lxml.etree.tostring(the_doc, pretty_print=True)

I am getting the following output where 'x' is taken as a string instead of taking the value from x = sheet.cell(row=12, column=1).value :

<x>
  <doc>
    <field1 name="blah">some value1</field1>
    <field2 name="asdfasd">some value2</field2>
  </doc>
</x>

Can someone please let me know if I can pass values in ROOT and other elements from excel sheet or it is not possible using LXML?

Code from example are differs to code from stacktrace

nick_gabpe
– nick_gabpe

2017-04-13 05:45:58 +00:00
Commented Apr 13, 2017 at 5:45 — nick_gabpe
– nick_gabpe, Commented Apr 13, 2017 at 5:45
@nick_gabpe sorry, I updated the stacktrace

Powell Mittra
– Powell Mittra

2017-04-13 05:58:27 +00:00
Commented Apr 13, 2017 at 5:58 — Powell Mittra
– Powell Mittra, Commented Apr 13, 2017 at 5:58

nick_gabpe · Accepted Answer · 2017-04-13 09:50:58Z

For those goals I prefer to use xml.etree library. This is from python standard library so you even not need to install it.

import openpyxl
import xml.etree.ElementTree as ET


wb = openpyxl.load_workbook('C:\Users\powell.mittra\Excel.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
x = sheet.cell(row=12, column=1).value

root = ET.Element(x)
doc = ET.SubElement(root, "doc")

ET.SubElement(doc, "field1", name="blah").text = "some value1"
ET.SubElement(doc, "field2", name="asdfasd").text = "some value2"

tree = ET.ElementTree(root)
tree.write("filename.xml")

But if you want use lxml you can just change tag in the end

import lxml.etree
import lxml.builder
import openpyxl

wb = openpyxl.load_workbook('C:\Users\powell.mittra\Excel.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
x = sheet.cell(row=12, column=1).value

E = lxml.builder.ElementMaker()
ROOT = E.x
DOC = E.doc
FIELD1 = E.field1
FIELD2 = E.field2

the_doc = ROOT(
        DOC(
            FIELD1('some value1', name='blah'),
            FIELD2('some value2', name='asdfasd'),
            )   
        )   
the_doc.tag = x
print lxml.etree.tostring(the_doc, pretty_print=True)

Collectives™ on Stack Overflow

Unable to create XML using data from Excel sheet with Python LXML library

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related