C# Util.CellReference类代码示例

        public void TestEvaluateColumnGreaterThan255()
        {
            XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("50096.xlsx");
            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            /**
             *  The first row simply Contains the numbers 1 - 300.
             *  The second row simply refers to the cell value above in the first row by a simple formula.
             */
            for (int i = 245; i < 265; i++)
            {
                ICell cell_noformula = wb.GetSheetAt(0).GetRow(0).GetCell(i);
                ICell cell_formula = wb.GetSheetAt(0).GetRow(1).GetCell(i);

                CellReference ref_noformula = new CellReference(cell_noformula.RowIndex, cell_noformula.ColumnIndex);
                CellReference ref_formula = new CellReference(cell_noformula.RowIndex, cell_noformula.ColumnIndex);
                String fmla = cell_formula.CellFormula;
                // assure that the formula refers to the cell above.
                // the check below is 'deep' and involves conversion of the shared formula:
                // in the sample file a shared formula in GN1 is spanned in the range GN2:IY2,
                Assert.AreEqual(ref_noformula.FormatAsString(), fmla);

                CellValue cv_noformula = Evaluator.Evaluate(cell_noformula);
                CellValue cv_formula = Evaluator.Evaluate(cell_formula);
                Assert.AreEqual(cv_noformula.NumberValue, cv_formula.NumberValue, "Wrong Evaluation result in " + ref_formula.FormatAsString());
            }
        }

 public void TestReadCalcSheet()
 {
     try
     {
         HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
         Assert.AreEqual("Sheet1!$A$2", wb.GetName("QUANT").RefersToFormula);
         Assert.AreEqual("Sheet1!$B$2", wb.GetName("PART").RefersToFormula);
         Assert.AreEqual("x123", wb.GetSheet("Sheet1").GetRow(1).GetCell(1).StringCellValue);
         Assert.AreEqual("Sheet1!$C$2", wb.GetName("UNITCOST").RefersToFormula);
         CellReference cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula);
         ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
         Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);
         Assert.AreEqual("Sheet1!$D$2", wb.GetName("COST").RefersToFormula);
         cellRef = new CellReference(wb.GetName("COST").RefersToFormula);
         cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
         Assert.AreEqual("UNITCOST*Quant", cell.CellFormula);
         Assert.AreEqual("Sheet1!$E$2", wb.GetName("TOTALCOST").RefersToFormula);
         cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula);
         cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
         Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);
     }
     catch (Exception e)
     {
         Assert.Fail(e.Message);
     }
 }

        /**
     * @return the text format of this range using specified sheet name.
     */
        public String FormatAsString(String sheetName, bool useAbsoluteAddress)
        {
            StringBuilder sb = new StringBuilder();
            if (sheetName != null)
            {
                sb.Append(SheetNameFormatter.Format(sheetName));
                sb.Append("!");
            }
            CellReference cellRefFrom = new CellReference(FirstRow, FirstColumn,
                    useAbsoluteAddress, useAbsoluteAddress);
            CellReference cellRefTo = new CellReference(LastRow, LastColumn,
                    useAbsoluteAddress, useAbsoluteAddress);
            sb.Append(cellRefFrom.FormatAsString());

            //for a single-cell reference return A1 instead of A1:A1
            //for full-column ranges or full-row ranges return A:A instead of A,
            //and 1:1 instead of 1
            if (!cellRefFrom.Equals(cellRefTo)
                || IsFullColumnRange || IsFullRowRange)
            {
                sb.Append(':');
                sb.Append(cellRefTo.FormatAsString());
            }
            return sb.ToString();
        }

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

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

        private void buildWorkbook(IWorkbook wb)
        {
            ISheet sh = wb.CreateSheet();
            IRow row1 = sh.CreateRow(0);
            IRow row2 = sh.CreateRow(1);
            row3 = sh.CreateRow(2);

            row1.CreateCell(0, CellType.Numeric);
            row1.CreateCell(1, CellType.Numeric);

            row2.CreateCell(0, CellType.Numeric);
            row2.CreateCell(1, CellType.Numeric);

            row3.CreateCell(0);
            row3.CreateCell(1);

            CellReference a1 = new CellReference("A1");
            CellReference a2 = new CellReference("A2");
            CellReference b1 = new CellReference("B1");
            CellReference b2 = new CellReference("B2");

            sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35);
            sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0);
            sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2");
            sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()");

            Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
        }

 protected RefPtgBase(CellReference c)
 {
     Row = (c.Row);
     Column = (c.Col);
     IsColRelative = (!c.IsColAbsolute);
     IsRowRelative = (!c.IsRowAbsolute);
 }

 public Ref3DPtg(String cellref, int externIdx)
 {
     CellReference c = new CellReference(cellref);
     Row=c.Row;
     Column=c.Col;
     IsColRelative=!c.IsColAbsolute;
     IsRowRelative=!c.IsRowAbsolute;
     ExternSheetIndex=externIdx;
 }

 public String FormatAsString()
 {
     StringBuilder sb = new StringBuilder();
     CellReference cellRefFrom = new CellReference(FirstRow, FirstColumn);
     CellReference cellRefTo = new CellReference(LastRow, LastColumn);
     sb.Append(cellRefFrom.FormatAsString());
     sb.Append(':');
     sb.Append(cellRefTo.FormatAsString());
     return sb.ToString();
 }

        /**
         * Create an area ref from a string representation.  Sheet names containing special Chars should be
         * delimited and escaped as per normal syntax rules for formulas.<br/> 
         * The area reference must be contiguous (i.e. represent a single rectangle, not a Union of rectangles)
         */
        public AreaReference(String reference)
        {
            if (!IsContiguous(reference))
            {
                throw new ArgumentException(
                        "References passed to the AreaReference must be contiguous, " +
                        "use generateContiguous(ref) if you have non-contiguous references");
            }

            String[] parts = SeparateAreaRefs(reference);

            String part0 = parts[0];
            if (parts.Length == 1)
            {
                // TODO - probably shouldn't initialize area ref when text is really a cell ref
                // Need to fix some named range stuff to get rid of this
                _firstCell = new CellReference(part0);

                _lastCell = _firstCell;
                _isSingleCell = true;
                return;
            }
            if (parts.Length != 2)
            {
                throw new ArgumentException("Bad area ref '" + reference + "'");
            }
            String part1 = parts[1];
            if (IsPlainColumn(part0))
            {
                if (!IsPlainColumn(part1))
                {
                    throw new Exception("Bad area ref '" + reference + "'");
                }
                // Special handling for whole-column references
                // Represented internally as x$1 to x$65536
                //  which is the maximum range of rows

                bool firstIsAbs = CellReference.IsPartAbsolute(part0);
                bool lastIsAbs = CellReference.IsPartAbsolute(part1);

                int col0 = CellReference.ConvertColStringToIndex(part0);
                int col1 = CellReference.ConvertColStringToIndex(part1);

                _firstCell = new CellReference(0, col0, true, firstIsAbs);
                _lastCell = new CellReference(0xFFFF, col1, true, lastIsAbs);
                _isSingleCell = false;
                // TODO - whole row refs
            }
            else
            {
                _firstCell = new CellReference(part0);
                _lastCell = new CellReference(part1);
                _isSingleCell = part0.Equals(part1);
            }
        }

 public override String ToString()
 {
     CellReference cr = new CellReference(Row, Column);
     StringBuilder sb = new StringBuilder();
     sb.Append(GetType().Name).Append("[");
     sb.Append(_evaluator.SheetName);
     sb.Append('!');
     sb.Append(cr.FormatAsString());
     sb.Append("]");
     return sb.ToString();
 }

 public override String ToString()
 {
     CellReference cr = new CellReference(Row, Column, !IsRowRelative, !IsColRelative);
     StringBuilder sb = new StringBuilder();
     sb.Append(GetType().Name);
     sb.Append(" [");
     sb.Append("sheetIx=").Append(ExternSheetIndex);
     sb.Append(" ! ");
     sb.Append(cr.FormatAsString());
     sb.Append("]");
     return sb.ToString();
 }

        public void TestSpecialSheetNames()
        {
            CellReference cf;
            cf = new CellReference("'profit + loss'!A1");
            ConfirmCell(cf, "profit + loss", 0, 0, false, false, "'profit + loss'!A1");

            cf = new CellReference("'O''Brien''s Sales'!A1");
            ConfirmCell(cf, "O'Brien's Sales", 0, 0, false, false, "'O''Brien''s Sales'!A1");

            cf = new CellReference("'Amazing!'!A1");
            ConfirmCell(cf, "Amazing!", 0, 0, false, false, "'Amazing!'!A1");
        }

 public override String ToString()
 {
     CellReference crA = new CellReference(FirstRow, FirstColumn);
     CellReference crB = new CellReference(LastRow, LastColumn);
     StringBuilder sb = new StringBuilder();
     sb.Append(GetType().Name).Append("[");
     sb.Append(_evaluator.SheetName);
     sb.Append('!');
     sb.Append(crA.FormatAsString());
     sb.Append(':');
     sb.Append(crB.FormatAsString());
     sb.Append("]");
     return sb.ToString();
 }

 public Ref3DPxg(int externalWorkbookNumber, SheetIdentifier sheetName, CellReference c)
     : base(c)
 {
     this.externalWorkbookNumber = externalWorkbookNumber;
     this.firstSheetName = sheetName.SheetId.Name;
     if (sheetName is SheetRangeIdentifier)
     {
         this.lastSheetName = ((SheetRangeIdentifier)sheetName).LastSheetIdentifier.Name;
     }
     else
     {
         this.lastSheetName = null;
     }
 }