Skip to content
Advertisement

sheet.getDataValidations() returns an empty list when a cell is validated by a sequence on another sheet

I have a workbook with two sheets:

  1. Sheet1
  2. Sheet2

And there is a sequence on Sheet2 at the range of A1 to A5:

  • aa
  • bb
  • cc
  • dd
  • ee

And in Sheet1, the cell A1 is validated by the sequence in Sheet2.

Excel screenshot

However, sheet.getDataValidations() returns an empty list for this case. Did I miss something?

JavaScript

“[] 0” was printed out.

Advertisement

Answer

Apache POI bases on Office Open XML published for Excel 2007. And Excel 2007 had not supported data validation list constraint coming directly from another worksheet. There had must be created a named range for the data validation list constraint. Now current Excel versions support data validation list constraint coming directly from another worksheet but of course not backwards compatible. That’s why apache poi also cannot read those constraints as it only reads CTDataValidations which are from Office Open XML published for Excel 2007.

In the XML the difference looks like so in /xl/worksheets/sheet1.xml:

Excel 2007:

JavaScript

There “Sheet2_A1_A5” is a named range in the workbook that points to Sheet2!A1:A5.

Excel 365:

JavaScript

There “Sheet2!A1:A5” is a direct reference to the other worksheet.

As you see, the new x14:dataValidation is in a separate name space. This is not covered by apache poi until now.

What one could do is using low level XML parsing methods to get the new XSSFX14DataValidations additional to the XSSFDataValidations. The following example shows a working draft for how to do this.

JavaScript
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement