From 965a3829f4bb557435dbb67b95a906602d1885c1 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Tue, 8 Jan 2008 15:08:51 +0000 Subject: [PATCH] Fix from bug #43510 - support named ranges in formulas git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@610016 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/hssf/model/FormulaParser.java | 27 ++++++++++++--- .../poi/hssf/model/TestFormulaParser.java | 34 +++++++++++++++++++ 4 files changed, 59 insertions(+), 4 deletions(-) diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index 06c9b0c071..448cca0a4a 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,6 +36,7 @@ + 43510 - Add support for named ranges in formulas 43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status 44167 - Fix for non-contiguous named ranges 44070 - Fix for shifting comments when shifting rows diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index c7bf2de8d9..edc7cccd37 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 43510 - Add support for named ranges in formulas 43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status 44167 - Fix for non-contiguous named ranges 44070 - Fix for shifting comments when shifting rows diff --git a/src/java/org/apache/poi/hssf/model/FormulaParser.java b/src/java/org/apache/poi/hssf/model/FormulaParser.java index 9697dadafd..832dbdef7a 100644 --- a/src/java/org/apache/poi/hssf/model/FormulaParser.java +++ b/src/java/org/apache/poi/hssf/model/FormulaParser.java @@ -24,6 +24,7 @@ import java.util.ArrayList; import java.util.Iterator; import java.util.LinkedList; import java.util.List; +import java.util.regex.Pattern; //import PTG's .. since we need everything, import * import org.apache.poi.hssf.record.formula.*; @@ -65,6 +66,12 @@ public class FormulaParser { * Using an unsynchronized linkedlist to implement a stack since we're not multi-threaded. */ private List functionTokens = new LinkedList(); + + /** + * Used for spotting if we have a cell reference, + * or a named range + */ + private final static Pattern CELL_REFERENCE_PATTERN = Pattern.compile("(?:('?)[^:\\\\/\\?\\*\\[\\]]+\\1!)?\\$?[A-Za-z]+\\$?[\\d]+"); private static char TAB = '\t'; private static char CR = '\n'; @@ -306,15 +313,27 @@ public class FormulaParser { tokens.add(new Ref3DPtg(first,externIdx)); } } else { - //this can be either a cell ref or a named range !! - boolean cellRef = true ; //we should probably do it with reg exp?? + // This can be either a cell ref or a named range + // Try to spot which it is + boolean cellRef = CELL_REFERENCE_PATTERN.matcher(name).matches(); boolean boolLit = (name.equals("TRUE") || name.equals("FALSE")); + if (boolLit) { tokens.add(new BoolPtg(name)); } else if (cellRef) { tokens.add(new ReferencePtg(name)); - }else { - //handle after named range is integrated!! + } else { + boolean nameRecordExists = false; + for(int i = 0; i < book.getNumNames(); i++) { + // Our formula will by now contain an upper-cased + // version of any named range names + if(book.getNameRecord(i).getNameText().toUpperCase().equals(name)) { + nameRecordExists = true; + } + } + if(!nameRecordExists) + Abort("Found reference to named range \"" + name + "\", but that named range wasn't defined!"); + tokens.add(new NamePtg(name, book)); } } } diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java index 2d1a25c067..51d3e66996 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java @@ -39,6 +39,8 @@ import org.apache.poi.hssf.record.formula.StringPtg; import org.apache.poi.hssf.record.formula.UnaryMinusPtg; import org.apache.poi.hssf.record.formula.UnaryPlusPtg; import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; @@ -349,6 +351,38 @@ public class TestFormulaParser extends TestCase { assertTrue("ptg0 is a StringPtg", ptg[0] instanceof StringPtg); assertTrue("ptg0 contains exact value", ((StringPtg)ptg[0]).getValue().equals(value)); } + + public void testWithNamedRange() throws Exception { + HSSFWorkbook workbook = new HSSFWorkbook(); + FormulaParser fp; + Ptg[] ptgs; + + HSSFSheet s = workbook.createSheet("Foo"); + s.createRow(0).createCell((short)0).setCellValue(1.1); + s.createRow(1).createCell((short)0).setCellValue(2.3); + s.createRow(2).createCell((short)2).setCellValue(3.1); + + HSSFName name = workbook.createName(); + name.setNameName("testName"); + name.setReference("A1:A2"); + + fp = HSSFFormulaEvaluator.getUnderlyingParser(workbook, "SUM(testName)"); + fp.parse(); + ptgs = fp.getRPNPtg(); + assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); + assertEquals(NamePtg.class, ptgs[0].getClass()); + assertEquals(FuncVarPtg.class, ptgs[1].getClass()); + + // Now make it a single cell + name.setReference("C3"); + + fp = HSSFFormulaEvaluator.getUnderlyingParser(workbook, "SUM(testName)"); + fp.parse(); + ptgs = fp.getRPNPtg(); + assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); + assertEquals(NamePtg.class, ptgs[0].getClass()); + assertEquals(FuncVarPtg.class, ptgs[1].getClass()); + } public void testLookupAndMatchFunctionArgs() { -- 2.39.5