Converting excel to xml with Null values in excel

428 Views Asked by At

I'm trying to convert an excel file to xml using this skeleton code:

wb = load_workbook("deneme.xlsx")
# Getting an object of active sheet 1
ws = wb.worksheets[0]
doc, tag, text = Doc().tagtext()

xml_header = '<?xml version="1.0" encoding="UTF-8"?>'

# Appends the String to document
doc.asis(xml_header)
with tag('userdata'):
    with tag('basicinf'):
        for row in ws.iter_rows(min_row=2, max_row=None, min_col=1, max_col=90):
            row = [cell.value for cell in row]
            a=row[0]


            with tag("usernumber"):
                text(row[0])

            with tag("username"):

                text(row[1])
            with tag("serviceareacode"):
                text(row[2])
            with tag("language"):
                text(row[3])
            with tag("welcomemsgid"):
                text(row[4])
            with tag("calledlimitedid"):
                text(row[5])
            with tag("followmeflag"):
                text(row[6])
            with tag("followmenumber"):
                text(row[7])
            with tag("mobilespecial"):
                text(row[8])
result = indent(
    doc.getvalue(),
    indentation='  ',
    indent_text=False
)

print(result)
with open("routescheme_{}.xml".format(a), "w") as f:
    f.write(result)

Now if I don't write any input on row[0] in excel, I get the below error:

Traceback (most recent call last):
  File "C:\Python39\lib\site-packages\yattag\simpledoc.py", line 489, in html_escape
    return s.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")
AttributeError: 'NoneType' object has no attribute 'replace'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:\Users\abdul\Desktop\mmm\main.py", line 36, in <module>
    text(row[0])
  File "C:\Python39\lib\site-packages\yattag\simpledoc.py", line 179, in text
    transformed_string = html_escape(strg)
  File "C:\Python39\lib\site-packages\yattag\simpledoc.py", line 491, in html_escape
    raise TypeError(
TypeError: You can only insert a string, an int or a float inside a xml/html text node. Got None (type <class 'NoneType'>) instead.

My expectation is that when row[0] is empty it should be like <usernumber></usernumber> in my xml result file.

How can I do that?

1

There are 1 best solutions below

0
On

I guess ı found solution by myself. I am not sure this is a good solution but,

            with tag("usernumber"):
            if (row[0] == None):
                text()
            else:
             text(row[0])

So if serviceare code is empty result is: <serviceareacode></serviceareacode> if it is not empty, result is: <serviceareacode>value</serviceareacode>