python lxml - 循环/遍历 excel 行并将每一行保存为一个 xml

Question

问题是第二个 xml 文件还包含来自 excel 行的第一次迭代的数据，第三个 xml 文件包含来自第一行和第二行的所有数据

为此工作了几个小时，还是想不通

from lxml import etree
import openpyxl


# Create root element with namespace information
xmlns = "http://xml.datev.de/bedi/tps/ledger/v040"
xsi = "http://www.w3.org/2001/XMLSchema-instance"
schemaLocation = "http://xml.datev.de/bedi/tps/ledger/v040 Belegverwaltung_online_ledger_import_v040.xsd"
version = "4.0"
generator_info = "DATEV Musterdaten"
generating_system = "DATEV manuell"

xmlRoot = etree.Element(
    "{" + xmlns + "}LedgerImport",
    version=version,
    attrib={"{" + xsi + "}schemaLocation": schemaLocation},
    generator_info=generator_info,
    generating_system=generating_system,
    nsmap={'xsi': xsi, None: xmlns}
)

####open excel file speadsheet
wb = openpyxl.load_workbook('import_spendesk_datev.xlsx')
sheet = wb['Import']

# build the xml tree
for i in range(2,6):
        consolidate = etree.SubElement(xmlRoot, 'consolidate', attrib={'consolidatedAmount': str(sheet.cell(row=i,column=16).value),'consolidatedDate': str(sheet.cell(row=i,column=2).value), 'consolidatedInvoiceId': str(sheet.cell(row=i,column=13).value), 'consolidatedCurrencyCode': str(sheet.cell(row=i,column=12).value) })
        accountsPayableLedger = etree.SubElement(consolidate, 'accountsPayableLedger')
        account = etree.SubElement(accountsPayableLedger, 'bookingText')
        account.text = sheet.cell(row=i,column=21).value
        invoice = etree.SubElement(accountsPayableLedger, 'invoiceId')
        invoice.text = sheet.cell(row=i,column=13).value
        date = etree.SubElement(accountsPayableLedger, 'date')
        date.text = sheet.cell(row=i,column=2).value
        amount = etree.SubElement(accountsPayableLedger, 'amount')
        amount.text = sheet.cell(row=i,column=16).value
        account_no = etree.SubElement(accountsPayableLedger, 'accountNo')
        account_no.text = sheet.cell(row=i,column=19).value
        cost1 = etree.SubElement(accountsPayableLedger, 'costCategoryId')
        cost1.text = sheet.cell(row=i,column=15).value
        currency_code = etree.SubElement(accountsPayableLedger, 'currencyCode')
        currency_code.text = sheet.cell(row=i,column=12).value
        party_id = etree.SubElement(accountsPayableLedger, 'partyId')
        party_id.text = sheet.cell(row=i,column=20).value
        bpaccount = etree.SubElement(accountsPayableLedger, 'bpAccountNo')
        bpaccount.text = sheet.cell(row=i,column=20).value
        doc = etree.ElementTree(xmlRoot)
        doc.write( str(sheet.cell(row=i,column=13).value)+".xml", xml_declaration=True, encoding='utf-8', pretty_print=True)

如前所述这适用于每一行 excel 和每一行一个 .xml 文件

<?xml version='1.0' encoding='UTF-8'?>
<LedgerImport xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xml.datev.de/bedi/tps/ledger/v040" generating_system="DATEV manuell" generator_info="DATEV Musterdaten" version="4.0" xsi:schemaLocation="http://xml.datev.de/bedi/tps/ledger/v040 Belegverwaltung_online_ledger_import_v040.xsd">
  <consolidate consolidatedAmount="1337.01">
    <accountsPayableLedger>
      <bookingText>amazon</bookingText>
      <invoiceId>1</invoiceId>
    </accountsPayableLedger>
  </consolidate>
</LedgerImport>

Answer 1

同一个 xmlRoot 对象被多次重复使用。您需要为 for 循环中的每次迭代创建一个新的根元素。

创建根元素的代码可以放在函数中。这是一个简化的示例:

from lxml import etree

def makeroot():
    return etree.Element("LedgerImport")

for i in range(2, 6):
    xmlRoot = makeroot()
    consolidate = etree.SubElement(xmlRoot, 'consolidate',
                                   attrib={'consolidatedAmount': str(i)})
    doc = etree.ElementTree(xmlRoot)
    doc.write(str(i) + ".xml", xml_declaration=True, encoding='utf-8', pretty_print=True)