From Blake Watson via user@ mailing list on 2016-10-12: Between 3.14 and 3.15 the return from .prettyPrint, when passed a ValidationConstraint with a list range in it changed. This appears not to be noted in the Changes log. Formerly, a list would be returned as: list [$A$1:$Z$26] And is now returned as: list "$A$1:$Z$26" Brackets replaced with quotes.
This changed was introduced in r1749129 [1] as part of bug 59719 and may or may not have been intentional. > - final String QUOTE = ""; > + > if (validationType == ValidationType.LIST && explicitListOfValues != null) { > builder.append(QUOTE).append(Arrays.asList(explicitListOfValues)).append(QUOTE).append(' '); > } else { > builder.append(QUOTE).append(formula1).append(QUOTE).append(' '); > } > if (formula2 != null) { > builder.append(QUOTE).append(formula2).append(QUOTE).append(' '); > } I removed the empty-string QUOTE variable, replacing it with a class-scoped QUOTE variable (defined as a double-quote), thinking that it was a mistake. I have reverted this in r1764822. The other change was in the constructor when parsing the formula. This change was made to handle parsing of static list text. > //FIXME: Need to confirm if this is not a formula. > - if( ValidationType.LIST==validationType) { > - explicitListOfValues = formula1.split(","); > + // empirical testing shows Excel saves explicit lists surrounded by double quotes, > + // range formula expressions can't start with quotes (I think - anyone have a creative counter example?) > + if( ValidationType.LIST==validationType > + && formula1 != null > + && formula1.startsWith(QUOTE) > + && formula1.endsWith(QUOTE) ) { > + final String formulaWithoutQuotes = formula1.substring(1, formula1.length()-1); > + explicitListOfValues = LIST_SPLIT_REGEX.split(formulaWithoutQuotes); > } > Brackets replaced with quotes The only way this could be happening is if > builder.append(QUOTE).append(formula1).append(QUOTE).append(' '); is executing instead of > builder.append(QUOTE).append(Arrays.asList(explicitListOfValues)).append(QUOTE).append(' '); If "$A$1:$Z$26" is meant to be a range reference, per bug 59719 Excel would save this formula without quotes and prettyPrint would not include brackets > list $A$1:$Z$26 If "$A$1:$Z$26" is meant to be a text list literal, per bug 59719 Excel would save this formula with quotes and prettyPrint would include brackets > list [$A$1:$AZ26] I'll leave this open for discussion to decide what the correct behavior here is. I made a few additional changes in r1764831. [1] https://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFDataValidationConstraint.java?r1=947644&r2=1749129