Java > Open Source Codes > org > apache > poi > hssf > usermodel > TestFormulas


1
2 /* ====================================================================
3    Copyright 2002-2004 Apache Software Foundation
4
5    Licensed under the Apache License, Version 2.0 (the "License");
6    you may not use this file except in compliance with the License.
7    You may obtain a copy of the License at
8
9        http://www.apache.org/licenses/LICENSE-2.0
10
11    Unless required by applicable law or agreed to in writing, software
12    distributed under the License is distributed on an "AS IS" BASIS,
13    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14    See the License for the specific language governing permissions and
15    limitations under the License.
16 ==================================================================== */
17         
18
19 package org.apache.poi.hssf.usermodel;
20
21 import java.io.File ;
22 import java.io.FileInputStream ;
23 import java.io.FileOutputStream ;
24 import java.io.IOException ;
25 import java.util.Date ;
26
27 import junit.framework.TestCase;
28
29 import org.apache.poi.hssf.util.CellReference;
30 import org.apache.poi.util.TempFile;
31
32 /**
33  * @author Andrew C. Oliver (acoliver at apache dot org)
34  * @author Avik Sengupta
35  */
36
37 public class TestFormulas
38 extends TestCase {
39     public TestFormulas(String s) {
40         super(s);
41     }
42     
43     /**
44      * Add 1+1 -- WHoohoo!
45      */
46     
47     public void testBasicAddIntegers()
48     throws Exception {
49         
50         short rownum = 0;
51         File file = TempFile.createTempFile("testFormula",".xls");
52         FileOutputStream out = new FileOutputStream (file);
53         HSSFWorkbook wb = new HSSFWorkbook();
54         HSSFSheet s = wb.createSheet();
55         HSSFRow r = null;
56         HSSFCell c = null;
57         
58         //get our minimum values
59 r = s.createRow((short)1);
60         c = r.createCell((short)1);
61         c.setCellFormula(1 + "+" + 1);
62         
63         wb.write(out);
64         out.close();
65         
66         FileInputStream in = new FileInputStream (file);
67         wb = new HSSFWorkbook(in);
68         s = wb.getSheetAt(0);
69         r = s.getRow((short)1);
70         c = r.getCell((short)1);
71         
72         assertTrue("Formula is as expected",("1+1".equals(c.getCellFormula())));
73         in.close();
74     }
75     
76     /**
77      * Add various integers
78      */
79     
80     public void testAddIntegers()
81     throws Exception {
82         binomialOperator("+");
83     }
84     
85     /**
86      * Multiply various integers
87      */
88     
89     public void testMultplyIntegers()
90     throws Exception {
91         binomialOperator("*");
92     }
93     
94     /**
95      * Subtract various integers
96      */
97     public void testSubtractIntegers()
98     throws Exception {
99         binomialOperator("-");
100     }
101     
102     /**
103      * Subtract various integers
104      */
105     public void testDivideIntegers()
106     throws Exception {
107         binomialOperator("/");
108     }
109     
110     /**
111      * Exponentialize various integers;
112      */
113     public void testPowerIntegers()
114     throws Exception {
115         binomialOperator("^");
116     }
117
118     /**
119      * Concatinate two numbers 1&2 = 12
120      */
121     public void testConcatIntegers()
122     throws Exception {
123         binomialOperator("&");
124     }
125     
126     /**
127      * tests 1*2+3*4
128      */
129     public void testOrderOfOperationsMultiply()
130     throws Exception {
131         orderTest("1*2+3*4");
132     }
133     
134     /**
135      * tests 1*2+3^4
136      */
137     public void testOrderOfOperationsPower()
138     throws Exception {
139         orderTest("1*2+3^4");
140     }
141     
142     /**
143      * Tests that parenthesis are obeyed
144      */
145     public void testParenthesis()
146     throws Exception {
147         orderTest("(1*3)+2+(1+2)*(3^4)^5");
148     }
149     
150     public void testReferencesOpr()
151     throws Exception {
152         String [] operation = new String [] {
153                             "+", "-", "*", "/", "^", "&"
154                            };
155         for (int k = 0; k < operation.length; k++) {
156             operationRefTest(operation[k]);
157         }
158     }
159     
160     /**
161      * Tests creating a file with floating point in a formula.
162      *
163      */
164     public void testFloat()
165     throws Exception {
166         floatTest("*");
167         floatTest("/");
168     }
169     
170     private void floatTest(String operator)
171     throws Exception {
172         short rownum = 0;
173         File file = TempFile.createTempFile("testFormulaFloat",".xls");
174         FileOutputStream out = new FileOutputStream (file);
175         HSSFWorkbook wb = new HSSFWorkbook();
176         HSSFSheet s = wb.createSheet();
177         HSSFRow r = null;
178         HSSFCell c = null;
179         
180         //get our minimum values
181
182         r = s.createRow((short)0);
183         c = r.createCell((short)1);
184         c.setCellFormula(""+Float.MIN_VALUE + operator + Float.MIN_VALUE);
185  
186        for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2) ) {
187             r = s.createRow((short) x);
188             
189             for (short y = 1; y < 256 && y > 0; y= (short) (y +2)) {
190                 
191                 c = r.createCell((short) y);
192                 c.setCellFormula("" + x+"."+y + operator + y +"."+x);
193                 
194                 
195             }
196         }
197         if (s.getLastRowNum() < Short.MAX_VALUE) {
198             r = s.createRow((short)0);
199             c = r.createCell((short)0);
200             c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE);
201         }
202         wb.write(out);
203         out.close();
204         assertTrue("file exists",file.exists());
205         out=null;wb=null; //otherwise we get out of memory error!
206 floatVerify(operator,file);
207         
208     }
209         
210             private void floatVerify(String operator, File file)
211     throws Exception {
212         short rownum = 0;
213         
214         FileInputStream in = new FileInputStream (file);
215         HSSFWorkbook wb = new HSSFWorkbook(in);
216         HSSFSheet s = wb.getSheetAt(0);
217         HSSFRow r = null;
218         HSSFCell c = null;
219         
220         // dont know how to check correct result .. for the moment, we just verify that the file can be read.
221
222         for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
223             r = s.getRow((short) x);
224
225             for (short y = 1; y < 256 && y > 0; y=(short)(y+2)) {
226
227                 c = r.getCell((short) y);
228                 assertTrue("got a formula",c.getCellFormula()!=null);
229                 
230                 assertTrue("loop Formula is as expected "+x+"."+y+operator+y+"."+x+"!="+c.getCellFormula(),(
231                 (""+x+"."+y+operator+y+"."+x).equals(c.getCellFormula()) ));
232  
233             }
234         }
235         
236        in.close();
237        assertTrue("file exists",file.exists());
238     }
239     
240     public void testAreaSum()
241     throws Exception {
242         areaFunctionTest("SUM");
243     }
244     
245     public void testAreaAverage()
246     throws Exception {
247         areaFunctionTest("AVERAGE");
248     }
249     
250     public void testRefArraySum()
251     throws Exception {
252         refArrayFunctionTest("SUM");
253     }
254     
255     public void testAreaArraySum()
256     throws Exception {
257         refAreaArrayFunctionTest("SUM");
258     }
259
260     
261     
262     private void operationRefTest(String operator)
263     throws Exception {
264         File file = TempFile.createTempFile("testFormula",".xls");
265         FileOutputStream out = new FileOutputStream (file);
266         HSSFWorkbook wb = new HSSFWorkbook();
267         HSSFSheet s = wb.createSheet();
268         HSSFRow r = null;
269         HSSFCell c = null;
270         
271         //get our minimum values
272 r = s.createRow((short)0);
273         c = r.createCell((short)1);
274         c.setCellFormula("A2" + operator + "A3");
275         
276         for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
277             r = s.createRow((short) x);
278
279             for (short y = 1; y < 256 && y > 0; y++) {
280                 
281                 String ref=null;
282                 String ref2=null;
283                 short refx1=0;
284                 short refy1=0;
285                 short refx2=0;
286                 short refy2=0;
287                 if (x +50 < Short.MAX_VALUE) {
288                     refx1=(short)(x+50);
289                     refx2=(short)(x+46);
290                 } else {
291                     refx1=(short)(x-4);
292                     refx2=(short)(x-3);
293                 }
294                     
295                 if (y+50 < 255) {
296                     refy1=(short)(y+50);
297                     refy2=(short)(y+49);
298                 } else {
299                     refy1=(short)(y-4);
300                     refy2=(short)(y-3);
301                 }
302                 
303                 c = r.getCell((short) y);
304                 CellReference cr= new CellReference(refx1,refy1);
305                 ref=cr.toString();
306                 cr=new CellReference(refx2,refy2);
307                 ref2=cr.toString();
308
309                 c = r.createCell((short) y);
310                 c.setCellFormula("" + ref + operator + ref2);
311                 
312
313                 
314             }
315         }
316         
317         //make sure we do the maximum value of the Int operator
318 if (s.getLastRowNum() < Short.MAX_VALUE) {
319             r = s.createRow((short)0);
320             c = r.createCell((short)0);
321             c.setCellFormula("" + "B1" + operator + "IV255");
322         }
323         
324         wb.write(out);
325         out.close();
326         assertTrue("file exists",file.exists());
327         operationalRefVerify(operator,file);
328     }
329     
330     /**
331      * Opens the sheet we wrote out by binomialOperator and makes sure the formulas
332      * all match what we expect (x operator y)
333      */
334     private void operationalRefVerify(String operator, File file)
335     throws Exception {
336         short rownum = 0;
337         
338         FileInputStream in = new FileInputStream (file);
339         HSSFWorkbook wb = new HSSFWorkbook(in);
340         HSSFSheet s = wb.getSheetAt(0);
341         HSSFRow r = null;
342         HSSFCell c = null;
343         
344         //get our minimum values
345 r = s.getRow((short)0);
346         c = r.getCell((short)1);
347         //get our minimum values
348 assertTrue("minval Formula is as expected A2"+operator+"A3 != "+c.getCellFormula(),
349         ( ("A2"+operator+"A3").equals(c.getCellFormula())
350         ));
351
352         
353         for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
354             r = s.getRow((short) x);
355
356             for (short y = 1; y < 256 && y > 0; y++) {
357                 
358                 String ref=null;
359                 String ref2=null;
360                 short refx1=0;
361                 short refy1=0;
362                 short refx2=0;
363                 short refy2=0;
364                 if (x +50 < Short.MAX_VALUE) {
365                     refx1=(short)(x+50);
366                     refx2=(short)(x+46);
367                 } else {
368                     refx1=(short)(x-4);
369                     refx2=(short)(x-3);
370                 }
371                     
372                 if (y+50 < 255) {
373                     refy1=(short)(y+50);
374                     refy2=(short)(y+49);
375                 } else {
376                     refy1=(short)(y-4);
377                     refy2=(short)(y-3);
378                 }
379
380                 c = r.getCell((short) y);
381                 CellReference cr= new CellReference(refx1,refy1);
382                 ref=cr.toString();
383                 cr=new CellReference(refx2,refy2);
384                 ref2=cr.toString();
385                 
386                 
387                 assertTrue("loop Formula is as expected "+ref+operator+ref2+"!="+c.getCellFormula(),(
388                 (""+ref+operator+ref2).equals(c.getCellFormula())
389                                                          )
390                 );
391                 
392                 
393             }
394         }
395         
396         //test our maximum values
397 r = s.getRow((short)0);
398         c = r.getCell((short)0);
399                 
400         assertTrue("maxval Formula is as expected",(
401         ("B1"+operator+"IV255").equals(c.getCellFormula())
402                                                    )
403         );
404         
405         in.close();
406         assertTrue("file exists",file.exists());
407     }
408     
409     
410
411     /**
412      * tests order wrting out == order writing in for a given formula
413      */
414     private void orderTest(String formula)
415     throws Exception {
416         File file = TempFile.createTempFile("testFormula",".xls");
417         FileOutputStream out = new FileOutputStream (file);
418         HSSFWorkbook wb = new HSSFWorkbook();
419         HSSFSheet s = wb.createSheet();
420         HSSFRow r = null;
421         HSSFCell c = null;
422         
423         //get our minimum values
424 r = s.createRow((short)0);
425         c = r.createCell((short)1);
426         c.setCellFormula(formula);
427                        
428         wb.write(out);
429         out.close();
430         assertTrue("file exists",file.exists());
431
432         FileInputStream in = new FileInputStream (file);
433         wb = new HSSFWorkbook(in);
434         s = wb.getSheetAt(0);
435         
436         //get our minimum values
437 r = s.getRow((short)0);
438         c = r.getCell((short)1);
439         assertTrue("minval Formula is as expected",
440                    formula.equals(c.getCellFormula())
441                   );
442         
443         in.close();
444     }
445     
446     /**
447      * All multi-binomial operator tests use this to create a worksheet with a
448      * huge set of x operator y formulas. Next we call binomialVerify and verify
449      * that they are all how we expect.
450      */
451     private void binomialOperator(String operator)
452     throws Exception {
453         short rownum = 0;
454         File file = TempFile.createTempFile("testFormula",".xls");
455         FileOutputStream out = new FileOutputStream (file);
456         HSSFWorkbook wb = new HSSFWorkbook();
457         HSSFSheet s = wb.createSheet();
458         HSSFRow r = null;
459         HSSFCell c = null;
460         
461         //get our minimum values
462 r = s.createRow((short)0);
463         c = r.createCell((short)1);
464         c.setCellFormula(1 + operator + 1);
465         
466         for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
467             r = s.createRow((short) x);
468
469             for (short y = 1; y < 256 && y > 0; y++) {
470
471                 c = r.createCell((short) y);
472                 c.setCellFormula("" + x + operator + y);
473                 
474             }
475         }
476         
477         //make sure we do the maximum value of the Int operator
478 if (s.getLastRowNum() < Short.MAX_VALUE) {
479             r = s.createRow((short)0);
480             c = r.createCell((short)0);
481             c.setCellFormula("" + Short.MAX_VALUE + operator + Short.MAX_VALUE);
482         }
483         
484         wb.write(out);
485         out.close();
486         assertTrue("file exists",file.exists());
487         
488         binomialVerify(operator,file);
489     }
490     
491     /**
492      * Opens the sheet we wrote out by binomialOperator and makes sure the formulas
493      * all match what we expect (x operator y)
494      */
495     private void binomialVerify(String operator, File file)
496     throws Exception {
497         short rownum = 0;
498         
499         FileInputStream in = new FileInputStream (file);
500         HSSFWorkbook wb = new HSSFWorkbook(in);
501         HSSFSheet s = wb.getSheetAt(0);
502         HSSFRow r = null;
503         HSSFCell c = null;
504         
505         //get our minimum values
506 r = s.getRow((short)0);
507         c = r.getCell((short)1);
508         assertTrue("minval Formula is as expected 1"+operator+"1 != "+c.getCellFormula(),
509         ( ("1"+operator+"1").equals(c.getCellFormula())
510         ));
511         
512         for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
513             r = s.getRow((short) x);
514
515             for (short y = 1; y < 256 && y > 0; y++) {
516
517                 c = r.getCell((short) y);
518
519                 assertTrue("loop Formula is as expected "+x+operator+y+"!="+c.getCellFormula(),(
520                 (""+x+operator+y).equals(c.getCellFormula())
521                                                          )
522                 );
523                 
524                 
525             }
526         }
527         
528         //test our maximum values
529 r = s.getRow((short)0);
530         c = r.getCell((short)0);
531         
532         
533         assertTrue("maxval Formula is as expected",(
534         (""+Short.MAX_VALUE+operator+Short.MAX_VALUE).equals(c.getCellFormula())
535                                                    )
536         );
537         
538         in.close();
539         assertTrue("file exists",file.exists());
540     }
541
542  
543     
544     /**
545      * Writes a function then tests to see if its correct
546      *
547      */
548     public void areaFunctionTest(String function)
549     throws Exception {
550             
551             short rownum = 0;
552             File file = TempFile.createTempFile("testFormulaAreaFunction"+function,".xls");
553             FileOutputStream out = new FileOutputStream (file);
554             HSSFWorkbook wb = new HSSFWorkbook();
555             HSSFSheet s = wb.createSheet();
556             HSSFRow r = null;
557             HSSFCell c = null;
558
559
560             r = s.createRow((short) 0);
561
562             c = r.createCell((short) 0);
563             c.setCellFormula(function+"(A2:A3)");
564
565
566             wb.write(out);
567             out.close();
568             assertTrue("file exists",file.exists());
569             
570             FileInputStream in = new FileInputStream (file);
571             wb = new HSSFWorkbook(in);
572             s = wb.getSheetAt(0);
573             r = s.getRow(0);
574             c = r.getCell((short)0);
575             
576             assertTrue("function ="+function+"(A2:A3)",
577                         ( (function+"(A2:A3)").equals((function+"(A2:A3)")) )
578                       );
579             in.close();
580     }
581     
582     /**
583      * Writes a function then tests to see if its correct
584      *
585      */
586     public void refArrayFunctionTest(String function)
587     throws Exception {
588             
589             short rownum = 0;
590             File file = TempFile.createTempFile("testFormulaArrayFunction"+function,".xls");
591             FileOutputStream out = new FileOutputStream (file);
592             HSSFWorkbook wb = new HSSFWorkbook();
593             HSSFSheet s = wb.createSheet();
594             HSSFRow r = null;
595             HSSFCell c = null;
596
597
598             r = s.createRow((short) 0);
599
600             c = r.createCell((short) 0);
601             c.setCellFormula(function+"(A2,A3)");
602
603
604             wb.write(out);
605             out.close();
606             assertTrue("file exists",file.exists());
607             
608             FileInputStream in = new FileInputStream (file);
609             wb = new HSSFWorkbook(in);
610             s = wb.getSheetAt(0);
611             r = s.getRow(0);
612             c = r.getCell((short)0);
613             
614             assertTrue("function ="+function+"(A2,A3)",
615                         ( (function+"(A2,A3)").equals(c.getCellFormula()) )
616                       );
617             in.close();
618     }
619     
620     
621     /**
622      * Writes a function then tests to see if its correct
623      *
624      */
625     public void refAreaArrayFunctionTest(String function)
626     throws Exception {
627             
628             short rownum = 0;
629             File file = TempFile.createTempFile("testFormulaAreaArrayFunction"+function,".xls");
630             FileOutputStream out = new FileOutputStream (file);
631             HSSFWorkbook wb = new HSSFWorkbook();
632             HSSFSheet s = wb.createSheet();
633             HSSFRow r = null;
634             HSSFCell c = null;
635
636
637             r = s.createRow((short) 0);
638
639             c = r.createCell((short) 0);
640             c.setCellFormula(function+"(A2:A4,B2:B4)");
641             c=r.createCell((short) 1);
642             c.setCellFormula(function+"($A$2:$A4,B$2:B4)");
643
644             wb.write(out);
645             out.close();
646             assertTrue("file exists",file.exists());
647             
648             FileInputStream in = new FileInputStream (file);
649             wb = new HSSFWorkbook(in);
650             s = wb.getSheetAt(0);
651             r = s.getRow(0);
652             c = r.getCell((short)0);
653             
654             assertTrue("function ="+function+"(A2:A4,B2:B4)",
655                         ( (function+"(A2:A4,B2:B4)").equals(c.getCellFormula()) )
656                       );
657             
658             c=r.getCell((short) 1);
659              assertTrue("function ="+function+"($A$2:$A4,B$2:B4)",
660                         ( (function+"($A$2:$A4,B$2:B4)").equals(c.getCellFormula()) )
661                       );
662             in.close();
663     }
664     
665     
666     
667     public void testAbsRefs() throws Exception {
668             File file = TempFile.createTempFile("testFormulaAbsRef",".xls");
669             FileOutputStream out = new FileOutputStream (file);
670             HSSFWorkbook wb = new HSSFWorkbook();
671             HSSFSheet s = wb.createSheet();
672             HSSFRow r = null;
673             HSSFCell c = null;
674
675
676             r = s.createRow((short) 0);
677
678             c = r.createCell((short) 0);
679             c.setCellFormula("A3+A2");
680             c=r.createCell( (short) 1);
681             c.setCellFormula("$A3+$A2");
682             c=r.createCell( (short) 2);
683             c.setCellFormula("A$3+A$2");
684             c=r.createCell( (short) 3);
685             c.setCellFormula("$A$3+$A$2");
686             c=r.createCell( (short) 4);
687             c.setCellFormula("SUM($A$3,$A$2)");
688             
689             wb.write(out);
690             out.close();
691             assertTrue("file exists",file.exists());
692             
693             FileInputStream in = new FileInputStream (file);
694             wb = new HSSFWorkbook(in);
695             s = wb.getSheetAt(0);
696             r = s.getRow(0);
697             c = r.getCell((short)0);
698             assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula()));
699              c = r.getCell((short)1);
700             assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula()));
701              c = r.getCell((short)2);
702             assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula()));
703              c = r.getCell((short)3);
704             assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula()));
705              c = r.getCell((short)4);
706             assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula()));
707             in.close();
708     }
709     
710     public void testSheetFunctions()
711         throws IOException
712     {
713         String filename = System.getProperty("HSSF.testdata.path");
714
715             File file = TempFile.createTempFile("testSheetFormula",".xls");
716             FileOutputStream out = new FileOutputStream (file);
717             HSSFWorkbook wb = new HSSFWorkbook();
718             HSSFSheet s = wb.createSheet("A");
719             HSSFRow r = null;
720        &n