testformulaparser.java

来自「EXCEL read and write」· Java 代码 · 共 948 行 · 第 1/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 void testNumbers() {
		HSSFWorkbook wb = new HSSFWorkbook();

		wb.createSheet("Cash_Flow");

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

		// starts from decimal point

		cell.setCellFormula(".1");
		formula = cell.getCellFormula();
		assertEquals("0.1", formula);

		cell.setCellFormula("+.1");
		formula = cell.getCellFormula();
		assertEquals("+0.1", formula);

		cell.setCellFormula("-.1");
		formula = cell.getCellFormula();
		assertEquals("-0.1", formula);

		// has exponent

		cell.setCellFormula("10E1");
		formula = cell.getCellFormula();
		assertEquals("100.0", formula);

		cell.setCellFormula("10E+1");
		formula = cell.getCellFormula();
		assertEquals("100.0", formula);

		cell.setCellFormula("10E-1");
		formula = cell.getCellFormula();
		assertEquals("1.0", formula);
	}

	public void testRanges() {
		HSSFWorkbook wb = new HSSFWorkbook();

		wb.createSheet("Cash_Flow");

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

		cell.setCellFormula("A1.A2");
		formula = cell.getCellFormula();
		assertEquals("A1:A2", formula);

		cell.setCellFormula("A1..A2");
		formula = cell.getCellFormula();
		assertEquals("A1:A2", formula);

		cell.setCellFormula("A1...A2");
		formula = cell.getCellFormula();
		assertEquals("A1:A2", formula);
	}

	/**
	 * Test for bug observable at svn revision 618865 (5-Feb-2008)<br/>
	 * a formula consisting of a single no-arg function got rendered without the function braces
	 */
	public void testToFormulaStringZeroArgFunction() {
		HSSFWorkbook book = new HSSFWorkbook();

		Ptg[] ptgs = {
				new FuncPtg(10),
		};
		assertEquals("NA()", HSSFFormulaParser.toFormulaString(book, ptgs));
	}

	public void testPercent() {
		Ptg[] ptgs;
		ptgs = parseFormula("5%");
		assertEquals(2, ptgs.length);
		assertEquals(ptgs[0].getClass(), IntPtg.class);
		assertEquals(ptgs[1].getClass(), PercentPtg.class);

		// spaces OK
		ptgs = parseFormula(" 250 % ");
		assertEquals(2, ptgs.length);
		assertEquals(ptgs[0].getClass(), IntPtg.class);
		assertEquals(ptgs[1].getClass(), PercentPtg.class);


		// double percent OK
		ptgs = parseFormula("12345.678%%");
		assertEquals(3, ptgs.length);
		assertEquals(ptgs[0].getClass(), NumberPtg.class);
		assertEquals(ptgs[1].getClass(), PercentPtg.class);
		assertEquals(ptgs[2].getClass(), PercentPtg.class);

		// percent of a bracketed expression
		ptgs = parseFormula("(A1+35)%*B1%");
		assertEquals(8, ptgs.length);
		assertEquals(ptgs[4].getClass(), PercentPtg.class);
		assertEquals(ptgs[6].getClass(), PercentPtg.class);

		// percent of a text quantity
		ptgs = parseFormula("\"8.75\"%");
		assertEquals(2, ptgs.length);
		assertEquals(ptgs[0].getClass(), StringPtg.class);
		assertEquals(ptgs[1].getClass(), PercentPtg.class);

		// percent to the power of
		ptgs = parseFormula("50%^3");
		assertEquals(4, ptgs.length);
		assertEquals(ptgs[0].getClass(), IntPtg.class);
		assertEquals(ptgs[1].getClass(), PercentPtg.class);
		assertEquals(ptgs[2].getClass(), IntPtg.class);
		assertEquals(ptgs[3].getClass(), PowerPtg.class);

		//
		// things that parse OK but would *evaluate* to an error

		ptgs = parseFormula("\"abc\"%");
		assertEquals(2, ptgs.length);
		assertEquals(ptgs[0].getClass(), StringPtg.class);
		assertEquals(ptgs[1].getClass(), PercentPtg.class);

		ptgs = parseFormula("#N/A%");
		assertEquals(2, ptgs.length);
		assertEquals(ptgs[0].getClass(), ErrPtg.class);
		assertEquals(ptgs[1].getClass(), PercentPtg.class);
	}

	/**
	 * Tests combinations of various operators in the absence of brackets
	 */
	public void testPrecedenceAndAssociativity() {

		Class[] expClss;

		// TRUE=TRUE=2=2  evaluates to FALSE
		expClss = new Class[] { BoolPtg.class, BoolPtg.class, EqualPtg.class,
				IntPtg.class, EqualPtg.class, IntPtg.class, EqualPtg.class,  };
		confirmTokenClasses("TRUE=TRUE=2=2", expClss);


		//  2^3^2	evaluates to 64 not 512
		expClss = new Class[] { IntPtg.class, IntPtg.class, PowerPtg.class,
				IntPtg.class, PowerPtg.class, };
		confirmTokenClasses("2^3^2", expClss);

		// "abc" & 2 + 3 & "def"   evaluates to "abc5def"
		expClss = new Class[] { StringPtg.class, IntPtg.class, IntPtg.class,
				AddPtg.class, ConcatPtg.class, StringPtg.class, ConcatPtg.class, };
		confirmTokenClasses("\"abc\"&2+3&\"def\"", expClss);


		//  (1 / 2) - (3 * 4)
		expClss = new Class[] { IntPtg.class, IntPtg.class, DividePtg.class,
				IntPtg.class, IntPtg.class, MultiplyPtg.class, SubtractPtg.class, };
		confirmTokenClasses("1/2-3*4", expClss);

		// 2 * (2^2)
		expClss = new Class[] { IntPtg.class, IntPtg.class, IntPtg.class, PowerPtg.class, MultiplyPtg.class, };
		// NOT: (2 *2) ^ 2 -> int int multiply int power
		confirmTokenClasses("2*2^2", expClss);

		//  2^200% -> 2 not 1.6E58
		expClss = new Class[] { IntPtg.class, IntPtg.class, PercentPtg.class, PowerPtg.class, };
		confirmTokenClasses("2^200%", expClss);
	}

	/* package */ static Ptg[] confirmTokenClasses(String formula, Class[] expectedClasses) {
		Ptg[] ptgs = parseFormula(formula);
		assertEquals(expectedClasses.length, ptgs.length);
		for (int i = 0; i < expectedClasses.length; i++) {
			if(expectedClasses[i] != ptgs[i].getClass()) {
				fail("difference at token[" + i + "]: expected ("
					+ expectedClasses[i].getName() + ") but got ("
					+ ptgs[i].getClass().getName() + ")");
			}
		}
		return ptgs;
	}

	public void testPower() {
		confirmTokenClasses("2^5", new Class[] { IntPtg.class, IntPtg.class, PowerPtg.class, });
	}

	private static Ptg parseSingleToken(String formula, Class ptgClass) {
		Ptg[] ptgs = parseFormula(formula);
		assertEquals(1, ptgs.length);
		Ptg result = ptgs[0];
		assertEquals(ptgClass, result.getClass());
		return result;
	}

	public void testParseNumber() {
		IntPtg ip;

		// bug 33160
		ip = (IntPtg) parseSingleToken("40", IntPtg.class);
		assertEquals(40, ip.getValue());
		ip = (IntPtg) parseSingleToken("40000", IntPtg.class);
		assertEquals(40000, ip.getValue());

		// check the upper edge of the IntPtg range:
		ip = (IntPtg) parseSingleToken("65535", IntPtg.class);
		assertEquals(65535, ip.getValue());
		NumberPtg np = (NumberPtg) parseSingleToken("65536", NumberPtg.class);
		assertEquals(65536, np.getValue(), 0);

		np = (NumberPtg) parseSingleToken("65534.6", NumberPtg.class);
		assertEquals(65534.6, np.getValue(), 0);
	}

	public void testMissingArgs() {

		Class[] expClss;

		expClss = new Class[] {
				RefPtg.class,
				AttrPtg.class, // tAttrIf
				MissingArgPtg.class,
				AttrPtg.class, // tAttrSkip
				RefPtg.class,
				AttrPtg.class, // tAttrSkip
				FuncVarPtg.class,
		};

		confirmTokenClasses("if(A1, ,C1)", expClss);

		expClss = new Class[] { MissingArgPtg.class, AreaPtg.class, MissingArgPtg.class,
				FuncVarPtg.class, };
		confirmTokenClasses("counta( , A1:B2, )", expClss);
	}

	public void testParseErrorLiterals() {

		confirmParseErrorLiteral(ErrPtg.NULL_INTERSECTION, "#NULL!");
		confirmParseErrorLiteral(ErrPtg.DIV_ZERO, "#DIV/0!");
		confirmParseErrorLiteral(ErrPtg.VALUE_INVALID, "#VALUE!");
		confirmParseErrorLiteral(ErrPtg.REF_INVALID, "#REF!");
		confirmParseErrorLiteral(ErrPtg.NAME_INVALID, "#NAME?");
		confirmParseErrorLiteral(ErrPtg.NUM_ERROR, "#NUM!");
		confirmParseErrorLiteral(ErrPtg.N_A, "#N/A");
		parseFormula("HLOOKUP(F7,#REF!,G7,#REF!)");
	}

	private static void confirmParseErrorLiteral(ErrPtg expectedToken, String formula) {
		assertEquals(expectedToken, parseSingleToken(formula, ErrPtg.class));
	}

	/**
	 * To aid readability the parameters have been encoded with single quotes instead of double
	 * quotes.  This method converts single quotes to double quotes before performing the parse
	 * and result check.
	 */
	private static void confirmStringParse(String singleQuotedValue) {
		// formula: internal quotes become double double, surround with double quotes
		String formula = '"' + singleQuotedValue.replaceAll("'", "\"\"") + '"';
		String expectedValue = singleQuotedValue.replace('\'', '"');

		StringPtg sp = (StringPtg) parseSingleToken(formula, StringPtg.class);
		assertEquals(expectedValue, sp.getValue());
	}
	public void testParseStringLiterals_bug28754() {

		StringPtg sp;
		try {
			sp = (StringPtg) parseSingleToken("\"test\"\"ing\"", StringPtg.class);
		} catch (RuntimeException e) {
			if(e.getMessage().startsWith("Cannot Parse")) {
				throw new AssertionFailedError("Identified bug 28754a");
			}
			throw e;
		}
		assertEquals("test\"ing", sp.getValue());

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

		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
		cell.setCellFormula("right(\"test\"\"ing\", 3)");
		String actualCellFormula = cell.getCellFormula();
		if("RIGHT(\"test\"ing\",3)".equals(actualCellFormula)) {
			throw new AssertionFailedError("Identified bug 28754b");
		}
		assertEquals("RIGHT(\"test\"\"ing\",3)", actualCellFormula);
	}

	public void testParseStringLiterals() {
		confirmStringParse("goto considered harmful");