Author: fanningpj
Date: Tue Sep 12 10:36:14 2023
New Revision: 1912263
URL: http://svn.apache.org/viewvc?rev=1912263&view=rev
Log:
[bug-63934] Fix parsing of structure references. Thanks to Matthias Raschhofer.
This closes #514
Added:
poi/trunk/test-data/spreadsheet/63934.xlsx (with props)
Modified:
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
Modified:
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java?rev=1912263&r1=1912262&r2=1912263&view=diff
==============================================================================
---
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
(original)
+++
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
Tue Sep 12 10:36:14 2023
@@ -24,6 +24,10 @@ import static org.junit.jupiter.api.Asse
import static org.junit.jupiter.api.Assertions.assertThrows;
import java.io.IOException;
+import java.math.BigDecimal;
+import java.math.MathContext;
+import java.math.RoundingMode;
+import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.function.Function;
@@ -32,6 +36,7 @@ import org.apache.poi.hssf.HSSFTestDataS
import org.apache.poi.ss.usermodel.BaseTestFormulaEvaluator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
+import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
@@ -444,4 +449,17 @@ public final class TestXSSFFormulaEvalua
assertEquals("another value", value.getStringCellValue(), "wrong
value A5");
}
}
+
+ @Test
+ void testBug63934() throws IOException {
+ try (Workbook wb =
XSSFTestDataSamples.openSampleWorkbook("63934.xlsx")) {
+
+ final Cell cell = wb.getSheetAt(0).getRow(1).getCell(1);
+ assertNotNull(cell);
+
+ final FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
+ final CellValue value = evaluator.evaluate(cell);
+ assertEquals("Male", value.getStringValue());
+ }
+ }
}
Modified:
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java?rev=1912263&r1=1912262&r2=1912263&view=diff
==============================================================================
---
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
(original)
+++
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
Tue Sep 12 10:36:14 2023
@@ -655,6 +655,7 @@ public final class TestXSSFFormulaParser
16 Table1[[#Headers],[#Data],[col2]]
17 Table1[[#This Row], [col1]]
18 Table1[ [col1]:[col2] ]
+ 19 Table1[]
*/
final String tbl = "\\_Prime.1";
@@ -697,7 +698,7 @@ public final class TestXSSFFormulaParser
assertEquals(1, ptgs.length);
assertEquals("Table!A1:C7", ptgs[0].toFormulaString(), "Table1[#All]");
- ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Data
rows) ////////
+ ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Total
rows) ////////
ptgs = parse(fpb, tbl+"[#Data]");
assertEquals(1, ptgs.length);
assertEquals("Table!A2:C7", ptgs[0].toFormulaString(),
"Table1[#Data]");
@@ -790,6 +791,13 @@ public final class TestXSSFFormulaParser
assertEquals(1, ptgs.length);
assertEquals("Table!B2:C7", ptgs[0].toFormulaString(),
"Table1[[col]:[col2]]");
+ ////// Case 19: Evaluate "Table1[]" ////////
+ // Excludes Header and Total rows, equivalent to Table1[#Data] (see
case 5).
+ // This is the only case where [] is allowed.
+ ptgs = parse(fpb, tbl+"[]");
+ assertEquals(1, ptgs.length);
+ assertEquals("Table!A2:C7", ptgs[0].toFormulaString(), "Table1[]");
+
wb.close();
}
}
Modified:
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
URL:
http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java?rev=1912263&r1=1912262&r2=1912263&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
(original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
Tue Sep 12 10:36:14 2023
@@ -595,6 +595,7 @@ public final class FormulaParser {
* Parses a structured reference, returns it as area reference.
* Examples:
* <pre>
+ * Table1[]
* Table1[col]
* Table1[[#Totals],[col]]
* Table1[#Totals]
@@ -637,6 +638,29 @@ public final class FormulaParser {
int savePtr0 = _pointer;
nextChar();
+ // Special case: Table1[] is equivalent to Table1[#Data]
+ if (look == ']') {
+
+ // Consume the ']' character
+ nextChar();
+
+ // Skip header and total rows if available
+ int actualStartRow = startRow;
+ if (tbl.getHeaderRowCount() > 0) {
+ actualStartRow = startRow + 1;
+ }
+ int actualEndRow = endRow;
+ if (tbl.getTotalsRowCount() > 0) {
+ actualEndRow = endRow - 1;
+ }
+
+ final CellReference topLeft = new CellReference(actualStartRow,
startCol);
+ final CellReference bottomRight = new CellReference(actualEndRow,
endCol);
+ final SheetIdentifier sheetIden = new SheetIdentifier( null, new
NameIdentifier(sheetName, true));
+ final Ptg ptg = _book.get3DReferencePtg(new AreaReference(topLeft,
bottomRight, _ssVersion), sheetIden);
+ return new ParseNode(ptg);
+ }
+
boolean isTotalsSpec = false;
boolean isThisRowSpec = false;
boolean isDataSpec = false;
Added: poi/trunk/test-data/spreadsheet/63934.xlsx
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/63934.xlsx?rev=1912263&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/63934.xlsx
------------------------------------------------------------------------------
--- svn:mime-type (added)
+++ svn:mime-type Tue Sep 12 10:36:14 2023
@@ -0,0 +1 @@
+application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
