Пример #1
0
        public ValueEval GetArea3DEval(Area3DPxg aptg)
        {
            SheetRangeEvaluator sre = CreateExternSheetRefEvaluator(aptg.SheetName, aptg.LastSheetName, aptg.ExternalWorkbookNumber);

            return(new LazyAreaEval(aptg.FirstRow, aptg.FirstColumn,
                                    aptg.LastRow, aptg.LastColumn, sre));
        }
Пример #2
0
 private static Ptg CreateDeletedRef(Ptg ptg)
 {
     if (ptg is RefPtg)
     {
         return(new RefErrorPtg());
     }
     if (ptg is Ref3DPtg)
     {
         Ref3DPtg rptg = (Ref3DPtg)ptg;
         return(new DeletedRef3DPtg(rptg.ExternSheetIndex));
     }
     if (ptg is AreaPtg)
     {
         return(new AreaErrPtg());
     }
     if (ptg is Area3DPtg)
     {
         Area3DPtg area3DPtg = (Area3DPtg)ptg;
         return(new DeletedArea3DPtg(area3DPtg.ExternSheetIndex));
     }
     if (ptg is Ref3DPxg)
     {
         Ref3DPxg pxg = (Ref3DPxg)ptg;
         return(new Deleted3DPxg(pxg.ExternalWorkbookNumber, pxg.SheetName));
     }
     if (ptg is Area3DPxg)
     {
         Area3DPxg pxg = (Area3DPxg)ptg;
         return(new Deleted3DPxg(pxg.ExternalWorkbookNumber, pxg.SheetName));
     }
     throw new ArgumentException("Unexpected ref ptg class (" + ptg.GetType().Name + ")");
 }
        // Fetch the workbook this refers to, and the name as defined with that
        private ValueEval GetExternalNameXEval(ExternalName externName, string workbookName)
        {
            try
            {
                WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.GetOtherWorkbookEvaluator(workbookName);
                IEvaluationName   evaluationName       = refWorkbookEvaluator.GetName(externName.Name, externName.Ix - 1);
                if (evaluationName != null && evaluationName.HasFormula)
                {
                    if (evaluationName.NameDefinition.Length > 1)
                    {
                        throw new Exception("Complex name formulas not supported yet");
                    }
                    // Need to Evaluate the reference in the context of the other book
                    OperationEvaluationContext refWorkbookContext = new OperationEvaluationContext(
                        refWorkbookEvaluator, refWorkbookEvaluator.Workbook, -1, -1, -1, _tracker);

                    Ptg ptg = evaluationName.NameDefinition[0];
                    if (ptg is Ref3DPtg)
                    {
                        Ref3DPtg ref3D = (Ref3DPtg)ptg;
                        return(refWorkbookContext.GetRef3DEval(ref3D));
                    }
                    else if (ptg is Ref3DPxg)
                    {
                        Ref3DPxg ref3D = (Ref3DPxg)ptg;
                        return(refWorkbookContext.GetRef3DEval(ref3D));
                    }
                    else if (ptg is Area3DPtg)
                    {
                        Area3DPtg area3D = (Area3DPtg)ptg;
                        return(refWorkbookContext.GetArea3DEval(area3D));
                    }
                    else if (ptg is Area3DPxg)
                    {
                        Area3DPxg area3D = (Area3DPxg)ptg;
                        return(refWorkbookContext.GetArea3DEval(area3D));
                    }
                }
                return(ErrorEval.REF_INVALID);
            }
            catch (WorkbookNotFoundException)
            {
                return(ErrorEval.REF_INVALID);
            }
        }
Пример #4
0
        public void ParseStructuredReferences()
        {
            XSSFWorkbook           wb  = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
            XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);

            Ptg[] ptgs;

            /*
             * The following cases are tested (copied from FormulaParser.parseStructuredReference)
             * 1 Table1[col]
             * 2 Table1[[#Totals],[col]]
             * 3 Table1[#Totals]
             * 4 Table1[#All]
             * 5 Table1[#Data]
             * 6 Table1[#Headers]
             * 7 Table1[#Totals]
             * 8 Table1[#This Row]
             * 9 Table1[[#All],[col]]
             * 10 Table1[[#Headers],[col]]
             * 11 Table1[[#Totals],[col]]
             * 12 Table1[[#All],[col1]:[col2]]
             * 13 Table1[[#Data],[col1]:[col2]]
             * 14 Table1[[#Headers],[col1]:[col2]]
             * 15 Table1[[#Totals],[col1]:[col2]]
             * 16 Table1[[#Headers],[#Data],[col2]]
             * 17 Table1[[#This Row], [col1]]
             * 18 Table1[ [col1]:[col2] ]
             */
            String tbl = "\\_Prime.1";
            String noTotalsRowReason = ": Tables without a Totals row should return #REF! on [#Totals]";

            ////// Case 1: Evaluate Table1[col] with apostrophe-escaped #-signs ////////
            ptgs = Parse(fpb, "SUM(" + tbl + "[calc='#*'#])");
            Assert.AreEqual(2, ptgs.Length);
            // Area3DPxg [sheet=Table ! A2:A7]
            Assert.IsTrue(ptgs[0] is Area3DPxg);
            Area3DPxg ptg0 = (Area3DPxg)ptgs[0];

            Assert.AreEqual("Table", ptg0.SheetName);
            Assert.AreEqual("A2:A7", ptg0.Format2DRefAsString());
            // Note: structured references are evaluated and resolved to regular 3D area references.
            Assert.AreEqual("Table!A2:A7", ptg0.ToFormulaString());
            // AttrPtg [sum ]
            Assert.IsTrue(ptgs[1] is AttrPtg);
            AttrPtg ptg1 = (AttrPtg)ptgs[1];

            Assert.IsTrue(ptg1.IsSum);

            ////// Case 1: Evaluate "Table1[col]" ////////
            ptgs = Parse(fpb, tbl + "[Name]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B2:B7", ptgs[0].ToFormulaString(), "Table1[col]");

            ////// Case 2: Evaluate "Table1[[#Totals],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals],[col]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);

            ////// Case 3: Evaluate "Table1[#Totals]" ////////
            ptgs = Parse(fpb, tbl + "[#Totals]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);

            ////// Case 4: Evaluate "Table1[#All]" ////////
            ptgs = Parse(fpb, tbl + "[#All]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A1:C7", ptgs[0].ToFormulaString(), "Table1[#All]");

            ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Data rows) ////////
            ptgs = Parse(fpb, tbl + "[#Data]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A2:C7", ptgs[0].ToFormulaString(), "Table1[#Data]");

            ////// Case 6: Evaluate "Table1[#Headers]" ////////
            ptgs = Parse(fpb, tbl + "[#Headers]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A1:C1", ptgs[0].ToFormulaString(), "Table1[#Headers]");

            ////// Case 7: Evaluate "Table1[#Totals]" ////////
            ptgs = Parse(fpb, tbl + "[#Totals]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);

            ////// Case 8: Evaluate "Table1[#This Row]" ////////
            ptgs = Parse(fpb, tbl + "[#This Row]", 2);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A3:C3", ptgs[0].ToFormulaString(), "Table1[#This Row]");

            ////// Evaluate "Table1[@]" (equivalent to "Table1[#This Row]") ////////
            ptgs = Parse(fpb, tbl + "[@]", 2);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A3:C3", ptgs[0].ToFormulaString());

            ////// Evaluate "Table1[#This Row]" when rowIndex is outside Table ////////
            ptgs = Parse(fpb, tbl + "[#This Row]", 10);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[#This Row]");

            ////// Evaluate "Table1[@]" when rowIndex is outside Table ////////
            ptgs = Parse(fpb, tbl + "[@]", 10);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[@]");
            ////// Evaluate "Table1[[#Data],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Data], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!C2:C7", ptgs[0].ToFormulaString(), "Table1[[#Data],[col]]");

            ////// Case 9: Evaluate "Table1[[#All],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#All], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!C1:C7", ptgs[0].ToFormulaString(), "Table1[[#All],[col]]");

            ////// Case 10: Evaluate "Table1[[#Headers],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            // also acceptable: Table1!B1
            Assert.AreEqual("Table!C1:C1", ptgs[0].ToFormulaString(), "Table1[[#Headers],[col]]");
            ////// Case 11: Evaluate "Table1[[#Totals],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals],[Name]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);

            ////// Case 12: Evaluate "Table1[[#All],[col1]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#All], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B1:C7", ptgs[0].ToFormulaString(), "Table1[[#All],[col1]:[col2]]");

            ////// Case 13: Evaluate "Table1[[#Data],[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Data], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B2:C7", ptgs[0].ToFormulaString(), "Table1[[#Data],[col]:[col2]]");

            ////// Case 14: Evaluate "Table1[[#Headers],[col1]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B1:C1", ptgs[0].ToFormulaString(), "Table1[[#Headers],[col1]:[col2]]");

            ////// Case 15: Evaluate "Table1[[#Totals],[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]:[col2]]" + noTotalsRowReason);

            ////// Case 16: Evaluate "Table1[[#Headers],[#Data],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers],[#Data],[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!C1:C7", ptgs[0].ToFormulaString(), "Table1[[#Headers],[#Data],[col]]");

            ////// Case 17: Evaluate "Table1[[#This Row], [col1]]" ////////
            ptgs = Parse(fpb, tbl + "[[#This Row], [Number]]", 2);
            Assert.AreEqual(1, ptgs.Length);
            // also acceptable: Table!C3
            Assert.AreEqual("Table!C3:C3", ptgs[0].ToFormulaString(), "Table1[[#This Row], [col1]]");

            ////// Case 18: Evaluate "Table1[[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!B2:C7", ptgs[0].ToFormulaString(), "Table1[[col]:[col2]]");
            wb.Close();
        }
Пример #5
0
        private static ValueEval EvaluateIndirect(OperationEvaluationContext ec, String text,
                                                  bool isA1style)
        {
            int tmp = ec.RowIndex;

            tmp = ec.ColumnIndex;
            // Search backwards for '!' because sheet names can contain '!'
            int plingPos = text.LastIndexOf('!');

            String workbookName;
            String sheetName;
            String refText; // whitespace around this Gets Trimmed OK

            if (plingPos < 0)
            {
                workbookName = null;
                sheetName    = null;
                refText      = text;
            }
            else
            {
                String[] parts = ParseWorkbookAndSheetName(text.Substring(0, plingPos));
                if (parts == null)
                {
                    return(ErrorEval.REF_INVALID);
                }
                workbookName = parts[0];
                sheetName    = parts[1];
                refText      = text.Substring(plingPos + 1);
            }

            String refStrPart1;
            String refStrPart2;

            if (Table.IsStructuredReference.Match(refText).Success)
            { // The argument is structured reference
                Area3DPxg areaPtg = null;
                try
                {
                    areaPtg = FormulaParser.ParseStructuredReference(refText, (IFormulaParsingWorkbook)ec.GetWorkbook(), ec.RowIndex);
                }
                catch (FormulaParseException e)
                {
                    return(ErrorEval.REF_INVALID);
                }
                return(ec.GetArea3DEval(areaPtg));
            }
            else
            { // The argumnet is regular reference
                int colonPos = refText.IndexOf(':');
                if (colonPos < 0)
                {
                    refStrPart1 = refText.Trim();
                    refStrPart2 = null;
                }
                else
                {
                    refStrPart1 = refText.Substring(0, colonPos).Trim();
                    refStrPart2 = refText.Substring(colonPos + 1).Trim();
                }
                return(ec.GetDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style));
            }
        }
Пример #6
0
        /**
         * @return <c>true</c> if this Ptg needed to be changed
         */

        private Ptg AdjustPtgDueToRowMove(Ptg ptg, int currentExternSheetIx)
        {
            if (ptg is RefPtg)
            {
                if (currentExternSheetIx != _externSheetIndex)
                {
                    // local refs on other sheets are unaffected
                    return(null);
                }
                RefPtg rptg = (RefPtg)ptg;
                return(RowMoveRefPtg(rptg));
            }
            if (ptg is Ref3DPtg)
            {
                Ref3DPtg rptg = (Ref3DPtg)ptg;
                if (_externSheetIndex != rptg.ExternSheetIndex)
                {
                    // only move 3D refs that refer to the sheet with cells being moved
                    // (currentExternSheetIx is irrelevant)
                    return(null);
                }
                return(RowMoveRefPtg(rptg));
            }

            if (ptg is Ref3DPxg)
            {
                Ref3DPxg rpxg = (Ref3DPxg)ptg;
                if (rpxg.ExternalWorkbookNumber > 0 ||
                    !_sheetName.Equals(rpxg.SheetName))
                {
                    // only move 3D refs that refer to the sheet with cells being moved
                    return(null);
                }
                return(RowMoveRefPtg(rpxg));
            }
            if (ptg is Area2DPtgBase)
            {
                if (currentExternSheetIx != _externSheetIndex)
                {
                    // local refs on other sheets are unaffected
                    return(ptg);
                }
                return(RowMoveAreaPtg((Area2DPtgBase)ptg));
            }
            if (ptg is Area3DPtg)
            {
                Area3DPtg aptg = (Area3DPtg)ptg;
                if (_externSheetIndex != aptg.ExternSheetIndex)
                {
                    // only move 3D refs that refer to the sheet with cells being moved
                    // (currentExternSheetIx is irrelevant)
                    return(null);
                }
                return(RowMoveAreaPtg(aptg));
            }

            if (ptg is Area3DPxg)
            {
                Area3DPxg apxg = (Area3DPxg)ptg;
                if (apxg.ExternalWorkbookNumber > 0 ||
                    !_sheetName.Equals(apxg.SheetName))
                {
                    // only move 3D refs that refer to the sheet with cells being moved
                    return(null);
                }
                return(RowMoveAreaPtg(apxg));
            }
            return(null);
        }