From 6d0dbe6f953bd02feed28cab1245d4a7fcb0e970 Mon Sep 17 00:00:00 2001
From: PJ Fanning <fanningpj@apache.org>
Date: Mon, 14 Nov 2022 17:41:21 +0000
Subject: [PATCH] [bug-66181] support VALUE function where input is a blank
 cell

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1905308 13f79535-47bb-0310-9956-ffa450edef68
---
 .../poi/ss/formula/functions/Value.java       |  4 ++++
 .../poi/ss/formula/functions/TestValue.java   | 21 +++++++++++++++++++
 2 files changed, 25 insertions(+)

diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/Value.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/Value.java
index b3c6cb3c45..451f0a2fab 100644
--- a/poi/src/main/java/org/apache/poi/ss/formula/functions/Value.java
+++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/Value.java
@@ -17,6 +17,7 @@
 
 package org.apache.poi.ss.formula.functions;
 
+import org.apache.poi.ss.formula.eval.BlankEval;
 import org.apache.poi.ss.formula.eval.ErrorEval;
 import org.apache.poi.ss.formula.eval.EvaluationException;
 import org.apache.poi.ss.formula.eval.NumberEval;
@@ -53,6 +54,9 @@ public final class Value extends Fixed1ArgFunction implements ArrayFunction {
             return e.getErrorEval();
         }
         String strText = OperandResolver.coerceValueToString(veText);
+        if (veText == BlankEval.instance) {
+            return new NumberEval(0.0);
+        }
         if (StringUtil.isBlank(strText)) {
             return ErrorEval.VALUE_INVALID;
         }
diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestValue.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestValue.java
index 7166037788..33d1e7bfa4 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestValue.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestValue.java
@@ -19,12 +19,20 @@ package org.apache.poi.ss.formula.functions;
 
 import static org.junit.jupiter.api.Assertions.assertEquals;
 
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.formula.eval.ErrorEval;
 import org.apache.poi.ss.formula.eval.NumberEval;
 import org.apache.poi.ss.formula.eval.StringEval;
 import org.apache.poi.ss.formula.eval.ValueEval;
+import org.apache.poi.ss.util.Utils;
 import org.junit.jupiter.api.Test;
 
+import java.io.IOException;
+
 /**
  * Tests for {@link Value}
  */
@@ -97,4 +105,17 @@ final class TestValue {
         confirmValueError("1e2.5");
         confirmValueError("");
     }
+
+    @Test
+    void testBlank() throws IOException {
+        try (HSSFWorkbook wb = new HSSFWorkbook()) {
+            HSSFSheet sheet = wb.createSheet();
+            HSSFRow row = sheet.createRow(0);
+            HSSFCell b1 = row.createCell(1);
+            HSSFCell c1 = row.createCell(2);
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            Utils.assertDouble(fe, b1, "VALUE(A1)", 0.0);
+            Utils.assertDouble(fe, c1, "VALUE(B1)", 0.0);
+        }
+    }
 }
-- 
2.39.5