C# CellReference.FormatAsString方法代码示例

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

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

        /**
         * @return never <c>null</c>, never {@link BlankEval}
         */
        private ValueEval EvaluateAny(EvaluationCell srcCell, int sheetIndex,
                    int rowIndex, int columnIndex, EvaluationTracker tracker)
        {

            if (srcCell == null || srcCell.CellType != CellType.FORMULA)
            {
                ValueEval result = GetValueFromNonFormulaCell(srcCell);
                tracker.AcceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
                return result;
            }

            FormulaCellCacheEntry cce = _cache.GetOrCreateFormulaCellEntry(srcCell);
            tracker.AcceptFormulaDependency(cce);
            IEvaluationListener evalListener = _evaluationListener;
            if (cce.GetValue() == null)
            {
                if (!tracker.StartEvaluate(cce))
                {
                    return ErrorEval.CIRCULAR_REF_ERROR;
                }
                OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);

                try
                {
                    ValueEval result;

                    Ptg[] ptgs = _workbook.GetFormulaTokens(srcCell);
                    if (evalListener == null)
                    {
                        result = EvaluateFormula(ec, ptgs);
                    }
                    else
                    {
                        evalListener.OnStartEvaluate(srcCell, cce);
                        result = EvaluateFormula(ec, ptgs);
                        evalListener.OnEndEvaluate(cce, result);
                    }

                    tracker.UpdateCacheResult(result);
                }
                finally
                {
                    tracker.EndEvaluate(cce);
                }
            }
            else
            {
                if (evalListener != null)
                {
                    evalListener.OnCacheHit(sheetIndex, rowIndex, columnIndex, cce.GetValue());
                }
                return cce.GetValue();
            }
            if (IsDebugLogEnabled())
            {
                String sheetName = GetSheetName(sheetIndex);
                CellReference cr = new CellReference(rowIndex, columnIndex);
                LogDebug("Evaluated " + sheetName + "!" + cr.FormatAsString() + " To " + cce.GetValue().ToString());
            }
            return cce.GetValue();
        }

 /**
  * Location of the top left visible cell Location of the top left visible cell in the bottom right
  * pane (when in Left-to-Right mode).
  *
  * @param toprow the top row to show in desktop window pane
  * @param leftcol the left column to show in desktop window pane
  */
 public void ShowInPane(short toprow, short leftcol)
 {
     CellReference cellReference = new CellReference(toprow, leftcol);
     String cellRef = cellReference.FormatAsString();
     GetPane().topLeftCell = (cellRef);
 }

        public ValueEval Evaluate(ValueEval[] args, int srcRowIndex,
                                  int srcColumnIndex)
        {
            if (args.Length < 2 || args.Length > 5)
            {
                return ErrorEval.VALUE_INVALID;
            }
            try
            {
                bool pAbsRow, pAbsCol;

                int row = (int)NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
                int col = (int)NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);

                int refType;
                if (args.Length > 2 && args[2] != MissingArgEval.instance)
                {
                    refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
                }
                else
                {
                    refType = REF_ABSOLUTE; // this is also the default if parameter is not given
                }
                switch (refType)
                {
                    case REF_ABSOLUTE:
                        pAbsRow = true;
                        pAbsCol = true;
                        break;
                    case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
                        pAbsRow = true;
                        pAbsCol = false;
                        break;
                    case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
                        pAbsRow = false;
                        pAbsCol = true;
                        break;
                    case REF_RELATIVE:
                        pAbsRow = false;
                        pAbsCol = false;
                        break;
                    default:
                        throw new EvaluationException(ErrorEval.VALUE_INVALID);
                }

                bool a1;
                if (args.Length > 3)
                {
                    ValueEval ve = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
                    // TODO R1C1 style is not yet supported
                    a1 = ve == MissingArgEval.instance ? true : OperandResolver.CoerceValueToBoolean(ve, false).Value;
                }
                else
                {
                    a1 = true;
                }

                String sheetName;
                if (args.Length == 5)
                {
                    ValueEval ve = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
                    sheetName = ve == MissingArgEval.instance ? null : OperandResolver.CoerceValueToString(ve);
                }
                else
                {
                    sheetName = null;
                }

                CellReference ref1 = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
                StringBuilder sb = new StringBuilder(32);
                if (sheetName != null)
                {
                    SheetNameFormatter.AppendFormat(sb, sheetName);
                    sb.Append('!');
                }
                sb.Append(ref1.FormatAsString());

                return new StringEval(sb.ToString());

            }
            catch (EvaluationException e)
            {
                return e.GetErrorEval();
            }
        }

        /**
         * Shifts rows between startRow and endRow n number of rows.
         * If you use a negative number, it will shift rows up.
         * Code ensures that rows don't wrap around
         *
         * <p>
         * Additionally Shifts merged regions that are completely defined in these
         * rows (ie. merged 2 cells on a row to be Shifted).
         * <p>
         * @param startRow the row to start Shifting
         * @param endRow the row to end Shifting
         * @param n the number of rows to shift
         * @param copyRowHeight whether to copy the row height during the shift
         * @param reSetOriginalRowHeight whether to set the original row's height to the default
         */
        //YK: GetXYZArray() array accessors are deprecated in xmlbeans with JDK 1.5 support
        public void ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool reSetOriginalRowHeight)
        {
            List<int> rowsToRemove = new List<int>();
            foreach (KeyValuePair<int,XSSFRow> rowDict in _rows)
            {
                XSSFRow row = rowDict.Value;
                int rownum = row.RowNum;

                if (RemoveRow(startRow, endRow, n, rownum))
                {
                    // remove row from worksheet.SheetData row array
                    int idx = rowDict.Key+1;
                    //if (n > 0) 
                    //{ 
                    //    idx -= rowsToRemove.Count; 
                    //} 
                    //else 
                    //{ 
                    //    idx += rowsToRemove.Count; 
                    //} 
                    // compensate removed rows
                    worksheet.sheetData.RemoveRow(idx);
                    // remove row from _rows
                    rowsToRemove.Add(rowDict.Key);
                }

                if (!copyRowHeight)
                {
                    row.Height = (short)-1;
                }

                if (sheetComments != null && rownum >= startRow && rownum <= endRow)
                {
                    //TODO shift Note's anchor in the associated /xl/drawing/vmlDrawings#.vml
                    CT_CommentList lst = sheetComments.GetCTComments().commentList;
                    foreach (CT_Comment comment in lst.comment)
                    {
                        CellReference ref1 = new CellReference([email protected]);
                        if (ref1.Row == rownum)
                        {
                            CellReference ref2 = new CellReference(rownum + n, ref1.Col);
                            string originRef = [email protected];
                            [email protected] = ref2.FormatAsString();
                            break;
                        }
                    }
                }
            }
            
            foreach(int rowKey in rowsToRemove)
            {

                _rows.Remove(rowKey);
            }
            if(sheetComments!=null)
                sheetComments.RecreateReference();
            foreach (XSSFRow row in _rows.Values)
            {
                int rownum = row.RowNum;

                if (rownum >= startRow && rownum <= endRow)
                {
                    row.Shift(n);
                }

            }
            XSSFRowShifter rowShifter = new XSSFRowShifter(this);

            int sheetIndex = Workbook.GetSheetIndex(this);
            FormulaShifter Shifter = FormulaShifter.CreateForRowShift(sheetIndex, startRow, endRow, n);

            rowShifter.UpdateNamedRanges(Shifter);
            rowShifter.UpdateFormulas(Shifter);
            rowShifter.ShiftMerged(startRow, endRow, n);
            rowShifter.UpdateConditionalFormatting(Shifter);

            //rebuild the _rows map 
            SortedList<int, XSSFRow> map = new SortedList<int, XSSFRow>();
            foreach (XSSFRow r in _rows.Values)
            {
                map.Add(r.RowNum, r);
            }
            _rows = map;
        }

 private static String formatTestCaseDetails(String sheetName, int rowIndex, ICell c)
 {
     StringBuilder sb = new StringBuilder();
     CellReference cr = new CellReference(sheetName, rowIndex, c.ColumnIndex, false, false);
     sb.Append(cr.FormatAsString());
     sb.Append(" [formula: ").Append(c.CellFormula).Append(" ]");
     return sb.ToString();
 }

        public void Test51710()
        {
            IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("51710.xlsx");

            String[] columns = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N" };
            int rowMax = 500; // bug triggers on row index 59

            ISheet sheet = wb.GetSheetAt(0);


            // go through all formula cells
            for (int rInd = 2; rInd <= rowMax; rInd++)
            {
                IRow row = sheet.GetRow(rInd);

                for (int cInd = 1; cInd <= 12; cInd++)
                {
                    ICell cell = row.GetCell(cInd);
                    String formula = cell.CellFormula;
                    CellReference ref1 = new CellReference(cell);

                    //simulate correct answer
                    String correct = "$A" + (rInd + 1) + "*" + columns[cInd] + "$2";

                    Assert.AreEqual(correct, formula, "Incorrect formula in " + ref1.FormatAsString());
                }

            }
        }

        /* package */
        internal static void ConfirmCell(CellReference cf, String expSheetName, int expRow,
            int expCol, bool expIsRowAbs, bool expIsColAbs, String expText)
        {

            Assert.AreEqual(expSheetName, cf.SheetName);
            Assert.AreEqual(expRow, cf.Row, "row index is wrong");
            Assert.AreEqual(expCol, cf.Col, "col index is wrong");
            Assert.AreEqual(expIsRowAbs, cf.IsRowAbsolute, "isRowAbsolute is wrong");
            Assert.AreEqual(expIsColAbs, cf.IsColAbsolute, "isColAbsolute is wrong");
            Assert.AreEqual(expText, cf.FormatAsString(), "text is wrong");
        }

        /**
         * Opens the sheet we wrote out by BinomialOperator and makes sure the formulas
         * all Match what we expect (x operator y)
         */
        private static void OperationalRefVerify(String operator1, HSSFWorkbook wb)
        {

            NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);
            IRow r = null;
            ICell c = null;

            //get our minimum values
            r = s.GetRow(0);
            c = r.GetCell(1);
            //get our minimum values
            Assert.IsTrue(("A2" + operator1 + "A3").Equals(c.CellFormula), "minval Formula is as expected A2" + operator1 + "A3 != " + c.CellFormula);


            for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
            {
                r = s.GetRow(x);

                for (int y = 1; y < 256 && y > 0; y++)
                {

                    int refx1;
                    int refy1;
                    int refx2;
                    int refy2;
                    if (x + 50 < short.MaxValue)
                    {
                        refx1 = x + 50;
                        refx2 = x + 46;
                    }
                    else
                    {
                        refx1 = x - 4;
                        refx2 = x - 3;
                    }

                    if (y + 50 < 255)
                    {
                        refy1 = y + 50;
                        refy2 = y + 49;
                    }
                    else
                    {
                        refy1 = y - 4;
                        refy2 = y - 3;
                    }

                    c = r.GetCell(y);
                    CellReference cr = new CellReference(refx1, refy1, false, false);
                    String ref1 = cr.FormatAsString();
                    ref1 = cr.FormatAsString();
                    cr = new CellReference(refx2, refy2, false, false);
                    String ref2 = cr.FormatAsString();


                    Assert.IsTrue((
                    ("" + ref1 + operator1 + ref2).Equals(c.CellFormula)
                                                             ), "loop Formula is as expected " + ref1 + operator1 + ref2 + "!=" + c.CellFormula
                    );
                }
            }

            //Test our maximum values
            r = s.GetRow(0);
            c = r.GetCell(0);

            Assert.AreEqual("B1" + operator1 + "IV255", c.CellFormula);
        }

        private static void OperationRefTest(String operator1)
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow r = null;
            ICell c = null;

            //get our minimum values
            r = s.CreateRow(0);
            c = r.CreateCell(1);
            c.CellFormula = ("A2" + operator1 + "A3");

            for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
            {
                r = s.CreateRow(x);

                for (int y = 1; y < 256 && y > 0; y++)
                {

                    String ref1 = null;
                    String ref2 = null;
                    short refx1 = 0;
                    short refy1 = 0;
                    short refx2 = 0;
                    short refy2 = 0;
                    if (x + 50 < short.MaxValue)
                    {
                        refx1 = (short)(x + 50);
                        refx2 = (short)(x + 46);
                    }
                    else
                    {
                        refx1 = (short)(x - 4);
                        refx2 = (short)(x - 3);
                    }

                    if (y + 50 < 255)
                    {
                        refy1 = (short)(y + 50);
                        refy2 = (short)(y + 49);
                    }
                    else
                    {
                        refy1 = (short)(y - 4);
                        refy2 = (short)(y - 3);
                    }

                    c = r.GetCell(y);
                    CellReference cr = new CellReference(refx1, refy1, false, false);
                    ref1 = cr.FormatAsString();
                    cr = new CellReference(refx2, refy2, false, false);
                    ref2 = cr.FormatAsString();

                    c = r.CreateCell(y);
                    c.CellFormula = ("" + ref1 + operator1 + ref2);



                }
            }

            //make sure we do the maximum value of the Int operator
            if (s.LastRowNum < short.MaxValue)
            {
                r = s.GetRow(0);
                c = r.CreateCell(0);
                c.CellFormula = ("" + "B1" + operator1 + "IV255");
            }

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            OperationalRefVerify(operator1, wb);
        }