testformulaparser.java

来自「EXCEL read and write」· Java 代码 · 共 948 行 · 第 1/3 页

		confirmStringParse("goto 'considered' harmful");

		confirmStringParse("");
		confirmStringParse("'");
		confirmStringParse("''");
		confirmStringParse("' '");
		confirmStringParse(" ' ");
	}

	public void testParseSumIfSum() {
		String formulaString;
		Ptg[] ptgs;
		ptgs = parseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))");
		formulaString = toFormulaString(ptgs);
		assertEquals("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString);

		ptgs = parseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)");
		formulaString = toFormulaString(ptgs);
		assertEquals("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString);
	}
	public void testParserErrors() {
		parseExpectedException("1 2");
		parseExpectedException(" 12 . 345  ");
		parseExpectedException("1 .23  ");

		parseExpectedException("sum(#NAME)");
		parseExpectedException("1 + #N / A * 2");
		parseExpectedException("#value?");
		parseExpectedException("#DIV/ 0+2");


		parseExpectedException("IF(TRUE)");
		parseExpectedException("countif(A1:B5, C1, D1)");
	}

	private static void parseExpectedException(String formula) {
		try {
			parseFormula(formula);
			throw new AssertionFailedError("expected parse exception");
		} catch (RuntimeException e) {
			// expected during successful test
			FormulaParserTestHelper.confirmParseException(e);
		}
	}

	public void testSetFormulaWithRowBeyond32768_Bug44539() {

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		wb.setSheetName(0, "Sheet1");

		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
		cell.setCellFormula("SUM(A32769:A32770)");
		if("SUM(A-32767:A-32766)".equals(cell.getCellFormula())) {
			fail("Identified bug 44539");
		}
		assertEquals("SUM(A32769:A32770)", cell.getCellFormula());
	}

	public void testSpaceAtStartOfFormula() {
		// Simulating cell formula of "= 4" (note space)
		// The same Ptg array can be observed if an excel file is saved with that exact formula

		AttrPtg spacePtg = AttrPtg.createSpace(AttrPtg.SpaceType.SPACE_BEFORE, 1);
		Ptg[] ptgs = { spacePtg, new IntPtg(4), };
		String formulaString;
		try {
			formulaString = toFormulaString(ptgs);
		} catch (IllegalStateException e) {
			if(e.getMessage().equalsIgnoreCase("too much stuff left on the stack")) {
				throw new AssertionFailedError("Identified bug 44609");
			}
			// else some unexpected error
			throw e;
		}
		// FormulaParser strips spaces anyway
		assertEquals("4", formulaString);

		ptgs = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, AddPtg.instance, };
		formulaString = toFormulaString(ptgs);
		assertEquals("3+4", formulaString);
	}

	/**
	 * Checks some internal error detecting logic ('stack underflow error' in toFormulaString)
	 */
	public void testTooFewOperandArgs() {
		// Simulating badly encoded cell formula of "=/1"
		// Not sure if Excel could ever produce this
		Ptg[] ptgs = {
				// Excel would probably have put tMissArg here
				new IntPtg(1),
				DividePtg.instance,
		};
		try {
			toFormulaString(ptgs);
			fail("Expected exception was not thrown");
		} catch (IllegalStateException e) {
			// expected during successful test
			assertTrue(e.getMessage().startsWith("Too few arguments supplied to operation"));
		}
	}
	/**
	 * Make sure that POI uses the right Func Ptg when encoding formulas.  Functions with variable
	 * number of args should get FuncVarPtg, functions with fixed args should get FuncPtg.<p/>
	 *
	 * Prior to the fix for bug 44675 POI would encode FuncVarPtg for all functions.  In many cases
	 * Excel tolerates the wrong Ptg and evaluates the formula OK (e.g. SIN), but in some cases
	 * (e.g. COUNTIF) Excel fails to evaluate the formula, giving '#VALUE!' instead.
	 */
	public void testFuncPtgSelection() {

		Ptg[] ptgs;
		ptgs = parseFormula("countif(A1:A2, 1)");
		assertEquals(3, ptgs.length);
		if(FuncVarPtg.class == ptgs[2].getClass()) {
			throw new AssertionFailedError("Identified bug 44675");
		}
		assertEquals(FuncPtg.class, ptgs[2].getClass());
		ptgs = parseFormula("sin(1)");
		assertEquals(2, ptgs.length);
		assertEquals(FuncPtg.class, ptgs[1].getClass());
	}

	public void testWrongNumberOfFunctionArgs() {
		confirmArgCountMsg("sin()", "Too few arguments to function 'SIN'. Expected 1 but got 0.");
		confirmArgCountMsg("countif(1, 2, 3, 4)", "Too many arguments to function 'COUNTIF'. Expected 2 but got 4.");
		confirmArgCountMsg("index(1, 2, 3, 4, 5, 6)", "Too many arguments to function 'INDEX'. At most 4 were expected but got 6.");
		confirmArgCountMsg("vlookup(1, 2)", "Too few arguments to function 'VLOOKUP'. At least 3 were expected but got 2.");
	}

	private static void confirmArgCountMsg(String formula, String expectedMessage) {
		HSSFWorkbook book = new HSSFWorkbook();
		try {
			HSSFFormulaParser.parse(formula, book);
			throw new AssertionFailedError("Didn't get parse exception as expected");
		} catch (RuntimeException e) {
			FormulaParserTestHelper.confirmParseException(e, expectedMessage);
		}
	}

	public void testParseErrorExpectedMsg() {

		try {
			parseFormula("round(3.14;2)");
			throw new AssertionFailedError("Didn't get parse exception as expected");
		} catch (RuntimeException e) {
			FormulaParserTestHelper.confirmParseException(e,
					"Parse error near char 10 ';' in specified formula 'round(3.14;2)'. Expected ',' or ')'");
		}

		try {
			parseFormula(" =2+2");
			throw new AssertionFailedError("Didn't get parse exception as expected");
		} catch (RuntimeException e) {
			FormulaParserTestHelper.confirmParseException(e,
					"The specified formula ' =2+2' starts with an equals sign which is not allowed.");
		}
	}

	/**
	 * this function name has a dot in it.
	 */
	public void testParseErrorTypeFunction() {

		Ptg[] ptgs;
		try {
			ptgs = parseFormula("error.type(A1)");


		} catch (IllegalArgumentException e) {
			if (e.getMessage().equals("Invalid Formula cell reference: 'error'")) {
				throw new AssertionFailedError("Identified bug 45334");
			}
			throw e;
		}
		assertEquals(2, ptgs.length);
		assertEquals(FuncPtg.class, ptgs[1].getClass());
		FuncPtg funcPtg = (FuncPtg) ptgs[1];
		assertEquals("ERROR.TYPE", funcPtg.getName());
	}

	public void testNamedRangeThatLooksLikeCell() {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("Sheet1");
		HSSFName name = wb.createName();
		name.setReference("Sheet1!B1");
		name.setNameName("pfy1");

		Ptg[] ptgs;
		try {
			ptgs = HSSFFormulaParser.parse("count(pfy1)", wb);
		} catch (IllegalArgumentException e) {
			if (e.getMessage().equals("Specified colIx (1012) is out of range")) {
				throw new AssertionFailedError("Identified bug 45354");
			}
			throw e;
		}
		assertEquals(2, ptgs.length);
		assertEquals(NamePtg.class, ptgs[0].getClass());

		HSSFCell cell = sheet.createRow(0).createCell(0);
		cell.setCellFormula("count(pfy1)");
		assertEquals("COUNT(pfy1)", cell.getCellFormula());
		try {
			cell.setCellFormula("count(pf1)");
			throw new AssertionFailedError("Expected formula parse execption");
		} catch (RuntimeException e) {
			FormulaParserTestHelper.confirmParseException(e,
					"Specified named range 'pf1' does not exist in the current workbook.");
		}
		cell.setCellFormula("count(fp1)"); // plain cell ref, col is in range
	}

	public void testParseAreaRefHighRow_bug45358() {
		Ptg[] ptgs;
		AreaI aptg;

		HSSFWorkbook book = new HSSFWorkbook();
		book.createSheet("Sheet1");

		ptgs = HSSFFormulaParser.parse("Sheet1!A10:A40000", book);
		aptg = (AreaI) ptgs[0];
		if (aptg.getLastRow() == -25537) {
			throw new AssertionFailedError("Identified bug 45358");
		}
		assertEquals(39999, aptg.getLastRow());

		ptgs = HSSFFormulaParser.parse("Sheet1!A10:A65536", book);
		aptg = (AreaI) ptgs[0];
		assertEquals(65535, aptg.getLastRow());

		// plain area refs should be ok too
		ptgs = parseFormula("A10:A65536");
		aptg = (AreaI) ptgs[0];
		assertEquals(65535, aptg.getLastRow());

	}
	public void testParseArray()  {
		Ptg[] ptgs;
		ptgs = parseFormula("mode({1,2,2,#REF!;FALSE,3,3,2})");
		assertEquals(2, ptgs.length);
		Ptg ptg0 = ptgs[0];
		assertEquals(ArrayPtg.class, ptg0.getClass());
		assertEquals("{1.0,2.0,2.0,#REF!;FALSE,3.0,3.0,2.0}", ptg0.toFormulaString());

		ArrayPtg aptg = (ArrayPtg) ptg0;
		Object[][] values = aptg.getTokenArrayValues();
		assertEquals(ErrorConstant.valueOf(HSSFErrorConstants.ERROR_REF), values[0][3]);
		assertEquals(Boolean.FALSE, values[1][0]);
	}

	public void testRangeOperator() {

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCell cell = sheet.createRow(0).createCell(0);

		wb.setSheetName(0, "Sheet1");
		cell.setCellFormula("Sheet1!B$4:Sheet1!$C1"); // explicit range ':' operator
		assertEquals("Sheet1!B$4:Sheet1!$C1", cell.getCellFormula()); 

		cell.setCellFormula("Sheet1!B$4:$C1"); // plain area ref
		assertEquals("Sheet1!B1:$C$4", cell.getCellFormula()); // note - area ref is normalised
		
		cell.setCellFormula("Sheet1!$C1...B$4"); // different syntax for plain area ref
		assertEquals("Sheet1!B1:$C$4", cell.getCellFormula());

		// with funny sheet name
		wb.setSheetName(0, "A1...A2");
		cell.setCellFormula("A1...A2!B1");
		assertEquals("A1...A2!B1", cell.getCellFormula());
	}

	public void testBooleanNamedSheet() {

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("true");
		HSSFCell cell = sheet.createRow(0).createCell(0);
		cell.setCellFormula("'true'!B2");

		assertEquals("'true'!B2", cell.getCellFormula());
	}
	
	public void testParseExternalWorkbookReference() {
		HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
		HSSFCell cell = wbA.getSheetAt(0).getRow(0).getCell(0);

		// make sure formula in sample is as expected
		assertEquals("[multibookFormulaB.xls]BSheet1!B1", cell.getCellFormula());
		Ptg[] expectedPtgs = FormulaExtractor.getPtgs(cell);
		confirmSingle3DRef(expectedPtgs, 1);
		
		// now try (re-)parsing the formula
		Ptg[] actualPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]BSheet1!B1", wbA);
		confirmSingle3DRef(actualPtgs, 1); // externalSheetIndex 1 -> BSheet1
		
		// try parsing a formula pointing to a different external sheet
		Ptg[] otherPtgs = HSSFFormulaParser.parse("[multibookFormulaB.xls]AnotherSheet!B1", wbA);
		confirmSingle3DRef(otherPtgs, 0); // externalSheetIndex 0 -> AnotherSheet
		
		// try setting the same formula in a cell
		cell.setCellFormula("[multibookFormulaB.xls]AnotherSheet!B1");
		assertEquals("[multibookFormulaB.xls]AnotherSheet!B1", cell.getCellFormula());
	}
	private static void confirmSingle3DRef(Ptg[] ptgs, int expectedExternSheetIndex) {
		assertEquals(1, ptgs.length);
		Ptg ptg0 = ptgs[0];
		assertEquals(Ref3DPtg.class, ptg0.getClass());
		assertEquals(expectedExternSheetIndex, ((Ref3DPtg)ptg0).getExternSheetIndex());
	}
	
}