From f0547a9ef79f76eccf4855625550dad19db90fb3 Mon Sep 17 00:00:00 2001 From: Javen O'Neal Date: Fri, 10 Jun 2016 00:21:21 +0000 Subject: [PATCH] bug 57840: apply patch from Daniel Livshen and Greg Woolsey to get structured references working in XSSFWorkbook formulas git-svn-id: https://svn.apache.org/repos/asf/poi/branches/xssf_structured_references@1747611 13f79535-47bb-0310-9956-ffa450edef68 --- .../ss/formula/TestStructuredReferences.java | 63 +++++++++++++++++++ 1 file changed, 63 insertions(+) create mode 100644 src/ooxml/testcases/org/apache/poi/ss/formula/TestStructuredReferences.java diff --git a/src/ooxml/testcases/org/apache/poi/ss/formula/TestStructuredReferences.java b/src/ooxml/testcases/org/apache/poi/ss/formula/TestStructuredReferences.java new file mode 100644 index 0000000000..2fb7cb89ff --- /dev/null +++ b/src/ooxml/testcases/org/apache/poi/ss/formula/TestStructuredReferences.java @@ -0,0 +1,63 @@ +package org.apache.poi.ss.formula; + +import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.assertFalse; +import static org.junit.Assert.fail; + +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Table; +import org.apache.poi.xssf.XSSFTestDataSamples; +import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator; +import org.apache.poi.xssf.usermodel.XSSFWorkbook; +import org.junit.Test; + +/** + * Tests Excel Table expressions (structured references) + * @see + * Excel Structured Reference Syntax + * + */ +public class TestStructuredReferences { + + /** + * Test the regular expression used in INDIRECT() evaluation to recognize structured references + */ + @Test + public void testTableExpressionSyntax() { + assertTrue("Valid structured reference syntax didn't match expression", Table.isStructuredReference.matcher("abc[col1]").matches()); + assertTrue("Valid structured reference syntax didn't match expression", Table.isStructuredReference.matcher("_abc[col1]").matches()); + assertTrue("Valid structured reference syntax didn't match expression", Table.isStructuredReference.matcher("_[col1]").matches()); + assertTrue("Valid structured reference syntax didn't match expression", Table.isStructuredReference.matcher("\\[col1]").matches()); + assertTrue("Valid structured reference syntax didn't match expression", Table.isStructuredReference.matcher("\\[col1]").matches()); + assertTrue("Valid structured reference syntax didn't match expression", Table.isStructuredReference.matcher("\\[#This Row]").matches()); + assertTrue("Valid structured reference syntax didn't match expression", Table.isStructuredReference.matcher("\\[ [col1], [col2] ]").matches()); + + // can't have a space between the table name and open bracket + assertFalse("Invalid structured reference syntax didn't fail expression", Table.isStructuredReference.matcher("\\abc [ [col1], [col2] ]").matches()); + } + + @Test + public void testTableFormulas() throws Exception { + XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx"); + try { + + final FormulaEvaluator eval = new XSSFFormulaEvaluator(wb); + confirm(eval, wb.getSheet("Table").getRow(5).getCell(0), 49); + confirm(eval, wb.getSheet("Formulas").getRow(0).getCell(0), 209); + } finally { + wb.close(); + } + } + + private static void confirm(FormulaEvaluator fe, Cell cell, double expectedResult) { + fe.clearAllCachedResultValues(); + CellValue cv = fe.evaluate(cell); + if (cv.getCellType() != Cell.CELL_TYPE_NUMERIC) { + fail("expected numeric cell type but got " + cv.formatAsString()); + } + assertEquals(expectedResult, cv.getNumberValue(), 0.0); + } +} -- 2.39.5