testformulas.java

java 读写word excel ppt

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */
        

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;
import org.apache.poi.util.TempFile;

/**
 * @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 = TempFile.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 = TempFile.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 = TempFile.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);
                }