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             HSSFCell c = null;
721             r = s.createRow((short)0);
722             c = r.createCell((short)0);c.setCellValue(1);
723             c = r.createCell((short)1);c.setCellValue(2);
724             
725             s = wb.createSheet("B");
726             r = s.createRow((short)0);
727             c=r.createCell((short)0); c.setCellFormula("AVERAGE(A!A1:B1)");
728             c=r.createCell((short)1); c.setCellFormula("A!A1+A!B1");
729             c=r.createCell((short)2); c.setCellFormula("A!$A$1+A!$B1");
730             wb.write(out);
731             out.close();
732             
733              assertTrue("file exists",file.exists());
734             
735             FileInputStream in = new FileInputStream (file);
736             wb = new HSSFWorkbook(in);
737             s = wb.getSheet("B");
738             r = s.getRow(0);
739             c = r.getCell((short)0);
740             assertTrue("expected: AVERAGE(A!A1:B1) got: "+c.getCellFormula(), ("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
741             c = r.getCell((short)1);
742             assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
743             in.close();
744     }
745     
746     public void testRVAoperands() throws Exception {
747          File file = TempFile.createTempFile("testFormulaRVA",".xls");
748             FileOutputStream out = new FileOutputStream (file);
749             HSSFWorkbook wb = new HSSFWorkbook();
750             HSSFSheet s = wb.createSheet();
751             HSSFRow r = null;
752             HSSFCell c = null;
753
754
755             r = s.createRow((short) 0);
756
757             c = r.createCell((short) 0);
758             c.setCellFormula("A3+A2");
759             c=r.createCell( (short) 1);
760             c.setCellFormula("AVERAGE(A3,A2)");
761             c=r.createCell( (short) 2);
762             c.setCellFormula("ROW(A3)");
763             c=r.createCell( (short) 3);
764             c.setCellFormula("AVERAGE(A2:A3)");
765             c=r.createCell( (short) 4);
766             c.setCellFormula("POWER(A2,A3)");
767             c=r.createCell( (short) 5);
768             c.setCellFormula("SIN(A2)");
769             
770             c=r.createCell( (short) 6);
771             c.setCellFormula("SUM(A2:A3)");
772             
773             c=r.createCell( (short) 7);
774             c.setCellFormula("SUM(A2,A3)");
775             
776             r = s.createRow((short) 1);c=r.createCell( (short) 0); c.setCellValue(2.0);
777              r = s.createRow((short) 2);c=r.createCell( (short) 0); c.setCellValue(3.0);
778             
779             wb.write(out);
780             out.close();
781             assertTrue("file exists",file.exists());
782     }
783     
784     public void testStringFormulas()
785         throws IOException
786     {
787         String readFilename = System.getProperty("HSSF.testdata.path");
788
789             File file = TempFile.createTempFile("testStringFormula",".xls");
790             FileOutputStream out = new FileOutputStream (file);
791             HSSFWorkbook wb = new HSSFWorkbook();
792             HSSFSheet s = wb.createSheet("A");
793             HSSFRow r = null;
794             HSSFCell c = null;
795             r = s.createRow((short)0);
796             c=r.createCell((short)1); c.setCellFormula("UPPER(\"abc\")");
797             c=r.createCell((short)2); c.setCellFormula("LOWER(\"ABC\")");
798             c=r.createCell((short)3); c.setCellFormula("CONCATENATE(\" my \",\" name \")");
799             
800             wb.write(out);
801             out.close();
802             
803              assertTrue("file exists",file.exists());
804             
805             FileInputStream in = new FileInputStream (readFilename+File.separator+"StringFormulas.xls");
806             wb = new HSSFWorkbook(in);
807             s = wb.getSheetAt(0);
808             r = s.getRow(0);
809             c = r.getCell((short)0);
810             assertTrue("expected: UPPER(\"xyz\") got "+c.getCellFormula(), ("UPPER(\"xyz\")").equals(c.getCellFormula()));
811             //c = r.getCell((short)1);
812 //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
813 in.close();
814     }
815     
816     
817     
818     public void testLogicalFormulas()
819         throws IOException
820     {
821
822             File file = TempFile.createTempFile("testLogicalFormula",".xls");
823             FileOutputStream out = new FileOutputStream (file);
824             HSSFWorkbook wb = new HSSFWorkbook();
825             HSSFSheet s = wb.createSheet("A");
826             HSSFRow r = null;
827             HSSFCell c = null;
828             r = s.createRow((short)0);
829             c=r.createCell((short)1); c.setCellFormula("IF(A1<A2,B1,B2)");
830
831             
832             wb.write(out);
833             out.close();
834             
835              assertTrue("file exists",file.exists());
836             
837             FileInputStream in = new FileInputStream (file);
838             wb = new HSSFWorkbook(in);
839             s = wb.getSheetAt(0);
840             r = s.getRow(0);
841             c = r.getCell((short)1);
842             assertEquals("Formula in cell 1 ","IF(A1<A2,B1,B2)",c.getCellFormula());
843             in.close();
844     }
845     
846     public void testDateFormulas()
847         throws IOException
848     {
849         String readFilename = System.getProperty("HSSF.testdata.path");
850
851             File file = TempFile.createTempFile("testDateFormula",".xls");
852             FileOutputStream out = new FileOutputStream (file);
853             HSSFWorkbook wb = new HSSFWorkbook();
854             HSSFSheet s = wb.createSheet("Sheet1");
855             HSSFRow r = null;
856             HSSFCell c = null;
857
858             r = s.createRow( (short)0 );
859             c = r.createCell( (short)0 );
860
861             HSSFCellStyle cellStyle = wb.createCellStyle();
862             cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
863             c.setCellValue(new Date ());
864             c.setCellStyle(cellStyle);
865
866            // assertEquals("Checking hour = " + hour, date.getTime().getTime(),
867 // HSSFDateUtil.getJavaDate(excelDate).getTime());
868
869             for (int k=1; k < 100; k++) {
870               r=s.createRow((short)k);
871               c=r.createCell((short)0);
872               c.setCellFormula("A"+(k)+"+1");
873               c.setCellStyle(cellStyle);
874             }
875
876             wb.write(out);
877             out.close();
878             
879             assertTrue("file exists",file.exists());
880             
881     }
882
883     
884     public void testIfFormulas()
885         throws IOException
886     {
887         String readFilename = System.getProperty("HSSF.testdata.path");
888
889             File file = TempFile.createTempFile("testIfFormula",".xls");
890             FileOutputStream out = new FileOutputStream (file);
891             HSSFWorkbook wb = new HSSFWorkbook();
892             HSSFSheet s = wb.createSheet("Sheet1");
893             HSSFRow r = null;
894             HSSFCell c = null;
895             r = s.createRow((short)0);
896             c=r.createCell((short)1); c.setCellValue(1);
897             c=r.createCell((short)2); c.setCellValue(2);
898             c=r.createCell((short)3); c.setCellFormula("MAX(A1:B1)");
899             c=r.createCell((short)4); c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")");
900             
901             wb.write(out);
902             out.close();
903             
904             assertTrue("file exists",file.exists());
905             
906             FileInputStream in = new FileInputStream (file);
907             wb = new HSSFWorkbook(in);
908             s = wb.getSheetAt(0);
909             r = s.getRow(0);
910             c = r.getCell((short)4);
911             
912             assertTrue("expected: IF(A1=D1,\"A1\",\"B1\") got "+c.getCellFormula(), ("IF(A1=D1,\"A1\",\"B1\")").equals(c.getCellFormula()));
913             in.close();
914             
915             
916             in = new FileInputStream (readFilename+File.separator+"IfFormulaTest.xls");
917             wb = new HSSFWorkbook(in);
918             s = wb.getSheetAt(0);
919             r = s.getRow(3);
920             c = r.getCell((short)0);
921             assertTrue("expected: IF(A3=A1,\"A1\",\"A2\") got "+c.getCellFormula(), ("IF(A3=A1,\"A1\",\"A2\")").equals(c.getCellFormula()));
922             //c = r.getCell((short)1);
923 //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
924 in.close();
925             
926         File simpleIf = TempFile.createTempFile("testSimpleIfFormulaWrite",".xls");
927         out = new FileOutputStream (simpleIf);
928         wb = new HSSFWorkbook();
929         s = wb.createSheet("Sheet1");
930         r = null;
931         c = null;
932         r = s.createRow((short)0);
933         c=r.createCell((short)0); c.setCellFormula("IF(1=1,0,1)");
934             
935         wb.write(out);
936         out.close();
937         assertTrue("file exists", simpleIf.exists());
938             
939         assertTrue("length of simpleIf file is zero", (simpleIf.length()>0));
940             
941         File nestedIf = TempFile.createTempFile("testNestedIfFormula",".xls");
942         out = new FileOutputStream (nestedIf);
943         wb = new HSSFWorkbook();
944         s = wb.createSheet("Sheet1");
945         r = null;
946         c = null;
947         r = s.createRow((short)0);
948         c=r.createCell((short)0);
949         c.setCellValue(1);
950
951         c=r.createCell((short)1);
952         c.setCellValue(3);
953
954             
955         HSSFCell formulaCell=r.createCell((short)3);
956
957         r = s.createRow((short)1);
958         c=r.createCell((short)0);
959         c.setCellValue(3);
960
961         c=r.createCell((short)1);
962         c.setCellValue(7);
963
964         formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");
965
966             
967         wb.write(out);
968         out.close();
969         assertTrue("file exists", nestedIf.exists());
970             
971         assertTrue("length of nestedIf file is zero", (nestedIf.length()>0));
972     }
973
974     public void testSumIf()
975         throws IOException
976     {
977         String readFilename = System.getProperty("HSSF.testdata.path");
978         String function ="SUMIF(A1:A5,\">4000\",B1:B5)";
979         
980         File inFile = new File (readFilename+"/sumifformula.xls");
981         FileInputStream in = new FileInputStream (inFile);
982         HSSFWorkbook wb = new HSSFWorkbook(in);
983         in.close();
984         
985         HSSFSheet s = wb.getSheetAt(0);
986         HSSFRow r = s.getRow(0);
987         HSSFCell c = r.getCell((short)2);
988         assertEquals(function, c.getCellFormula());
989         
990
991         File file = TempFile.createTempFile("testSumIfFormula",".xls");
992         FileOutputStream out = new FileOutputStream (file);
993         wb = new HSSFWorkbook();
994         s = wb.createSheet();
995         
996         r = s.createRow((short)0);
997         c=r.createCell((short)0); c.setCellValue((double)1000);
998         c=r.createCell((short)1); c.setCellValue((double)1);
999         
1000        
1001        r = s.createRow((short)1);
1002        c=r.createCell((short)0); c.setCellValue((double)2000);
1003        c=r.createCell((short)1); c.setCellValue((double)2);
1004
1005        r = s.createRow((short)2);
1006        c=r.createCell((short)0); c.setCellValue((double)3000);
1007        c=r.createCell((short)1); c.setCellValue((double)3);
1008
1009        r = s.createRow((short)3);
1010        c=r.createCell((short)0); c.setCellValue((double)4000);
1011        c=r.createCell((short)1); c.setCellValue((double)4);
1012
1013        r = s.createRow((short)4);
1014        c=r.createCell((short)0); c.setCellValue((double)5000);
1015        c=r.createCell((short)1); c.setCellValue((double)5);
1016        
1017        r = s.getRow(0);
1018        c=r.createCell((short)2); c.setCellFormula(function);
1019            
1020        wb.write(out);
1021        out.close();
1022        
1023        assertTrue("sumif file doesnt exists", (file.exists()));
1024        assertTrue("sumif == 0 bytes", file.length() > 0);
1025    }
1026    
1027    public void testSquareMacro() throws IOException {
1028        File dir = new File (System.getProperty("HSSF.testdata.path"));
1029        File xls = new File (dir, "SquareMacro.xls");
1030        FileInputStream in = new FileInputStream (xls);
1031        HSSFWorkbook w;
1032        try {
1033            w = new HSSFWorkbook(in);
1034        } finally {
1035            in.close();
1036        }
1037        HSSFSheet s0 = w.getSheetAt(0);
1038        HSSFRow[] r = {s0.getRow(0), s0.getRow(1)};
1039
1040        HSSFCell a1 = r[0].getCell((short) 0);
1041        assertEquals("square(1)", a1.getCellFormula());
1042        assertEquals(1d, a1.getNumericCellValue(), 1e-9);
1043
1044        HSSFCell a2 = r[1].getCell((short) 0);
1045        assertEquals("square(2)", a2.getCellFormula());
1046        assertEquals(4d, a2.getNumericCellValue(), 1e-9);
1047
1048        HSSFCell b1 = r[0].getCell((short) 1);
1049        assertEquals("IF(TRUE,square(1))", b1.getCellFormula());
1050        assertEquals(1d, b1.getNumericCellValue(), 1e-9);
1051
1052        HSSFCell b2 = r[1].getCell((short) 1);
1053        assertEquals("IF(TRUE,square(2))", b2.getCellFormula());
1054        assertEquals(4d, b2.getNumericCellValue(), 1e-9);
1055
1056        HSSFCell c1 = r[0].getCell((short) 2);
1057        assertEquals("square(square(1))", c1.getCellFormula());
1058        assertEquals(1d, c1.getNumericCellValue(), 1e-9);
1059
1060        HSSFCell c2 = r[1].getCell((short) 2);
1061        assertEquals("square(square(2))", c2.getCellFormula());
1062        assertEquals(16d, c2.getNumericCellValue(), 1e-9);
1063
1064        HSSFCell d1 = r[0].getCell((short) 3);
1065        assertEquals("square(one())", d1.getCellFormula());
1066        assertEquals(1d, d1.getNumericCellValue(), 1e-9);
1067
1068        HSSFCell d2 = r[1].getCell((short) 3);
1069        assertEquals("square(two())", d2.getCellFormula());
1070        assertEquals(4d, d2.getNumericCellValue(), 1e-9);
1071    }
1072
1073    public void testStringFormulaRead() throws IOException {
1074        File dir = new File (System.getProperty("HSSF.testdata.path"));
1075        File xls = new File (dir, "StringFormulas.xls");
1076        FileInputStream in = new FileInputStream (xls);
1077        HSSFWorkbook w;
1078        try {
1079            w = new HSSFWorkbook(in);
1080        } finally {
1081            in.close();
1082        }
1083        HSSFCell c = w.getSheetAt(0).getRow(0).getCell((short)0);
1084        assertEquals("String Cell value","XYZ",c.getStringCellValue());
1085    }
1086    
1087    public static void main(String [] args) {
1088        System.out
1089        .println("Testing org.apache.poi.hssf.usermodel.TestFormulas");
1090        junit.textui.TestRunner.run(TestFormulas.class);
1091    }
1092    
1093}
1094

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags