/**
* returns the OperationEval concrete impl instance corresponding
* to the supplied operationPtg
*/
public static ValueEval Evaluate(OperationPtg ptg, ValueEval[] args,
OperationEvaluationContext ec)
{
if (ptg == null)
{
throw new ArgumentException("ptg must not be null");
}
NPOI.SS.Formula.Functions.Function result = _instancesByPtgClass[ptg] as NPOI.SS.Formula.Functions.Function;
if (result != null)
{
return(result.Evaluate(args, ec.RowIndex, (short)ec.ColumnIndex));
}
if (ptg is AbstractFunctionPtg)
{
AbstractFunctionPtg fptg = (AbstractFunctionPtg)ptg;
int functionIndex = fptg.FunctionIndex;
switch (functionIndex)
{
case NPOI.SS.Formula.Function.FunctionMetadataRegistry.FUNCTION_INDEX_INDIRECT:
return(Indirect.instance.Evaluate(args, ec));
case NPOI.SS.Formula.Function.FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL:
return(UserDefinedFunction.instance.Evaluate(args, ec));
}
return(FunctionEval.GetBasicFunction(functionIndex).Evaluate(args, ec.RowIndex, ec.ColumnIndex));
}
throw new Exception("Unexpected operation ptg class (" + ptg.GetType().Name + ")");
}
private void ConfirmFuncClass(Ptg[] ptgs, int i, String expectedFunctionName, byte operandClass)
{
ConfirmTokenClass(ptgs, i, operandClass);
AbstractFunctionPtg afp = (AbstractFunctionPtg)ptgs[i];
Assert.AreEqual(expectedFunctionName, afp.Name);
}
private static void ConfirmFunc(string formula, int expPtgArraySize, bool isVarArgFunc, int funcIx)
{
Ptg[] ptgs = Parse(formula);
Ptg ptgF = ptgs[ptgs.Length - 1]; // func is last RPN token in all these formulas
// Check critical things in the Ptg array encoding.
if (!(ptgF is AbstractFunctionPtg))
{
throw new Exception("function token missing");
}
AbstractFunctionPtg func = (AbstractFunctionPtg)ptgF;
if (func.FunctionIndex == 255)
{
throw new AssertionException("Failed to recognise built-in function in formula '"
+ formula + "'");
}
Assert.AreEqual(expPtgArraySize, ptgs.Length);
Assert.AreEqual(funcIx, func.FunctionIndex);
Type expCls = isVarArgFunc ? typeof(FuncVarPtg) : typeof(FuncPtg);
Assert.AreEqual(expCls, ptgF.GetType());
// check that Parsed Ptg array Converts back to formula text OK
HSSFWorkbook book = new HSSFWorkbook();
string reRenderedFormula = HSSFFormulaParser.ToFormulaString(book, ptgs);
Assert.AreEqual(formula, reRenderedFormula);
}
// copied from org.apache.poi.hssf.model.TestFormulaParser
public void TestMacroFunction()
{
// testNames.xlsm contains a VB function called 'myFunc'
String testFile = "testNames.xlsm";
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook(testFile);
try
{
XSSFEvaluationWorkbook workbook = XSSFEvaluationWorkbook.Create(wb);
//Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands would go here...), FunctionPtg(myFunc)]
Ptg[] ptg = FormulaParser.Parse("myFunc(\"arg\")", workbook, FormulaType.Cell, -1);
Assert.AreEqual(3, ptg.Length);
// the name gets encoded as the first operand on the stack
NameXPxg tname = (NameXPxg)ptg[0];
Assert.AreEqual("myFunc", tname.ToFormulaString());
// the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
StringPtg arg = (StringPtg)ptg[1];
Assert.AreEqual("arg", arg.Value);
// 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];
Assert.IsTrue(tfunc.IsExternalFunction);
// confirm formula parsing is case-insensitive
FormulaParser.Parse("mYfUnC(\"arg\")", workbook, 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()", workbook, FormulaType.Cell, -1);
FormulaParser.Parse("myFunc(\"arg\", 0, TRUE)", workbook, 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\")", workbook, FormulaType.Cell, -1);
// Make sure workbook can be written and read
XSSFTestDataSamples.WriteOutAndReadBack(wb).Close();
// Manually check to make sure file isn't corrupted
// TODO: develop a process for occasionally manually reviewing workbooks
// to verify workbooks are not corrupted
/*
* FileInfo fileIn = XSSFTestDataSamples.GetSampleFile(testFile);
* FileInfo reSavedFile = new FileInfo(fileIn.FullName.Replace(".xlsm", "-saved.xlsm"));
* FileStream fos = new FileStream(reSavedFile.FullName, FileMode.Create, FileAccess.ReadWrite);
* wb.Write(fos);
* fos.Close();
*/
}
finally
{
wb.Close();
}
}
private void SetSimpleValueFuncClass(AbstractFunctionPtg afp,
byte desiredOperandClass, bool callerForceArrayFlag)
{
if (callerForceArrayFlag || desiredOperandClass == Ptg.CLASS_ARRAY)
{
afp.PtgClass = (Ptg.CLASS_ARRAY);
}
else
{
afp.PtgClass = (Ptg.CLASS_VALUE);
}
}
/**
* returns the OperationEval concrete impl instance corresponding
* to the supplied operationPtg
*/
public static ValueEval Evaluate(OperationPtg ptg, ValueEval[] args,
OperationEvaluationContext ec)
{
if (ptg == null)
{
throw new ArgumentException("ptg must not be null");
}
NPOI.SS.Formula.Functions.Function result = _instancesByPtgClass[ptg] as NPOI.SS.Formula.Functions.Function;
FreeRefFunction udfFunc = null;
if (result == null)
{
if (ptg is AbstractFunctionPtg)
{
AbstractFunctionPtg fptg = (AbstractFunctionPtg)ptg;
int functionIndex = fptg.FunctionIndex;
switch (functionIndex)
{
case NPOI.SS.Formula.Function.FunctionMetadataRegistry.FUNCTION_INDEX_INDIRECT:
udfFunc = Indirect.instance;
break;
case NPOI.SS.Formula.Function.FunctionMetadataRegistry.FUNCTION_INDEX_EXTERNAL:
udfFunc = UserDefinedFunction.instance;
break;
default:
result = FunctionEval.GetBasicFunction(functionIndex);
break;
}
}
}
if (result != null)
{
if (result is ArrayFunction)
{
ArrayFunction func = (ArrayFunction)result;
ValueEval eval = EvaluateArrayFunction(func, args, ec);
if (eval != null)
{
return(eval);
}
}
return(result.Evaluate(args, ec.RowIndex, (short)ec.ColumnIndex));
}
else if (udfFunc != null)
{
return(udfFunc.Evaluate(args, ec));
}
throw new Exception("Unexpected operation ptg class (" + ptg.GetType().Name + ")");
}
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);
// the name Gets encoded as the first arg
NamePtg tname = (NamePtg)ptg[0];
Assert.AreEqual("myFunc", tname.ToFormulaString(book));
AbstractFunctionPtg tfunc = (AbstractFunctionPtg)ptg[1];
Assert.IsTrue(tfunc.IsExternalFunction);
}
/**
* Note - Excel function names are 'case aware but not case sensitive'. This method may end
* up creating a defined name record in the workbook if the specified name Is not an internal
* Excel function, and has not been encountered before.
*
* @param name case preserved function name (as it was entered/appeared in the formula).
*/
private ParseNode function(String name)
{
NamePtg nameToken = null;
// Note regarding parameter -
if (!AbstractFunctionPtg.IsInternalFunctionName(name))
{
// external functions Get a Name Token which points To a defined name record
nameToken = new NamePtg(name, this.book);
// in the Token tree, the name Is more or less the first argument
}
Match('(');
ParseNode[] args = Arguments();
Match(')');
return(GetFunction(name, nameToken, args));
}
private static bool IsSimpleValueFunction(Ptg token)
{
if (token is AbstractFunctionPtg)
{
AbstractFunctionPtg aptg = (AbstractFunctionPtg)token;
if (aptg.DefaultOperandClass != Ptg.CLASS_VALUE)
{
return(false);
}
int numberOfOperands = aptg.NumberOfOperands;
for (int i = numberOfOperands - 1; i >= 0; i--)
{
if (aptg.GetParameterClass(i) != Ptg.CLASS_VALUE)
{
return(false);
}
}
return(true);
}
return(false);
}
private void TransformFunctionNode(AbstractFunctionPtg afp, ParseNode[] children,
byte desiredOperandClass, bool callerForceArrayFlag)
{
bool localForceArrayFlag;
byte defaultReturnOperandClass = afp.DefaultOperandClass;
if (callerForceArrayFlag)
{
switch (defaultReturnOperandClass)
{
case Ptg.CLASS_REF:
if (desiredOperandClass == Ptg.CLASS_REF)
{
afp.PtgClass = (Ptg.CLASS_REF);
}
else
{
afp.PtgClass = (Ptg.CLASS_ARRAY);
}
localForceArrayFlag = false;
break;
case Ptg.CLASS_ARRAY:
afp.PtgClass = (Ptg.CLASS_ARRAY);
localForceArrayFlag = false;
break;
case Ptg.CLASS_VALUE:
afp.PtgClass = (Ptg.CLASS_ARRAY);
localForceArrayFlag = true;
break;
default:
throw new InvalidOperationException("Unexpected operand class ("
+ defaultReturnOperandClass + ")");
}
}
else
{
if (defaultReturnOperandClass == desiredOperandClass)
{
localForceArrayFlag = false;
// an alternative would have been To for non-base Ptgs To Set their operand class
// from their default, but this would require the call in many subclasses because
// the default OC is not known until the end of the constructor
afp.PtgClass = (defaultReturnOperandClass);
}
else
{
switch (desiredOperandClass)
{
case Ptg.CLASS_VALUE:
// always OK To Set functions To return 'value'
afp.PtgClass = (Ptg.CLASS_VALUE);
localForceArrayFlag = false;
break;
case Ptg.CLASS_ARRAY:
switch (defaultReturnOperandClass)
{
case Ptg.CLASS_REF:
afp.PtgClass = (Ptg.CLASS_REF);
// afp.SetClass(Ptg.CLASS_ARRAY);
break;
case Ptg.CLASS_VALUE:
afp.PtgClass = (Ptg.CLASS_ARRAY);
break;
default:
throw new InvalidOperationException("Unexpected operand class ("
+ defaultReturnOperandClass + ")");
}
localForceArrayFlag = (defaultReturnOperandClass == Ptg.CLASS_VALUE);
break;
case Ptg.CLASS_REF:
switch (defaultReturnOperandClass)
{
case Ptg.CLASS_ARRAY:
afp.PtgClass = (Ptg.CLASS_ARRAY);
break;
case Ptg.CLASS_VALUE:
afp.PtgClass = (Ptg.CLASS_VALUE);
break;
default:
throw new InvalidOperationException("Unexpected operand class ("
+ defaultReturnOperandClass + ")");
}
localForceArrayFlag = false;
break;
default:
throw new InvalidOperationException("Unexpected operand class ("
+ desiredOperandClass + ")");
}
}
}
for (int i = 0; i < children.Length; i++)
{
ParseNode child = children[i];
byte paramOperandClass = afp.GetParameterClass(i);
TransformNode(child, paramOperandClass, localForceArrayFlag);
}
}
