C# HSSFFormulaEvaluator.Evaluate方法代码示例

        public void TestEvaluateMissingArgs()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            ISheet sheet = wb.CreateSheet("Sheet1");
            ICell cell = sheet.CreateRow(0).CreateCell(0);

            cell.CellFormula=("if(true,)");
            fe.ClearAllCachedResultValues();
            CellValue cv;
            try
            {
                cv = fe.Evaluate(cell);
            }
            catch (Exception e)
            {
                Console.Error.WriteLine(e.Message);
                throw new AssertionException("Missing args Evaluation not implemented (bug 43354");
            }
            // MissingArg -> BlankEval -> zero (as formula result)
            Assert.AreEqual(0.0, cv.NumberValue, 0.0);

            // MissingArg -> BlankEval -> empty string (in concatenation)
            cell.CellFormula=("\"abc\"&if(true,)");
            fe.ClearAllCachedResultValues();
            Assert.AreEqual("abc", fe.Evaluate(cell).StringValue);
        }

        public void TestRegisterInRuntime()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
            HSSFRow row = (HSSFRow)sheet.CreateRow(0);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            HSSFCell cellA = (HSSFCell)row.CreateCell(0);
            cellA.CellFormula = ("FISHER(A5)");
            CellValue cv;
            try
            {
                //NPOI
                //Run it twice in NUnit Gui Window, the first passed but the second failed.
                //Maybe the function was cached. Ignore it.
                cv = fe.Evaluate(cellA);
                Assert.Fail("expectecd exception");
            }
            catch (NotImplementedException)
            {
                ;
            }

            FunctionEval.RegisterFunction("FISHER", new Function1());/*Function() {
            public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
                return ErrorEval.NA;
            }
        });*/

            cv = fe.Evaluate(cellA);
            Assert.AreEqual(ErrorEval.NA.ErrorCode, cv.ErrorValue);

            HSSFCell cellB = (HSSFCell)row.CreateCell(1);
            cellB.CellFormula = ("CUBEMEMBERPROPERTY(A5)");
            try
            {
                cv = fe.Evaluate(cellB);
                Assert.Fail("expectecd exception");
            }
            catch (NotImplementedException)
            {
                ;
            }

            AnalysisToolPak.RegisterFunction("CUBEMEMBERPROPERTY", new FreeRefFunction1());/*FreeRefFunction() {
            public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec) {
                return ErrorEval.NUM_ERROR;
            }
        });*/

            cv = fe.Evaluate(cellB);
            Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, cv.ErrorValue);
        }

        public void TestCountFuncs()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            ISheet sheet = wb.CreateSheet("Sheet1");
            ICell cell = sheet.CreateRow(0).CreateCell(0);

            cell.CellFormula=("COUNT(C5,,,,)"); // 4 missing args, C5 is blank 
            Assert.AreEqual(4.0, fe.Evaluate(cell).NumberValue, 0.0);

            cell.CellFormula=("COUNTA(C5,,)"); // 2 missing args, C5 is blank 
            fe.ClearAllCachedResultValues();
            Assert.AreEqual(2.0, fe.Evaluate(cell).NumberValue, 0.0);
        }

        public void Test27349()
        {
            // 27349-vLookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622
            Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vLookupAcrossSheets.xls");
            HSSFWorkbook wb;
            try
            {
                // original bug may have thrown exception here, or output warning to
                // stderr
                wb = new HSSFWorkbook(is1);
            }
            catch (IOException e)
            {
                throw new SystemException(e.Message);
            }

            ISheet sheet = wb.GetSheetAt(0);
            IRow row = sheet.GetRow(1);
            ICell cell = row.GetCell(0);

            // this defInitely would have failed due to 27349
            Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
                    .CellFormula);

            // We might as well Evaluate the formula
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue cv = fe.Evaluate(cell);

            Assert.AreEqual(CellType.Numeric, cv.CellType);
            Assert.AreEqual(3.0, cv.NumberValue, 0.0);
        }

        [Ignore] //TestUnfixedBugs
        public void Test49612()
        {
            IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("49612.xls");
            ISheet sh = wb.GetSheetAt(0);
            IRow row = sh.GetRow(0);
            ICell c1 = row.GetCell(2);
            ICell d1 = row.GetCell(3);
            ICell e1 = row.GetCell(2);

            Assert.AreEqual("SUM(BOB+JIM)", c1.CellFormula);

            // Problem 1: java.lang.ArrayIndexOutOfBoundsException in NPOI.HSSF.Model.LinkTable$ExternalBookBlock.GetNameText
            Assert.AreEqual("SUM('49612.xls'!BOB+'49612.xls'!JIM)", d1.CellFormula);

            //Problem 2
            //junit.framework.ComparisonFailure:
            //Expected :SUM('49612.xls'!BOB+'49612.xls'!JIM)
            //Actual   :SUM(BOB+JIM)
            Assert.AreEqual("SUM('49612.xls'!BOB+'49612.xls'!JIM)", e1.CellFormula);

            HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
            Assert.AreEqual(30.0, eval.Evaluate(c1).NumberValue, "Evaluating c1");

            //Problem 3:  java.lang.Exception: Unexpected arg eval type (NPOI.HSSF.Record.Formula.Eval.NameXEval)
            Assert.AreEqual(30, eval.Evaluate(d1).NumberValue, "Evaluating d1");

            Assert.AreEqual(30, eval.Evaluate(e1).NumberValue, "Evaluating e1");
        }

        public void TestFromFile()
        {

            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("finance.xls");
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            ISheet example1 = wb.GetSheet("IPMT");
            ICell ex1cell1 = example1.GetRow(6).GetCell(0);
            fe.Evaluate(ex1cell1);
            Assert.AreEqual(-22.41, ex1cell1.NumericCellValue, 0.1);

            ICell ex1cell2 = example1.GetRow(7).GetCell(0);
            fe.Evaluate(ex1cell2);
            Assert.AreEqual(-292.45, ex1cell2.NumericCellValue, 0.1);

        }

        public void TestInSpreadSheet()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.CellFormula=("B1%");
            row.CreateCell(1).SetCellValue(50.0);

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue cv;
            try
            {
                cv = fe.Evaluate(cell);
            }
            catch (SystemException e)
            {
                if (e.InnerException is NullReferenceException)
                {
                    throw new AssertionException("Identified bug 44608");
                }
                // else some other unexpected error
                throw e;
            }
            Assert.AreEqual(CellType.NUMERIC, cv.CellType);
            Assert.AreEqual(0.5, cv.NumberValue, 0.0);
        }

 private static void ConfirmError(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
     int expectedErrorCode)
 {
     cell.CellFormula=(formulaText);
     fe.NotifyUpdateCell(cell);
     CellValue result = fe.Evaluate(cell);
     Assert.AreEqual(result.CellType, CellType.ERROR);
     Assert.AreEqual(expectedErrorCode, result.ErrorValue);
 }

 private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
     int expectedResult)
 {
     cell.CellFormula=(formulaText);
     fe.NotifyUpdateCell(cell);
     CellValue result = fe.Evaluate(cell);
     Assert.AreEqual(result.CellType, CellType.NUMERIC);
     Assert.AreEqual(expectedResult, result.NumberValue, 0.0);
 }

 private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
     String expectedResult)
 {
     cell.CellFormula=(formulaText);
     fe.NotifyUpdateCell(cell);
     CellValue result = fe.Evaluate(cell);
     Assert.AreEqual(result.CellType, CellType.STRING);
     Assert.AreEqual(expectedResult, result.StringValue);
 }

 public void TestEvaluateSimple()
 {
     HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestNames.xls");
     NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
     ICell cell = sheet.GetRow(8).GetCell(0);
     HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
     NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
     Assert.AreEqual(NPOI.SS.UserModel.CellType.Numeric, cv.CellType);
     Assert.AreEqual(3.72, cv.NumberValue, 0.0);
 }

        public void TestRangeUsingOffsetFunc_bug46948()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            IRow row = wb.CreateSheet("Sheet1").CreateRow(0);
            ICell cellA1 = row.CreateCell(0);
            ICell cellB1 = row.CreateCell(1);
            row.CreateCell(2).SetCellValue(5.0); // C1
            row.CreateCell(3).SetCellValue(7.0); // D1
            row.CreateCell(4).SetCellValue(9.0); // E1

            cellA1.CellFormula = ("SUM(C1:OFFSET(C1,0,B1))");

            cellB1.SetCellValue(1.0); // range will be C1:D1

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue cv;
            try
            {
                cv = fe.Evaluate(cellA1);
            }
            catch (ArgumentException e)
            {
                if (e.Message.Equals("Unexpected ref arg class (NPOI.SS.Formula.LazyAreaEval)"))
                {
                    throw new AssertionException("Identified bug 46948");
                }
                throw e;
            }

            Assert.AreEqual(12.0, cv.NumberValue, 0.0);

            cellB1.SetCellValue(2.0); // range will be C1:E1
            fe.NotifyUpdateCell(cellB1);
            cv = fe.Evaluate(cellA1);
            Assert.AreEqual(21.0, cv.NumberValue, 0.0);

            cellB1.SetCellValue(0.0); // range will be C1:C1
            fe.NotifyUpdateCell(cellB1);
            cv = fe.Evaluate(cellA1);
            Assert.AreEqual(5.0, cv.NumberValue, 0.0);
        }

 /**
  * Translates StackOverflowError into AssertionFailedError
  */
 private static CellValue EvaluateWithCycles(HSSFWorkbook wb, ICell testCell)
 {
     HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb);
     try
     {
         return Evaluator.Evaluate(testCell);
     }
     catch (StackOverflowException)
     {
         throw new AssertionException("circular reference caused stack overflow error");
     }
 }

        public void TestFromFile()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("rank.xls");
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            HSSFSheet example1 = (HSSFSheet)wb.GetSheet("Example 1");
            HSSFCell ex1cell1 = (HSSFCell)example1.GetRow(7).GetCell(0);
            Assert.AreEqual(3.0, fe.Evaluate(ex1cell1).NumberValue);
            HSSFCell ex1cell2 = (HSSFCell)example1.GetRow(8).GetCell(0);
            Assert.AreEqual(5.0, fe.Evaluate(ex1cell2).NumberValue);

            HSSFSheet example2 = (HSSFSheet)wb.GetSheet("Example 2");
            for (int rownum = 1; rownum <= 10; rownum++)
            {
                HSSFCell cell = (HSSFCell)example2.GetRow(rownum).GetCell(2);
                double cachedResult = cell.NumericCellValue; //cached formula result
                Assert.AreEqual(cachedResult, fe.Evaluate(cell).NumberValue);
            }

            HSSFSheet example3 = (HSSFSheet)wb.GetSheet("Example 3");
            for (int rownum = 1; rownum <= 10; rownum++)
            {
                HSSFCell cellD = (HSSFCell)example3.GetRow(rownum).GetCell(3);
                double cachedResultD = cellD.NumericCellValue; //cached formula result
                Assert.AreEqual(cachedResultD, fe.Evaluate(cellD).NumberValue, new CellReference(cellD).FormatAsString());

                HSSFCell cellE = (HSSFCell)example3.GetRow(rownum).GetCell(4);
                double cachedResultE = cellE.NumericCellValue; //cached formula result
                Assert.AreEqual(cachedResultE, fe.Evaluate(cellE).NumberValue, new CellReference(cellE).FormatAsString());

                HSSFCell cellF = (HSSFCell)example3.GetRow(rownum).GetCell(5);
                double cachedResultF = cellF.NumericCellValue; //cached formula result
                Assert.AreEqual(cachedResultF, fe.Evaluate(cellF).NumberValue, new CellReference(cellF).FormatAsString());
            }
        }

        public void Test3DArea()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");
            wb.CreateSheet();
            wb.SetSheetName(1, "Sheet2");
            IRow row = sheet1.CreateRow(0);
            ICell cell = row.CreateCell(0);

            cell.CellFormula=("isblank(Sheet2!A1:A1)");

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue result = fe.Evaluate(cell);
            Assert.AreEqual(CellType.BOOLEAN, result.CellType);
            Assert.AreEqual(true, result.BooleanValue);

            cell.CellFormula=("isblank(D7:D7)");

            result = fe.Evaluate(cell);
            Assert.AreEqual(CellType.BOOLEAN, result.CellType);
            Assert.AreEqual(true, result.BooleanValue);
        }