Bug 59200 - Creating a data validation with prompt text longer than max leads to file failing office validation
Summary: Creating a data validation with prompt text longer than max leads to file fai...
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.13-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
: 54662 (view as bug list)
Depends on:
Reported: 2016-03-18 14:57 UTC by Morgan Hull
Modified: 2020-02-09 19:29 UTC (History)
2 users (show)


Note You need to log in before you can comment on or make changes to this bug.
Description Morgan Hull 2016-03-18 14:57:12 UTC
If you create a DataValidation with a prompt text longer than the excel maximum (seems to be 32 characters) POI allows this to be made.  However, when opening the file with excel, assuming protected view is enabled, excel will go to protected view.  Furthermore, if you open up the data validation from excel you will not be able to edit the prompt title.
Comment 1 Dominik Stadler 2016-03-28 19:33:35 UTC
Can you provide a unit-test or at least a code-snippet that shows how you create the invalid data validation? This would make it much easier for others to reproduce the problem.
Comment 2 Morgan Hull 2016-03-28 19:47:55 UTC
Sorry if this isn't in a reasonable format, I'm completely new to this.

        String promptTitle;
        DataValidation dataValidation;
        CellRangeAddressList headerCell = new CellRangeAddressList(ImportFileBundleSheets.FIRST_ROW_INDEX, ImportFileBundleSheets.FIRST_ROW_INDEX, columnNum, columnNum);
        DataValidationConstraint constraint = sheet.getDataValidationHelper().createCustomConstraint("A1<>\"\"");

        dataValidation = new HSSFDataValidation(headerCell, constraint);
        promptTitle = PortalServices.getI18nString(adminLocale, GroupImportConstants.PROMPT_TITLE);

        dataValidation.createPromptBox("Some long title that is greater than 32 characters in length", promptDescription);
Comment 3 Dominik Stadler 2016-11-25 08:57:46 UTC
Fixed via r1771254, the spec states a limit for HSSF of 32 length for title and 255 length for text. For XSSF it does not state a limit as far as I saw, but Excel imposes a 255 length limit for both title and text, so I applied this limit as well here.
Comment 4 Javen O'Neal 2016-11-29 01:45:00 UTC
(In reply to Morgan Hull from comment #2)
> Sorry if this isn't in a reasonable format, I'm completely new to this.

It's fine to submit small, trivial changes as a comment, preferably prefixing each line with "> " so that Bugzilla doesn't text-wrap the code.

If your changes get much bigger or it's ambiguous *what* you're suggesting should be changed, there's an easier way.

Check out the source code using SVN [1], save your changes in the source code, then save an svn patch file ("svn diff > patch.txt" or using a graphical client). Then upload the patch file.
See [2] for more info.

Thanks for contributing!

[1] https://poi.apache.org/subversion.html
[2] https://poi.apache.org/guidelines.html#Submitting+Patches
Comment 5 Dominik Stadler 2020-02-09 19:29:23 UTC
*** Bug 54662 has been marked as a duplicate of this bug. ***