Bug 60253 - XSSFValidationConstraint.prettyPrint() changed from 3.14 to 3.15
Summary: XSSFValidationConstraint.prettyPrint() changed from 3.14 to 3.15
Status: NEEDINFO
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P4 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-10-14 06:38 UTC by Javen O'Neal
Modified: 2016-10-14 08:10 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Javen O'Neal 2016-10-14 06:38:43 UTC
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.
Comment 1 Javen O'Neal 2016-10-14 08:10:58 UTC
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