You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2008/01/08 16:08:57 UTC
svn commit: r610016 - in /poi/trunk/src:
documentation/content/xdocs/changes.xml
documentation/content/xdocs/status.xml
java/org/apache/poi/hssf/model/FormulaParser.java
testcases/org/apache/poi/hssf/model/TestFormulaParser.java
Author: nick
Date: Tue Jan 8 07:08:51 2008
New Revision: 610016
URL: http://svn.apache.org/viewvc?rev=610016&view=rev
Log:
Fix from bug #43510 - support named ranges in formulas
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java
poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=610016&r1=610015&r2=610016&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Tue Jan 8 07:08:51 2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">43510 - Add support for named ranges in formulas</action>
<action dev="POI-DEVELOPERS" type="add">43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status</action>
<action dev="POI-DEVELOPERS" type="fix">44167 - Fix for non-contiguous named ranges</action>
<action dev="POI-DEVELOPERS" type="fix">44070 - Fix for shifting comments when shifting rows</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=610016&r1=610015&r2=610016&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Tue Jan 8 07:08:51 2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.0.2-FINAL" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="add">43510 - Add support for named ranges in formulas</action>
<action dev="POI-DEVELOPERS" type="add">43937 - Add support for hiding and un-hiding sheets, and checking their current hidden status</action>
<action dev="POI-DEVELOPERS" type="fix">44167 - Fix for non-contiguous named ranges</action>
<action dev="POI-DEVELOPERS" type="fix">44070 - Fix for shifting comments when shifting rows</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java?rev=610016&r1=610015&r2=610016&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/model/FormulaParser.java Tue Jan 8 07:08:51 2008
@@ -24,6 +24,7 @@
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 @@
* 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 @@
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));
}
}
}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java?rev=610016&r1=610015&r2=610016&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java Tue Jan 8 07:08:51 2008
@@ -39,6 +39,8 @@
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;
@@ -348,6 +350,38 @@
assertTrue("got 1 ptg", ptg.length == 1);
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()
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org