Bug 61624 - Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS
Summary: Formula COUNTIFS not converted to Excel French Version, the formula don't tra...
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: PC All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-10-17 08:42 UTC by Herve
Modified: 2017-12-28 13:55 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Herve 2017-10-17 08:42:24 UTC
My formula : &(COUNTIFS(G5:G{0},"DISPO",T5:T{0},"IDF",C5:C{0},"<>STOP")+COUNTIFS(G5:G{0},"DISPO PA",T5:T{0},"IDF",C5:C{0},"<>STOP")+COUNTIFS(G5:G{0},"DISPO PARTIELLE",T5:T{0},"IDF",C5:C{0},"<>STOP")/2)&"  / dont PA : "&COUNTIFS(G5:G{0},"DISPO PA",T5:T{0},"IDF",C5:C{0},"<>STOP")

My code :
Cell cellG = myRow.createCell(7);
    cellG.setCellFormula(formuleXSemaine);
    FormulaEvaluator evaluatorG = workbook.getCreationHelper().createFormulaEvaluator();
    CellValue evaluateG = evaluatorG.evaluate(cellG);

When i evaluate my formule the result is good, but when i open my XLS File the formula doesn't work i have #NOM? and COUNTIFS isn't converted to NB.SI.ENS
Comment 1 Greg Woolsey 2017-10-17 23:16:13 UTC
POI doesn't do any formula translation, that's all done in Excel UI localization code.  

POI operates only on the OOXML file, and the OOXML spec (and all files saved by internationalized versions of Excel) save all formulas and formats in US locale forms.

Thus if you want to set a formula for a cell using POI, you need to set the US English function names, the ones listed as supported by POI [1]

[1] https://poi.apache.org/spreadsheet/formula.html
Comment 2 Herve 2017-10-18 14:03:22 UTC
COUNTIFS is not supported, so i will write FROMULA in FRENCH VERSION and users must execute in local the formule
Thanks for your answers
Comment 3 Javen O'Neal 2017-10-18 15:04:40 UTC
(In reply to Herve from comment #2)
> COUNTIFS is not supported

The COUNTIFS function *is* supported for XLSX files as part of the AnalysisToolPak, which must be loaded at runtime. COUNTIFS is not available in XLS.

Supported for COUNTIFS was added in POI 3.10 via bug 55873
https://poi.apache.org/changes.html#3.10-FINAL

Supported functions:
https://poi.apache.org/spreadsheet/eval-devguide.html#Appendix+A

Implementation
https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countifs.java

AnalysisToolPak:
https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java

Unit tests:
https://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/ss/formula/functions/CountifsTests.java

OOXML saves formulas as strings instead of binary Ptgs, so it's possible that your version of Excel expects the function names to be translated into your locale.

The curly brackets ({0}) in your formula don't look like they're part of the standard Excel formula language spec and very likely not supported by POI. Maybe this is the source of the problem.
Comment 4 Herve 2017-10-18 16:09:21 UTC
Perfect ! My formula works !
Thanks you for your quickly help !
Comment 5 Javen O'Neal 2017-10-18 16:23:45 UTC
I'm curious if there is some language translation that should be performed for non-English Locales when reading or writing an XLS or XLSX file.

If there's anything that POI can reasonably do to make this easier, I'd be happy to entertain the idea.
Comment 6 Greg Woolsey 2017-10-18 17:30:57 UTC
If I remember right, in the past I've experimented a bit and noticed that Excel always saves things in US-English locale.  That may even be part of the OOXML spec.  For example, number formats are saved with . for decimal separator and , for grouping, and translated dynamically by the UI based on Windows Locale settings.  Same with formula function names I think.

I tried once to force different number conventions, but couldn't do it, having to tell users to make their Windows settings match the display they wanted.
Comment 7 Dominik Stadler 2017-12-28 13:55:01 UTC
Nothing left to do here as far as I see, the spec seems to mandate storing formulas in ENGLISH locale and thus we do it correctly already.