Attachment #33168 for bug #58452

View | Details | Raw Unified | Return to bug 58452
Collapse All | Expand All

Lines 67-75 Link Here

(-)src/java/org/apache/poi/ss/formula/FormulaParser.java (-11 / +38 lines)
67	import org.apache.poi.ss.formula.ptg.UnionPtg;	67	import org.apache.poi.ss.formula.ptg.UnionPtg;
68	import org.apache.poi.ss.formula.ptg.ValueOperatorPtg;	68	import org.apache.poi.ss.formula.ptg.ValueOperatorPtg;
69	import org.apache.poi.ss.usermodel.ErrorConstants;	69	import org.apache.poi.ss.usermodel.ErrorConstants;
		70	import org.apache.poi.ss.usermodel.Name;
70	import org.apache.poi.ss.util.AreaReference;	71	import org.apache.poi.ss.util.AreaReference;
71	import org.apache.poi.ss.util.CellReference;	72	import org.apache.poi.ss.util.CellReference;
72	import org.apache.poi.ss.util.CellReference.NameType;	73	import org.apache.poi.ss.util.CellReference.NameType;
		74	import org.apache.poi.util.POILogFactory;
		75	import org.apache.poi.util.POILogger;
73		76
74	/**	77	/**
75	* This class parses a formula string into a List of tokens in RPN order.	78	* This class parses a formula string into a List of tokens in RPN order.
Lines 85-90 Link Here
85	* <p/>	88	* <p/>
86	*/	89	*/
87	public final class FormulaParser {	90	public final class FormulaParser {
		91	private final static POILogger log = POILogFactory.getLogger(FormulaParser.class);
88	private final String _formulaString;	92	private final String _formulaString;
89	private final int _formulaLength;	93	private final int _formulaLength;
90	/** points at the next character to be read (after the {@link #look} char) */	94	/** points at the next character to be read (after the {@link #look} char) */
Lines 108-117 Link Here
108	*/	112	*/
109	private boolean _inIntersection = false;	113	private boolean _inIntersection = false;
110		114
111	private FormulaParsingWorkbook _book;	115	private final FormulaParsingWorkbook _book;
112	private SpreadsheetVersion _ssVersion;	116	private final SpreadsheetVersion _ssVersion;
113		117
114	private int _sheetIndex;	118	private final int _sheetIndex;
115		119
116		120
117	/**	121	/**
Lines 137-142 Link Here
137		141
138	/**	142	/**
139	* Parse a formula into a array of tokens	143	* Parse a formula into a array of tokens
		144	* Side effect: creates name (Workbook.createName) if formula contains unrecognized names (names are likely UDFs)
140	*	145	*
141	* @param formula the formula to parse	146	* @param formula the formula to parse
142	* @param workbook the parent workbook	147	* @param workbook the parent workbook
Lines 927-932 Link Here
927	* Note - Excel function names are 'case aware but not case sensitive'. This method may end	932	* Note - Excel function names are 'case aware but not case sensitive'. This method may end
928	* up creating a defined name record in the workbook if the specified name is not an internal	933	* up creating a defined name record in the workbook if the specified name is not an internal
929	* Excel function, and has not been encountered before.	934	* Excel function, and has not been encountered before.
		935	*
		936	* Side effect: creates workbook name if name is not recognized (name is probably a UDF)
930	*	937	*
931	* @param name case preserved function name (as it was entered/appeared in the formula).	938	* @param name case preserved function name (as it was entered/appeared in the formula).
932	*/	939	*/
Lines 940-961 Link Here
940	// Only test cases omit the book (expecting it not to be needed)	947	// Only test cases omit the book (expecting it not to be needed)
941	throw new IllegalStateException("Need book to evaluate name '" + name + "'");	948	throw new IllegalStateException("Need book to evaluate name '" + name + "'");
942	}	949	}
		950	// Check to see if name is a named range in the workbook
943	EvaluationName hName = _book.getName(name, _sheetIndex);	951	EvaluationName hName = _book.getName(name, _sheetIndex);
944	if (hName == null) {	952	if (hName != null) {
945	nameToken = _book.getNameXPtg(name, null);
946	if (nameToken == null) {
947	throw new FormulaParseException("Name '" + name
948	+ "' is completely unknown in the current workbook");
949	}
950	} else {
951	if (!hName.isFunctionName()) {	953	if (!hName.isFunctionName()) {
952	throw new FormulaParseException("Attempt to use name '" + name	954	throw new FormulaParseException("Attempt to use name '" + name
953	+ "' as a function, but defined name in workbook does not refer to a function");	955	+ "' as a function, but defined name in workbook does not refer to a function");
954	}	956	}
955		957
956	// calls to user-defined functions within the workbook	958	// calls to user-defined functions within the workbook
957	// get a Name token which points to a defined name record	959	// get a Name token which points to a defined name record
958	nameToken = hName.createPtg();	960	nameToken = hName.createPtg();
		961	} else {
		962	// Check if name is an external names table
		963	nameToken = _book.getNameXPtg(name, null);
		964	if (nameToken == null) {
		965	// name is not an internal or external name
		966	if (log.check(POILogger.WARN)) {
		967	log.log(POILogger.WARN,
		968	"Name '" + name + "' is completely unknown in the current workbook.");
		969	}
		970	// name is probably the name of an unregistered User-Defined Function
		971	addName(name);
		972	hName = _book.getName(name, _sheetIndex);
		973	nameToken = hName.createPtg();
		974	}
959	}	975	}
960	}	976	}
961		977
Lines 965-970 Link Here
965		981
966	return getFunction(name, nameToken, args);	982	return getFunction(name, nameToken, args);
967	}	983	}
		984
		985	/**
		986	* Adds a name (named range or user defined function) to underlying workbook's names table
		987	* @param functionName
		988	*/
		989	private final void addName(String functionName) {
		990	final Name name = _book.createName();
		991	name.setFunction(true);
		992	name.setNameName(functionName);
		993	name.setSheetIndex(_sheetIndex);
		994	}
968		995
969	/**	996	/**
970	* Generates the variable function ptg for the formula.	997	* Generates the variable function ptg for the formula.

Lines 20-25 Link Here

(-)src/ooxml/testcases/org/apache/poi/ss/formula/TestFormulaParser.java (+81 lines)
20		20
21	import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;	21	import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;
22	import org.apache.poi.hssf.usermodel.HSSFWorkbook;	22	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
		23	import org.apache.poi.ss.formula.ptg.AbstractFunctionPtg;
		24	import org.apache.poi.ss.formula.ptg.NamePtg;
		25	import org.apache.poi.ss.formula.ptg.Ptg;
		26	import org.apache.poi.ss.formula.ptg.StringPtg;
		27	import org.apache.poi.xssf.XSSFTestDataSamples;
23	import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;	28	import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
24	import org.apache.poi.xssf.usermodel.XSSFWorkbook;	29	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
25		30
Lines 62-66 Link Here
62	catch (FormulaParseException expected) {	67	catch (FormulaParseException expected) {
63	}	68	}
64	}	69	}
		70
		71	// copied from org.apache.poi.hssf.model.TestFormulaParser
		72	public void testMacroFunction() throws Exception {
		73	// testNames.xlsx contains a VB function called 'myFunc'
		74	XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("testNames.xlsm");
		75	try {
		76	XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.create(wb);
		77
		78	//Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands would go here...), FunctionPtg(myFunc)]
		79	Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", workbook, FormulaType.CELL, -1);
		80	assertEquals(3, ptg.length);
		81
		82	// the name gets encoded as the first operand on the stack
		83	NamePtg tname = (NamePtg) ptg[0];
		84	assertEquals("myFunc", tname.toFormulaString(workbook));
		85
		86	// the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
		87	StringPtg arg = (StringPtg) ptg[1];
		88	assertEquals("arg", arg.getValue());
		89
		90	// The external FunctionPtg is the last Ptg added to the stack
		91	// During formula evaluation, this Ptg pops off the the appropriate number of
		92	// arguments (getNumberOfOperands()) and pushes the result on the stack
		93	AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2];
		94	assertTrue(tfunc.isExternalFunction());
		95
		96	// confirm formula parsing is case-insensitive
		97	FormulaParser.parse("mYfUnC(\"arg\")", workbook, FormulaType.CELL, -1);
		98
		99	// confirm formula parsing doesn't care about argument count or type
		100	// this should only throw an error when evaluating the formula.
		101	FormulaParser.parse("myFunc()", workbook, FormulaType.CELL, -1);
		102	FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", workbook, FormulaType.CELL, -1);
		103
		104	// A completely unknown formula name (not saved in workbook) should still be parseable and renderable
		105	// but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated.
		106	FormulaParser.parse("yourFunc(\"arg\")", workbook, FormulaType.CELL, -1);
		107	} finally {
		108	wb.close();
		109	}
		110	}
		111
		112	public void testParserErrors() throws Exception {
		113	XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("testNames.xlsm");
		114	try {
		115	XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.create(wb);
		116
		117	parseExpectedException("(");
		118	parseExpectedException(")");
		119	parseExpectedException("+");
		120	parseExpectedException("42+");
		121	parseExpectedException("IF()");
		122	parseExpectedException("IF("); //no closing paren
		123	parseExpectedException("myFunc(", workbook); //no closing paren
		124	} finally {
		125	wb.close();
		126	}
		127	}
		128
		129	private static void parseExpectedException(String formula) {
		130	parseExpectedException(formula, null);
		131	}
		132
		133	/** confirm formula has invalid syntax and parsing the formula results in FormulaParseException
134	* @param formula
135	* @param wb
136	*/
137	private static void parseExpectedException(String formula, FormulaParsingWorkbook wb) {
138	try {
139	FormulaParser.parse(formula, wb, FormulaType.CELL, -1);
140	fail("Expected FormulaParseException: " + formula);
141	} catch (final FormulaParseException e) {
142	// expected during successful test
143	assertNotNull(e.getMessage());
144	}
145	}
65		146
66	}	147	}




import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.TestHSSFName;
import org.apache.poi.ss.formula.EvaluationName;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.constant.ErrorConstant;
import org.apache.poi.ss.formula.function.FunctionMetadataRegistry;
import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;
import org.apache.poi.ss.usermodel.Name;

		HSSFWorkbook w = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
		HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(w);

		//Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands go here...), FunctionPtg(myFunc)]
		Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", book, FormulaType.CELL, -1);
		assertEquals(3, ptg.length); 

		// the name gets encoded as the first operand on the stack
		NamePtg tname = (NamePtg) ptg[0];
		assertEquals("myFunc", tname.toFormulaString(book));
		
		// the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
		StringPtg arg = (StringPtg) ptg[1];
		assertEquals("arg", arg.getValue());

		// The external FunctionPtg is the last Ptg added to the stack
		// During formula evaluation, this Ptg pops off the the appropriate number of
		// arguments (getNumberOfOperands()) and pushes the result on the stack
		AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2]; //FuncVarPtg
		assertTrue(tfunc.isExternalFunction());
		
		// confirm formula parsing is case-insensitive
		FormulaParser.parse("mYfUnC(\"arg\")", book, FormulaType.CELL, -1);
		
		// confirm formula parsing doesn't care about argument count or type
		// this should only throw an error when evaluating the formula.
		FormulaParser.parse("myFunc()", book, FormulaType.CELL, -1);
		FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", book, FormulaType.CELL, -1);
		
		// A completely unknown formula name (not saved in workbook) should still be parseable and renderable
		// but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated.
		FormulaParser.parse("yourFunc(\"arg\")", book, FormulaType.CELL, -1);
	}

	public void testEmbeddedSlash() {


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

	private static void parseExpectedException(String formula) {
		try {
			parseFormula(formula);
			throw new AssertionFailedError("Expected FormulaParseException: " + formula);
		} catch (FormulaParseException e) {
			// expected during successful test
			assertNotNull(e.getMessage());

Lines 19-24 Link Here

(-)src/testcases/org/apache/poi/ss/formula/BaseTestExternalFunctions.java (-5 / +11 lines)
19	import junit.framework.TestCase;	19	import junit.framework.TestCase;
20	import org.apache.poi.ss.ITestDataProvider;	20	import org.apache.poi.ss.ITestDataProvider;
21	import org.apache.poi.ss.formula.eval.ErrorEval;	21	import org.apache.poi.ss.formula.eval.ErrorEval;
		22	import org.apache.poi.ss.formula.eval.NotImplementedException;
		23	import org.apache.poi.ss.formula.eval.NotImplementedFunctionException;
22	import org.apache.poi.ss.formula.eval.StringEval;	24	import org.apache.poi.ss.formula.eval.StringEval;
23	import org.apache.poi.ss.formula.eval.ValueEval;	25	import org.apache.poi.ss.formula.eval.ValueEval;
24	import org.apache.poi.ss.formula.functions.FreeRefFunction;	26	import org.apache.poi.ss.formula.functions.FreeRefFunction;
Lines 84-89 Link Here
84		86
85	public void testExternalFunctions() {	87	public void testExternalFunctions() {
86	Workbook wb = _testDataProvider.createWorkbook();	88	Workbook wb = _testDataProvider.createWorkbook();
		89	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
87		90
88	Sheet sh = wb.createSheet();	91	Sheet sh = wb.createSheet();
89		92
Lines 92-102 Link Here
92	assertEquals("ISODD(1)+ISEVEN(2)", cell1.getCellFormula());	95	assertEquals("ISODD(1)+ISEVEN(2)", cell1.getCellFormula());
93		96
94	Cell cell2 = sh.createRow(1).createCell(0);	97	Cell cell2 = sh.createRow(1).createCell(0);
		98	cell2.setCellFormula("MYFUNC(\"B1\")"); //unregistered functions are parseable and renderable, but may not be evaluateable
95	try {	99	try {
96	cell2.setCellFormula("MYFUNC(\"B1\")");	100	evaluator.evaluate(cell2);
97	fail("Should fail because MYFUNC is an unknown function");	101	} catch (final NotImplementedException e) {
98	} catch (FormulaParseException e){	102	if (!(e.getCause() instanceof NotImplementedFunctionException))
99	; //expected	103	throw e;
		104	// expected
		105	// Alternatively, a future implementation of evaluate could return #NAME? error to align behavior with Excel
		106	// assertEquals(ErrorEval.NAME_INVALID, ErrorEval.valueOf(evaluator.evaluate(cell2).getErrorValue()));
100	}	107	}
101		108
102	wb.addToolPack(customToolpack);	109	wb.addToolPack(customToolpack);
Lines 108-114 Link Here
108	cell3.setCellFormula("MYFUNC2(\"C1\")&\"-\"&A2"); //where A2 is defined above	115	cell3.setCellFormula("MYFUNC2(\"C1\")&\"-\"&A2"); //where A2 is defined above
109	assertEquals("MYFUNC2(\"C1\")&\"-\"&A2", cell3.getCellFormula());	116	assertEquals("MYFUNC2(\"C1\")&\"-\"&A2", cell3.getCellFormula());
110		117
111	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
112	assertEquals(2.0, evaluator.evaluate(cell1).getNumberValue());	118	assertEquals(2.0, evaluator.evaluate(cell1).getNumberValue());
113	assertEquals("B1abc", evaluator.evaluate(cell2).getStringValue());	119	assertEquals("B1abc", evaluator.evaluate(cell2).getStringValue());
114	assertEquals("C1abc2-B1abc", evaluator.evaluate(cell3).getStringValue());	120	assertEquals("C1abc2-B1abc", evaluator.evaluate(cell3).getStringValue());

Return to bug 58452

Lines 36-45 Link Here

(-)src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java (-5 / +34 lines)
36	import org.apache.poi.hssf.usermodel.HSSFSheet;	36	import org.apache.poi.hssf.usermodel.HSSFSheet;
37	import org.apache.poi.hssf.usermodel.HSSFWorkbook;	37	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
38	import org.apache.poi.hssf.usermodel.TestHSSFName;	38	import org.apache.poi.hssf.usermodel.TestHSSFName;
		39	import org.apache.poi.ss.formula.EvaluationName;
39	import org.apache.poi.ss.formula.FormulaParseException;	40	import org.apache.poi.ss.formula.FormulaParseException;
40	import org.apache.poi.ss.formula.FormulaParser;	41	import org.apache.poi.ss.formula.FormulaParser;
41	import org.apache.poi.ss.formula.FormulaType;	42	import org.apache.poi.ss.formula.FormulaType;
42	import org.apache.poi.ss.formula.constant.ErrorConstant;	43	import org.apache.poi.ss.formula.constant.ErrorConstant;
		44	import org.apache.poi.ss.formula.function.FunctionMetadataRegistry;
43	import org.apache.poi.ss.formula.ptg.*;	45	import org.apache.poi.ss.formula.ptg.*;
44	import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;	46	import org.apache.poi.ss.usermodel.BaseTestBugzillaIssues;
45	import org.apache.poi.ss.usermodel.Name;	47	import org.apache.poi.ss.usermodel.Name;
Lines 116-130 Link Here
116	HSSFWorkbook w = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");	118	HSSFWorkbook w = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
117	HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(w);	119	HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(w);
118		120
119	Ptg[] ptg = HSSFFormulaParser.parse("myFunc()", w);	121	//Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands go here...), FunctionPtg(myFunc)]
120	// myFunc() actually takes 1 parameter. Don't know if POI will ever be able to detect this problem	122	Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", book, FormulaType.CELL, -1);
		123	assertEquals(3, ptg.length);
121		124
122	// the name gets encoded as the first arg	125	// the name gets encoded as the first operand on the stack
123	NamePtg tname = (NamePtg) ptg[0];	126	NamePtg tname = (NamePtg) ptg[0];
124	assertEquals("myFunc", tname.toFormulaString(book));	127	assertEquals("myFunc", tname.toFormulaString(book));
		128
		129	// the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
		130	StringPtg arg = (StringPtg) ptg[1];
		131	assertEquals("arg", arg.getValue());
125		132
126	AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[1];	133	// The external FunctionPtg is the last Ptg added to the stack
		134	// During formula evaluation, this Ptg pops off the the appropriate number of
		135	// arguments (getNumberOfOperands()) and pushes the result on the stack
		136	AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2]; //FuncVarPtg
127	assertTrue(tfunc.isExternalFunction());	137	assertTrue(tfunc.isExternalFunction());
		138
		139	// confirm formula parsing is case-insensitive
		140	FormulaParser.parse("mYfUnC(\"arg\")", book, FormulaType.CELL, -1);
		141
		142	// confirm formula parsing doesn't care about argument count or type
		143	// this should only throw an error when evaluating the formula.
		144	FormulaParser.parse("myFunc()", book, FormulaType.CELL, -1);
		145	FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", book, FormulaType.CELL, -1);
		146
		147	// A completely unknown formula name (not saved in workbook) should still be parseable and renderable
		148	// but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated.
		149	FormulaParser.parse("yourFunc(\"arg\")", book, FormulaType.CELL, -1);
128	}	150	}
129		151
130	public void testEmbeddedSlash() {	152	public void testEmbeddedSlash() {
Lines 713-724 Link Here
713		735
714	parseExpectedException("IF(TRUE)");	736	parseExpectedException("IF(TRUE)");
715	parseExpectedException("countif(A1:B5, C1, D1)");	737	parseExpectedException("countif(A1:B5, C1, D1)");
		738
		739	parseExpectedException("(");
		740	parseExpectedException(")");
		741	parseExpectedException("+");
		742	parseExpectedException("42+");
		743
		744	parseExpectedException("IF(");
716	}	745	}
717		746
718	private static void parseExpectedException(String formula) {	747	private static void parseExpectedException(String formula) {
719	try {	748	try {
720	parseFormula(formula);	749	parseFormula(formula);
721	throw new AssertionFailedError("expected parse exception");	750	throw new AssertionFailedError("Expected FormulaParseException: " + formula);
722	} catch (FormulaParseException e) {	751	} catch (FormulaParseException e) {
723	// expected during successful test	752	// expected during successful test
724	assertNotNull(e.getMessage());	753	assertNotNull(e.getMessage());