How can I prevent the error "TypeError: expected string or bytes-like object" when using load_workbook

[chevell...@gmail.com]

Hello,

I am attempting to load the attached workbook and this one and others that I am working with give me the following error when attempting to use the load_workbook so that I can modify this existing workbook. Any idea why I am getting this error and how I can correct it?  Thanks!!!

TypeError                                 Traceback (most recent call last)
<ipython-input-13-12f75956434c> in <module>()
      1 import openpyxl
      2
----> 3 book = openpyxl.load_workbook('c:/examples/testing.xlsx')
      4 sheet = book.active;
      5 sheet.sheet_properties.tabColor = "0072BA"

~\AppData\Local\Continuum\anaconda3\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, guess_types, keep_links)
    209     if ARC_CORE in valid_files:
    210         src = fromstring(archive.read(ARC_CORE))
--> 211         wb.properties = DocumentProperties.from_tree(src)
    212
    213

~\AppData\Local\Continuum\anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py in from_tree(cls, node)
     69
     70         for el in node:
---> 71             tag = localname(el)
     72             if tag in KEYWORDS:
     73                 tag = "_" + tag

~\AppData\Local\Continuum\anaconda3\lib\site-packages\openpyxl\xml\functions.py in localname(node)
    103
    104 def localname(node):
--> 105     m = NS_REGEX.match(node.tag)
    106     return m.group('localname')

TypeError: expected string or bytes-like object

[Charlie Clark]

Am .11.2018, 13:50 Uhr, schrieb <chevell...@gmail.com>:

>
> I am attempting to load the attached workbook and this one and others
> that
> I am working with give me the following error when attempting to use the
> load_workbook so that I can modify this existing workbook. Any idea why I
> am getting this error and how I can correct it? Thanks!!!

Looks like there is a problem due to comments embedded in the XML in the
document properties. Never come across this before and it's sort of
unnecessary as there are other ways of storing this kind of metadata. You
can workaround this by opening and then saving the file in Excel. I assume
the file comes from some kind of application.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Chevell Parker]

Thanks Charlie, I did notice that the applications saved by Excel did not give me this error.

Sincerely,
Chevell

Sent from my iPhone

> --
> You received this message because you are subscribed to a topic in the Google Groups "openpyxl-users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/openpyxl-users/rVogvQWqe6g/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to openpyxl-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Charlie Clark]

Am .11.2018, 19:16 Uhr, schrieb Chevell Parker <chevell...@gmail.com>:

> Thanks Charlie, I did notice that the applications saved by Excel did
> not give me this error.

It actually looks like this could be a bug in lxml which I'd like not to
have to work round. Which version of openpyxl and lmxl do you have
installed?

[Chevell Parker]

Charley, I have the latest version of openpyxl. How do I determine the version of the lmxl?

Thanks,

Chevell

Sent from my iPhone

[Chevell Parker]

Hello Charlie,

The version of openpyxl installed on this machine is 2.4.1 and the version of link is (3, 7, 2, 0). Thanks again!

Sincerely,
Chevell

Sent from my iPhone

> On Nov 29, 2018, at 1:43 PM, Charlie Clark <charli...@clark-consulting.eu> wrote:
>

[Charlie Clark]

Am .11.2018, 02:08 Uhr, schrieb Chevell Parker <chevell...@gmail.com>:

> Hello Charlie,
>
> The version of openpyxl installed on this machine is 2.4.1 and the
> version of link is (3, 7, 2, 0). Thanks again!

Okay. FWIW you're not on the most recent version of openpyxl, that's
2.5.12 which I released yesterday which, amongst other things, will use
defusedxml if it's installed but that's not your case.

You can work around the issue by setting an environment variable
OPENPYXL_LXML to False as the problem is related to lxml only: see
https://bugs.launchpad.net/bugs/1805888 for further details. And, as long
as you are not working with really large workbooks, this is probably the
easiest thing to do apart from passing the files through Excel.
Alternatively you can monkey patch openpyxl:

--- a/openpyxl/xml/functions.py Thu Nov 29 12:53:49 2018 +0100
+++ b/openpyxl/xml/functions.py Fri Nov 30 10:37:30 2018 +0100
@@ -133,5 +133,7 @@
NS_REGEX = re.compile("({(?P<namespace>.*)})?(?P<localname>.*)")

def localname(node):
+ if callable(node.tag):
+ return "comment"
m = NS_REGEX.match(node.tag)
return m.group('localname')

This is harmless, but it isn't pretty and not the sort of thing I'd like
to have in the library and this is the first time I've come across the
issue. If possible I'd suggest to whoever is responsible for the
application that is generating this that the switch to using custom
properties which are designed for this kind of annotation.

[Chevell Parker]

This is great information! I really appreciate all of your help and efforts!!!

Sincerely,
Chevell

Sent from my iPhone

[Charlie Clark]

Am .11.2018, 16:16 Uhr, schrieb Chevell Parker <chevell...@gmail.com>:

> This is great information! I really appreciate all of your help and
> efforts!!!

Beer tokens always gratefully accepted. ;-)

[Chevell Parker]

Charlie, this can be arranged :)

Sent from my iPhone