Loading workbook error: float() argument must be a string or a number, not 'NoneType'

Issue #885 resolved
megavolts
created an issue

Hello,

I have a script which importe several workbook in a row. However, the importation work only for one notebook LAB_CRREL_-20141112B.xlsx and failed with the same error for the other one, for example LAB_CRREL-2014110A.xlsx. I have tried to examine the .xlsx files to see what differences there. As they are all based on the same template, I wasn't able to find significant difference, beside the numbers.

(edit 2017-08-31 6:11pm) After downgrading openpyxl to 2.4.1, the problem dissapeared. Version 2.4.8 works well too.

All the workbook are based on the same template, only float value varies in them.

Code:

import openpyxl

wb1 = openpyxl.load_workbook('/mnt/data_local/UAF/data/RSOI/core/data/LAB_CRREL_-20141112B.xlsx')

wb2 = openpyxl.load_workbook('/mnt/data_local/UAF/data/RSOI/core/data/LAB_CRREL_-20141110A.xlsx')


Here is the error messages:

`
Traceback (most recent call last):

File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/base.py", line 57, in _convert
    value = expected_type(value)

TypeError: float() argument must be a string or a number, not 'NoneType'

During handling of the above exception, another exception occurred:
Traceback (most recent call last):

  File "/usr/lib/python3.6/site-packages/IPython/core/interactiveshell.py", line 2862, in run_code exec(code_obj, self.user_global_ns, self.user_ns)

  File "<ipython-input-23-e7757d6ead32>", line 1, in <module>
    openpyxl.load_workbook('/mnt/data_local/UAF/data/RSOI/core/data/LAB_CRREL-20141112B.xlsx')

  File "/usr/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 274, in load_workbook for c in find_charts(archive, rel.target):

  File "/usr/lib/python3.6/site-packages/openpyxl/chart/reader.py", line 52, in find_charts
    cs = get_rel(archive, deps, rel.id, ChartSpace)

  File "/usr/lib/python3.6/site-packages/openpyxl/packaging/relationship.py", line 158, in get_rel obj = cls.from_tree(tree)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py", line 83, in from_tree obj = desc.expected_type.from_tree(el)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py", line 83, in from_tree obj = desc.expected_type.from_tree(el)

  File "/usr/lib/python3.6/site-packages/openpyxl/chart/plotarea.py", line 146, in from_tree self = super(PlotArea, cls).from_tree(node)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py", line 83, in from_tree obj = desc.expected_type.from_tree(el)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py", line 83, in from_tree obj = desc.expected_type.from_tree(el)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py", line 83, in from_tree obj = desc.expected_type.from_tree(el)

  [Previous line repeated 2 more times]

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py", line 99, in from_tree return cls(**attrib)

  File "/usr/lib/python3.6/site-packages/openpyxl/chart/data_source.py", line 62, in __init__
    self.v = v

  File "/usr/lib/python3.6/site-packages/openpyxl/chart/data_source.py", line 46, in __set__
    super(NumberValueDescriptor, self).__set__(instance, value)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/nested.py", line 36, in __set__
    super(Nested, self).__set__(instance, value)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/base.py", line 69, in __set__
    value = _convert(self.expected_type, value)

  File "/usr/lib/python3.6/site-packages/openpyxl/descriptors/base.py", line 59, in _convert
    raise TypeError('expected ' + str(expected_type))

TypeError: expected <class 'float'>

`

Comments (21)

  1. CharlieC

    The problem is related to reading a chart. Support for this was only introduced in version 2.5 which is why you're not seeing any problems with 2.4.x. Can you supply a sample file? Otherwise it will be very difficult to debug.

  2. megavolts reporter

    Sorry about that, I was sure I had previously attached the file as I give the name in the description.

    I looked at the files, using libreoffice. The original template of the spreadsheet was to display salinity and temperature graphs in the sheets S_figure and T_figure. I quickly looked through several files, and only LAB_CRREL-20141112B.xlsx which did not rise any error has a figure. I am wondering if there is some leftover information in the file about the presence of a graph, which may explaine the None rather than float data.

  3. CharlieC

    It looks very much to me like this is a problem with the files themselves which were created with LibreOffice. If pass the files through MS Excel then there is no problem opening them. The issue itself is related to handling empty values in y-axis of the second chart. The specification says that NumVal elements must have a v child which must contain a string.

    For comparison the original

            <c:pt idx="1">
             <c:v>
    
             </c:v>
    

    In Excel this becomes

            <c:pt idx="1">
             <c:v>
              0.0
             </c:v>
    
  4. megavolts reporter

    Hi Charlie,

    Thanks for the precision. The file was first created with MS Excel and then modified with LibreOffice. I will report this bug to LibreOffice.

    However, with the number of people using LibreOffice nowadays, if it is easy for you to implement a solution to this issue. Maybe raising a warning, rather than an error it would be great.

  5. CharlieC

    I've added a fix to 2.5 because the XML specification does allow an empty string and Excel already abuses this field to include #N/A. But in the larger context: LibreOffice unfortunately gets a lot of things wrong and I won't ever be adding workarounds for stuff that does not conform with the specification.

  6. Log in to comment