// 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();
}
}
/**
* To aid Readability the parameters have been encoded with single quotes instead of double
* quotes. This method converts single quotes to double quotes before performing the Parse
* and result check.
*/
private static void ConfirmStringParse(String singleQuotedValue)
{
// formula: internal quotes become double double, surround with double quotes
String formula = '"' + singleQuotedValue.Replace("'", "\"\"") + '"';
String expectedValue = singleQuotedValue.Replace('\'', '"');
StringPtg sp = (StringPtg)ParseSingleToken(formula, typeof(StringPtg));
Assert.AreEqual(expectedValue, sp.Value);
}
public void TestNonAlphaFormula()
{
String currencyCell = "F3";
Ptg[] ptgs = ParseFormula("\"TOTAL[\"&" + currencyCell + "&\"]\"");
Assert.AreEqual(5, ptgs.Length);
Assert.IsTrue((ptgs[0] is StringPtg), "Ptg[0] is1 a string");
StringPtg firstString = (StringPtg)ptgs[0];
Assert.AreEqual("TOTAL[", firstString.Value);
//the PTG order isn't 100% correct but it still works - dmui
}
public void TestYN()
{
Ptg[] ptgs = ParseFormula("IF(TRUE,\"Y\",\"N\")");
Assert.AreEqual(7, ptgs.Length);
BoolPtg flag = (BoolPtg)ptgs[0];
AttrPtg funif = (AttrPtg)ptgs[1];
StringPtg y = (StringPtg)ptgs[2];
AttrPtg goto1 = (AttrPtg)ptgs[3];
StringPtg n = (StringPtg)ptgs[4];
Assert.AreEqual(true, flag.Value);
Assert.AreEqual("Y", y.Value);
Assert.AreEqual("N", n.Value);
Assert.AreEqual("IF", funif.ToFormulaString());
Assert.IsTrue(goto1.IsSkip, "Goto ptg exists");
}