broken worksheet when validation is not applied to any cell

Issue #837 resolved
Björn Quast
created an issue

As of 2.4 openpyxl produces a broken worksheet when a data validation is added but not used by any cell:

float_validation = DataValidation(type='decimal')
worksheet.add_data_validation(float_validation)

# when the validation is not used later on 
# not executed: float_validation.add(cell)

When opening the resulting excel table, Excel complains about unreadable content in that worksheet. This applies to Excel 2010. Sorry, I have only a german language version of Excel, thus the attached image of the warning message is in german. One can choose to trust the excel worksheet.

excel_error_validation.png

Comments (4)

  1. CharlieC

    Thanks for the report. I think this is as much about the API and documentation as anything else. Prior to 2.4 any validations without SQREFs (the range of cells to which they apply) would be silently removed when saving the file.

    2.4 moved to a much closer implementation of the actual specification, making it more both more reliable and flexible, while maintaining the existing API that allows validations to be created without initially specifying the cell range allowing these to be specified using the relevant methods.

    If no cells are specified then Excel will simply remove the validation and complain about it. The alternative for openpyxl would be to raise an exception when serialising a validation or to change the API to require at least always a single cell to be specified. I don't use validations myself so I'm not particularly sure what would be best. Maybe it would be sufficient to add a note to the documentation and the leave it to users.

    Another necessary change at some point is to change the behaviour when adding cells to allow validations to apply to discrete cells and not always create a range covering them, ie. 'A1' and 'A3' but not 'A2'

  2. Log in to comment