testformulaparser.java

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

/* ====================================================================
   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.model;

import junit.framework.AssertionFailedError;
import junit.framework.TestCase;

import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.record.constant.ErrorConstant;
import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
import org.apache.poi.hssf.record.formula.AddPtg;
import org.apache.poi.hssf.record.formula.AreaI;
import org.apache.poi.hssf.record.formula.AreaPtg;
import org.apache.poi.hssf.record.formula.ArrayPtg;
import org.apache.poi.hssf.record.formula.AttrPtg;
import org.apache.poi.hssf.record.formula.BoolPtg;
import org.apache.poi.hssf.record.formula.ConcatPtg;
import org.apache.poi.hssf.record.formula.DividePtg;
import org.apache.poi.hssf.record.formula.EqualPtg;
import org.apache.poi.hssf.record.formula.ErrPtg;
import org.apache.poi.hssf.record.formula.FuncPtg;
import org.apache.poi.hssf.record.formula.FuncVarPtg;
import org.apache.poi.hssf.record.formula.IntPtg;
import org.apache.poi.hssf.record.formula.MissingArgPtg;
import org.apache.poi.hssf.record.formula.MultiplyPtg;
import org.apache.poi.hssf.record.formula.NamePtg;
import org.apache.poi.hssf.record.formula.NumberPtg;
import org.apache.poi.hssf.record.formula.PercentPtg;
import org.apache.poi.hssf.record.formula.PowerPtg;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.Ref3DPtg;
import org.apache.poi.hssf.record.formula.RefPtg;
import org.apache.poi.hssf.record.formula.StringPtg;
import org.apache.poi.hssf.record.formula.SubtractPtg;
import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
import org.apache.poi.hssf.usermodel.FormulaExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFErrorConstants;
import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;
import org.apache.poi.hssf.usermodel.HSSFName;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaParserTestHelper;

/**
 * Test the low level formula parser functionality. High level tests are to
 * be done via usermodel/HSSFCell.setFormulaValue().
 */
public final class TestFormulaParser extends TestCase {

	/**
	 * @return parsed token array already confirmed not <code>null</code>
	 */
	/* package */ static Ptg[] parseFormula(String formula) {
		Ptg[] result = HSSFFormulaParser.parse(formula, (HSSFWorkbook)null);
		assertNotNull("Ptg array should not be null", result);
		return result;
	}
	private static String toFormulaString(Ptg[] ptgs) {
		return HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, ptgs);
	}

	public void testSimpleFormula() {
		Ptg[] ptgs = parseFormula("2+2");
		assertEquals(3, ptgs.length);
	}

	public void testFormulaWithSpace1() {
		Ptg[] ptgs = parseFormula(" 2 + 2 ");
		assertEquals(3, ptgs.length);
		assertTrue("",(ptgs[0] instanceof IntPtg));
		assertTrue("",(ptgs[1] instanceof IntPtg));
		assertTrue("",(ptgs[2] instanceof AddPtg));
	}

	public void testFormulaWithSpace2() {
		Ptg[] ptgs = parseFormula("2+ sum( 3 , 4) ");
		assertEquals(5, ptgs.length);
	}

	public void testFormulaWithSpaceNRef() {
		Ptg[] ptgs = parseFormula("sum( A2:A3 )");
		assertEquals(2, ptgs.length);
	}

	public void testFormulaWithString() {
		Ptg[] ptgs = parseFormula("\"hello\" & \"world\" ");
		assertEquals(3, ptgs.length);
	}

	public void testTRUE() {
		Ptg[] ptgs = parseFormula("TRUE");
		assertEquals(1, ptgs.length);
		BoolPtg flag  = (BoolPtg) ptgs[0];
		assertEquals(true, flag.getValue());
	}

	public void testSumIf() {
		Ptg[] ptgs = parseFormula("SUMIF(A1:A5,\">4000\",B1:B5)");
		assertEquals(4, ptgs.length);
	}

	/**
	 * Bug Reported by [e-mail] (Jens Riis)
	 * Refers to Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=17582">#17582</a>
	 *
	 */
	public void testNonAlphaFormula() {
		String currencyCell = "F3";
		Ptg[] ptgs = parseFormula("\"TOTAL[\"&"+currencyCell+"&\"]\"");
		assertEquals(5, ptgs.length);
		assertTrue ("Ptg[0] is a string", (ptgs[0] instanceof StringPtg));
		StringPtg firstString = (StringPtg)ptgs[0];

		assertEquals("TOTAL[", firstString.getValue());
		//the PTG order isn't 100% correct but it still works - dmui
	}

	public void testMacroFunction() {
		// testNames.xls contains a VB function called 'myFunc'
		HSSFWorkbook w = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
		HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(w);

		Ptg[] ptg = HSSFFormulaParser.parse("myFunc()", w);
		// myFunc() actually takes 1 parameter.  Don't know if POI will ever be able to detect this problem

		// the name gets encoded as the first arg
		NamePtg tname = (NamePtg) ptg[0];
		assertEquals("myFunc", tname.toFormulaString(book));

		AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[1];
		assertTrue(tfunc.isExternalFunction());
	}

	public void testEmbeddedSlash() {
		Ptg[] ptgs = parseFormula("HYPERLINK(\"http://www.jakarta.org\",\"Jakarta\")");
		assertTrue("first ptg is string", ptgs[0] instanceof StringPtg);
		assertTrue("second ptg is string", ptgs[1] instanceof StringPtg);
	}

	public void testConcatenate() {
		Ptg[] ptgs = parseFormula("CONCATENATE(\"first\",\"second\")");
		assertTrue("first ptg is string", ptgs[0] instanceof StringPtg);
		assertTrue("second ptg is string", ptgs[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(0);
		cell.setCellFormula("NoQuotesNeeded!A1");

		cell = row.createCell(1);
		cell.setCellFormula("'Quotes Needed Here &#$@'!A1");
	}

	public void testUnaryMinus() {
		Ptg[] ptgs = parseFormula("-A1");
		assertEquals(2, ptgs.length);
		assertTrue("first ptg is reference",ptgs[0] instanceof RefPtg);
		assertTrue("second ptg is Minus",ptgs[1] instanceof UnaryMinusPtg);
	}

	public void testUnaryPlus() {
		Ptg[] ptgs = parseFormula("+A1");
		assertEquals(2, ptgs.length);
		assertTrue("first ptg is reference",ptgs[0] instanceof RefPtg);
		assertTrue("second ptg is Plus",ptgs[1] instanceof UnaryPlusPtg);
	}

	public void testLeadingSpaceInString() {
		String value = "  hi  ";
		Ptg[] ptgs = parseFormula("\"" + value + "\"");

		assertEquals(1, ptgs.length);
		assertTrue("ptg0 is a StringPtg", ptgs[0] instanceof StringPtg);
		assertTrue("ptg0 contains exact value", ((StringPtg)ptgs[0]).getValue().equals(value));
	}

	public void testLookupAndMatchFunctionArgs() {
		Ptg[] ptgs = parseFormula("lookup(A1, A3:A52, B3:B52)");

		assertEquals(4, ptgs.length);
		assertTrue("ptg0 has Value class", ptgs[0].getPtgClass() == Ptg.CLASS_VALUE);

		ptgs = parseFormula("match(A1, A3:A52)");

		assertEquals(3, ptgs.length);
		assertTrue("ptg0 has Value class", ptgs[0].getPtgClass() == Ptg.CLASS_VALUE);
	}

	/** bug 33160*/
	public void testLargeInt() {
		Ptg[] ptgs = parseFormula("40");
		assertTrue("ptg is Int, is "+ptgs[0].getClass(),ptgs[0] instanceof IntPtg);

		ptgs = parseFormula("40000");
		assertTrue("ptg should be  IntPtg, is "+ptgs[0].getClass(), ptgs[0] instanceof IntPtg);
	}

	/** bug 33160, testcase by Amol Deshmukh*/
	public void testSimpleLongFormula() {
		Ptg[] ptgs = parseFormula("40000/2");
		assertEquals(3, ptgs.length);
		assertTrue("IntPtg", (ptgs[0] instanceof IntPtg));
		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(0);
		cell.setCellFormula("Cash_Flow!A1");
	}

	// bug 38396 : Formula with exponential numbers not parsed correctly.
	public void testExponentialParsing() {
		Ptg[] ptgs;
		ptgs = parseFormula("1.3E21/2");
		assertEquals(3, ptgs.length);
		assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
		assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
		assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));

		ptgs = parseFormula("1322E21/2");
		assertEquals(3, ptgs.length);
		assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
		assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
		assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));

		ptgs = parseFormula("1.3E1/2");
		assertEquals(3, ptgs.length);
		assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
		assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
		assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
	}

	public void testExponentialInSheet() {
		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("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");