[Bug 61624] New: Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61624] New: Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61624

            Bug ID: 61624
           Summary: Formula COUNTIFS not converted to Excel French
                    Version, the formula don't translate COUNTIFS to
                    NB.SI.ENS
           Product: POI
           Version: 3.17-FINAL
          Hardware: PC
            Status: NEW
          Severity: critical
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61624] Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61624

Greg Woolsey <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All

--- Comment #1 from Greg Woolsey <[hidden email]> ---
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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61624] Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61624

--- Comment #2 from Herve <[hidden email]> ---
COUNTIFS is not supported, so i will write FROMULA in FRENCH VERSION and users
must execute in local the formule
Thanks for your answers

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61624] Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61624

--- Comment #3 from Javen O'Neal <[hidden email]> ---
(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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61624] Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61624

--- Comment #4 from Herve <[hidden email]> ---
Perfect ! My formula works !
Thanks you for your quickly help !

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61624] Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61624

--- Comment #5 from Javen O'Neal <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 61624] Formula COUNTIFS not converted to Excel French Version, the formula don't translate COUNTIFS to NB.SI.ENS

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61624

--- Comment #6 from Greg Woolsey <[hidden email]> ---
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.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]