Esempio n. 1
0
 /**
  * Makes sure that a formula referring to the named range parses properly
  */
 private static void ConfirmParseFormula(HSSFWorkbook workbook)
 {
     Ptg[] ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
     Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
     Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
     Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
 }
Esempio n. 2
0
        public HSSFAutoFilter(string formula, HSSFWorkbook workbook)
        {
            //this.workbook = workbook;

            Ptg[] ptgs = HSSFFormulaParser.Parse(formula, workbook);
            if (!(ptgs[0] is Area3DPtg))
            {
                throw new ArgumentException("incorrect formula");
            }

            Area3DPtg ptg   = (Area3DPtg)ptgs[0];
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(ptg.ExternSheetIndex);
            //look for the prior record
            int loc = sheet.Sheet.FindFirstRecordLocBySid(DefaultColWidthRecord.sid);

            CreateFilterModeRecord(sheet, loc + 1);
            CreateAutoFilterInfoRecord(sheet, loc + 2, ptg);
            //look for "_FilterDatabase" NameRecord of the sheet
            NameRecord name = workbook.Workbook.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex + 1);

            if (name == null)
            {
                name = workbook.Workbook.CreateBuiltInName(NameRecord.BUILTIN_FILTER_DB, ptg.ExternSheetIndex + 1);
            }
            name.IsHiddenName = true;

            name.NameDefinition = ptgs;
        }
        private static void ConfirmFunc(string formula, int expPtgArraySize, bool isVarArgFunc, int funcIx)
        {
            Ptg[] ptgs = Parse(formula);
            Ptg ptgF = ptgs[ptgs.Length - 1];  // func is last RPN token in all these formulas

            // Check critical things in the Ptg array encoding.
            if (!(ptgF is AbstractFunctionPtg))
            {
                throw new Exception("function token missing");
            }
            AbstractFunctionPtg func = (AbstractFunctionPtg)ptgF;
            if (func.FunctionIndex == 255)
            {
                throw new AssertionException("Failed to recognise built-in function in formula '"
                        + formula + "'");
            }
            Assert.AreEqual(expPtgArraySize, ptgs.Length);
            Assert.AreEqual(funcIx, func.FunctionIndex);
            Type expCls = isVarArgFunc ? typeof(FuncVarPtg) : typeof(FuncPtg);
            Assert.AreEqual(expCls, ptgF.GetType());

            // check that Parsed Ptg array Converts back to formula text OK
            HSSFWorkbook book = new HSSFWorkbook();
            string reRenderedFormula = HSSFFormulaParser.ToFormulaString(book, ptgs);
            Assert.AreEqual(formula, reRenderedFormula);
        }
Esempio n. 4
0
        public void TestArrayFormulas()
        {
            int rownum = 4;
            int colnum = 4;

            FormulaRecord fr = new FormulaRecord();

            fr.Row    = (rownum);
            fr.Column = ((short)colnum);

            FormulaRecordAggregate agg = new FormulaRecordAggregate(fr, null, SharedValueManager.CreateEmpty());

            Ptg[] ptgsForCell = { new ExpPtg(rownum, colnum) };
            agg.SetParsedExpression(ptgsForCell);

            String formula = "SUM(A1:A3*B1:B3)";

            Ptg[] ptgs = HSSFFormulaParser.Parse(formula, null, FormulaType.Array, 0);
            agg.SetArrayFormula(new CellRangeAddress(rownum, rownum, colnum, colnum), ptgs);

            Assert.IsTrue(agg.IsPartOfArrayFormula);
            Assert.AreEqual("E5", agg.GetArrayFormulaRange().FormatAsString());
            Ptg[]  ptg     = agg.FormulaTokens;
            String fmlaSer = FormulaRenderer.ToFormulaString(null, ptg);

            Assert.AreEqual(formula, fmlaSer);

            agg.RemoveArrayFormula(rownum, colnum);
            Assert.IsFalse(agg.IsPartOfArrayFormula);
        }
Esempio n. 5
0
        public void TestSpaceAtStartOfFormula()
        {
            // Simulating cell formula of "= 4" (note space)
            // The same Ptg array can be observed if an excel file is1 saved with that exact formula

            AttrPtg spacePtg = AttrPtg.CreateSpace(AttrPtg.SpaceType.SPACE_BEFORE, 1);

            Ptg[]  ptgs = { spacePtg, new IntPtg(4), };
            String formulaString;

            try
            {
                formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
            }
            catch (InvalidOperationException e)
            {
                if (e.Message.Equals("too much stuff left on the stack", StringComparison.InvariantCultureIgnoreCase))
                {
                    throw new AssertFailedException("Identified bug 44609");
                }
                // else some unexpected error
                throw e;
            }
            // FormulaParser strips spaces anyway
            Assert.AreEqual("4", formulaString);

            ptgs          = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, AddPtg.instance, };
            formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
            Assert.AreEqual("3+4", formulaString);
        }
Esempio n. 6
0
 private String FormatValue(Object value)
 {
     if (value is Ptg[])
     {
         Ptg[] ptgs = (Ptg[])value;
         return(HSSFFormulaParser.ToFormulaString(_book, ptgs));
     }
     if (value is NumberEval)
     {
         NumberEval ne = (NumberEval)value;
         return(ne.StringValue);
     }
     if (value is StringEval)
     {
         StringEval se = (StringEval)value;
         return("'" + se.StringValue + "'");
     }
     if (value is BoolEval)
     {
         BoolEval be = (BoolEval)value;
         return(be.StringValue);
     }
     if (value == BlankEval.instance)
     {
         return("#BLANK#");
     }
     if (value is ErrorEval)
     {
         ErrorEval ee = (ErrorEval)value;
         return(ErrorEval.GetText(ee.ErrorCode));
     }
     throw new ArgumentException("Unexpected value class ("
                                 + value.GetType().Name + ")");
 }
Esempio n. 7
0
        public void TestFindBuiltInNameRecord()
        {
            // TestRRaC has multiple (3) built-in name records
            // The second print titles name record has SheetNumber==4
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRRaC.xls");
            NameRecord   nr;

            Assert.AreEqual(3, wb.Workbook.NumNames);
            nr = wb.Workbook.GetNameRecord(2);
            // TODO - render full row and full column refs properly
            Assert.AreEqual("Sheet2!$A$1:$IV$1", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // 1:1

            try
            {
                wb.SetRepeatingRowsAndColumns(3, 4, 5, 8, 11);
            }
            catch (Exception e)
            {
                if (e.Message.Equals("Builtin (7) already exists for sheet (4)"))
                {
                    // there was a problem in the code which locates the existing print titles name record
                    throw new Exception("Identified bug 45720b");
                }
                throw e;
            }
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            Assert.AreEqual(3, wb.Workbook.NumNames);
            nr = wb.Workbook.GetNameRecord(2);
            Assert.AreEqual("Sheet2!E:F,Sheet2!$A$9:$IV$12", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // E:F,9:12
        }
Esempio n. 8
0
        public void SetCellFormula(String formula)
        {
            if (IsPartOfArrayFormulaGroup)
            {
                NotifyArrayFormulaChanging();
            }
            int   row        = _record.Row;
            int   col        = _record.Column;
            short styleIndex = _record.XFIndex;

            if (string.IsNullOrEmpty(formula))
            {
                NotifyFormulaChanging();
                SetCellType(CellType.Blank, false, row, col, styleIndex);
                return;
            }
            int sheetIndex = book.GetSheetIndex(_sheet);

            Ptg[] ptgs = HSSFFormulaParser.Parse(formula, book, FormulaType.Cell, sheetIndex);

            SetCellType(CellType.Formula, false, row, col, styleIndex);
            FormulaRecordAggregate agg  = (FormulaRecordAggregate)_record;
            FormulaRecord          frec = agg.FormulaRecord;

            frec.Options = ((short)2);
            frec.Value   = (0);

            //only set to default if there is no extended format index already set
            if (agg.XFIndex == (short)0)
            {
                agg.XFIndex = ((short)0x0f);
            }
            agg.SetParsedExpression(ptgs);
        }
        public void TestParse()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");

            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("externalFunctionExample.xls");

            Ptg[] ptgs = HSSFFormulaParser.Parse("YEARFRAC(B1,C1)", wb);
            Assert.AreEqual(4, ptgs.Length);
            Assert.AreEqual(typeof(NameXPtg), ptgs[0].GetType());

            wb.GetSheetAt(0).GetRow(0).CreateCell(6).CellFormula = ("YEARFRAC(C1,B1)");
#if !HIDE_UNREACHABLE_CODE
            if (false)
            {
                // In case you fancy Checking in excel
                try
                {
                    FileStream tempFile = File.Create("testExtFunc.xls");
                    //FileOutputStream fout = new FileOutputStream(tempFile);
                    wb.Write(tempFile);
                    tempFile.Close();
                    //Console.WriteLine("check out " + tempFile.getAbsolutePath());
                }
                catch (IOException e)
                {
                    throw e;
                }
            }
#endif
        }
Esempio n. 10
0
 private String ToFormulaString(Ptg[] ParsedExpression)
 {
     if (ParsedExpression == null)
     {
         return(null);
     }
     return(HSSFFormulaParser.ToFormulaString(workbook, ParsedExpression));
 }
 protected internal String ToFormulaString(Ptg[] ParsedExpression)
 {
     if (ParsedExpression == null)
     {
         return(null);
     }
     return(HSSFFormulaParser.ToFormulaString(this.workbook, ParsedExpression));
 }
 protected internal static String ToFormulaString(Ptg[] parsedExpression, HSSFWorkbook workbook)
 {
     if (parsedExpression == null || parsedExpression.Length == 0)
     {
         return(null);
     }
     return(HSSFFormulaParser.ToFormulaString(workbook, parsedExpression));
 }
Esempio n. 13
0
 /**
  * TODO - Parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
  * this call will produce the wrong results if the formula Contains any cell references
  * One approach might be to apply the inverse of SharedFormulaRecord.ConvertSharedFormulas(Stack, int, int)
  * Note - two extra parameters (rowIx &colIx) will be required. They probably come from one of the Region objects.
  *
  * @return <c>null</c> if <c>formula</c> was null.
  */
 private static Ptg[] ParseFormula(String formula, HSSFWorkbook workbook)
 {
     if (formula == null)
     {
         return(null);
     }
     return(HSSFFormulaParser.Parse(formula, workbook));
 }
Esempio n. 14
0
        /**
         * TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
         * this call will produce the wrong results if the formula contains any cell references
         * One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int)
         * Note - two extra parameters (rowIx &amp; colIx) will be required. They probably come from one of the Region objects.
         *
         * @return <code>null</code> if <c>formula</c> was null.
         */
        private static Ptg[] ParseFormula(String formula, HSSFSheet sheet)
        {
            if (formula == null)
            {
                return(null);
            }
            int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);

            return(HSSFFormulaParser.Parse(formula, (HSSFWorkbook)sheet.Workbook, FormulaType.CELL, sheetIndex));
        }
Esempio n. 15
0
        public void TestToFormulaStringZeroArgFunction()
        {
            HSSFWorkbook book = new HSSFWorkbook();

            Ptg[] ptgs =
            {
                new FuncPtg(10),
            };
            Assert.AreEqual("NA()", HSSFFormulaParser.ToFormulaString(book, ptgs));
        }
Esempio n. 16
0
        /**
         * TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
         * this call will produce the wrong results if the formula Contains any cell references
         * One approach might be to apply the inverse of SharedFormulaRecord.ConvertSharedFormulas(Stack, int, int)
         * Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects.
         *
         * @return <code>null</code> if <tt>formula</tt> was null.
         */
        public static Ptg[] ParseFormula(String formula, HSSFSheet sheet)
        {
            if (formula == null)
            {
                return(null);
            }
            int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);

            return(HSSFFormulaParser.Parse(formula, sheet.Workbook as HSSFWorkbook, FormulaType.Cell, sheetIndex));
        }
Esempio n. 17
0
        public void TestTypeOfRootPtg()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            wb.CreateSheet("CSCO");

            Ptg[] ptgs = HSSFFormulaParser.Parse("CSCO!$E$71", wb, FormulaType.NamedRange, 0);
            for (int i = 0; i < ptgs.Length; i++)
            {
                Assert.AreEqual('R', ptgs[i].RVAType);
            }
        }
Esempio n. 18
0
        public void TestTypeOfRootPtg()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            wb.CreateSheet("CSCO");

            Ptg[] ptgs = HSSFFormulaParser.Parse("CSCO!$E$71", wb, FormulaType.NamedRange, 0);
            foreach (Ptg ptg in ptgs)
            {
                Assert.AreEqual('R', ptg.RVAType);
            }
        }
Esempio n. 19
0
        public void TestParseSumIfSum()
        {
            String formulaString;

            Ptg[] ptgs;
            ptgs          = ParseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))");
            formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
            Assert.AreEqual("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString);

            ptgs          = ParseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)");
            formulaString = HSSFFormulaParser.ToFormulaString(null, ptgs);
            Assert.AreEqual("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString);
        }
Esempio n. 20
0
        private static void ConfirmArgCountMsg(String formula, String expectedMessage)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            try
            {
                HSSFFormulaParser.Parse(formula, book);
                throw new AssertFailedException("Didn't Get Parse exception as expected");
            }
            catch (Exception e)
            {
                Assert.AreEqual(expectedMessage, e.Message);
            }
        }
Esempio n. 21
0
        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);
        }
Esempio n. 22
0
 /**
  * @return The parsed token array representing the formula or value specified.
  * Empty array if both formula and value are <c>null</c>
  */
 private static Ptg[] ConvertDoubleFormula(String formula, Double value, HSSFWorkbook workbook)
 {
     if (formula == null)
     {
         if (double.IsNaN(value))
         {
             return(Ptg.EMPTY_PTG_ARRAY);
         }
         return(new Ptg[] { new NumberPtg(value), });
     }
     if (!double.IsNaN(value))
     {
         throw new InvalidOperationException("Both formula and value cannot be present");
     }
     return(HSSFFormulaParser.Parse(formula, workbook));
 }
Esempio n. 23
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);
        }
Esempio n. 24
0
        /**
         * @return The Parsed token array representing the formula or value specified.
         * Empty array if both formula and value are <code>null</code>
         */
        private static Ptg[] ConvertDoubleFormula(String formula, Double value, HSSFSheet sheet)
        {
            if (formula == null)
            {
                if (double.IsNaN(value))
                {
                    return(Ptg.EMPTY_PTG_ARRAY);
                }
                return(new Ptg[] { new NumberPtg(value), });
            }
            if (!double.IsNaN(value))
            {
                throw new InvalidOperationException("Both formula and value cannot be present");
            }
            IWorkbook wb = sheet.Workbook;

            return(HSSFFormulaParser.Parse(formula, (HSSFWorkbook)wb, FormulaType.CELL, wb.GetSheetIndex(sheet)));
        }
Esempio n. 25
0
        private static String ShiftAllColumnsBy1(String formula)
        {
            int          letUsShiftColumn1By1Column = 1;
            HSSFWorkbook wb = null;

            Ptg[] ptgs = HSSFFormulaParser.Parse(formula, wb);
            for (int i = 0; i < ptgs.Length; i++)
            {
                Ptg ptg = ptgs[i];
                if (ptg is AreaPtg)
                {
                    AreaPtg aptg = (AreaPtg)ptg;
                    aptg.FirstColumn = ((short)(aptg.FirstColumn + letUsShiftColumn1By1Column));
                    aptg.LastColumn  = ((short)(aptg.LastColumn + letUsShiftColumn1By1Column));
                }
            }
            String newFormula = HSSFFormulaParser.ToFormulaString(wb, ptgs);

            return(newFormula);
        }
Esempio n. 26
0
 public void TestTooFewOperandArgs()
 {
     // Simulating badly encoded cell formula of "=/1"
     // Not sure if Excel could ever produce this
     Ptg[] ptgs =
     {
         // Excel would probably have put tMissArg here
         new IntPtg(1),
         DividePtg.instance,
     };
     try
     {
         HSSFFormulaParser.ToFormulaString(null, ptgs);
         Assert.Fail("Expected exception was not thrown");
     }
     catch (InvalidOperationException e)
     {
         // expected during successful test
         Assert.IsTrue(e.Message.StartsWith("Too few arguments supplied to operation"));
     }
 }
Esempio n. 27
0
        public void TestOperandClass()
        {
            HSSFWorkbook book = new HSSFWorkbook();

            Ptg[] ptgs = HSSFFormulaParser.Parse("sum(A1:A2)", book);
            Assert.AreEqual(2, ptgs.Length);
            Assert.AreEqual(typeof(AreaPtg), ptgs[0].GetType());

            switch (ptgs[0].PtgClass)
            {
            case Ptg.CLASS_REF:
                // correct behaviour
                break;

            case Ptg.CLASS_VALUE:
                throw new AssertionException("Identified bug 44675b");

            default:
                throw new Exception("Unexpected operand class");
            }
        }
Esempio n. 28
0
        private Ptg[] CreateListFormula(HSSFWorkbook workbook)
        {
            if (_explicitListValues == null)
            {
                // formula is parsed with slightly different RVA rules: (root node type must be 'reference')
                return(HSSFFormulaParser.Parse(_formula1, workbook, FormulaType.DATAVALIDATION_LIST));
                // To do: Excel places restrictions on the available operations within a list formula.
                // Some things like union and intersection are not allowed.
            }
            // explicit list was provided
            StringBuilder sb = new StringBuilder(_explicitListValues.Length * 16);

            for (int i = 0; i < _explicitListValues.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append('\0'); // list delimiter is the nul char
                }
                sb.Append(_explicitListValues[i]);
            }
            return(new Ptg[] { new StringPtg(sb.ToString()), });
        }
Esempio n. 29
0
        public void TestWithNamedRange()
        {
            HSSFWorkbook  workbook = new HSSFWorkbook();
            FormulaParser fp;

            Ptg[] ptgs;

            NPOI.SS.UserModel.Sheet s = workbook.CreateSheet("Foo");
            s.CreateRow(0).CreateCell((short)0).SetCellValue(1.1);
            s.CreateRow(1).CreateCell((short)0).SetCellValue(2.3);
            s.CreateRow(2).CreateCell((short)2).SetCellValue(3.1);

            NPOI.SS.UserModel.Name name = workbook.CreateName();
            name.NameName        = ("testName");
            name.RefersToFormula = ("A1:A2");

            ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
            Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
            Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
            Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());

            // Now make it a single cell
            name.RefersToFormula = ("C3");

            ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
            Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
            Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
            Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());

            // And make it non-contiguous
            name.RefersToFormula = ("A1:A2,C3");
            ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
            Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
            Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
            Assert.AreEqual(typeof(FuncVarPtg), ptgs[1].GetType());
        }
Esempio n. 30
0
        private void ConfirmCell(ICell formulaCell, String formula, HSSFWorkbook wb)
        {
            Ptg[] excelPtgs    = FormulaExtractor.GetPtgs(formulaCell);
            Ptg[] poiPtgs      = HSSFFormulaParser.Parse(formula, wb);
            int   nExcelTokens = excelPtgs.Length;
            int   nPoiTokens   = poiPtgs.Length;

            if (nExcelTokens != nPoiTokens)
            {
                if (nExcelTokens == nPoiTokens + 1 && excelPtgs[0].GetType() == typeof(AttrPtg))
                {
                    // compensate for missing tAttrVolatile, which belongs in any formula
                    // involving OFFSET() et al. POI currently does not insert where required
                    Ptg[] temp = new Ptg[nExcelTokens];
                    temp[0] = excelPtgs[0];
                    Array.Copy(poiPtgs, 0, temp, 1, nPoiTokens);
                    poiPtgs = temp;
                }
                else
                {
                    throw new Exception("Expected " + nExcelTokens + " tokens but got "
                                        + nPoiTokens);
                }
            }
            bool          hasMismatch = false;
            StringBuilder sb          = new StringBuilder();

            for (int i = 0; i < nExcelTokens; i++)
            {
                Ptg poiPtg   = poiPtgs[i];
                Ptg excelPtg = excelPtgs[i];
                if (excelPtg.GetType() != poiPtg.GetType())
                {
                    hasMismatch = true;
                    sb.Append("  mismatch token type[" + i + "] " + GetShortClassName(excelPtg) + " "
                              + excelPtg.RVAType + " - " + GetShortClassName(poiPtg) + " "
                              + poiPtg.RVAType);
                    sb.Append(Environment.NewLine);
                    continue;
                }
                if (poiPtg.IsBaseToken)
                {
                    continue;
                }
                sb.Append("  token[" + i + "] " + excelPtg.ToString() + " "
                          + excelPtg.RVAType);

                if (excelPtg.PtgClass != poiPtg.PtgClass)
                {
                    hasMismatch = true;
                    sb.Append(" - was " + poiPtg.RVAType);
                }
                sb.Append(Environment.NewLine);
            }
            //if (false)
            //{ // Set 'true' to see trace of RVA values
            //    Console.WriteLine(formula);
            //    Console.WriteLine(sb.ToString());
            //}
            if (hasMismatch)
            {
                throw new AssertionException(sb.ToString());
            }
        }