support custom data classes in cell value writing
Created originally on Bitbucket by Lars Yunker
From what I gather, openpyxl only supports python builtin classes when writing to cell.value
. While this covers the vast majority of users' needs, it blocks users from writing custom data classes. As an example, I'm trying to provide excel support for a custom class (a value with a unit). This can be represented in an excel spreadsheet by setting the cell value to the numerical value and applying a custom format. Instead, I'm greeted with a ValueError
(line 238 of cell._bind_value
).
I suggest that in the elif value is not None
block there be an attribute check of the provided value for a _openpyxl_format
method (or similarly named) which will expect a value
and number_format
return. If the return fails to produce the expected values then the usual error could be raised. By doing this, you would enable developers to add openpyxl support to their custom classes.
Proposed change to cell._bind_value
:
elif value is not None:
if hasattr(value, '_openpyxl_format'): # if coded with openpyxl support
# retrieve value and number format
value, number_format = value._openpyxl_format()
# if a number format was specified, apply
if number_format is not None:
self.number_format = number_format
else:
raise ValueError("Cannot convert {0!r} to Excel".format(value))
A developer could then add a method like this to their class
def _openpyxl_format(self) -> Tuple[float, str]:
"""adds openpyxl custom formatting support, returning the value and custom number format"""
return self.value, self.custom_format
For example "10.123 mL" would give the return (10.123, '0.0" mL"')
. This correctly sets the value in the cell and applies a valid custom format to the number in Excel. Applying this change appears to work in my brief testing and should not affect other users (unless the method is specifically coded, nothing different will happen). The developer will have to ensure that the custom format is valid, but I see that as a reasonable caveat. Improper specification of this format results in an workbook recovery prompt, but this is identical to the user manually specifying an invalid number format.
I appreciate that I could accomplish the same thing with this code:
cell.value = float(passed_value)
cell.number_format = '0.0" mL"'
but I feel that it is unnecessary to expect users who want to use a custom class and openpyxl together to manually code value writing where the addition of a class method will define the writing behaviour of all instances of that class.
I’m happy to break this out into a branch and create a merge request, but I wanted to open it for discussion before doing that.