Apache Poi createFormulaListConstraint Results in "Unreadable Content" When Opening Excel
Like the Title Says: When I Try to Add a Datavalidationconstraint Using Xssfdatavalidationhelper's Method createFormulaListConstraint(". ..") the Excel File's...
Like the title says: when I try to add a DataValidationConstraint using XSSFDataValidationHelper's method createFormulaListConstraint("...") the excel file's content is incorrect according to the pop-up when I try to open it in MS Excel.
When I create a data validation using excel this formula works as expected:
=OFFSET(Sites!$A$2;0;0;COUNTA(Sites!$A:$A)- 1;1)
Now I am trying to do the same using apache poi 3.8:
XSSFDataValidationConstraint siteNamesConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint("=OFFSET('Sites'!$A$2;0;0;COUNTA('Sites'!$A:$A)- 1;1)");
I get the error when opening the excel file.
I have tried other formulas as well:
- =OFFSET('Sites'!$A$2;0;0;COUNTA('Sites'!$A:$A)- 1;1) (with '' around the sheet names, with '=')
- OFFSET('Sites'!$A$2;0;0;COUNTA('Sites'!$A:$A)- 1;1) (with '' around the sheet names, no '=')
- =OFFSET(Sites!$A$2;0;0;COUNTA(Sites!$A:$A)- 1;1) (no '' around the names, with '=')
- OFFSET(Sites!$A$2;0;0;COUNTA(Sites!$A:$A)- 1;1) (no '' around the names, no '=')
None of these seem to be correct.
What I had before this was just 'Sites'!$A$2:$A$100 and this worked but it included blank values in the result which is not what I need.
Am I doing something wrong here or is there another way not to include blank values in the list?
1 Answer
Sigh, I just found the solution 5 minutes after posting this question.
When typing formulas in excel ";" is used as a delimiter in functions, in POI however the delimiter is ",".
I have discovered this when trying the following code:
Name namedRange = wb.createName();
namedRange.setNameName("sites");
namedRange.setRefersToFormula("OFFSET('Sites'!$A$2,0,0,COUNTA('Sites'!$A:$A)- 1,1)");
And then referring to it with:
(XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint("sites");
setRefersToFormula of the 'Name' class provides much better error handling and displayed what was wrong with my formula ("," expected but ";" found).
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
