public void TestExternalFunctions()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
ICell cell1 = sh.CreateRow(0).CreateCell(0);
cell1.CellFormula = ("ISODD(1)+ISEVEN(2)"); // functions from the Excel Analysis Toolpack
Assert.AreEqual("ISODD(1)+ISEVEN(2)", cell1.CellFormula);
ICell cell2 = sh.CreateRow(1).CreateCell(0);
try
{
//NPOI
//Run it twice in NUnit Gui Window, the first passed but the second failed.
//Maybe the function was cached. Ignore it.
cell2.CellFormula = ("MYBASEEXTFUNC(\"B1\")");
Assert.Fail("Should fail because MYBASEEXTFUNC is an unknown function");
}
catch (FormulaParseException)
{
; //expected
}
wb.AddToolPack(customToolpack);
cell2.CellFormula = ("MYBASEEXTFUNC(\"B1\")");
Assert.AreEqual("MYBASEEXTFUNC(\"B1\")", cell2.CellFormula);
ICell cell3 = sh.CreateRow(2).CreateCell(0);
cell3.CellFormula = ("MYBASEEXTFUNC2(\"C1\")&\"-\"&A2"); //where A2 is defined above
Assert.AreEqual("MYBASEEXTFUNC2(\"C1\")&\"-\"&A2", cell3.CellFormula);
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(2.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual("B1abc", Evaluator.Evaluate(cell2).StringValue);
Assert.AreEqual("C1abc2-B1abc", Evaluator.Evaluate(cell3).StringValue);
}
public void TestExternalFunctions()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.CreateSheet();
ICell cell1 = sh.CreateRow(0).CreateCell(0);
// functions from the Excel Analysis Toolpack
cell1.CellFormula = ("ISODD(1)+ISEVEN(2)");
Assert.AreEqual("ISODD(1)+ISEVEN(2)", cell1.CellFormula);
ICell cell2 = sh.CreateRow(1).CreateCell(0);
//unregistered functions are parseable and renderable, but may not be evaluateable
cell2.SetCellFormula("MYFUNC(\"B1\")");
try
{
evaluator.Evaluate(cell2);
Assert.Fail("Expected NotImplementedFunctionException/NotImplementedException");
}
catch (NotImplementedException e)
{
if (!(e.InnerException is NotImplementedFunctionException))
{
throw e;
}
// expected
// Alternatively, a future implementation of evaluate could return #NAME? error to align behavior with Excel
// assertEquals(ErrorEval.NAME_INVALID, ErrorEval.valueOf(evaluator.evaluate(cell2).getErrorValue()));
}
//try
//{
// //NPOI
// //Run it twice in NUnit Gui Window, the first passed but the second failed.
// //Maybe the function was cached. Ignore it.
// cell2.CellFormula=("MYBASEEXTFUNC(\"B1\")");
// Assert.Fail("Should fail because MYBASEEXTFUNC is an unknown function");
//}
//catch (FormulaParseException)
//{
// ; //expected
//}
wb.AddToolPack(customToolpack);
cell2.CellFormula = ("MYBASEEXTFUNC(\"B1\")");
Assert.AreEqual("MYBASEEXTFUNC(\"B1\")", cell2.CellFormula);
ICell cell3 = sh.CreateRow(2).CreateCell(0);
cell3.CellFormula = ("MYBASEEXTFUNC2(\"C1\")&\"-\"&A2"); //where A2 is defined above
Assert.AreEqual("MYBASEEXTFUNC2(\"C1\")&\"-\"&A2", cell3.CellFormula);
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(2.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual("B1abc", Evaluator.Evaluate(cell2).StringValue);
Assert.AreEqual("C1abc2-B1abc", Evaluator.Evaluate(cell3).StringValue);
wb.Close();
}