testformulaparser.java

java 读写word excel ppt

		assertEquals("11th Ptg is not a goto (Attr) ptg",AttrPtg.class,ptgs[10].getClass());
	}
	
	public void testEmbeddedIf() {
		FormulaParser fp=new FormulaParser("IF(3>=1,\"*\",IF(4<>1,\"first\",\"second\"))",null);
		fp.parse();
		Ptg[] ptgs = fp.getRPNPtg();
		assertTrue("Ptg array should not be null", ptgs !=null);
		assertEquals("Ptg array length", 17, ptgs.length);
		
		assertEquals("6th Ptg is not a goto (Attr) ptg",AttrPtg.class,ptgs[5].getClass());
		assertEquals("9th Ptg is not a not equal ptg",NotEqualPtg.class,ptgs[8].getClass());
		assertEquals("15th Ptg is not the inner IF variable function ptg",FuncVarPtg.class,ptgs[14].getClass());
		
	}
	    
    public void testMacroFunction() {
        Workbook w = new Workbook();
        FormulaParser fp = new FormulaParser("FOO()", w);
        fp.parse();
        Ptg[] ptg = fp.getRPNPtg();

        AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[0];
        assertEquals("externalflag", tfunc.getName());

        NamePtg tname = (NamePtg) ptg[1];
        assertEquals("FOO", tname.toFormulaString(w));
    }

    public void testEmbeddedSlash() {
        FormulaParser fp = new FormulaParser("HYPERLINK(\"http://www.jakarta.org\",\"Jakarta\");",null);
        fp.parse();
        Ptg[] ptg = fp.getRPNPtg();
        assertTrue("first ptg is string",ptg[0] instanceof StringPtg);
        assertTrue("second ptg is string",ptg[1] instanceof StringPtg);
        
    }
    
    public void testConcatenate(){
         FormulaParser fp = new FormulaParser("CONCATENATE(\"first\",\"second\")",null);
         fp.parse();
         Ptg[] ptg = fp.getRPNPtg();
        assertTrue("first ptg is string",ptg[0] instanceof StringPtg);
        assertTrue("second ptg is string",ptg[1] instanceof StringPtg);
    }
    
    public void testWorksheetReferences()
    {
    	HSSFWorkbook wb = new HSSFWorkbook();
    	
    	wb.createSheet("NoQuotesNeeded");
    	wb.createSheet("Quotes Needed Here &#$@");
    	
    	HSSFSheet sheet = wb.createSheet("Test");
    	HSSFRow row = sheet.createRow(0);
    	HSSFCell cell;
    	
    	cell = row.createCell((short)0);
    	cell.setCellFormula("NoQuotesNeeded!A1");
    	
    	cell = row.createCell((short)1);
    	cell.setCellFormula("'Quotes Needed Here &#$@'!A1");
    }
    
    public void testUnaryMinus()
    {
		FormulaParser fp = new FormulaParser("-A1", null);
		fp.parse();
		Ptg[] ptg = fp.getRPNPtg();
		assertTrue("got 2 ptgs", ptg.length == 2);
		assertTrue("first ptg is reference",ptg[0] instanceof ReferencePtg);
		assertTrue("second ptg is Minus",ptg[1] instanceof UnaryMinusPtg);
     }
    
    public void testUnaryPlus()
    {
		FormulaParser fp = new FormulaParser("+A1", null);
		fp.parse();
		Ptg[] ptg = fp.getRPNPtg();
		assertTrue("got 2 ptgs", ptg.length == 2);
		assertTrue("first ptg is reference",ptg[0] instanceof ReferencePtg);
		assertTrue("second ptg is Plus",ptg[1] instanceof UnaryPlusPtg);
     }
    
	public void testLeadingSpaceInString()
	{
		String value = "  hi  ";
		FormulaParser fp = new FormulaParser("\"" + value + "\"", null);
		fp.parse();
		Ptg[] ptg = fp.getRPNPtg();
    
		assertTrue("got 1 ptg", ptg.length == 1);
		assertTrue("ptg0 is a StringPtg", ptg[0] instanceof StringPtg);
		assertTrue("ptg0 contains exact value", ((StringPtg)ptg[0]).getValue().equals(value));
	}

	public void testLookupAndMatchFunctionArgs()
	{
		FormulaParser fp = new FormulaParser("lookup(A1, A3:A52, B3:B52)", null);
		fp.parse();
		Ptg[] ptg = fp.getRPNPtg();
    
		assertTrue("got 4 ptg", ptg.length == 4);
		assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
		
		fp = new FormulaParser("match(A1, A3:A52)", null);
		fp.parse();
		ptg = fp.getRPNPtg();
    
		assertTrue("got 3 ptg", ptg.length == 3);
		assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
	}
	
	/** bug 33160*/
	public void testLargeInt() {
		FormulaParser fp = new FormulaParser("40", null);
		fp.parse();
		Ptg[] ptg=fp.getRPNPtg();
		assertTrue("ptg is Int, is "+ptg[0].getClass(),ptg[0] instanceof IntPtg);
		
		fp = new FormulaParser("40000", null);
		fp.parse();
		ptg=fp.getRPNPtg();
		assertTrue("ptg should be  Number, is "+ptg[0].getClass(), ptg[0] instanceof NumberPtg);
	}
	/** bug 33160, testcase by Amol Deshmukh*/
	public void testSimpleLongFormula() {
		        FormulaParser fp = new FormulaParser("40000/2", null);
		        fp.parse();
		        Ptg[] ptgs = fp.getRPNPtg();
		        assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
		        assertTrue("NumberPtg",(ptgs[0] instanceof NumberPtg));
		        assertTrue("IntPtg",(ptgs[1] instanceof IntPtg));
		        assertTrue("DividePtg",(ptgs[2] instanceof DividePtg));
	}
	
	/** bug 35027, underscore in sheet name*/
	public void testUnderscore() {
		HSSFWorkbook wb = new HSSFWorkbook();
    	
    	wb.createSheet("Cash_Flow");;
    	
    	HSSFSheet sheet = wb.createSheet("Test");
    	HSSFRow row = sheet.createRow(0);
    	HSSFCell cell;
    	
    	cell = row.createCell((short)0);
    	cell.setCellFormula("Cash_Flow!A1");
		
	}

    // bug 38396 : Formula with exponential numbers not parsed correctly.
    public void testExponentialParsing() {
        FormulaParser fp = new FormulaParser("1.3E21/2", null);
        fp.parse();
        Ptg[] ptgs = fp.getRPNPtg();
        assertTrue("three tokens expected, got " + ptgs.length, ptgs.length == 3);
        assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
        assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
        assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));

        fp = new FormulaParser("1322E21/2", null);
        fp.parse();
        ptgs = fp.getRPNPtg();
        assertTrue("three tokens expected, got " + ptgs.length, ptgs.length == 3);
        assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
        assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
        assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));

        fp = new FormulaParser("1.3E1/2", null);
        fp.parse();
        ptgs = fp.getRPNPtg();
        assertTrue("three tokens expected, got " + ptgs.length, ptgs.length == 3);
        assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
        assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
        assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));

    }
    public void testExponentialInSheet() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();

        wb.createSheet("Cash_Flow");;

        HSSFSheet sheet = wb.createSheet("Test");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell((short)0);
        String formula = null;

        cell.setCellFormula("1.3E21/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "1.3E21/3", formula);

        cell.setCellFormula("-1.3E21/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-1.3E21/3", formula);

        cell.setCellFormula("1322E21/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "1.322E24/3", formula);

        cell.setCellFormula("-1322E21/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-1.322E24/3", formula);

        cell.setCellFormula("1.3E1/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "13.0/3", formula);

        cell.setCellFormula("-1.3E1/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-13.0/3", formula);

        cell.setCellFormula("1.3E-4/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "1.3E-4/3", formula);

        cell.setCellFormula("-1.3E-4/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-1.3E-4/3", formula);

        cell.setCellFormula("13E-15/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "1.3E-14/3", formula);

        cell.setCellFormula("-13E-15/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-1.3E-14/3", formula);

        cell.setCellFormula("1.3E3/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "1300.0/3", formula);

        cell.setCellFormula("-1.3E3/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-1300.0/3", formula);

        cell.setCellFormula("1300000000000000/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "1.3E15/3", formula);

        cell.setCellFormula("-1300000000000000/3");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-1.3E15/3", formula);

        cell.setCellFormula("-10E-1/3.1E2*4E3/3E4");
        formula = cell.getCellFormula();
        assertEquals("Exponential formula string", "-1.0/310.0*4000.0/30000.0", formula);
    }

     public static void main(String [] args) {
        System.out.println("Testing org.apache.poi.hssf.record.formula.FormulaParser");
        junit.textui.TestRunner.run(TestFormulaParser.class);
    }
}