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
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
COUNTIFS is not supported, so i will write FROMULA in FRENCH VERSION and users must execute in local the formule Thanks for your answers
(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.
Perfect ! My formula works ! Thanks you for your quickly help !
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.
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.
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.