How to create an XML file from an Excel document with Python.

Here I show you how you can generate an XML file with Python from an Excel document using OpenPyXL and Yattag

[tinaeslami]

Very easy to follow and straight to the point! Thank you very much

[alexanderjimenez]

Excellent video, very useful. Thank you Conny

[ketovoronov]

That was a very usefull video for me! Many thanks!

[cireguisse]

Very good content. Thank you for sharing.

[other]

In the bottom portion, with tag and the text rows. TEXT is not working, it's at times looking for an INT or STR. If I put STR in the data comes back blank, and it won't accept INT as I am trying to put it like this
OK Field example in excel is
mm/dd/yyyy hh:mm


How do I convert that using your examples below

with tag("Date Field")
Text(row[20])

str((row[12]).replace('/', '')[:6]) is an example I tried to replicate

** EDIT** great video btw, when i comment out those types it works, so that's awesome! ****

[orangutanklaus]

Worked really well, thanks man!

Just one question: How can I do the same thing for more than one file with all the same structure?
Like I have "convert_file.xlsx" / "convert_file_2.xlsx" / "convert_file_3.xlsx"... and want to get "result_file.xml" / "result_file_2.xml" / "result_file_3.xml"...

from openpyxl import load_workbook

wb = load_workbook("convert_file.xlsx", data_only=True)
ws = wb.active
sheetname_row_list:list =[]

for row in ws.iter_rows(min_row=2, max_row=7, min_col=1, max_col=6):
row = [cell.value for cell in row]


from yattag import Doc, indent
doc, tag, text = Doc().tagtext()

for row in sheetname_row_list:
with tag("culumn0"):
text(row[0])
with tag("culumn1"):
text(row[1])
with tag("culumn2"):
text(row[2])
with tag("culumn3"):
text(row[3])
with tag("culumn4"):
text(row[4])
with tag("culumn5"):
text(row[5])

result = indent(
doc.getvalue(),
indentation = ' ',
indent_text = False
)

with open("result_file.xml", "w") as f:
f.write(result)

[mattpensa]

Hello Conny, I'm receiving an error after inputting the "with tag("Invoice"): step. ~ 9min mark of the video. The error I get seems to be related to the yattag library. Have you seen this before?


File "Desktop\Python\exceltoXML\yattag\simpledoc.py", line 489, in html_escape
return s.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")

TypeError: an integer is required (got type str)

[mahmudulhasan]

how to edit xml or mos file where value will get from excel

[wjimenezu]

it's difficult to understand this example at a maximum video quality of 360p :(