support custom data classes in cell value writing

Issue #1320 new
Lars Yunker created an issue

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
        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.

Comments (1)

  1. CharlieC

    Not having come across this kind of request before I suspect YAGNI is the answer. As you note, the work in client code is trivial and it also clear who’s responsible for what. And, eugh, type annotations! 😉

  2. Log in to comment