title: python lxml - loop/iterate through excel rows and save each row as one xml
body: <p>the problem is that the 2nd xml file contains also the data from the first iteration of the excel row and the third xml file every data from the first and 2nd rows</p>
  
  <p><a href="https://i.stack.imgur.com/JV68w.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JV68w.png" alt="enter image description here"></a></p>
  
  <p>Working since hours on that and cant figure it out</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>as described 
  this for every single excel row and for each row one .xml file</p>
  
  <pre><code>&lt;?xml version='1.0' encoding='UTF-8'?&gt;
  &lt;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"&gt;
    &lt;consolidate consolidatedAmount="1337.01"&gt;
      &lt;accountsPayableLedger&gt;
        &lt;bookingText&gt;amazon&lt;/bookingText&gt;
        &lt;invoiceId&gt;1&lt;/invoiceId&gt;
      &lt;/accountsPayableLedger&gt;
    &lt;/consolidate&gt;
  &lt;/LedgerImport&gt;
  </code></pre>