incubator-ooo-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ar...@apache.org
Subject svn commit: r1381447 - in /incubator/ooo/trunk/main: formula/inc/formula/ formula/source/core/resource/ formula/source/ui/dlg/ sc/inc/ sc/source/core/data/ sc/source/core/inc/ sc/source/core/tool/ sc/source/ui/formdlg/ sc/source/ui/src/ sc/source/ui/un...
Date Thu, 06 Sep 2012 00:37:35 GMT
Author: arist
Date: Thu Sep  6 00:37:34 2012
New Revision: 1381447

URL: http://svn.apache.org/viewvc?rev=1381447&view=rev
Log:
calcishmakkica: #i90269# #i95144# #i101466# implement SUMIFS, AVERAGEIFS, COUNTIFS; patch
from <makkica>

From: Eike Rathke <erack@apache.org>

Original Author: Marina Plakalovic <makkica@openoffice.org>
Original Committer: Eike Rathke [er] <eike.rathke@oracle.com>

# HG changeset patch
# User Eike Rathke [er] <eike.rathke@oracle.com>
# Date 1288810126 -3600
# Node ID 02cf226fcde498f6fd926d45df497e9fb412fe0f
# Parent  528da6bfd0daed4355d745590d5ac3a319b08fb4

Modified:
    incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc
    incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx
    incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src
    incubator/ooo/trunk/main/formula/source/ui/dlg/parawin.cxx
    incubator/ooo/trunk/main/sc/inc/helpids.h
    incubator/ooo/trunk/main/sc/inc/sc.hrc
    incubator/ooo/trunk/main/sc/source/core/data/global.cxx
    incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx
    incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx
    incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx
    incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx
    incubator/ooo/trunk/main/sc/source/ui/formdlg/dwfunctr.cxx
    incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src
    incubator/ooo/trunk/main/sc/source/ui/unoobj/appluno.cxx
    incubator/ooo/trunk/main/sc/util/hidother.src

Modified: incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc (original)
+++ incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc Thu Sep  6 00:37:34 2012
@@ -390,9 +390,12 @@
 #define SC_OPCODE_CHISQ_INV         394
 #define SC_OPCODE_AVERAGE_IF        395
 #define SC_OPCODE_XOR               396
-#define SC_OPCODE_STOP_2_PAR        397
+#define SC_OPCODE_SUM_IFS           397
+#define SC_OPCODE_AVERAGE_IFS       398
+#define SC_OPCODE_COUNT_IFS         399
+#define SC_OPCODE_STOP_2_PAR        400
 
-#define SC_OPCODE_LAST_OPCODE_ID    396      /* last OpCode */
+#define SC_OPCODE_LAST_OPCODE_ID    399      /* last OpCode */
 
 /*** Interna ***/
 #define SC_OPCODE_INTERNAL_BEGIN   9999

Modified: incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx (original)
+++ incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx Thu Sep  6 00:37:34 2012
@@ -275,6 +275,9 @@ enum OpCodeEnum
 		ocCountIf			= SC_OPCODE_COUNT_IF,
 		ocSumIf				= SC_OPCODE_SUM_IF,
 		ocAverageIf			= SC_OPCODE_AVERAGE_IF,
+		ocSumIfs			= SC_OPCODE_SUM_IFS,
+		ocAverageIfs		= SC_OPCODE_AVERAGE_IFS,
+		ocCountIfs			= SC_OPCODE_COUNT_IFS,
 		ocLookup			= SC_OPCODE_LOOKUP,
 		ocVLookup			= SC_OPCODE_V_LOOKUP,
 		ocHLookup			= SC_OPCODE_H_LOOKUP,

Modified: incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src (original)
+++ incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src Thu Sep  6 00:37:34
2012
@@ -242,6 +242,9 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGL
 	String SC_OPCODE_COUNT_IF { Text = "COUNTIF" ; };
 	String SC_OPCODE_SUM_IF { Text = "SUMIF" ; };
 	String SC_OPCODE_AVERAGE_IF { Text = "AVERAGEIF" ; };
+	String SC_OPCODE_SUM_IFS { Text = "SUMIFS" ; };
+	String SC_OPCODE_AVERAGE_IFS { Text = "AVERAGEIFS" ; };
+	String SC_OPCODE_COUNT_IFS { Text = "COUNTIFS" ; };
 	String SC_OPCODE_LOOKUP { Text = "LOOKUP" ; };
 	String SC_OPCODE_V_LOOKUP { Text = "VLOOKUP" ; };
 	String SC_OPCODE_H_LOOKUP { Text = "HLOOKUP" ; };
@@ -572,6 +575,9 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGL
 	String SC_OPCODE_COUNT_IF { Text = "COUNTIF" ; };
 	String SC_OPCODE_SUM_IF { Text = "SUMIF" ; };
 	String SC_OPCODE_AVERAGE_IF { Text = "AVERAGEIF" ; };
+	String SC_OPCODE_SUM_IFS { Text = "SUMIFS" ; };
+	String SC_OPCODE_AVERAGE_IFS { Text = "AVERAGEIFS" ; };
+	String SC_OPCODE_COUNT_IFS { Text = "COUNTIFS" ; };
 	String SC_OPCODE_LOOKUP { Text = "LOOKUP" ; };
 	String SC_OPCODE_V_LOOKUP { Text = "VLOOKUP" ; };
 	String SC_OPCODE_H_LOOKUP { Text = "HLOOKUP" ; };
@@ -1471,6 +1477,18 @@ Resource RID_STRLIST_FUNCTION_NAMES
 	{
 		Text [ en-US ] = "AVERAGEIF" ;
 	};
+	String SC_OPCODE_SUM_IFS
+	{
+		Text [ en-US ] = "SUMIFS" ;
+	};
+	String SC_OPCODE_AVERAGE_IFS
+	{
+		Text [ en-US ] = "AVERAGEIFS" ;
+	};
+	String SC_OPCODE_COUNT_IFS
+	{
+		Text [ en-US ] = "COUNTIFS" ;
+	};
 	String SC_OPCODE_LOOKUP
 	{
 		Text [ en-US ] = "LOOKUP" ;

Modified: incubator/ooo/trunk/main/formula/source/ui/dlg/parawin.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/formula/source/ui/dlg/parawin.cxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/formula/source/ui/dlg/parawin.cxx (original)
+++ incubator/ooo/trunk/main/formula/source/ui/dlg/parawin.cxx Thu Sep  6 00:37:34 2012
@@ -40,6 +40,7 @@
 #include "ForResId.hrc"
 
 #define VAR_ARGS 30
+#define PAIRED_VAR_ARGS (VAR_ARGS + VAR_ARGS)
 namespace formula
 {
 //============================================================================
@@ -126,7 +127,7 @@ void ParaWin::UpdateArgDesc( sal_uInt16 
             aArgName += ' ';
 			aArgName += (pFuncDesc->isParameterOptional(nRealArg)) ? m_sOptional : m_sRequired
;
 		}
-		else
+		else if ( nArgs < PAIRED_VAR_ARGS )
 		{
 			sal_uInt16 nFix = nArgs - VAR_ARGS;
 			sal_uInt16 nPos = ( nArg < nFix ? nArg : nFix );
@@ -140,6 +141,24 @@ void ParaWin::UpdateArgDesc( sal_uInt16 
 
             aArgName += (nArg > nFix || pFuncDesc->isParameterOptional(nRealArg)) ?
m_sOptional : m_sRequired ;
 		}
+		else
+		{
+		    sal_uInt16 nFix = nArgs - PAIRED_VAR_ARGS;
+			sal_uInt16 nPos;
+			if ( nArg < nFix )
+			    nPos = nArg;
+			else
+			    nPos = nFix + ( (nArg-nFix) % 2);
+            sal_uInt16 nRealArg = (nPos < aVisibleArgMapping.size() ?
+                    aVisibleArgMapping[nPos] : aVisibleArgMapping.back());
+			aArgDesc  = pFuncDesc->getParameterDescription(nRealArg);
+			aArgName  = pFuncDesc->getParameterName(nRealArg);
+			if ( nArg >= nFix )
+				aArgName += String::CreateFromInt32((nArg-nFix)/2 + 1);
+            aArgName += ' ';
+
+            aArgName += (nArg > (nFix+1) || pFuncDesc->isParameterOptional(nRealArg))
? m_sOptional : m_sRequired ;
+		}
 
 		SetArgumentDesc(aArgDesc);
 		SetArgumentText(aArgName);
@@ -159,7 +178,7 @@ void ParaWin::UpdateArgInput( sal_uInt16
 			SetArgName		(i,pFuncDesc->getParameterName(nRealArg));
 		}
 	}
-	else
+	else if ( nArgs < PAIRED_VAR_ARGS)
 	{
 		sal_uInt16 nFix = nArgs - VAR_ARGS;
 		sal_uInt16 nPos = ( nArg < nFix ? nArg : nFix );
@@ -177,6 +196,28 @@ void ParaWin::UpdateArgInput( sal_uInt16
 		else
 			SetArgName( i, pFuncDesc->getParameterName(nRealArg) );
 	}
+	else
+	{
+	    sal_uInt16 nFix = nArgs - PAIRED_VAR_ARGS;
+	    sal_uInt16 nPos;
+			if ( nArg < nFix )
+			    nPos = nArg;
+			else
+			    nPos = nFix + ( (nArg-nFix) % 2);
+        sal_uInt16 nRealArg = (nPos < aVisibleArgMapping.size() ?
+                aVisibleArgMapping[nPos] : aVisibleArgMapping.back());
+		SetArgNameFont( i,
+                (nArg > (nFix+1) || pFuncDesc->isParameterOptional(nRealArg)) ?
+                aFntLight : aFntBold );
+		if ( nArg >= nFix )
+		{
+			String aArgName( pFuncDesc->getParameterName(nRealArg) );
+			aArgName += String::CreateFromInt32((nArg-nFix)/2 + 1);
+			SetArgName( i, aArgName );
+		}
+		else
+			SetArgName( i, pFuncDesc->getParameterName(nRealArg) );
+	}
 	if(nArg<nArgs) SetArgVal(i,aParaArray[nArg]);
 	//@ aArgInput[i].SetArgVal( *(pArgArr[nOffset+i]) );
 

Modified: incubator/ooo/trunk/main/sc/inc/helpids.h
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/inc/helpids.h?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/inc/helpids.h (original)
+++ incubator/ooo/trunk/main/sc/inc/helpids.h Thu Sep  6 00:37:34 2012
@@ -497,6 +497,9 @@
 #define HID_FUNC_PRODUKT                                        "SC_HID_FUNC_PRODUKT"
 #define HID_FUNC_SUMMEWENN                                      "SC_HID_FUNC_SUMMEWENN"
 #define HID_FUNC_AVERAGEIF                                      "SC_HID_FUNC_AVERAGEIF"
+#define HID_FUNC_SUMIFS                                         "SC_HID_FUNC_SUMIFS"
+#define HID_FUNC_AVERAGEIFS                                     "SC_HID_FUNC_AVERAGEIFS"
+#define HID_FUNC_COUNTIFS                                       "SC_HID_FUNC_COUNTIFS"
 #define HID_FUNC_ZAEHLENWENN                                    "SC_HID_FUNC_ZAEHLENWENN"
 #define HID_FUNC_WURZEL                                         "SC_HID_FUNC_WURZEL"
 #define HID_FUNC_ZUFALLSZAHL                                    "SC_HID_FUNC_ZUFALLSZAHL"

Modified: incubator/ooo/trunk/main/sc/inc/sc.hrc
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/inc/sc.hrc?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/inc/sc.hrc (original)
+++ incubator/ooo/trunk/main/sc/inc/sc.hrc Thu Sep  6 00:37:34 2012
@@ -37,6 +37,7 @@
 #include "helpids.h"
 
 #define VAR_ARGS				30 // variable Parameter in scfuncs.src
+#define PAIRED_VAR_ARGS			(VAR_ARGS + VAR_ARGS)
 
 #define RID_HANDLEBITMAP		10005
 

Modified: incubator/ooo/trunk/main/sc/source/core/data/global.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/data/global.cxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/data/global.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/data/global.cxx Thu Sep  6 00:37:34 2012
@@ -1123,7 +1123,9 @@ ScFuncRes::ScFuncRes( ResId &aRes, ScFun
 	pDesc->sHelpId = ReadByteStringRes();		//! Hack, see scfuncs.src
 	pDesc->nArgCount = GetNum();
 	sal_uInt16 nArgs = pDesc->nArgCount;
-	if (nArgs >= VAR_ARGS)
+	if (nArgs >= PAIRED_VAR_ARGS)
+	    nArgs -= PAIRED_VAR_ARGS - 2;
+	else if (nArgs >= VAR_ARGS)
         nArgs -= VAR_ARGS - 1;
 	if (nArgs)
 	{
@@ -1149,7 +1151,12 @@ ScFuncRes::ScFuncRes( ResId &aRes, ScFun
             sal_uInt16 nParam = GetNum();
             if (nParam < nArgs)
             {
-                if (pDesc->nArgCount >= VAR_ARGS && nParam == nArgs-1)
+                if (pDesc->nArgCount >= PAIRED_VAR_ARGS && nParam >= nArgs-2)
+                {
+                    DBG_ERROR3( "ScFuncRes: PAIRED_VAR_ARGS parameters can't be suppressed,
on OpCode %u: param %d >= arg %d-2",
+                            aRes.GetId(), (int)nParam, (int)nArgs);
+                }
+                else if (pDesc->nArgCount >= VAR_ARGS && nParam == nArgs-1)
                 {
                     DBG_ERROR3( "ScFuncRes: VAR_ARGS parameters can't be suppressed, on OpCode
%u: param %d == arg %d-1",
                             aRes.GetId(), (int)nParam, (int)nArgs);
@@ -1426,7 +1433,8 @@ ScFuncDesc::~ScFuncDesc()
 void ScFuncDesc::Clear()
 {
     sal_uInt16 nArgs = nArgCount;
-    if (nArgs >= VAR_ARGS) nArgs -= VAR_ARGS-1;
+    if (nArgs >= PAIRED_VAR_ARGS) nArgs -= PAIRED_VAR_ARGS - 2;
+    else if (nArgs >= VAR_ARGS) nArgs -= VAR_ARGS - 1;
     if (nArgs)
     {
         for (sal_uInt16 i=0; i<nArgs; i++ )
@@ -1491,7 +1499,7 @@ String ScFuncDesc::GetParamList() const
                     aSig.Len() >= 2)
                 aSig.Erase( aSig.Len() - 2 );
         }
-        else
+        else if ( nArgCount < PAIRED_VAR_ARGS)
         {
             sal_uInt16 nFix = nArgCount - VAR_ARGS;
             for ( sal_uInt16 nArg = 0; nArg < nFix; nArg++ )
@@ -1516,6 +1524,34 @@ String ScFuncDesc::GetParamList() const
             aSig.Append(sep);
             aSig.AppendAscii(RTL_CONSTASCII_STRINGPARAM( " ... " ));
         }
+        else
+        {
+            sal_uInt16 nFix = nArgCount - PAIRED_VAR_ARGS;
+            for ( sal_uInt16 nArg = 0; nArg < nFix; nArg++ )
+            {
+                if (!pDefArgFlags[nArg].bSuppress)
+                {
+                    aSig += *(ppDefArgNames[nArg]);
+                    aSig.Append(sep);
+                    aSig.AppendAscii(RTL_CONSTASCII_STRINGPARAM( " " ));
+                }
+            }
+
+            aSig += *(ppDefArgNames[nFix]);
+            aSig += '1';
+            aSig.AppendAscii(RTL_CONSTASCII_STRINGPARAM( ", " ));
+            aSig += *(ppDefArgNames[nFix+1]);
+            aSig += '1';
+            aSig.Append(sep);
+            aSig.AppendAscii(RTL_CONSTASCII_STRINGPARAM( " " ));
+            aSig += *(ppDefArgNames[nFix]);
+            aSig += '2';
+            aSig.AppendAscii(RTL_CONSTASCII_STRINGPARAM( ", " ));
+            aSig += *(ppDefArgNames[nFix+1]);
+            aSig += '2';
+            aSig.Append(sep);
+            aSig.AppendAscii(RTL_CONSTASCII_STRINGPARAM( " ... " ));
+        }
     }
 
     return aSig;
@@ -1592,7 +1628,9 @@ sal_uInt16 ScFuncDesc::GetSuppressedArgC
         return nArgCount;
 
     sal_uInt16 nArgs = nArgCount;
-    if (nArgs >= VAR_ARGS)
+    if (nArgs >= PAIRED_VAR_ARGS)
+	    nArgs -= PAIRED_VAR_ARGS - 2;
+	else if (nArgs >= VAR_ARGS)
         nArgs -= VAR_ARGS - 1;
     sal_uInt16 nCount = nArgs;
     for (sal_uInt16 i=0; i < nArgs; ++i)
@@ -1600,7 +1638,9 @@ sal_uInt16 ScFuncDesc::GetSuppressedArgC
         if (pDefArgFlags[i].bSuppress)
             --nCount;
     }
-    if (nArgCount >= VAR_ARGS)
+    if (nArgCount >= PAIRED_VAR_ARGS)
+        nCount += PAIRED_VAR_ARGS - 2;
+    else if (nArgCount >= VAR_ARGS)
         nCount += VAR_ARGS - 1;
     return nCount;
 }
@@ -1645,7 +1685,9 @@ void ScFuncDesc::fillVisibleArgumentMapp
 
     _rArguments.reserve( nArgCount);
     sal_uInt16 nArgs = nArgCount;
-    if (nArgs >= VAR_ARGS)
+    if (nArgs >= PAIRED_VAR_ARGS)
+	    nArgs -= PAIRED_VAR_ARGS - 2;
+	else if (nArgs >= VAR_ARGS)
         nArgs -= VAR_ARGS - 1;
     for (sal_uInt16 i=0; i < nArgs; ++i)
     {

Modified: incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx Thu Sep  6 00:37:34 2012
@@ -104,6 +104,13 @@ enum ScIterFuncIf
     ifAVERAGEIF // Conditional average
 };
 
+enum ScIterFuncIfs
+{
+    ifSUMIFS,     // Multi-Conditional sum
+    ifAVERAGEIFS, // Multi-Conditional average
+    ifCOUNTIFS    // Multi-Conditional count
+};
+
 struct FormulaTokenRef_less
 {
     bool operator () ( const formula::FormulaConstTokenRef& r1, const formula::FormulaConstTokenRef&
r2 ) const
@@ -482,6 +489,10 @@ double IterateParametersIf( ScIterFuncIf
 void ScCountIf();
 void ScSumIf();
 void ScAverageIf();
+double IterateParametersIfs( ScIterFuncIfs );
+void ScSumIfs();
+void ScAverageIfs();
+void ScCountIfs();
 void ScCountEmptyCells();
 void ScLookup();
 void ScHLookup();

Modified: incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx Thu Sep  6 00:37:34 2012
@@ -5078,6 +5078,391 @@ void ScInterpreter::ScCountIf()
     }
 }
 
+double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc )
+{
+    sal_uInt8 nParamCount = GetByte();
+    sal_uInt8 nQueryCount = nParamCount / 2;
+
+    bool bCheck;
+    if ( eFunc == ifCOUNTIFS )
+        bCheck = (nParamCount >= 2) && (nParamCount % 2 == 0);
+    else
+        bCheck = (nParamCount >= 3) && (nParamCount % 2 == 1);
+
+    if ( !bCheck )
+    {
+        SetError( errParameterExpected);
+    }
+    else
+    {
+        ScMatrixRef pResMat;
+        double fVal = 0.0;
+        double fSum = 0.0;
+        double fMem = 0.0;
+        double fRes = 0.0;
+        double fCount = 0.0;
+        short nParam = 1;
+        size_t nRefInList = 0;
+
+        while (nParamCount > 1 && !nGlobalError)
+        {
+            // take criteria
+            String rString;
+            fVal = 0.0;
+            bool bIsString = true;
+            switch ( GetStackType() )
+            {
+                case svDoubleRef :
+                case svSingleRef :
+                    {
+                        ScAddress aAdr;
+                        if ( !PopDoubleRefOrSingleRef( aAdr ) )
+                            return 0;
+
+                        ScBaseCell* pCell = GetCell( aAdr );
+                        switch ( GetCellType( pCell ) )
+                        {
+                            case CELLTYPE_VALUE :
+                                fVal = GetCellValue( aAdr, pCell );
+                                bIsString = false;
+                                break;
+                            case CELLTYPE_FORMULA :
+                                if( ((ScFormulaCell*)pCell)->IsValue() )
+                                {
+                                    fVal = GetCellValue( aAdr, pCell );
+                                    bIsString = false;
+                                }
+                                else
+                                    GetCellString(rString, pCell);
+                                break;
+                            case CELLTYPE_STRING :
+                            case CELLTYPE_EDIT :
+                                GetCellString(rString, pCell);
+                                break;
+                            default:
+                                fVal = 0.0;
+                                bIsString = false;
+                        }
+                    }
+                    break;
+                case svString:
+                    rString = GetString();
+                    break;
+                case svMatrix :
+                    {
+                        ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
+                        bIsString = ScMatrix::IsNonValueType( nType);
+                    }
+                    break;
+                default:
+                    {
+                        fVal = GetDouble();
+                        bIsString = false;
+                    }
+            }
+
+            // take range
+            nParam = 1;
+            nRefInList = 0;
+            SCCOL nCol1;
+            SCROW nRow1;
+            SCTAB nTab1;
+            SCCOL nCol2;
+            SCROW nRow2;
+            SCTAB nTab2;
+            ScMatrixRef pQueryMatrix;
+            switch ( GetStackType() )
+            {
+                case svRefList :
+                    {
+                        ScRange aRange;
+                        PopDoubleRef( aRange, nParam, nRefInList);
+                        aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
+                    }
+                    break;
+                case svDoubleRef :
+                    PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nCol1, nRow1, nTab1 );
+                    nCol2 = nCol1;
+                    nRow2 = nRow1;
+                    nTab2 = nTab1;
+                    break;
+                case svMatrix:
+                    {
+                        pQueryMatrix = PopMatrix();
+                        if (!pQueryMatrix)
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                        nCol1 = 0;
+                        nRow1 = 0;
+                        nTab1 = 0;
+                        SCSIZE nC, nR;
+                        pQueryMatrix->GetDimensions( nC, nR);
+                        nCol2 = static_cast<SCCOL>(nC - 1);
+                        nRow2 = static_cast<SCROW>(nR - 1);
+                        nTab2 = 0;
+                    }
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+            if ( nTab1 != nTab2 )
+            {
+                SetError( errIllegalParameter);
+            }
+            // initialize temporary result matrix
+            if (!pResMat)
+            {
+                SCSIZE nResC, nResR;
+                nResC = nCol2 - nCol1 + 1;
+                nResR = nRow2 - nRow1 + 1;
+                pResMat = GetNewMat(nResC, nResR);
+                if (!pResMat)
+                {
+                    SetError( errIllegalParameter);
+                }
+                else
+                {
+                    pResMat->FillDouble( 0.0, 0, 0, nResC-1, nResR-1);
+                }
+            }
+            // recalculate matrix values
+            if (nGlobalError == 0)
+            {
+                ScQueryParam rParam;
+                rParam.nRow1       = nRow1;
+                rParam.nRow2       = nRow2;
+
+                ScQueryEntry& rEntry = rParam.GetEntry(0);
+                rEntry.bDoQuery = true;
+                if (!bIsString)
+                {
+                    rEntry.bQueryByString = false;
+                    rEntry.nVal = fVal;
+                    rEntry.eOp = SC_EQUAL;
+                }
+                else
+                {
+                    rParam.FillInExcelSyntax(rString, 0);
+                    sal_uInt32 nIndex = 0;
+                    rEntry.bQueryByString =
+                        !(pFormatter->IsNumberFormat(
+                                    *rEntry.pStr, nIndex, rEntry.nVal));
+                    if ( rEntry.bQueryByString )
+                        rParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
+                }
+                ScAddress aAdr;
+                aAdr.SetTab( nTab1 );
+                rParam.nCol1  = nCol1;
+                rParam.nCol2  = nCol2;
+                rEntry.nField = nCol1;
+                SCsCOL nColDiff = -nCol1;
+                SCsROW nRowDiff = -nRow1;
+                if (pQueryMatrix)
+                {
+                    // Never case-sensitive.
+                    ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
+                    ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
+                    if (nGlobalError || !pResultMatrix)
+                    {
+                        SetError( errIllegalParameter);
+                    }
+
+                    for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+                    {
+                        for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+                        {
+                            if (pResultMatrix->IsValue( nCol, nRow) &&
+                                    pResultMatrix->GetDouble( nCol, nRow))
+                            {
+                                SCSIZE nC = nCol + nColDiff;
+                                SCSIZE nR = nRow + nRowDiff;
+                                pResMat->PutDouble(pResMat->GetDouble(nC, nR)+1.0,
nC, nR);
+                            }
+                        }
+                    }
+                }
+                else
+                {
+                    ScQueryCellIterator aCellIter(pDok, nTab1, rParam, false);
+                    // Increment Entry.nField in iterator when switching to next column.
+                    aCellIter.SetAdvanceQueryParamEntryField( true );
+                    if ( aCellIter.GetFirst() )
+                    {
+                        do
+                        {
+                            SCSIZE nC = aCellIter.GetCol() + nColDiff;
+                            SCSIZE nR = aCellIter.GetRow() + nRowDiff;
+                            pResMat->PutDouble(pResMat->GetDouble(nC, nR)+1.0, nC,
nR);
+                        } while ( aCellIter.GetNext() );
+                    }
+                }
+            }
+            else
+            {
+                SetError( errIllegalParameter);
+            }
+            nParamCount -= 2;
+        }
+
+        // main range - only for AVERAGEIFS and SUMIFS
+        if (nParamCount == 1)
+        {
+            nParam = 1;
+            nRefInList = 0;
+            bool bNull = true;
+            SCCOL nMainCol1;
+            SCROW nMainRow1;
+            SCTAB nMainTab1;
+            SCCOL nMainCol2;
+            SCROW nMainRow2;
+            SCTAB nMainTab2;
+            ScMatrixRef pMainMatrix;
+            switch ( GetStackType() )
+            {
+                case svRefList :
+                    {
+                        ScRange aRange;
+                        PopDoubleRef( aRange, nParam, nRefInList);
+                        aRange.GetVars( nMainCol1, nMainRow1, nMainTab1, nMainCol2, nMainRow2,
nMainTab2);
+                    }
+                    break;
+                case svDoubleRef :
+                    PopDoubleRef( nMainCol1, nMainRow1, nMainTab1, nMainCol2, nMainRow2,
nMainTab2 );
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nMainCol1, nMainRow1, nMainTab1 );
+                    nMainCol2 = nMainCol1;
+                    nMainRow2 = nMainRow1;
+                    nMainTab2 = nMainTab1;
+                    break;
+                case svMatrix:
+                    {
+                        pMainMatrix = PopMatrix();
+                        if (!pMainMatrix)
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                        nMainCol1 = 0;
+                        nMainRow1 = 0;
+                        nMainTab1 = 0;
+                        SCSIZE nC, nR;
+                        pMainMatrix->GetDimensions( nC, nR);
+                        nMainCol2 = static_cast<SCCOL>(nC - 1);
+                        nMainRow2 = static_cast<SCROW>(nR - 1);
+                        nMainTab2 = 0;
+                    }
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+            if ( nMainTab1 != nMainTab2 )
+            {
+                SetError( errIllegalParameter);
+            }
+            // end-result calculation
+            ScAddress aAdr;
+            aAdr.SetTab( nMainTab1 );
+            if (pMainMatrix)
+            {
+                SCSIZE nC, nR;
+                pResMat->GetDimensions(nC, nR);
+                for (SCSIZE nCol = 0; nCol < nC; ++nCol)
+                {
+                    for (SCSIZE nRow = 0; nRow < nR; ++nRow)
+                    {
+                        if (pResMat->GetDouble( nCol, nRow) == nQueryCount)
+                        {
+                            if (pMainMatrix->IsValue( nCol, nRow))
+                            {
+                                fVal = pMainMatrix->GetDouble( nCol, nRow);
+                                ++fCount;
+                                if ( bNull && fVal != 0.0 )
+                                {
+                                    bNull = false;
+                                    fMem = fVal;
+                                }
+                                else
+                                    fSum += fVal;
+                            }
+                        }
+                    }
+                }
+            }
+            else
+            {
+                SCSIZE nC, nR;
+                pResMat->GetDimensions(nC, nR);
+                for (SCSIZE nCol = 0; nCol < nC; ++nCol)
+                {
+                    for (SCSIZE nRow = 0; nRow < nR; ++nRow)
+                    {
+                        if (pResMat->GetDouble( nCol, nRow) == nQueryCount)
+                        {
+                            aAdr.SetCol( nCol + nMainCol1);
+                            aAdr.SetRow( nRow + nMainRow1);
+                            ScBaseCell* pCell = GetCell( aAdr );
+                            if ( HasCellValueData(pCell) )
+                            {
+                                fVal = GetCellValue( aAdr, pCell );
+                                ++fCount;
+                                if ( bNull && fVal != 0.0 )
+                                {
+                                    bNull = false;
+                                    fMem = fVal;
+                                }
+                                else
+                                    fSum += fVal;
+                            }
+                        }
+                    }
+                }
+            }
+        }
+        else
+        {
+            SCSIZE nC, nR;
+            pResMat->GetDimensions(nC, nR);
+            for (SCSIZE nCol = 0; nCol < nC; ++nCol)
+            {
+                for (SCSIZE nRow = 0; nRow < nR; ++nRow)
+                    if (pResMat->GetDouble( nCol, nRow) == nQueryCount)
+                        ++fCount;
+            }
+        }
+        //
+        switch( eFunc )
+        {
+            case ifSUMIFS:     fRes = ::rtl::math::approxAdd( fSum, fMem ); break;
+            case ifAVERAGEIFS: fRes = div( ::rtl::math::approxAdd( fSum, fMem ), fCount);
break;
+            case ifCOUNTIFS:   fRes = fCount; break;
+            default: ; // nothing
+        }
+        return fRes;
+    }
+    return 0;
+}
+
+void ScInterpreter::ScSumIfs()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScSumIfs" );
+    PushDouble( IterateParametersIfs( ifSUMIFS));
+}
+
+void ScInterpreter::ScAverageIfs()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScAverageIfs" );
+    PushDouble( IterateParametersIfs( ifAVERAGEIFS));
+}
+
+void ScInterpreter::ScCountIfs()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScCountIfs" );
+    PushDouble( IterateParametersIfs( ifCOUNTIFS));
+}
 
 void ScInterpreter::ScLookup()
 {

Modified: incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx Thu Sep  6 00:37:34 2012
@@ -3623,6 +3623,9 @@ StackVar ScInterpreter::Interpret()
                 case ocCountIf          : ScCountIf();                  break;
                 case ocSumIf            : ScSumIf();                    break;
                 case ocAverageIf        : ScAverageIf();                break;
+                case ocSumIfs           : ScSumIfs();                   break;
+                case ocAverageIfs       : ScAverageIfs();               break;
+                case ocCountIfs         : ScCountIfs();                 break;
                 case ocLookup           : ScLookup();                   break;
                 case ocVLookup          : ScVLookup();                  break;
                 case ocHLookup          : ScHLookup();                  break;

Modified: incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx Thu Sep  6 00:37:34 2012
@@ -80,6 +80,7 @@ const ScParameterClassification::RawData
     { ocAverage,         {{ Reference                                            }, true
}},
     { ocAverageA,        {{ Reference                                            }, true
}},
     { ocAverageIf,       {{ Reference, Value, Reference                          }, false
}},
+    { ocAverageIfs,      {{ Reference, Reference, Value                          }, true
}},
     { ocCell,            {{ Value, Reference                                     }, false
}},
     { ocColumn,          {{ Reference                                            }, false
}},
     { ocColumns,         {{ Reference                                            }, true
}},
@@ -88,6 +89,7 @@ const ScParameterClassification::RawData
     { ocCount2,          {{ Reference                                            }, true
}},
     { ocCountEmptyCells, {{ Reference                                            }, false
}},
     { ocCountIf,         {{ Reference, Value                                     }, false
}},
+    { ocCountIfs,        {{ Reference, Value                                     }, true
}},
     { ocCovar,           {{ ForceArray, ForceArray                               }, false
}},
     { ocDBAverage,       {{ Reference, Reference, Reference                      }, false
}},
     { ocDBCount,         {{ Reference, Reference, Reference                      }, false
}},
@@ -174,6 +176,7 @@ const ScParameterClassification::RawData
     { ocSubTotal,        {{ Value, Reference                                     }, true
}},
     { ocSum,             {{ Reference                                            }, true
}},
     { ocSumIf,           {{ Reference, Value, Reference                          }, false
}},
+    { ocSumIfs,          {{ Reference, Reference, Value                          }, true
}},
     { ocSumProduct,      {{ ForceArray                                           }, true
}},
     { ocSumSQ,           {{ Reference                                            }, true
}},
     { ocSumX2MY2,        {{ ForceArray, ForceArray                               }, false
}},
@@ -404,7 +407,12 @@ void ScParameterClassification::MergeArg
 
         RunData* pRun = &pData[ pDesc->nFIndex ];
         sal_uInt16 nArgs = pDesc->GetSuppressedArgCount();
-        if ( nArgs >= VAR_ARGS )
+        if ( nArgs >= PAIRED_VAR_ARGS )
+        {
+            nArgs -= PAIRED_VAR_ARGS - 2;
+            pRun->aData.bRepeatLast = true;
+        }
+        else if ( nArgs >= VAR_ARGS )
         {
             nArgs -= VAR_ARGS - 1;
             pRun->aData.bRepeatLast = true;

Modified: incubator/ooo/trunk/main/sc/source/ui/formdlg/dwfunctr.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/ui/formdlg/dwfunctr.cxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/ui/formdlg/dwfunctr.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/ui/formdlg/dwfunctr.cxx Thu Sep  6 00:37:34 2012
@@ -912,10 +912,16 @@ void ScFunctionDockWin::DoEnter(sal_Bool
 				aFirstArgStr.EraseLeadingAndTrailingChars();
                 aFirstArgStr.SearchAndReplaceAll(' ', '_');
 				aArgStr = aFirstArgStr;
-				if ( nArgs != VAR_ARGS )
+				if ( nArgs != VAR_ARGS && nArgs != PAIRED_VAR_ARGS )
 				{	// no VarArgs or Fix plus VarArgs, but not VarArgs only
 					String aArgSep = String::CreateFromAscii(RTL_CONSTASCII_STRINGPARAM( "; " ));
-					sal_uInt16 nFix = ( nArgs < VAR_ARGS ? nArgs : nArgs - VAR_ARGS + 1 );
+					sal_uInt16 nFix;
+					if (nArgs >= PAIRED_VAR_ARGS)
+					    nFix = nArgs - PAIRED_VAR_ARGS + 2;
+					else if (nArgs >= VAR_ARGS)
+					    nFix = nArgs - VAR_ARGS + 1;
+					else
+					    nFix = nArgs;
 					for ( sal_uInt16 nArg = 1;
 							nArg < nFix && !pDesc->pDefArgFlags[nArg].bOptional; nArg++ )
 					{

Modified: incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src (original)
+++ incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src Thu Sep  6 00:37:34 2012
@@ -2930,6 +2930,121 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
 			Text [ en-US ] = "The range from which the values are to be averaged." ;
 		};
 	};
+
+	Resource SC_OPCODE_SUM_IFS
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Totals the values of cells in a range that meet multiple criteria in
multiple ranges." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_SUMIFS );
+			PAIRED_VAR_ARGS+1;	0;	0;	0;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "sum_range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "The range from which the values are to be totalled." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "Range 1, range 2,... are the ranges to be evaluated by the criteria
given." ;
+		};
+		String 6 // Name of Parameter 3
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 7 // Description of Parameter 3
+		{
+			Text [ en-US ] = "Criteria 1, criteria 2,... are the cell ranges in which the search criteria
are given." ;
+		};
+	};
+
+	// -=*# Resource for function AVERAGEIFS #*=-
+	Resource SC_OPCODE_AVERAGE_IFS
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Averages the value of the cells that meet multiple criteria in multiple
ranges." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_AVERAGEIFS );
+			PAIRED_VAR_ARGS+1;	0;	0;	0;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "average_range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "The range from which the values are to be averaged." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "Range 1, range 2,... are the ranges to be evaluated by the criteria
given." ;
+		};
+		String 6 // Name of Parameter 3
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 7 // Description of Parameter 3
+		{
+			Text [ en-US ] = "Criteria 1, criteria 2,... are the cell ranges in which the search criteria
are given." ;
+		};
+	};
+
+	// -=*# Resource for function COUNTIFS #*=-
+	Resource SC_OPCODE_COUNT_IFS
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Counts the cells that meet multiple criteria in multiple ranges." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_COUNTIFS );
+			PAIRED_VAR_ARGS;	0;	0;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "Range 1, range 2,... are the ranges to be evaluated by the criteria
given." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "Criteria 1, criteria 2,... are the cell ranges in which the search criteria
are given." ;
+		};
+	};
+
 	 // -=*# Resource for function ZÄHLENWENN #*=-
 	Resource SC_OPCODE_COUNT_IF
 	{

Modified: incubator/ooo/trunk/main/sc/source/ui/unoobj/appluno.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/ui/unoobj/appluno.cxx?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/ui/unoobj/appluno.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/ui/unoobj/appluno.cxx Thu Sep  6 00:37:34 2012
@@ -762,10 +762,14 @@ static void lcl_FillSequence( uno::Seque
 	if (rDesc.ppDefArgNames && rDesc.ppDefArgDescs && rDesc.pDefArgFlags )
 	{
 		sal_uInt16 nCount = rDesc.nArgCount;
-        if (nCount >= VAR_ARGS)
+		if (nCount >= PAIRED_VAR_ARGS)
+		    nCount -= PAIRED_VAR_ARGS - 2;
+        else if (nCount >= VAR_ARGS)
             nCount -= VAR_ARGS - 1;
         sal_uInt16 nSeqCount = rDesc.GetSuppressedArgCount();
-        if (nSeqCount >= VAR_ARGS)
+        if (nSeqCount >= PAIRED_VAR_ARGS)
+            nSeqCount -= PAIRED_VAR_ARGS - 2;
+        else if (nSeqCount >= VAR_ARGS)
             nSeqCount -= VAR_ARGS - 1;
 
         if (nSeqCount)

Modified: incubator/ooo/trunk/main/sc/util/hidother.src
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/util/hidother.src?rev=1381447&r1=1381446&r2=1381447&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/util/hidother.src (original)
+++ incubator/ooo/trunk/main/sc/util/hidother.src Thu Sep  6 00:37:34 2012
@@ -165,6 +165,9 @@ hidspecial HID_FUNC_PRODUKT		 { HelpID =
 hidspecial HID_FUNC_SUMMEWENN		 { HelpID = HID_FUNC_SUMMEWENN; };
 hidspecial HID_FUNC_ZAEHLENWENN		 { HelpID = HID_FUNC_ZAEHLENWENN; };
 hidspecial HID_FUNC_AVERAGEIF		 { HelpID = HID_FUNC_AVERAGEIF; };
+hidspecial HID_FUNC_SUMIFS		 { HelpID = HID_FUNC_SUMIFS; };
+hidspecial HID_FUNC_AVERAGEIFS		 { HelpID = HID_FUNC_AVERAGEIFS; };
+hidspecial HID_FUNC_COUNTIFS		 { HelpID = HID_FUNC_COUNTIFS; };
 hidspecial HID_FUNC_WURZEL		 { HelpID = HID_FUNC_WURZEL; };
 hidspecial HID_FUNC_ZUFALLSZAHL		 { HelpID = HID_FUNC_ZUFALLSZAHL; };
 hidspecial HID_FUNC_ISTGERADE		 { HelpID = HID_FUNC_ISTGERADE; };



Mime
View raw message