
/* ====================================================================
 * The Apache Software License, Version 1.1
 *
 * Copyright (c) 2003, 2003 The Apache Software Foundation.  All rights
 * reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. The end-user documentation included with the redistribution,
 *    if any, must include the following acknowledgment:
 *       "This product includes software developed by the
 *        Apache Software Foundation (http://www.apache.org/)."
 *    Alternately, this acknowledgment may appear in the software itself,
 *    if and wherever such third-party acknowledgments normally appear.
 *
 * 4. The names "Apache" and "Apache Software Foundation" and
 *    "Apache POI" must not be used to endorse or promote products
 *    derived from this software without prior written permission. For
 *    written permission, please contact apache@apache.org.
 *
 * 5. Products derived from this software may not be called "Apache",
 *    "Apache POI", nor may "Apache" appear in their name, without
 *    prior written permission of the Apache Software Foundation.
 *
 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 * ====================================================================
 *
 * This software consists of voluntary contributions made by many
 * individuals on behalf of the Apache Software Foundation.  For more
 * information on the Apache Software Foundation, please see
 * <http://www.apache.org/>.
 */

package org.apache.poi.hssf.usermodel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import junit.framework.TestCase;

import org.apache.poi.hssf.util.CellReference;

/**
 * @author Andrew C. Oliver (acoliver at apache dot org)
 * @author Avik Sengupta
 */

public class TestFormulas
extends TestCase {
    public TestFormulas(String s) {
        super(s);
    }
    
    /**
     * Add 1+1 -- WHoohoo!
     */
    
    public void testBasicAddIntegers()
    throws Exception {
        
        short            rownum = 0;
        File file = File.createTempFile("testFormula",".xls");
        FileOutputStream out    = new FileOutputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook();
        HSSFSheet        s      = wb.createSheet();
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        //get our minimum values
        r = s.createRow((short)1);
        c = r.createCell((short)1);
        c.setCellFormula(1 + "+" + 1);
        
        wb.write(out);
        out.close();
        
        FileInputStream in = new FileInputStream(file);
        wb = new HSSFWorkbook(in);
        s  = wb.getSheetAt(0);
        r  = s.getRow((short)1);
        c  = r.getCell((short)1);
        
        assertTrue("Formula is as expected",("1+1".equals(c.getCellFormula())));
        in.close();
    }
    
    /**
     * Add various integers
     */
    
    public void testAddIntegers()
    throws Exception {
        binomialOperator("+");
    }
    
    /**
     * Multiply various integers
     */
    
    public void testMultplyIntegers()
    throws Exception {
        binomialOperator("*");
    }
    
    /**
     * Subtract various integers
     */
    public void testSubtractIntegers()
    throws Exception {
        binomialOperator("-");
    }
    
    /**
     * Subtract various integers
     */
    public void testDivideIntegers()
    throws Exception {
        binomialOperator("/");
    }
    
    /**
     * Exponentialize various integers;
     */
    public void testPowerIntegers()
    throws Exception {
        binomialOperator("^");
    }

    /**
     * Concatinate two numbers 1&2 = 12
     */
    public void testConcatIntegers() 
    throws Exception {
        binomialOperator("&");
    }
    
    /**
     * tests 1*2+3*4
     */
    public void testOrderOfOperationsMultiply() 
    throws Exception {
        orderTest("1*2+3*4");
    }
    
    /**
     * tests 1*2+3^4
     */
    public void testOrderOfOperationsPower() 
    throws Exception {
        orderTest("1*2+3^4");
    }
    
    /**
     * Tests that parenthesis are obeyed
     */
    public void testParenthesis() 
    throws Exception {
        orderTest("(1*3)+2+(1+2)*(3^4)^5");
    }
    
    public void testReferencesOpr() 
    throws Exception {
        String[] operation = new String[] {
                            "+", "-", "*", "/", "^", "&"
                           };
        for (int k = 0; k < operation.length; k++) {
            operationRefTest(operation[k]);
        }
    }
    
    /**
     * Tests creating a file with floating point in a formula.
     *
     */
    public void testFloat()
    throws Exception {
        floatTest("*");
        floatTest("/");
    }
    
    private void floatTest(String operator)
    throws Exception {
        short            rownum = 0;
        File file = File.createTempFile("testFormulaFloat",".xls");
        FileOutputStream out    = new FileOutputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook();
        HSSFSheet        s      = wb.createSheet();
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        //get our minimum values
        
        r = s.createRow((short)0);
        c = r.createCell((short)1);
        c.setCellFormula(""+Float.MIN_VALUE + operator + Float.MIN_VALUE);
 
       for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2) ) {
            r = s.createRow((short) x);
            
            for (short y = 1; y < 256 && y > 0; y= (short) (y +2)) {
                
                c = r.createCell((short) y);
                c.setCellFormula("" + x+"."+y + operator + y +"."+x);
                
                
            }
        }
        if (s.getLastRowNum() < Short.MAX_VALUE) {
            r = s.createRow((short)0);
            c = r.createCell((short)0);
            c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE);
        }
        wb.write(out);
        out.close();
        assertTrue("file exists",file.exists());
        out=null;wb=null;  //otherwise we get out of memory error!
        floatVerify(operator,file);
        
    }
        
            private void floatVerify(String operator, File file)
    throws Exception {
        short            rownum = 0;
        
        FileInputStream  in     = new FileInputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook(in);
        HSSFSheet        s      = wb.getSheetAt(0);
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        // dont know how to check correct result .. for the moment, we just verify that the file can be read. 
        
        for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
            r = s.getRow((short) x);

            for (short y = 1; y < 256 && y > 0; y=(short)(y+2)) {

                c = r.getCell((short) y);
                assertTrue("got a formula",c.getCellFormula()!=null);
                
                assertTrue("loop Formula is as expected "+x+"."+y+operator+y+"."+x+"!="+c.getCellFormula(),(
                (""+x+"."+y+operator+y+"."+x).equals(c.getCellFormula()) ));
 
            }
        }
        
       in.close();
       assertTrue("file exists",file.exists());
    }
    
    public void testAreaSum() 
    throws Exception {
        areaFunctionTest("SUM");
    }
    
    public void testAreaAverage() 
    throws Exception {
        areaFunctionTest("AVERAGE");
    }
    
    public void testRefArraySum() 
    throws Exception {
        refArrayFunctionTest("SUM");
    }
    
    public void testAreaArraySum() 
    throws Exception {
        refAreaArrayFunctionTest("SUM");
    }

    
    private void operationRefTest(String operator) 
    throws Exception {
        File file = File.createTempFile("testFormula",".xls");
        FileOutputStream out    = new FileOutputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook();
        HSSFSheet        s      = wb.createSheet();
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        //get our minimum values
        r = s.createRow((short)0);
        c = r.createCell((short)1);
        c.setCellFormula("A2" + operator + "A3");
        
        for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
            r = s.createRow((short) x);

            for (short y = 1; y < 256 && y > 0; y++) {                
                
                String ref=null;
                String ref2=null;
                short refx1=0;
                short refy1=0;
                short refx2=0;
                short refy2=0;
                if (x +50 < Short.MAX_VALUE) {
                    refx1=(short)(x+50);
                    refx2=(short)(x+46);
                } else {
                    refx1=(short)(x-4);
                    refx2=(short)(x-3);
                }
                    
                if (y+50 < 255) {
                    refy1=(short)(y+50);
                    refy2=(short)(y+49);
                } else {
                    refy1=(short)(y-4);
                    refy2=(short)(y-3);
                }
                
                c = r.getCell((short) y);
                CellReference cr= new CellReference(refx1,refy1);
                ref=cr.toString();
                cr=new CellReference(refx2,refy2);
                ref2=cr.toString();

                c = r.createCell((short) y);
                c.setCellFormula("" + ref + operator + ref2);
                

            }
        }
        
        //make sure we do the maximum value of the Int operator
        if (s.getLastRowNum() < Short.MAX_VALUE) {
            r = s.createRow((short)0);
            c = r.createCell((short)0);
            c.setCellFormula("" + "B1" + operator + "IV255");
        }
        
        wb.write(out);
        out.close();
        assertTrue("file exists",file.exists());
        operationalRefVerify(operator,file);
    }
    
    /**
     * Opens the sheet we wrote out by binomialOperator and makes sure the formulas
     * all match what we expect (x operator y)
     */
    private void operationalRefVerify(String operator, File file)
    throws Exception {
        short            rownum = 0;
        
        FileInputStream  in     = new FileInputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook(in);
        HSSFSheet        s      = wb.getSheetAt(0);
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        //get our minimum values
        r = s.getRow((short)0);
        c = r.getCell((short)1);
        //get our minimum values
        assertTrue("minval Formula is as expected A2"+operator+"A3 != "+c.getCellFormula(),
        ( ("A2"+operator+"A3").equals(c.getCellFormula())
        ));

        
        for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
            r = s.getRow((short) x);

            for (short y = 1; y < 256 && y > 0; y++) {                
                
                String ref=null;
                String ref2=null;
                short refx1=0;
                short refy1=0;
                short refx2=0;
                short refy2=0;
                if (x +50 < Short.MAX_VALUE) {
                    refx1=(short)(x+50);
                    refx2=(short)(x+46);
                } else {
                    refx1=(short)(x-4);
                    refx2=(short)(x-3);
                }
                    
                if (y+50 < 255) {
                    refy1=(short)(y+50);
                    refy2=(short)(y+49);
                } else {
                    refy1=(short)(y-4);
                    refy2=(short)(y-3);
                }

                c = r.getCell((short) y);
                CellReference cr= new CellReference(refx1,refy1);
                ref=cr.toString();
                cr=new CellReference(refx2,refy2);
                ref2=cr.toString();
                
                
                assertTrue("loop Formula is as expected "+ref+operator+ref2+"!="+c.getCellFormula(),(
                (""+ref+operator+ref2).equals(c.getCellFormula())
                                                         )
                );
                
                
            }
        }
        
        //test our maximum values
        r = s.getRow((short)0);
        c = r.getCell((short)0);
                
        assertTrue("maxval Formula is as expected",(
        ("B1"+operator+"IV255").equals(c.getCellFormula())
                                                   )
        );
        
        in.close();
        assertTrue("file exists",file.exists());
    }
    
    
    /**
     * tests order wrting out == order writing in for a given formula
     */    
    private void orderTest(String formula) 
    throws Exception {
        File file = File.createTempFile("testFormula",".xls");
        FileOutputStream out    = new FileOutputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook();
        HSSFSheet        s      = wb.createSheet();
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        //get our minimum values
        r = s.createRow((short)0);
        c = r.createCell((short)1);
        c.setCellFormula(formula);
                       
        wb.write(out);
        out.close();
        assertTrue("file exists",file.exists());

        FileInputStream  in     = new FileInputStream(file);
        wb     = new HSSFWorkbook(in);
        s      = wb.getSheetAt(0);
        
        //get our minimum values
        r = s.getRow((short)0);
        c = r.getCell((short)1);
        assertTrue("minval Formula is as expected",
                   formula.equals(c.getCellFormula())
                  );
        
        in.close();
    }
    
    /**
     * All multi-binomial operator tests use this to create a worksheet with a
     * huge set of x operator y formulas.  Next we call binomialVerify and verify
     * that they are all how we expect.
     */
    private void binomialOperator(String operator)
    throws Exception {
        short            rownum = 0;
        File file = File.createTempFile("testFormula",".xls");
        FileOutputStream out    = new FileOutputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook();
        HSSFSheet        s      = wb.createSheet();
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        //get our minimum values
        r = s.createRow((short)0);
        c = r.createCell((short)1);
        c.setCellFormula(1 + operator + 1);
        
        for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
            r = s.createRow((short) x);

            for (short y = 1; y < 256 && y > 0; y++) {

                c = r.createCell((short) y);
                c.setCellFormula("" + x + operator + y);
                
            }
        }
        
        //make sure we do the maximum value of the Int operator
        if (s.getLastRowNum() < Short.MAX_VALUE) {
            r = s.createRow((short)0);
            c = r.createCell((short)0);
            c.setCellFormula("" + Short.MAX_VALUE + operator + Short.MAX_VALUE);
        }
        
        wb.write(out);
        out.close();
        assertTrue("file exists",file.exists());
        
        binomialVerify(operator,file);
    }
    
    /**
     * Opens the sheet we wrote out by binomialOperator and makes sure the formulas
     * all match what we expect (x operator y)
     */
    private void binomialVerify(String operator, File file)
    throws Exception {
        short            rownum = 0;
        
        FileInputStream  in     = new FileInputStream(file);
        HSSFWorkbook     wb     = new HSSFWorkbook(in);
        HSSFSheet        s      = wb.getSheetAt(0);
        HSSFRow          r      = null;
        HSSFCell         c      = null;
        
        //get our minimum values
        r = s.getRow((short)0);
        c = r.getCell((short)1);
        assertTrue("minval Formula is as expected 1"+operator+"1 != "+c.getCellFormula(),
        ( ("1"+operator+"1").equals(c.getCellFormula())
        ));
        
        for (short x = 1; x < Short.MAX_VALUE && x > 0; x=(short)(x*2)) {
            r = s.getRow((short) x);

            for (short y = 1; y < 256 && y > 0; y++) {

                c = r.getCell((short) y);

                assertTrue("loop Formula is as expected "+x+operator+y+"!="+c.getCellFormula(),(
                (""+x+operator+y).equals(c.getCellFormula())
                                                         )
                );
                
                
            }
        }
        
        //test our maximum values
        r = s.getRow((short)0);
        c = r.getCell((short)0);
        
        
        assertTrue("maxval Formula is as expected",(
        (""+Short.MAX_VALUE+operator+Short.MAX_VALUE).equals(c.getCellFormula())
                                                   )
        );
        
        in.close();
        assertTrue("file exists",file.exists());
    }

 
    /**
     * Writes a function then tests to see if its correct
     *
     */
    public void areaFunctionTest(String function) 
    throws Exception {
            
            short            rownum = 0;
            File file = File.createTempFile("testFormulaAreaFunction"+function,".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet();
            HSSFRow          r      = null;
            HSSFCell         c      = null;


            r = s.createRow((short) 0);

            c = r.createCell((short) 0);
            c.setCellFormula(function+"(A2:A3)");


            wb.write(out);
            out.close();
            assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)0);
            
            assertTrue("function ="+function+"(A2:A3)",
                        ( (function+"(A2:A3)").equals((function+"(A2:A3)")) )
                      );
            in.close();
    }
    
    /**
     * Writes a function then tests to see if its correct
     *
     */
    public void refArrayFunctionTest(String function) 
    throws Exception {
            
            short            rownum = 0;
            File file = File.createTempFile("testFormulaArrayFunction"+function,".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet();
            HSSFRow          r      = null;
            HSSFCell         c      = null;


            r = s.createRow((short) 0);

            c = r.createCell((short) 0);
            c.setCellFormula(function+"(A2,A3)");


            wb.write(out);
            out.close();
            assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)0);
            
            assertTrue("function ="+function+"(A2,A3)",
                        ( (function+"(A2,A3)").equals(c.getCellFormula()) )
                      );
            in.close();
    }
    
    
    /**
     * Writes a function then tests to see if its correct
     *
     */
    public void refAreaArrayFunctionTest(String function) 
    throws Exception {
            
            short            rownum = 0;
            File file = File.createTempFile("testFormulaAreaArrayFunction"+function,".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet();
            HSSFRow          r      = null;
            HSSFCell         c      = null;


            r = s.createRow((short) 0);

            c = r.createCell((short) 0);
            c.setCellFormula(function+"(A2:A4,B2:B4)");
            c=r.createCell((short) 1);
            c.setCellFormula(function+"($A$2:$A4,B$2:B4)");

            wb.write(out);
            out.close();
            assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)0);
            
            assertTrue("function ="+function+"(A2:A4,B2:B4)",
                        ( (function+"(A2:A4,B2:B4)").equals(c.getCellFormula()) )
                      );
            
            c=r.getCell((short) 1);
             assertTrue("function ="+function+"($A$2:$A4,B$2:B4)",
                        ( (function+"($A$2:$A4,B$2:B4)").equals(c.getCellFormula()) )
                      );
            in.close();
    }
    
    
    public void testAbsRefs() throws Exception {
            File file = File.createTempFile("testFormulaAbsRef",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet();
            HSSFRow          r      = null;
            HSSFCell         c      = null;


            r = s.createRow((short) 0);

            c = r.createCell((short) 0);
            c.setCellFormula("A3+A2");
            c=r.createCell( (short) 1);
            c.setCellFormula("$A3+$A2");
            c=r.createCell( (short) 2);
            c.setCellFormula("A$3+A$2");
            c=r.createCell( (short) 3);
            c.setCellFormula("$A$3+$A$2");
            c=r.createCell( (short) 4);
            c.setCellFormula("SUM($A$3,$A$2)");
            
            wb.write(out);
            out.close();
            assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)0);
            assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula()));
             c = r.getCell((short)1);
            assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula()));
             c = r.getCell((short)2);
            assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula()));
             c = r.getCell((short)3);
            assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula()));
             c = r.getCell((short)4);
            assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula()));
            in.close();
    }
    
    public void testSheetFunctions()
        throws IOException
    {
        String filename = System.getProperty("HSSF.testdata.path");

            File file = File.createTempFile("testSheetFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("A");
            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c = r.createCell((short)0);c.setCellValue(1);
            c = r.createCell((short)1);c.setCellValue(2);
            
            s      = wb.createSheet("B");
            r = s.createRow((short)0);
            c=r.createCell((short)0); c.setCellFormula("AVERAGE(A!A1:B1)");
            c=r.createCell((short)1); c.setCellFormula("A!A1+A!B1");
            c=r.createCell((short)2); c.setCellFormula("A!$A$1+A!$B1");
            wb.write(out);
            out.close();
            
             assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheet("B");
            r = s.getRow(0);
            c = r.getCell((short)0);
            assertTrue("expected: AVERAGE(A!A1:B1) got: "+c.getCellFormula(), ("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
            c = r.getCell((short)1);
            assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
            in.close();
    }
    
    public void testRVAoperands() throws Exception {
         File file = File.createTempFile("testFormulaRVA",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet();
            HSSFRow          r      = null;
            HSSFCell         c      = null;


            r = s.createRow((short) 0);

            c = r.createCell((short) 0);
            c.setCellFormula("A3+A2");
            c=r.createCell( (short) 1);
            c.setCellFormula("AVERAGE(A3,A2)");
            c=r.createCell( (short) 2);
            c.setCellFormula("ROW(A3)");
            c=r.createCell( (short) 3);
            c.setCellFormula("AVERAGE(A2:A3)");
            c=r.createCell( (short) 4);
            c.setCellFormula("POWER(A2,A3)");
            c=r.createCell( (short) 5);
            c.setCellFormula("SIN(A2)");
            
            c=r.createCell( (short) 6);
            c.setCellFormula("SUM(A2:A3)");
            
            c=r.createCell( (short) 7);
            c.setCellFormula("SUM(A2,A3)");
            
            r = s.createRow((short) 1);c=r.createCell( (short) 0); c.setCellValue(2.0);
             r = s.createRow((short) 2);c=r.createCell( (short) 0); c.setCellValue(3.0);
            
            wb.write(out);
            out.close();
            assertTrue("file exists",file.exists());
    }
    
    public void testStringFormulas()
        throws IOException
    {
        String readFilename = System.getProperty("HSSF.testdata.path");

            File file = File.createTempFile("testStringFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("A");
            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c=r.createCell((short)1); c.setCellFormula("UPPER(\"abc\")");
            c=r.createCell((short)2); c.setCellFormula("LOWER(\"ABC\")");
            c=r.createCell((short)3); c.setCellFormula("CONCATENATE(\" my \",\" name \")");
            
            wb.write(out);
            out.close();
            
             assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(readFilename+File.separator+"StringFormulas.xls");
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)0);
            assertTrue("expected: UPPER(\"xyz\") got "+c.getCellFormula(), ("UPPER(\"xyz\")").equals(c.getCellFormula()));
            //c = r.getCell((short)1);
            //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
            in.close(); 
    }
    
    
    public void testLogicalFormulas()
        throws IOException
    {

            File file = File.createTempFile("testLogicalFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("A");
            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c=r.createCell((short)1); c.setCellFormula("IF(A1<A2,B1,B2)");

            
            wb.write(out);
            out.close();
            
             assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)1);
            assertEquals("Formula in cell 1 ","IF(A1<A2,B1,B2)",c.getCellFormula());
            in.close(); 
    }
    
    public void testDateFormulas()
        throws IOException
    {
        String readFilename = System.getProperty("HSSF.testdata.path");

            File file = File.createTempFile("testDateFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("Sheet1");
            HSSFRow          r      = null;
            HSSFCell         c      = null;

            r = s.createRow(  (short)0 );
            c = r.createCell( (short)0 );

            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            c.setCellValue(new Date());
            c.setCellStyle(cellStyle);

           // assertEquals("Checking hour = " + hour, date.getTime().getTime(),
           //              HSSFDateUtil.getJavaDate(excelDate).getTime());
           
            for (int k=1; k < 100; k++) { 
              r=s.createRow((short)k);
              c=r.createCell((short)0); 
              c.setCellFormula("A"+(k)+"+1");
              c.setCellStyle(cellStyle);
            }

            wb.write(out);
            out.close();
            
            assertTrue("file exists",file.exists());
            
    }

    
    public void testIfFormulas()
        throws IOException
    {
        String readFilename = System.getProperty("HSSF.testdata.path");

            File file = File.createTempFile("testIfFormula",".xls");
            FileOutputStream out    = new FileOutputStream(file);
            HSSFWorkbook     wb     = new HSSFWorkbook();
            HSSFSheet        s      = wb.createSheet("Sheet1");
            HSSFRow          r      = null;
            HSSFCell         c      = null;
            r = s.createRow((short)0);
            c=r.createCell((short)1); c.setCellValue(1);
            c=r.createCell((short)2); c.setCellValue(2);
            c=r.createCell((short)3); c.setCellFormula("MAX(A1:B1)");
            c=r.createCell((short)4); c.setCellFormula("IF(A1=D1,\"A1\",\"B1\")");
            
            wb.write(out);
            out.close();
            
            assertTrue("file exists",file.exists());
            
            FileInputStream in = new FileInputStream(file);
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(0);
            c = r.getCell((short)4);
            
            assertTrue("expected: IF(A1=D1,\"A1\",\"B1\") got "+c.getCellFormula(), ("IF(A1=D1,\"A1\",\"B1\")").equals(c.getCellFormula()));            
            in.close();
            
            
            in = new FileInputStream(readFilename+File.separator+"IfFormulaTest.xls");
            wb = new HSSFWorkbook(in);
            s = wb.getSheetAt(0);
            r = s.getRow(3);
            c = r.getCell((short)0);
            assertTrue("expected: IF(A3=A1,\"A1\",\"A2\") got "+c.getCellFormula(), ("IF(A3=A1,\"A1\",\"A2\")").equals(c.getCellFormula()));
            //c = r.getCell((short)1);
            //assertTrue("expected: A!A1+A!B1 got: "+c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
            in.close();
            
		File simpleIf = File.createTempFile("testSimpleIfFormulaWrite",".xls");
		out    = new FileOutputStream(simpleIf);
		wb     = new HSSFWorkbook();
		s      = wb.createSheet("Sheet1");
		r      = null;
		c      = null;
		r = s.createRow((short)0);
		c=r.createCell((short)0); c.setCellFormula("IF(1=1,0,1)");
            
		wb.write(out);
		out.close();
		assertTrue("file exists", simpleIf.exists());
			
		assertTrue("length of simpleIf file is zero", (simpleIf.length()>0));
			
		File nestedIf = File.createTempFile("testNestedIfFormula",".xls");
		out    = new FileOutputStream(nestedIf);
		wb     = new HSSFWorkbook();
		s      = wb.createSheet("Sheet1");
		r      = null;
		c      = null;
		r = s.createRow((short)0);
		c=r.createCell((short)0);
		c.setCellValue(1);

		c=r.createCell((short)1);
		c.setCellValue(3);

			
		HSSFCell formulaCell=r.createCell((short)3); 

		r = s.createRow((short)1);
		c=r.createCell((short)0);
		c.setCellValue(3);

		c=r.createCell((short)1);
		c.setCellValue(7);

		formulaCell.setCellFormula("IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))");

            
		wb.write(out);
		out.close();
		assertTrue("file exists", nestedIf.exists());
			
		assertTrue("length of nestedIf file is zero", (nestedIf.length()>0));             
    }

	public void testSumIf()
		throws 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 void testSquareMacro() throws IOException {
        File dir = new File(System.getProperty("HSSF.testdata.path"));
        File xls = new File(dir, "SquareMacro.xls");
        FileInputStream in = new FileInputStream(xls);
        HSSFWorkbook w;
        try {
            w = new HSSFWorkbook(in);
        } finally {
            in.close();
        }
        HSSFSheet s0 = w.getSheetAt(0);
        HSSFRow[] r = {s0.getRow(0), s0.getRow(1)};

        HSSFCell a1 = r[0].getCell((short) 0);
        assertEquals("square(1)", a1.getCellFormula());
        assertEquals(1d, a1.getNumericCellValue(), 1e-9);

        HSSFCell a2 = r[1].getCell((short) 0);
        assertEquals("square(2)", a2.getCellFormula());
        assertEquals(4d, a2.getNumericCellValue(), 1e-9);

        HSSFCell b1 = r[0].getCell((short) 1);
        assertEquals("IF(TRUE,square(1))", b1.getCellFormula());
        assertEquals(1d, b1.getNumericCellValue(), 1e-9);

        HSSFCell b2 = r[1].getCell((short) 1);
        assertEquals("IF(TRUE,square(2))", b2.getCellFormula());
        assertEquals(4d, b2.getNumericCellValue(), 1e-9);

        HSSFCell c1 = r[0].getCell((short) 2);
        assertEquals("square(square(1))", c1.getCellFormula());
        assertEquals(1d, c1.getNumericCellValue(), 1e-9);

        HSSFCell c2 = r[1].getCell((short) 2);
        assertEquals("square(square(2))", c2.getCellFormula());
        assertEquals(16d, c2.getNumericCellValue(), 1e-9);

        HSSFCell d1 = r[0].getCell((short) 3);
        assertEquals("square(one())", d1.getCellFormula());
        assertEquals(1d, d1.getNumericCellValue(), 1e-9);

        HSSFCell d2 = r[1].getCell((short) 3);
        assertEquals("square(two())", d2.getCellFormula());
        assertEquals(4d, d2.getNumericCellValue(), 1e-9);
    }

    public void testStringFormulaRead() throws IOException {
        File dir = new File(System.getProperty("HSSF.testdata.path"));
        File xls = new File(dir, "StringFormulas.xls");
        FileInputStream in = new FileInputStream(xls);
        HSSFWorkbook w;
        try {
            w = new HSSFWorkbook(in);
        } finally {
            in.close();
        }
        HSSFCell c = w.getSheetAt(0).getRow(0).getCell((short)0);
        assertEquals("String Cell value","XYZ",c.getStringCellValue());
    }
    
    public static void main(String [] args) {
        System.out
        .println("Testing org.apache.poi.hssf.usermodel.TestFormulas");
        junit.textui.TestRunner.run(TestFormulas.class);
    }
    
}