/* package */
internal IEvaluationName GetName(String name, int sheetIndex)
{
NamePtg namePtg = _workbook.GetName(name, sheetIndex).CreatePtg();
if (namePtg == null)
{
return(null);
}
else
{
return(_workbook.GetName(namePtg));
}
}
public void TestSheetLevelFormulas()
{
HSSFWorkbook wb = new HSSFWorkbook();
IRow row;
ISheet sh1 = wb.CreateSheet("Sheet1");
IName nm1 = wb.CreateName();
nm1.NameName = ("sales_1");
nm1.SheetIndex = (0);
nm1.RefersToFormula = ("Sheet1!$A$1");
row = sh1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*2");
ISheet sh2 = wb.CreateSheet("Sheet2");
IName nm2 = wb.CreateName();
nm2.NameName = ("sales_1");
nm2.SheetIndex = (1);
nm2.RefersToFormula = ("Sheet2!$A$1");
row = sh2.CreateRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).SetCellFormula("sales_1");
row.CreateCell(2).SetCellFormula("sales_1*3");
//check that NamePtg refers to the correct NameRecord
Ptg[] ptgs1 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 0);
NamePtg nPtg1 = (NamePtg)ptgs1[0];
Assert.AreSame(nm1, wb.GetNameAt(nPtg1.Index));
Ptg[] ptgs2 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 1);
NamePtg nPtg2 = (NamePtg)ptgs2[0];
Assert.AreSame(nm2, wb.GetNameAt(nPtg2.Index));
//check that the formula evaluator returns the correct result
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(3.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(6.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0);
Assert.AreEqual(5.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(15.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.0);
}
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));
}
/**
* Generates the variable function ptg for the formula.
* <p>
* For IF Formulas, Additional PTGs are Added To the Tokens
* @param name
* @param numArgs
* @return Ptg a null Is returned if we're in an IF formula, it needs extreme manipulation and Is handled in this function
*/
private ParseNode GetFunction(String name, NamePtg namePtg, ParseNode[] args)
{
FunctionMetadata fm = FunctionMetadataRegistry.GetFunctionByName(name.ToUpper());
int numArgs = args.Length;
if (fm == null)
{
if (namePtg == null)
{
throw new InvalidOperationException("NamePtg must be supplied for external functions");
}
// must be external function
ParseNode[] allArgs = new ParseNode[numArgs + 1];
allArgs[0] = new ParseNode(namePtg);
Array.Copy(args, 0, allArgs, 1, numArgs);
return(new ParseNode(new FuncVarPtg(name, (byte)(numArgs + 1)), allArgs));
}
if (namePtg != null)
{
throw new InvalidOperationException("NamePtg no applicable To internal functions");
}
bool IsVarArgs = !fm.HasFixedArgsLength;
int funcIx = fm.Index;
ValidateNumArgs(args.Length, fm);
AbstractFunctionPtg retval;
if (IsVarArgs)
{
retval = new FuncVarPtg(name, (byte)numArgs);
}
else
{
retval = new FuncPtg(funcIx);
}
return(new ParseNode(retval, args));
}
public IEvaluationName GetName(NamePtg namePtg)
{
int ix = namePtg.Index;
return(new Name(_uBook.GetNameAt(ix), ix, this));
}
public String GetNameText(NamePtg namePtg)
{
return(_uBook.GetNameAt(namePtg.Index).NameName);
}
/**
* returns an appropriate Eval impl instance for the Ptg. The Ptg must be
* one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
* StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
* passed here!
*/
private ValueEval GetEvalForPtg(Ptg ptg, OperationEvaluationContext ec)
{
// consider converting all these (ptg is XxxPtg) expressions To (ptg.GetType() == XxxPtg.class)
if (ptg is NamePtg)
{
// named ranges, macro functions
NamePtg namePtg = (NamePtg)ptg;
IEvaluationName nameRecord = _workbook.GetName(namePtg);
if (nameRecord.IsFunctionName)
{
return(new NameEval(nameRecord.NameText));
}
if (nameRecord.HasFormula)
{
return(EvaluateNameFormula(nameRecord.NameDefinition, ec));
}
throw new Exception("Don't now how To evalate name '" + nameRecord.NameText + "'");
}
if (ptg is NameXPtg)
{
return(ec.GetNameXEval(((NameXPtg)ptg)));
}
if (ptg is IntPtg)
{
return(new NumberEval(((IntPtg)ptg).Value));
}
if (ptg is NumberPtg)
{
return(new NumberEval(((NumberPtg)ptg).Value));
}
if (ptg is StringPtg)
{
return(new StringEval(((StringPtg)ptg).Value));
}
if (ptg is BoolPtg)
{
return(BoolEval.ValueOf(((BoolPtg)ptg).Value));
}
if (ptg is ErrPtg)
{
return(ErrorEval.ValueOf(((ErrPtg)ptg).ErrorCode));
}
if (ptg is MissingArgPtg)
{
return(MissingArgEval.instance);
}
if (ptg is AreaErrPtg || ptg is RefErrorPtg ||
ptg is DeletedArea3DPtg || ptg is DeletedRef3DPtg)
{
return(ErrorEval.REF_INVALID);
}
if (ptg is Ref3DPtg)
{
Ref3DPtg rptg = (Ref3DPtg)ptg;
return(ec.GetRef3DEval(rptg.Row, rptg.Column, rptg.ExternSheetIndex));
}
if (ptg is Area3DPtg)
{
Area3DPtg aptg = (Area3DPtg)ptg;
return(ec.GetArea3DEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn, aptg.ExternSheetIndex));
}
if (ptg is RefPtg)
{
RefPtg rptg = (RefPtg)ptg;
return(ec.GetRefEval(rptg.Row, rptg.Column));
}
if (ptg is AreaPtg)
{
AreaPtg aptg = (AreaPtg)ptg;
return(ec.GetAreaEval(aptg.FirstRow, aptg.FirstColumn, aptg.LastRow, aptg.LastColumn));
}
if (ptg is UnknownPtg)
{
// POI uses UnknownPtg when the encoded Ptg array seems To be corrupted.
// This seems To occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790)
// In any case, formulas are re-parsed before execution, so UnknownPtg should not Get here
throw new RuntimeException("UnknownPtg not allowed");
}
if (ptg is ExpPtg)
{
// ExpPtg is used for array formulas and shared formulas.
// it is currently unsupported, and may not even get implemented here
throw new RuntimeException("ExpPtg currently not supported");
}
throw new RuntimeException("Unexpected ptg class (" + ptg.GetType().Name + ")");
}
public IEvaluationName GetName(NamePtg namePtg)
{
int ix = namePtg.Index;
return(new Name(_iBook.GetNameRecord(ix), ix));
}
public String GetNameText(NamePtg namePtg)
{
return(_iBook.GetNameRecord(namePtg.Index).NameText);
}
public string GetNameText(NamePtg namePtg)
{
return(this._fpwb.GetNameText(namePtg));
}
public IEvaluationName GetName(NamePtg namePtg)
{
return(_masterBook.GetName(namePtg));
}
public IEvaluationName GetName(NamePtg namePtg)
{
int index = namePtg.Index;
return((IEvaluationName) new XSSFEvaluationWorkbook.Name(this._uBook.GetNameAt(index), index, (IFormulaParsingWorkbook)this));
}
public String GetNameText(NamePtg namePtg)
{
return(_fpwb.GetNameText(namePtg));
}
