Openpyxl - NoneType in value even though there are values in the xlsx file

[ders...@gmail.com]

Hi,

I got the following problem in a specific xlsx file with 6000 rows and 30 columns.

The xlsx file comes from an online website, which seems to create a report by the Java Apache POI engine. I can not post this file,since it has some company data inside.

Nevertheless, if i open the file in idle, i can not address the values of the file. 

for example if  i try to get the value of cell A1, it gives me no result in IDLE, even though there is a heading row in the file.

>>> ws.cell("A1").value

>>> 

If i save the file to a new sheet, only colums with a formula are saved (which is less than 1 % of the data).

Does anyone have an idea how to fix this or how I can troubleshoot this?

Thanks

[Charlie Clark]

Am .06.2014, 13:31 Uhr, schrieb <ders...@gmail.com>:

> If i save the file to a new sheet, only colums with a formula are saved
> (which is less than 1 % of the data).

Without a sample file to analyse there is not much to go on. It might be
that you have to choose between formula and data when opening the file but
I suspect not.

In lieu of being able to inspect the file, you'll have to do it for us.
Using either the MS OOXML Productivity Tool or simply unzipping the
archive locally.

Depending on your system, try the following:

unzip -d xml myfile.xlsx
tidy --xml -m xml/xl/sheets/worksheets/sheet1.xml

Look at sheet1.xml in a suitable editor. The cells are contained in the
sheetData part.
e.g. for cell A1:

<row r="1" spans="1:1">
<c r="A1" s="1" t="s">
<v>0</v>
</c>
</row>

Create an issue on the bug tracker
https://bitbucket.org/openpyxl/openpyxl/issues and post what you see there
- you can modify the values so nothing sensitive is revealed.

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

[ders...@gmail.com]

HI Charlie,

i again have almost the same problem, but in this case i updated python to 3.4.1 and also openpyxl to 2.0.5

I attached you a cleaned file, when I now open it, it gives me some more errors:

Do you see any solution for this file?

Traceback (most recent call last):

  File "<pyshell#18>", line 1, in <module>

    wb = openpyxl.load_workbook(r"C:\Users\Florian\Desktop\report\12.07.2014\savings_edited.xlsx")

  File "C:\Python341\lib\site-packages\openpyxl\reader\excel.py", line 165, in load_workbook

    _load_workbook(wb, archive, filename, use_iterators, keep_vba)

  File "C:\Python341\lib\site-packages\openpyxl\reader\excel.py", line 212, in _load_workbook

    style_properties = read_style_table(archive.read(ARC_STYLE))

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 222, in read_style_table

    p.parse()

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 39, in parse

    self.font_list = list(self.parse_fonts())

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 91, in parse_fonts

    yield self.parse_font(node)

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 106, in parse_font

    return Font(**font)

  File "C:\Python341\lib\site-packages\openpyxl\styles\fonts.py", line 86, in __init__

    self.family = family

  File "C:\Python341\lib\site-packages\openpyxl\styles\hashable.py", line 54, in __setattr__

    return object.__setattr__(self, *args, **kwargs)

  File "C:\Python341\lib\site-packages\openpyxl\descriptors\__init__.py", line 87, in __set__

    super(Min, self).__set__(instance, value)

  File "C:\Python341\lib\site-packages\openpyxl\descriptors\__init__.py", line 66, in __set__

    raise ValueError('Max value is {0}'.format(self.max))

ValueError: Max value is 14

[ders...@gmail.com]

I JUST SOLVED IT. ITS THE FONT :)

thanks anyway! 

[Charlie Clark]

Am .08.2014, 21:29 Uhr, schrieb <ders...@gmail.com>:

> HI Charlie,
> i again have almost the same problem, but in this case i updated python
> to
> 3.4.1 and also openpyxl to 2.0.5
> I attached you a cleaned file, when I now open it, it gives me some more
> errors:
> Do you see any solution for this file?

That is a blatant specification violation:

<xsd:simpleType name="ST_FontFamily">
<xsd:restriction base="xsd:integer">
<xsd:minInclusive value="0"/>
<xsd:maxInclusive value="14"/>
</xsd:restriction>
</xsd:simpleType>

This is currently implemented strictly (what's the point of specs if you
don't try and enforce them? answer: when Microsoft writes them) which is
why you get the error. Please file an issue with the Apache library.

You can easily monkey patch around this in styles/fonts.py

[f.west...@bravosolution.com]

On Tuesday, August 5, 2014 10:58:22 PM UTC+2, Charlie Clark wrote:

Am .08.2014, 21:29 Uhr, schrieb <ders...@gmail.com>:

> HI Charlie,
> i again have almost the same problem, but in this case i updated python  
> to
> 3.4.1 and also openpyxl to 2.0.5
> I attached you a cleaned file, when I now open it, it gives me some more
> errors:
> Do you see any solution for this file?

That is a blatant specification violation:

<xsd:simpleType name="ST_FontFamily">
       <xsd:restriction base="xsd:integer">
          <xsd:minInclusive value="0"/>
          <xsd:maxInclusive value="14"/>
       </xsd:restriction>
</xsd:simpleType>

This is currently implemented strictly (what's the point of specs if you
don't try and enforce them? answer: when Microsoft writes them) which is
why you get the error. Please file an issue with the Apache library.

You can easily monkey patch around this in styles/fonts.py

Hi Charlie,

thank you for your help. 

I actually "monky patched" the fonts.py and increased the max  <xsd:maxInclusive value="14"/>  to 100 I think.

Now that I have done this, I receive another error, where it tells me that the borders are wrong:

Traceback (most recent call last):

  File "<pyshell#1>", line 1, in <module>

    wb = openpyxl.load_workbook(r"xxx.xlsx")

  File "C:\Python341\lib\site-packages\openpyxl\reader\excel.py", line 165, in load_workbook

    _load_workbook(wb, archive, filename, use_iterators, keep_vba)

  File "C:\Python341\lib\site-packages\openpyxl\reader\excel.py", line 212, in _load_workbook

    style_properties = read_style_table(archive.read(ARC_STYLE))

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 222, in read_style_table

    p.parse()

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 41, in parse

    self.border_list = list(self.parse_borders())

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 143, in parse_borders

    yield self.parse_border(border_node)

  File "C:\Python341\lib\site-packages\openpyxl\reader\style.py", line 156, in parse_border

    border[side] = Side(**bside)

  File "C:\Python341\lib\site-packages\openpyxl\styles\borders.py", line 48, in __init__

    self.style = style

  File "C:\Python341\lib\site-packages\openpyxl\styles\hashable.py", line 54, in __setattr__

    return object.__setattr__(self, *args, **kwargs)

  File "C:\Python341\lib\site-packages\openpyxl\descriptors\__init__.py", line 105, in __set__

    raise ValueError("Value must be one of {0}".format(self.values))

ValueError: Value must be one of (None, 'dashDot', 'dashDotDot', 'dashed', 'dotted', 'double', 'hair', 'medium', 'mediumDashDot', 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick', 'thin')

Do you seen any possibility to patch this by myself? Or is the source file so crap? :)

[Charlie Clark]

Am .08.2014, 13:43 Uhr, schrieb <f.west...@bravosolution.com>:

> I actually "monky patched" the fonts.py and increased the max
> <xsd:maxInclusive value="14"/> to 100 I think.

Have you submitted a bug report upstream?

What value does the border have? This is the spec:

<xsd:simpleType name="ST_BorderStyle">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="none"/>
<xsd:enumeration value="thin"/>
<xsd:enumeration value="medium"/>
<xsd:enumeration value="dashed"/>
<xsd:enumeration value="dotted"/>
<xsd:enumeration value="thick"/>
<xsd:enumeration value="double"/>
<xsd:enumeration value="hair"/>
<xsd:enumeration value="mediumDashed"/>
<xsd:enumeration value="dashDot"/>
<xsd:enumeration value="mediumDashDot"/>
<xsd:enumeration value="dashDotDot"/>
<xsd:enumeration value="mediumDashDotDot"/>
<xsd:enumeration value="slantDashDot"/>
</xsd:restriction>
</xsd:simpleType>

You can easily monkey patch the descriptor for additional values as you
need. But, unless, we've missed one the problem should really be fixed in
the library you're using.

[f.west...@bravosolution.com]

Hi Charlie, 

as far as I can see the border style is "none". I attached the styles.xml from the workbook in which the error occurs. But refering to your post, "none" is in the list of allowed variables.

So my assumption is, that the exception is indicating me a wrong error, right? And that the real error comes from somewhere else, right?

 

[Charlie Clark]

Am .08.2014, 15:44 Uhr, schrieb <f.west...@bravosolution.com>:

> Hi Charlie,

> as far as I can see the border style is "none". I attached the styles.xml
> from the workbook in which the error occurs. But refering to your post,
> "none" is in the list of allowed variables.

What a great style:
<borders count="1">
<border>
<left style="none"/>
<right style="none"/>
<top style="none"/>
<bottom style="none"/>
</border>
</borders>

> So my assumption is, that the exception is indicating me a wrong error,
> right? And that the real error comes from somewhere else, right?

The problem is "none" as a string does not correspond to Python's None and
I haven't written something to cast or ignore it. It's allowed even though
it is redundant and useless: a "side" with style "none" is the same as not
having a style for the side. It's Microsoft's very own take on
three-valued logic. You can submit a bug but it's easy to monkey patch the
values to accept "none" as I'm not sure if I can find the energy to fix
this. :-/

[ders...@gmail.com]

Hey Charlie,

good news, I just fixed it for me. I added the variable  BORDER_NONE2 = 'none' to the borders.py and I also added in the "style = Set(values=" BORDER_NONE2.

Thats it :)

Thank you  very much. I've learned a lot

[Charlie Clark]

Am .08.2014, 17:07 Uhr, schrieb <ders...@gmail.com>:

> Hey Charlie,
> good news, I just fixed it for me. I added the variable BORDER_NONE2 =
> 'none' to the borders.py and I also added in the "style = Set(values="
> BORDER_NONE2.
> Thats it

> Thank you very much. I've learned a lot

Hiya Florian,

glad to know you've resolved your problem. I'd still file an issue with
the Apache library: there really is no value in that Border object.

The best thing is probably to intercept the "none" in the __init__ realise
the Border isn't required and not create it.

if style == 'none':
# sigh what did we do to deserve this "specification"?
return

[mdude...@gmail.com]

Hi Charlie,

I am through a similar problem. I am reading a .xlsx file that is being dumped by jasper reports. i am not able to address the values of the file. The file has many merged cells and string values. But each time the output is none 

[Charlie Clark]

Am .09.2014, 09:38 Uhr, schrieb <mdude...@gmail.com>:

> Hi Charlie,
> I am through a similar problem. I am reading a .xlsx file that is being
> dumped by jasper reports. i am not able to address the values of the
> file.
> The file has many merged cells and string values. But each time the
> output
> is none

Actually, the problem doesn't sound that similar as you can open files
without exceptions being raised. Can you submit a bug with a sample file
for us to look at?