Cannot write cell value using numpy

Issue #1181 resolved
Cory Kramer
created an issue

The following code works without issue

import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.create_sheet('Test')
sheet.cell(row=1, column=1).value = 1.0

However the following version of this code causes an error

import numpy
import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.create_sheet('Test')
a = numpy.array([1.0])
sheet.cell(row=1, column=1).value = a[0]

In the latter case, the value being assigned is of type numpy.float64. In the Cell._bind_value method, this type is not considered to be NUMERIC_TYPES so therefore causes a ValueError at the end of the method.

Comments (5)

  1. KaKiLa

    This isn't a good solution. It seems that the objective of using numpy.floating (which include all of these types) in NUMERIC_TYPES was to use isinstance directly on the value. However, _bind_value is manually extracting the type and checking if it is in NUMERIC_TYPES. Doesn't sound goo. I think _bind_value should be improved.

    I am not sure of other consequences but what about

        def _bind_value(self, value):
            """Given a value, infer the correct data type"""
    
            self.data_type = "n"
            t = type(value)
    
            if isinstance(value, NUMERIC_TYPES):
                pass
    
            elif isinstance(value, TIME_TYPES):
                if not is_date_format(self.number_format):
                    self.number_format = TIME_FORMATS[t]
                self.data_type = "d"
    
            elif isinstance(value, STRING_TYPES):
                value = self.check_string(value)
                self.data_type = 's'
                if len(value) > 1 and value.startswith("="):
                    self.data_type = 'f'
                elif value in ERROR_CODES:
                    self.data_type = 'e'
                elif self.guess_types: # deprecated
                    value = self._infer_value(value)
    
            elif isinstance(value, bool):
                self.data_type = 'b'
    
            elif value is not None:
                raise ValueError("Cannot convert {0!r} to Excel".format(value))
    
            self._value = value
    

    I only checked with the line checking for numeric types and it works without adding types to the list.

  2. KaKiLa

    I have been trying to post a changeset against branch 2.6, but I get many failures with pytest:

    $ pytest  openpyxl/cell
    ============================= test session starts ==============================
    platform linux -- Python 3.6.7, pytest-4.2.1, py-1.7.0, pluggy-0.8.1
    rootdir: /home/juanpi/Devel/openpyxl, inifile: pytest.ini
    collected 118 items                                                            
    
    openpyxl/cell/tests/test_cell.py .............FF..............F..x...... [ 33%]
    ..................                                                       [ 48%]
    openpyxl/cell/tests/test_read_only.py ........                           [ 55%]
    openpyxl/cell/tests/test_text.py ............                            [ 65%]
    openpyxl/cell/tests/test_writer.py ....F.......F........................ [ 96%]
    ....                                                                     [100%]
    

    However, when I tried the changes and I do not see any change in the (F)ailures reported. Hence I assume the changes do work. The changeset is below(also made consistent use of single quotes in _bind_value):

    # HG changeset patch
    # User Juan Pablo Carbajal <ajuanpi+dev@gmail.com>
    # Date 1550506504 -3600
    #      Mon Feb 18 17:15:04 2019 +0100
    # Branch 2.6
    # Node ID 0981561b880a96f8af556835831a73cbca0530ce
    # Parent  cc99242800ac8d51f9e4b5361bb584520c011a48
    use isinstance in cell _bind_value
    
    diff --git a/openpyxl/cell/cell.py b/openpyxl/cell/cell.py
    --- a/openpyxl/cell/cell.py
    +++ b/openpyxl/cell/cell.py
    @@ -190,18 +190,17 @@
         def _bind_value(self, value):
             """Given a value, infer the correct data type"""
    
    -        self.data_type = "n"
    -        t = type(value)
    +        self.data_type = 'n'
    
    -        if t in NUMERIC_TYPES:
    +        if isinstance(value, NUMERIC_TYPES):
                 pass
    
    -        elif t in TIME_TYPES:
    +        elif isinstance(value, TIME_TYPES):
                 if not is_date_format(self.number_format):
    -                self.number_format = TIME_FORMATS[t]
    -            self.data_type = "d"
    +                self.number_format = TIME_FORMATS[type(value)]
    +            self.data_type = 'd'
    
    -        elif t in STRING_TYPES:
    +        elif isinstance(value, STRING_TYPES):
                 value = self.check_string(value)
                 self.data_type = 's'
                 if len(value) > 1 and value.startswith("="):
    @@ -211,7 +210,7 @@
                 elif self.guess_types: # deprecated
                     value = self._infer_value(value)
    
    -        elif t is bool:
    +        elif isinstance(value, bool):
                 self.data_type = 'b'
    
             elif value is not None:
    diff --git a/openpyxl/compat/numbers.py b/openpyxl/compat/numbers.py
    --- a/openpyxl/compat/numbers.py
    +++ b/openpyxl/compat/numbers.py
    @@ -21,35 +21,7 @@
    
    
     if NUMPY:
    -    NUMERIC_TYPES = NUMERIC_TYPES + (numpy.short,
    -                                     numpy.ushort,
    -                                     numpy.intc,
    -                                     numpy.uintc,
    -                                     numpy.int_,
    -                                     numpy.uint,
    -                                     numpy.longlong,
    -                                     numpy.ulonglong,
    -                                     numpy.half,
    -                                     numpy.float16,
    -                                     numpy.single,
    -                                     numpy.double,
    -                                     numpy.longdouble,
    -                                     numpy.int8,
    -                                     numpy.int16,
    -                                     numpy.int32,
    -                                     numpy.int64,
    -                                     numpy.uint8,
    -                                     numpy.uint16,
    -                                     numpy.uint32,
    -                                     numpy.uint64,
    -                                     numpy.intp,
    -                                     numpy.uintp,
    -                                     numpy.float32,
    -                                     numpy.float64,
    -                                     numpy.float,
    -                                     numpy.bool_,
    -                                     numpy.floating,
    -                                     numpy.integer)
    +    NUMERIC_TYPES = NUMERIC_TYPES + (numpy.bool_, numpy.floating, numpy.integer)
    
    
     try:
    
  3. Log in to comment