DO NOT REPLY [Bug 52462] Add support for SUMIFS function

[bu...@apache.org]

https://issues.apache.org/bugzilla/show_bug.cgi?id=52462

--- Comment #14 from Yegor Kozlov <ye...@dinom.ru> 2012-02-03 10:21:07 UTC ---
Unfortunately I cannot easily fix it in HSSF. 

Strictly speaking, the SUMIFS function is not supported by the binary .xls
format and this is why POI writes incorrect data and the resulting formula is
not recognized by Excel.

SUMIFS is not a 'normal' function like MAX, SUM or SUMIF: it is included in the
Excel Analysis Toolpack (ATP) which is an Add-On and installed separately. From
this point of view, the Analysis Toolpack is like a VBA module, only in
pre-compiled form. 
The primary scope of the support for ATP functions in POI is evaluation, that
is, POI recognizes functions with SUMIFS and can evaluate them. Setting a
formula with ATP functions is another task and, I should say, a difficult one.
The main difficulty is when you create a formula with ATP functions then Excel
inserts supplemental data structures - in my test files these were defined
named ranges with names like "_xlfn." and these named ranges prefix the ATP
function names. To make it clear, the internal name of formula with ATP
functions look like _xlfn.SUMIFS(A2:A9,B2:B9,"=A*"). POI writes "pure" tokens
and the formula is written as SUMIFS(A2:A9,B2:B9,"=A*"). Evidently it is not
enough to make Excel happy.

This feature is not documented in the .xls spec and I don't see how to easily
implement it. 


Regards,
Yegor

-- 
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org