dmui 2003/03/16 20:47:58
Modified: src/java/org/apache/poi/hssf/model FormulaParser.java
src/testcases/org/apache/poi/hssf/model
TestFormulaParser.java
src/testcases/org/apache/poi/hssf/usermodel
TestFormulas.java
Added: src/testcases/org/apache/poi/hssf/data sumifformula.xls
Log:
Support for the SUMIF formula and non-alpha string literals
Also fixes Bug #17582
Test case for non-alpha string literals provided by [EMAIL PROTECTED] (Jens Riis)
Thanks Avik for your FormulaParser help
Revision Changes Path
1.9 +10 -4
jakarta-poi/src/java/org/apache/poi/hssf/model/FormulaParser.java
Index: FormulaParser.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/java/org/apache/poi/hssf/model/FormulaParser.java,v
retrieving revision 1.8
retrieving revision 1.9
diff -u -r1.8 -r1.9
--- FormulaParser.java 15 Mar 2003 01:57:41 -0000 1.8
+++ FormulaParser.java 17 Mar 2003 04:47:58 -0000 1.9
@@ -214,6 +214,14 @@
return (c ==' ' || c== TAB);
}
+ /**
+ * Determines special characters;primarily in use for definition of string
literals
+ * @param c
+ * @return boolean
+ */
+ private boolean IsSpecialChar(char c) {
+ return (c == '>' || c== '<' || c== '=' || c=='&' || c=='[' || c==']');
+ }
/** Skip Over Leading White Space */
@@ -253,10 +261,8 @@
converting to uppercase; used for literals */
private String GetNameAsIs() {
StringBuffer Token = new StringBuffer();
- if (!IsAlpha(look)) {
- Expected("Name");
- }
- while (IsAlNum(look) || IsWhite(look)) {
+
+ while (IsAlNum(look) || IsWhite(look) || IsSpecialChar(look)) {
Token = Token.append(look);
GetChar();
}
1.1
jakarta-poi/src/testcases/org/apache/poi/hssf/data/sumifformula.xls
<<Binary file>>
1.4 +30 -1
jakarta-poi/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java
Index: TestFormulaParser.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- TestFormulaParser.java 15 Mar 2003 01:57:41 -0000 1.3
+++ TestFormulaParser.java 17 Mar 2003 04:47:58 -0000 1.4
@@ -217,10 +217,39 @@
FuncVarPtg funcPtg = (FuncVarPtg)asts[6];
assertEquals("Arguments", 2, funcPtg.getNumberOfOperands());
-
+ }
+
+ public void testSumIf() {
+ String function ="SUMIF(A1:A5,\">4000\",B1:B5)";
+ FormulaParser fp = new FormulaParser(function, null);
+ fp.parse();
+ Ptg[] asts = fp.getRPNPtg();
+ assertEquals("4 Ptgs expected", 4, asts.length);
}
+
+ /**
+ * Bug Reported by [EMAIL PROTECTED] (Jens Riis)
+ * Refers to Bug <a
href="http://nagoya.apache.org/bugzilla/show_bug.cgi?id=17582";>#17582</a>
+ *
+ */
+ public void testNonAlphaFormula(){
+ String currencyCell = "F3";
+ String function="\"TOTAL[\"&"+currencyCell+"&\"]\"";
+
+ FormulaParser fp = new FormulaParser(function, null);
+ fp.parse();
+ Ptg[] asts = fp.getRPNPtg();
+ assertEquals("5 ptgs expected", 5, asts.length);
+ assertTrue ("Ptg[0] is a string", (asts[0] instanceof StringPtg));
+ StringPtg firstString = (StringPtg)asts[0];
+
+ assertEquals("TOTAL[", firstString.getValue());
+ //the PTG order isn't 100% correct but it still works - dmui
+
+
+ }
public static void main(String [] args) {
System.out.println("Testing
org.apache.poi.hssf.record.formula.FormulaParser");
1.28 +57 -13
jakarta-poi/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
Index: TestFormulas.java
===================================================================
RCS file:
/home/cvs/jakarta-poi/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java,v
retrieving revision 1.27
retrieving revision 1.28
diff -u -r1.27 -r1.28
--- TestFormulas.java 15 Mar 2003 01:57:41 -0000 1.27
+++ TestFormulas.java 17 Mar 2003 04:47:58 -0000 1.28
@@ -55,23 +55,15 @@
package org.apache.poi.hssf.usermodel;
-import junit.framework.TestCase;
-
-import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-import org.apache.poi.hssf.model.Sheet;
-import org.apache.poi.hssf.record.Record;
-import org.apache.poi.hssf.record.BOFRecord;
-import org.apache.poi.hssf.record.EOFRecord;
-import org.apache.poi.hssf.util.CellReference;
-
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
-
-import java.util.List;
-import java.util.Iterator;
+import java.io.IOException;
import java.util.Date;
-import java.util.GregorianCalendar;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.util.CellReference;
/**
* @author Andrew C. Oliver (acoliver at apache dot org)
@@ -985,6 +977,58 @@
assertTrue("length of nestedIf file is zero", (nestedIf.length()>0));
}
+ public void testSumIf()
+ throws java.io.IOException
+ {
+ String readFilename = System.getProperty("HSSF.testdata.path");
+ String function ="SUMIF(A1:A5,\">4000\",B1:B5)";
+
+ File inFile = new File(readFilename+"/sumifformula.xls");
+ FileInputStream in = new FileInputStream(inFile);
+ HSSFWorkbook wb = new HSSFWorkbook(in);
+ in.close();
+
+ HSSFSheet s = wb.getSheetAt(0);
+ HSSFRow r = s.getRow(0);
+ HSSFCell c = r.getCell((short)2);
+ assertEquals(function, c.getCellFormula());
+
+
+ File file = File.createTempFile("testSumIfFormula",".xls");
+ FileOutputStream out = new FileOutputStream(file);
+ wb = new HSSFWorkbook();
+ s = wb.createSheet();
+
+ r = s.createRow((short)0);
+ c=r.createCell((short)0); c.setCellValue((double)1000);
+ c=r.createCell((short)1); c.setCellValue((double)1);
+
+
+ r = s.createRow((short)1);
+ c=r.createCell((short)0); c.setCellValue((double)2000);
+ c=r.createCell((short)1); c.setCellValue((double)2);
+
+ r = s.createRow((short)2);
+ c=r.createCell((short)0); c.setCellValue((double)3000);
+ c=r.createCell((short)1); c.setCellValue((double)3);
+
+ r = s.createRow((short)3);
+ c=r.createCell((short)0); c.setCellValue((double)4000);
+ c=r.createCell((short)1); c.setCellValue((double)4);
+
+ r = s.createRow((short)4);
+ c=r.createCell((short)0); c.setCellValue((double)5000);
+ c=r.createCell((short)1); c.setCellValue((double)5);
+
+ r = s.getRow(0);
+ c=r.createCell((short)2); c.setCellFormula(function);
+
+ wb.write(out);
+ out.close();
+
+ assertTrue("sumif file doesnt exists", (file.exists()));
+ assertTrue("sumif == 0 bytes", file.length() > 0);
+ }
public static void main(String [] args) {
System.out
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
