Python Openpyxl Append issue

Question

I have hundreds of XML files that I need to extract two values from and ouput in an Excel or CSV file. This is the code I currently have:

#grabs idRoot and typeId root values from XML files
import glob
from openpyxl import Workbook
from xml.dom import minidom
import os


wb = Workbook()
ws = wb.active
def typeIdRoot (filename):

    f = open(filename, encoding = "utf8")
    for xml in f:

        xmldoc = minidom.parse(f)

        qmd = xmldoc.getElementsByTagName("MainTag")[0]

        typeIdElement = qmd.getElementsByTagName("typeId")[0]

        root = typeIdElement.attributes["root"]

        global rootValue
        rootValue = root.value
    print ('rootValue =' ,rootValue,)
    ws.append([rootValue])
    wb.save("some.xlsx")   



wb = Workbook()
ws = wb.active
def idRoot (filename):

    f = open(filename, encoding = "utf8")
    for xml in f:

        xmldoc = minidom.parse(f)

        tcd = xmldoc.getElementsByTagName("MainTag")[0]

        activitiesElement = tcd.getElementsByTagName("id")[0]

        sport = activitiesElement.attributes["root"]

        sportName = sport.value

        print ('idRoot =' ,sportName,)

        ws.append([idRoot])

        wb.save("some.xlsx")    


for file in glob.glob("*.xml"):
    typeIdRoot (file)

for file in glob.glob("*.xml"):
    idRoot (file)

The first value follows a 1.11.111.1.111111.1.3 format. The second mixes letters and numbers. I believe this is the reason for the error:

Traceback (most recent call last):
  File "C:\Python34\Scripts\xml\good.py", line 64, in <module>
    idRoot (file)
  File "C:\Python34\Scripts\xml\good.py", line 54, in idRoot
    ws.append([idRoot])
  File "C:\Python34\lib\site-packages\openpyxl\worksheet\worksheet.py", line 754, in append
    cell = self._new_cell(col, row_idx, content)
  File "C:\Python34\lib\site-packages\openpyxl\worksheet\worksheet.py", line 376, in _new_cell
    cell = Cell(self, column, row, value)
  File "C:\Python34\lib\site-packages\openpyxl\cell\cell.py", line 131, in __init__
    self.value = value
  File "C:\Python34\lib\site-packages\openpyxl\cell\cell.py", line 313, in value
    self._bind_value(value)
  File "C:\Python34\lib\site-packages\openpyxl\cell\cell.py", line 217, in _bind_value
    raise ValueError("Cannot convert {0} to Excel".format(value))
ValueError: Cannot convert <function idRoot at 0x037D24F8> to Excel

I would like the result to add both values on the same row. So then I would have a new row for each file in the directory. I need to add the second value to the second row.

as such:

  Value 1                           Value 2
1.11.111.1.111111.1.3           10101011-0d10-0101-010d-0dc1010e0101

Eric Levieil · Accepted Answer · 2015-06-26 22:50:32Z

2

idRoot is the name of your FUNCTION. So when you write

ws.append([idRoot])

you probably mean:

ws.append([sportName])

Of course, you can write something like:

ws.append([rootValue, sportName])

providing both variables are defined with reasonable values. One last thing, you should save your file only once.

answered Jun 26, 2015 at 22:50

Eric Levieil

3,5842 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Over a year ago

Perfect, syntax and little things will get you sometimes! Appreciate it!

Collectives™ on Stack Overflow

Python Openpyxl Append issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related