Author: fanningpj
Date: Sat May 10 09:23:11 2025
New Revision: 1925484
URL: http://svn.apache.org/viewvc?rev=1925484&view=rev
Log:
[github-803] Add support for SHEET function. Thanks to Richard V. This closes
#803
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sheet.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSheet.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1925484&r1=1925483&r2=1925484&view=diff
==============================================================================
---
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
(original)
+++
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java
Sat May 10 09:23:11 2025
@@ -191,7 +191,7 @@ public final class AnalysisToolPak imple
r(m, "RECEIVED", null);
r(m, "RTD", null);
r(m, "SERIESSUM", null);
- r(m, "SHEET", null);
+ r(m, "SHEET", Sheet.instance);
r(m, "SINGLE", Single.instance);
r(m, "SQRTPI", Sqrtpi.instance);
r(m, "STDEV.S", Stdevs.instance);
Added:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sheet.java?rev=1925484&view=auto
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sheet.java
(added)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/functions/Sheet.java
Sat May 10 09:23:11 2025
@@ -0,0 +1,89 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.formula.eval.*;
+
+/**
+ * Implementation for Excel SHEET() function.
+ * <p>
+ * <b>Syntax</b>:<br> <b>SHEET</b>([value])<br>
+ * </p>
+ * <p>
+ * Returns the sheet number of the referenced sheet or the current sheet if no
argument is provided.
+ * </p>
+ * <p>
+ * Examples:
+ * <ul>
+ * <li><code>=SHEET()</code> â returns the current sheet number
(1-based)</li>
+ * <li><code>=SHEET(A1)</code> â returns the sheet number of the
reference A1</li>
+ * <li><code>=SHEET(A1:B5)</code> â returns the sheet number of the
range A1:B5</li>
+ * <li><code>=SHEET("Sheet3")</code> â returns the sheet number of the
sheet named "Sheet3"</li>
+ * </ul>
+ * </p>
+ * <p>
+ * See <a
href="https://support.microsoft.com/en-us/office/sheet-function-44718b6f-8b87-47a1-a9d6-b701c06cff24";>Microsoft
Documentation</a>
+ * </p>
+ */
+public class Sheet implements FreeRefFunction {
+
+ public static final Sheet instance = new Sheet();
+
+ @Override
+ public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
+ try {
+ if (args.length == 0) {
+ // No argument provided â return the current sheet index +1
(Excel uses 1-based index)
+ return new NumberEval((double) ec.getSheetIndex() + 1);
+ } else {
+ ValueEval arg = args[0];
+
+ if (arg instanceof RefEval) {
+ // Argument is a single cell reference â return the
sheet index of that reference +1
+ RefEval ref = (RefEval) arg;
+ int sheetIndex = ref.getFirstSheetIndex();
+ return new NumberEval((double) sheetIndex + 1);
+ } else if (arg instanceof AreaEval) {
+ // Argument is a cell range â return the sheet index of
that area +1
+ AreaEval area = (AreaEval) arg;
+ int sheetIndex = area.getFirstSheetIndex();
+ return new NumberEval((double) sheetIndex + 1);
+ } else if (arg instanceof StringEval) {
+ // Argument is a string (sheet name, e.g., "Sheet3") â
look up the sheet index by name
+ String sheetName = ((StringEval) arg).getStringValue();
+ EvaluationWorkbook wb = ec.getWorkbook();
+ int sheetIndex = wb.getSheetIndex(sheetName);
+ if (sheetIndex >= 0) {
+ return new NumberEval((double) sheetIndex + 1);
+ } else {
+ // Sheet name not found â return #N/A error
+ return ErrorEval.NA;
+ }
+ } else {
+ // Unsupported argument type â return #N/A error
+ return ErrorEval.NA;
+ }
+ }
+ } catch (Exception e) {
+ // Any unexpected exception (e.g., null pointers) â return
#VALUE! error
+ return ErrorEval.VALUE_INVALID;
+ }
+ }
+}
Added:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSheet.java?rev=1925484&view=auto
==============================================================================
---
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSheet.java
(added)
+++
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestSheet.java
Sat May 10 09:23:11 2025
@@ -0,0 +1,89 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.formula.OperationEvaluationContext;
+import org.apache.poi.ss.usermodel.CellType;
+import org.apache.poi.ss.usermodel.FormulaError;
+import org.junit.jupiter.api.Test;
+
+import java.io.IOException;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+final class TestSheet {
+
+ private static final OperationEvaluationContext ec = new
OperationEvaluationContext(null, null, 2, 0, 2, null);
+
+ @Test
+ void testSheetFunctionWithRealWorkbook() throws IOException {
+ try (HSSFWorkbook wb = new HSSFWorkbook()) {
+ // Add three sheets: Sheet1, Sheet2, Sheet3
+ HSSFSheet sheet1 = wb.createSheet("Sheet1");
+ HSSFSheet sheet2 = wb.createSheet("Sheet2");
+ HSSFSheet sheet3 = wb.createSheet("Sheet3");
+
+ // Add data
+ sheet1.createRow(0).createCell(0).setCellValue(123); // A1 in
Sheet1
+ sheet2.createRow(1).createCell(0).setCellValue(456); // A2 in
Sheet2
+
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+ // Define formulas and expected results
+ String[] formulas = {
+ "SHEET()",
+ "SHEET(A1)",
+ "SHEET(A1:B5)",
+ "SHEET(Sheet2!A2)",
+ "SHEET(\"Sheet3\")",
+ "SHEET(\"invalid\")"
+ };
+
+ Object[] expected = {
+ 1.0, // current sheet
+ 1.0, // A1 in same sheet
+ 1.0, // A1:B5 in same sheet
+ 2.0, // Sheet2!A2
+ 3.0, // Sheet3
+ FormulaError.NA.getCode() // unknown sheet â #N/A
+ };
+
+ // Write formulas to separate cells and evaluate
+ HSSFRow formulaRow = sheet1.createRow(1);
+ for (int i = 0; i < formulas.length; i++) {
+ String formula = formulas[i];
+ HSSFCell cell = formulaRow.createCell(i);
+ cell.setCellFormula(formula);
+ CellType resultType = fe.evaluateFormulaCell(cell);
+
+ if (expected[i] instanceof Double) {
+ assertEquals(CellType.NUMERIC, resultType,
+ "Unexpected cell type for formula: " + formula);
+ assertEquals((Double) expected[i],
cell.getNumericCellValue(),
+ "Unexpected numeric result for formula: " +
formula);
+ } else if (expected[i] instanceof Byte) {
+ assertEquals(CellType.ERROR, resultType,
+ "Unexpected cell type for formula: " + formula);
+ assertEquals((byte) expected[i], cell.getErrorCellValue(),
+ "Unexpected error code for formula: " + formula);
+ }
+ }
+ }
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
