Return-Path: 
 <commits-return-3463-apmail-poi-commits-archive=poi.apache.org@poi.apache.org>
X-Original-To: apmail-poi-commits-archive@minotaur.apache.org
Delivered-To: apmail-poi-commits-archive@minotaur.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 004A59EAE
	for <apmail-poi-commits-archive@minotaur.apache.org>;
 Sat, 11 Feb 2012 13:16:57 +0000 (UTC)
Received: (qmail 19749 invoked by uid 500); 11 Feb 2012 13:16:56 -0000
Delivered-To: apmail-poi-commits-archive@poi.apache.org
Received: (qmail 19706 invoked by uid 500); 11 Feb 2012 13:16:56 -0000
Mailing-List: contact commits-help@poi.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:commits-help@poi.apache.org>
List-Unsubscribe: <mailto:commits-unsubscribe@poi.apache.org>
List-Post: <mailto:commits@poi.apache.org>
List-Id: <commits.poi.apache.org>
Reply-To: dev@poi.apache.org
Delivered-To: mailing list commits@poi.apache.org
Received: (qmail 19699 invoked by uid 99); 11 Feb 2012 13:16:56 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 11 Feb 2012 13:16:56 +0000
X-ASF-Spam-Status: No, hits=-2000.0 required=5.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 11 Feb 2012 13:16:54 +0000
Received: from eris.apache.org (localhost [127.0.0.1])
	by eris.apache.org (Postfix) with ESMTP id 5B0A7238889B
	for <commits@poi.apache.org>; Sat, 11 Feb 2012 13:16:34 +0000 (UTC)
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1243054 - in /poi/trunk:
 src/documentation/content/xdocs/
 src/java/org/apache/poi/ss/formula/functions/
 src/testcases/org/apache/poi/hssf/usermodel/
 src/testcases/org/apache/poi/ss/formula/functions/ test-data/spreadsheet/
Date: Sat, 11 Feb 2012 13:16:34 -0000
To: commits@poi.apache.org
From: yegor@apache.org
X-Mailer: svnmailer-1.0.8-patched
Message-Id: <20120211131634.5B0A7238889B@eris.apache.org>

Author: yegor
Date: Sat Feb 11 13:16:33 2012
New Revision: 1243054

URL: http://svn.apache.org/viewvc?rev=1243054&view=rev
Log:
fixed evaluation of blank cells in COUNTIF, see Bugzilla 51498

Modified:
    poi/trunk/src/documentation/content/xdocs/status.xml
    poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
    poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java
    poi/trunk/test-data/spreadsheet/51498.xls
    poi/trunk/test-data/spreadsheet/countifExamples.xls

Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=1243054&r1=1243053&r2=1243054&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Sat Feb 11 13:16:33 2012
@@ -34,6 +34,7 @@
 
     <changes>
         <release version="3.8-beta6" date="2012-??-??">
+           <action dev="poi-developers" type="fix">51498 - fixed evaluation of blank cells in COUNTIF</action>
            <action dev="poi-developers" type="add">52576 - support changing external file references in HSSFWorkbook</action>
            <action dev="poi-developers" type="add">49896 - support external references in FormulaRenderer</action>
            <action dev="poi-developers" type="fix">52527 - avoid exception when matching shared formula records in HSSF</action>

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java?rev=1243054&r1=1243053&r2=1243054&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Countif.java Sat Feb 11 13:16:33 2012
@@ -217,6 +217,14 @@ public final class Countif extends Fixed
 			} else if((x instanceof NumberEval)) {
 				NumberEval ne = (NumberEval) x;
 				testValue = ne.getNumberValue();
+            } else if((x instanceof BlankEval)) {
+                switch (getCode()) {
+                    case CmpOp.NE:
+                        // Excel counts blank values in range as not equal to any value. See Bugzilla 51498
+                        return true;
+                    default:
+                        return false;
+                }
 			} else {
 				return false;
 			}
@@ -258,7 +266,23 @@ public final class Countif extends Fixed
 			} else if((x instanceof BoolEval)) {
 				BoolEval be = (BoolEval) x;
 				testValue = boolToInt(be.getBooleanValue());
-			} else {
+            } else if((x instanceof BlankEval)) {
+                switch (getCode()) {
+                    case CmpOp.NE:
+                        // Excel counts blank values in range as not equal to any value. See Bugzilla 51498
+                        return true;
+                    default:
+                        return false;
+                }
+            } else if((x instanceof NumberEval)) {
+                switch (getCode()) {
+                    case CmpOp.NE:
+                        // not-equals comparison of a number to boolean always returnes false
+                        return true;
+                    default:
+                        return false;
+                }
+            } else {
 				return false;
 			}
 			return evaluate(testValue - _value);
@@ -318,6 +342,10 @@ public final class Countif extends Fixed
 					case CmpOp.NONE:
 					case CmpOp.EQ:
 						return _value.length() == 0;
+                    case CmpOp.NE:
+                        // pred '<>' matches empty string but not blank cell
+                        // pred '<>ABC'  matches blank and 'not ABC'
+                        return _value.length() != 0;
 				}
 				// no other criteria matches a blank cell
 				return false;
@@ -342,7 +370,9 @@ public final class Countif extends Fixed
 			if (_pattern != null) {
 				return evaluate(_pattern.matcher(testedValue).matches());
 			}
-			return evaluate(testedValue.compareTo(_value));
+            // String criteria in COUNTIF are case insensitive:
+            // for example, the string "apples" and the string "APPLES" will match the same cells.
+			return evaluate(testedValue.compareToIgnoreCase(_value));
 		}
 		/**
 		 * Translates Excel countif wildcard strings into java regex strings
@@ -394,7 +424,7 @@ public final class Countif extends Fixed
 				sb.append(ch);
 			}
 			if (hasWildCard) {
-				return Pattern.compile(sb.toString());
+				return Pattern.compile(sb.toString(), Pattern.CASE_INSENSITIVE);
 			}
 			return null;
 		}

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java?rev=1243054&r1=1243053&r2=1243054&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java Sat Feb 11 13:16:33 2012
@@ -2220,7 +2220,9 @@ if(1==2) {
     public void test49896() {
         HSSFWorkbook wb = openSample("49896.xls");
         HSSFCell  cell = wb.getSheetAt(0).getRow(1).getCell(1);
-        assertEquals("VLOOKUP(A2,'[C:Documents and Settings/Yegor/My Documents/csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)",
+        String PATH_SEPARATOR = System.getProperty("file.separator");
+        assertEquals("VLOOKUP(A2,'[C:Documents and Settings" + PATH_SEPARATOR+"Yegor"+PATH_SEPARATOR
+                +"My Documents"+PATH_SEPARATOR+"csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)",
                 cell.getCellFormula());
      }
 

Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java?rev=1243054&r1=1243053&r2=1243054&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestCountFuncs.java Sat Feb 11 13:16:33 2012
@@ -40,6 +40,7 @@ import org.apache.poi.hssf.usermodel.HSS
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.usermodel.CellValue;
 import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.util.CellReference;
 
 /**
  * Test cases for COUNT(), COUNTA() COUNTIF(), COUNTBLANK()
@@ -196,6 +197,27 @@ public final class TestCountFuncs extend
 		confirmCountIf(4, range, new StringEval("<>111"));
 	}
 
+    /**
+     * String criteria in COUNTIF are case insensitive;
+     * for example, the string "apples" and the string "APPLES" will match the same cells.
+     */
+    public void testCaseInsensitiveStringComparison() {
+        AreaEval range;
+        ValueEval[] values;
+
+        values = new ValueEval[] {
+                new StringEval("no"),
+                new StringEval("NO"),
+                new StringEval("No"),
+                new StringEval("Yes")
+        };
+
+        range = EvalFactory.createAreaEval("A1:A4", values);
+        confirmCountIf(3, range, new StringEval("no"));
+        confirmCountIf(3, range, new StringEval("NO"));
+        confirmCountIf(3, range, new StringEval("No"));
+    }
+
 	/**
 	 * special case where the criteria argument is a cell reference
 	 */
@@ -365,27 +387,48 @@ public final class TestCountFuncs extend
     * Bug #51498 - Check that CountIf behaves correctly for GTE, LTE
     *  and NEQ cases
     */
-	public void testCountifLTEGTE() throws Exception {
+	public void testCountifBug51498() throws Exception {
 		final int REF_COL = 4;
 		final int EVAL_COL = 3;
 		
-		// Note - POI currently agrees with OpenOffice on certain blank cell cases,
-		//  while Excel can differ. This is the list of checks to skip
-		List<Integer> skipRowsPendingExcelVsOpenOffice = Arrays.asList(
-		      new Integer[] {3});
-
-      HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
+        HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
 		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
 		HSSFSheet sheet = workbook.getSheetAt(0);
-		for (int i = 0; i < 8; i++) {
-		   if (skipRowsPendingExcelVsOpenOffice.contains(i)) {
-		      // Skip the check for now
-		      continue;
-		   }
+
+		// numeric criteria
+        for (int i = 0; i < 8; i++) {
 			CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
 			CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
 			assertEquals(expected.formatAsString(), actual.formatAsString());
 		}
+
+        // boolean criteria
+        for (int i = 0; i < 8; i++) {
+            HSSFCell cellFmla = sheet.getRow(i).getCell(8);
+            HSSFCell cellRef = sheet.getRow(i).getCell(9);
+
+            double expectedValue = cellRef.getNumericCellValue();
+            double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
+
+            assertEquals(
+                    "Problem with a formula at " +
+                            new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ",
+                    expectedValue, actualValue, 0.0001);
+        }
+
+        // string criteria
+        for (int i = 1; i < 9; i++) {
+            HSSFCell cellFmla = sheet.getRow(i).getCell(13);
+            HSSFCell cellRef = sheet.getRow(i).getCell(14);
+
+            double expectedValue = cellRef.getNumericCellValue();
+            double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
+
+            assertEquals(
+                    "Problem with a formula at " +
+                            new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula()+"] ",
+                    expectedValue, actualValue, 0.0001);
+        }
 	}
 
 	public void testWildCards() {
@@ -456,6 +499,53 @@ public final class TestCountFuncs extend
 		testCountFunctionFromSpreadsheet("countifExamples.xls", 1, 2, 3, "countif");
 	}
 
+    /**
+     * Two COUNTIF examples taken from
+     * http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
+     */
+    public void testCountifExamples() {
+        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
+        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+        HSSFSheet sheet1 = wb.getSheet("MSDN Example 1");
+        for (int rowIx=7; rowIx<=12; rowIx++) {
+            HSSFRow row = sheet1.getRow(rowIx-1);
+            HSSFCell cellA = row.getCell(0);  // cell containing a formula with COUNTIF
+            assertEquals(HSSFCell.CELL_TYPE_FORMULA, cellA.getCellType());
+            HSSFCell cellC = row.getCell(2);  // cell with a reference value
+            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cellC.getCellType());
+
+            CellValue cv = fe.evaluate(cellA);
+            double actualValue = cv.getNumberValue();
+            double expectedValue = cellC.getNumericCellValue();
+            assertEquals(
+                    "Problem with a formula at  " + new CellReference(cellA).formatAsString()
+                            + ": " + cellA.getCellFormula() + " :"
+                    + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ",
+                    expectedValue, actualValue, 0.0001);
+        }
+
+        HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
+        for (int rowIx=9; rowIx<=14; rowIx++) {
+            HSSFRow row = sheet2.getRow(rowIx-1);
+            HSSFCell cellA = row.getCell(0);  // cell containing a formula with COUNTIF
+            assertEquals(HSSFCell.CELL_TYPE_FORMULA, cellA.getCellType());
+            HSSFCell cellC = row.getCell(2);  // cell with a reference value
+            assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cellC.getCellType());
+
+            CellValue cv = fe.evaluate(cellA);
+            double actualValue = cv.getNumberValue();
+            double expectedValue = cellC.getNumericCellValue();
+
+            assertEquals(
+                    "Problem with a formula at " +
+                            new CellReference(cellA).formatAsString() + "[" + cellA.getCellFormula()+"]: "
+                            + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ",
+                    expectedValue, actualValue, 0.0001);
+
+        }
+    }
+
 	public void testCountBlankFromSpreadsheet() {
 		testCountFunctionFromSpreadsheet("countblankExamples.xls", 1, 3, 4, "countblank");
 	}

Modified: poi/trunk/test-data/spreadsheet/51498.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/51498.xls?rev=1243054&r1=1243053&r2=1243054&view=diff
==============================================================================
Binary files - no diff available.

Modified: poi/trunk/test-data/spreadsheet/countifExamples.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/countifExamples.xls?rev=1243054&r1=1243053&r2=1243054&view=diff
==============================================================================
Binary files - no diff available.



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