C# UserModel.HSSFFormulaEvaluator类代码示例

        public void TestExistingWorkbook()
        {
            HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
            HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
            HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0);
            HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0);

            Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
            Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
            Assert.AreEqual(CellType.Formula, lC1Cell.CellType);

            HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
            HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
            HSSFFormulaEvaluator.SetupEnvironment(
                    new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_DUMMY_WORKBOOK_FILENAME },
                    new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator });

            Assert.AreEqual(CellType.Numeric, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell));
            Assert.AreEqual(CellType.String, lMainWorkbookEvaluator.EvaluateFormulaCell(lB1Cell));
            Assert.AreEqual(CellType.Boolean, lMainWorkbookEvaluator.EvaluateFormulaCell(lC1Cell));

            Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d);
            Assert.AreEqual("Apache rocks!", lB1Cell.StringCellValue);
            Assert.AreEqual(false, lC1Cell.BooleanCellValue);
        }

        private static void Process(IRow row, HSSFFormulaEvaluator eval)
        {
            IEnumerator it = row.GetEnumerator();
            while (it.MoveNext())
            {
                ICell cell = (ICell)it.Current;
                if (cell.CellType != NPOI.SS.UserModel.CellType.FORMULA)
                {
                    continue;
                }
                FormulaRecordAggregate record = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
                FormulaRecord r = record.FormulaRecord;
                Ptg[] ptgs = r.ParsedExpression;

                String cellRef = new CellReference(row.RowNum, cell.ColumnIndex, false, false).FormatAsString();
#if !HIDE_UNREACHABLE_CODE
                if (false && cellRef.Equals("BP24"))
                { 
                    Console.Write(cellRef);
                    Console.WriteLine(" - has " + ptgs.Length + " ptgs:");
                    for (int i = 0; i < ptgs.Length; i++)
                    {
                        String c = ptgs[i].GetType().ToString();
                        Console.WriteLine("\t" + c.Substring(c.LastIndexOf('.') + 1));
                    }
                    Console.WriteLine("-> " + cell.CellFormula);
                }
#endif

                NPOI.SS.UserModel.CellValue evalResult = eval.Evaluate(cell);
                Assert.IsNotNull(evalResult);
            }
        }

 /// <summary>
 /// 根据Excel列类型获取列的值
 /// </summary>
 /// <param name="cell">Excel列</param>
 /// <returns></returns>
 private static string GetCellValue(ICell cell)
 {
     if (cell == null)
         return string.Empty;
     switch (cell.CellType)
     {
         case CellType.BLANK:
             return string.Empty;
         case CellType.BOOLEAN:
             return cell.BooleanCellValue.ToString();
         case CellType.ERROR:
             return cell.ErrorCellValue.ToString();
         case CellType.NUMERIC:
         case CellType.Unknown:
         default:
             return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
         case CellType.STRING:
             return cell.StringCellValue;
         case CellType.FORMULA:
             try
             {
                 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                 e.EvaluateInCell(cell);
                 return cell.ToString();
             }
             catch
             {
                 return cell.NumericCellValue.ToString();
             } 
     }
 }

        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 TestEvaluate()
 {
     HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
     HSSFWorkbook wb2 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
     CellReference cellRef = new CellReference(wb.GetName("QUANT").RefersToFormula);
     ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     cell.SetCellValue(NEW_QUANT);
     cell = wb2.GetSheet("CostSheet").GetRow(1).GetCell(1);
     cell.SetCellValue(NEW_PART_COST);
     HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb);
     HSSFFormulaEvaluator EvaluatorCost = new HSSFFormulaEvaluator(wb2);
     String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" };
     HSSFFormulaEvaluator[] Evaluators = { Evaluator, EvaluatorCost, };
     HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators);
     cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula);
     ICell uccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     cellRef = new CellReference(wb.GetName("COST").RefersToFormula);
     ICell ccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula);
     ICell tccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
     Evaluator.EvaluateFormulaCell(uccell);
     Evaluator.EvaluateFormulaCell(ccell);
     Evaluator.EvaluateFormulaCell(tccell);
     Assert.AreEqual(NEW_PART_COST, uccell.NumericCellValue);
     Assert.AreEqual(NEW_PART_COST * NEW_QUANT, ccell.NumericCellValue);
     Assert.AreEqual(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.NumericCellValue);
 }

        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);
        }

        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());
            }
        }

        [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 TestClean1()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            String[] asserts = {
            "aniket\u0007\u0017\u0019", "aniket",
            "\u0011aniket\u0007\u0017\u0010", "aniket",
            "\u0011aniket\u0007\u0017\u007F", "aniket\u007F",
            "\u2116aniket\u2211\uFB5E\u2039", "\u2116aniket\u2211\uFB5E\u2039",
        };

            for (int i = 0; i < asserts.Length; i += 2)
            {
                String formulaText = "CLEAN(\"" + asserts[i] + "\")";
                ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
            }

            asserts = new String[] {
            "CHAR(7)&\"text\"&CHAR(7)", "text",
            "CHAR(7)&\"text\"&CHAR(17)", "text",
            "CHAR(181)&\"text\"&CHAR(190)", "\u00B5text\u00BE",
            "\"text\"&CHAR(160)&\"'\"", "text\u00A0'",
        };
            for (int i = 0; i < asserts.Length; i += 2)
            {
                String formulaText = "CLEAN(" + asserts[i] + ")";
                ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
            }
        }

        public void TestNpvFromSpreadsheet()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("IrrNpvTestCaseData.xls");
            ISheet sheet = wb.GetSheet("IRR-NPV");
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            StringBuilder failures = new StringBuilder();
            int failureCount = 0;
            // TODO YK: Formulas in rows 16 and 17 operate with ArrayPtg which isn't yet supported
            // FormulaEvaluator as of r1041407 throws "Unexpected ptg class (NPOI.SS.Formula.PTG.ArrayPtg)"
            for (int rownum = 9; rownum <= 15; rownum++)
            {
                IRow row = sheet.GetRow(rownum);
                ICell cellB = row.GetCell(1);
                try
                {
                    CellValue cv = fe.Evaluate(cellB);
                    assertFormulaResult(cv, cellB);
                }
                catch (Exception e)
                {
                    if (failures.Length > 0) failures.Append('\n');
                    failures.Append("Row[" + (cellB.RowIndex + 1) + "]: " + cellB.CellFormula + " ");
                    failures.Append(e.Message);
                    failureCount++;
                }
            }

            if (failures.Length > 0)
            {
                throw new AssertionException(failureCount + " IRR Evaluations failed:\n" + failures.ToString());
            }
        }

        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);
        }

        public void TestEvaluateInSheetExample2()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);

            sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d);
            sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d);
            sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d);
            sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d);
            sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d);
            sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d);
            sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d);

            ICell cell = row.CreateCell(8);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            // Enumeration
            cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3");
            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            double res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);

            // Range
            cell.CellFormula = ("NPV(A2, A4:A8)+A3");

            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
        }

 public void SetUp()
 {
     HSSFWorkbook wb = new HSSFWorkbook();
     ISheet sheet = wb.CreateSheet("new sheet");
     cell11 = sheet.CreateRow(0).CreateCell(0);
     cell11.SetCellType(CellType.FORMULA);
     Evaluator = new HSSFFormulaEvaluator(wb);
 }

 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);
 }