C# CellRangeAddress.FormatAsString方法代码示例

        public void Test_47701()
        {
            byte[] data = HexRead.ReadFromString(
                            "15, 00, 12, 00, 12, 00, 02, 00, 11, 20, " +
                            "00, 00, 00, 00, 80, 3D, 03, 05, 00, 00, " +
                            "00, 00, 0C, 00, 14, 00, 00, 00, 00, 00, " +
                            "00, 00, 00, 00, 00, 00, 01, 00, 0A, 00, " +
                            "00, 00, 10, 00, 01, 00, 13, 00, EE, 1F, " +
                            "10, 00, 09, 00, 40, 9F, 74, 01, 25, 09, " +
                            "00, 0C, 00, 07, 00, 07, 00, 07, 04, 00, " +
                            "00, 00, 08, 00, 00, 00");
            RecordInputStream in1 = TestcaseRecordInputStream.Create(ObjRecord.sid, data);
            // check read OK
            ObjRecord record = new ObjRecord(in1);
            Assert.AreEqual(3, record.SubRecords.Count);
            SubRecord sr = record.SubRecords[(2)];
            Assert.IsTrue(sr is LbsDataSubRecord);
            LbsDataSubRecord lbs = (LbsDataSubRecord)sr;
            Assert.AreEqual(4, lbs.NumberOfItems);

            Assert.IsTrue(lbs.Formula is AreaPtg);
            AreaPtg ptg = (AreaPtg)lbs.Formula;
            CellRangeAddress range = new CellRangeAddress(
                    ptg.FirstRow, ptg.LastRow, ptg.FirstColumn, ptg.LastColumn);
            Assert.AreEqual("H10:H13", range.FormatAsString());

            // check that it re-Serializes to the same data
            byte[] ser = record.Serialize();
            TestcaseRecordInputStream.ConfirmRecordEncoding(ObjRecord.sid, data, ser);
        }

        public void TestHSSFSetArrayFormula_SingleCell()
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");

            CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
            ICell[] cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells;
            Assert.AreEqual(1, cells.Length);

            // sheet.SetArrayFormula Creates rows and cells for the designated range
            Assert.IsNotNull(sheet.GetRow(2));
            ICell cell = sheet.GetRow(2).GetCell(2);
            Assert.IsNotNull(cell);

            Assert.IsTrue(cell.IsPartOfArrayFormulaGroup);
            //retrieve the range and check it is the same
            Assert.AreEqual(range.FormatAsString(), cell.GetArrayFormulaRange().FormatAsString());

            FormulaRecordAggregate agg = (FormulaRecordAggregate)(((HSSFCell)cell).CellValueRecord);
            Assert.AreEqual(range.FormatAsString(), agg.GetArrayFormulaRange().FormatAsString());
            Assert.IsTrue(agg.IsPartOfArrayFormula);
        }

        public void TestShiftWithMergedRegions()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();
            ISheet sheet = wb.CreateSheet();
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(1.1);
            row.CreateCell(1).SetCellValue(2.2);
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
            Assert.AreEqual( region.FormatAsString(),"A1:C1");

            sheet.AddMergedRegion(region);

            sheet.ShiftRows(0, 1, 2);
            region = sheet.GetMergedRegion(0);
            Assert.AreEqual( region.FormatAsString(),"A3:C3");
        }

 internal void SetCellArrayFormula(String formula, CellRangeAddress range)
 {
     SetFormula(formula, FormulaType.Array);
     CT_CellFormula cellFormula = _cell.f;
     cellFormula.t = (ST_CellFormulaType.array);
     [email protected] = (range.FormatAsString());
 }

        public IAutoFilter SetAutoFilter(CellRangeAddress range)
        {
            CT_AutoFilter af = worksheet.autoFilter;
            if (af == null) af = worksheet.AddNewAutoFilter();

            CellRangeAddress norm = new CellRangeAddress(range.FirstRow, range.LastRow,
                    range.FirstColumn, range.LastColumn);
            String ref1 = norm.FormatAsString();
            [email protected] = (ref1);

            XSSFWorkbook wb = (XSSFWorkbook)Workbook;
            int sheetIndex = Workbook.GetSheetIndex(this);
            XSSFName name = wb.GetBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
            if (name == null)
            {
                name = wb.CreateBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
                name.GetCTName().hidden = true;
                CellReference r1 = new CellReference(SheetName, range.FirstRow, range.FirstColumn, true, true);
                CellReference r2 = new CellReference(null, range.LastRow, range.LastColumn, true, true);
                String fmla = r1.FormatAsString() + ":" + r2.FormatAsString();
                name.RefersToFormula = fmla;
            }

            return new XSSFAutoFilter(this);
        }

        private void ValidateArrayFormulas(CellRangeAddress region)
        {
            int firstRow = region.FirstRow;
            int firstColumn = region.FirstColumn;
            int lastRow = region.LastRow;
            int lastColumn = region.LastColumn;
            for (int rowIn = firstRow; rowIn <= lastRow; rowIn++)
            {
                for (int colIn = firstColumn; colIn <= lastColumn; colIn++)
                {
                    IRow row = GetRow(rowIn);
                    if (row == null) continue;

                    ICell cell = row.GetCell(colIn);
                    if (cell == null) continue;

                    if (cell.IsPartOfArrayFormulaGroup)
                    {
                        CellRangeAddress arrayRange = cell.ArrayFormulaRange;
                        if (arrayRange.NumberOfCells > 1 &&
                                (arrayRange.IsInRange(region.FirstRow, region.FirstColumn) ||
                                  arrayRange.IsInRange(region.FirstRow, region.FirstColumn)))
                        {
                            String msg = "The range " + region.FormatAsString() + " intersects with a multi-cell array formula. " +
                                    "You cannot merge cells of an array.";
                            throw new InvalidOperationException(msg);
                        }
                    }
                }
            }

        }

        /**
         * Adds a merged region of cells (hence those cells form one).
         *
         * @param region (rowfrom/colfrom-rowto/colto) to merge
         * @return index of this region
         */
        public int AddMergedRegion(CellRangeAddress region)
        {
            region.Validate(SpreadsheetVersion.EXCEL2007);

            // throw InvalidOperationException if the argument CellRangeAddress intersects with
            // a multi-cell array formula defined in this sheet
            ValidateArrayFormulas(region);


            CT_MergeCells ctMergeCells = worksheet.IsSetMergeCells() ? worksheet.mergeCells : worksheet.AddNewMergeCells();
            CT_MergeCell ctMergeCell = ctMergeCells.AddNewMergeCell();
            [email protected] = (region.FormatAsString());
            return ctMergeCells.sizeOfMergeCellArray();
        }

        public void TestFormatAsString()
        {
            CellRangeAddress ref1 = new CellRangeAddress(1, 2, 3, 4);

            Assert.AreEqual("D2:E3", ref1.FormatAsString());
            Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString());

            Assert.AreEqual("sheet1!$D$2:$E$3", ref1.FormatAsString("sheet1", true));
            Assert.AreEqual("sheet1!$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
            Assert.AreEqual("sheet1!$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));

            Assert.AreEqual("sheet1!D2:E3", ref1.FormatAsString("sheet1", false));
            Assert.AreEqual("sheet1!D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
            Assert.AreEqual("sheet1!D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));

            Assert.AreEqual("D2:E3", ref1.FormatAsString(null, false));
            Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, false));
            Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, false)).FormatAsString(null, false));

            Assert.AreEqual("$D$2:$E$3", ref1.FormatAsString(null, true));
            Assert.AreEqual("$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
            Assert.AreEqual("$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));

            ref1 = new CellRangeAddress(-1, -1, 3, 4);
            Assert.AreEqual("D:E", ref1.FormatAsString());
            Assert.AreEqual("sheet1!$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
            Assert.AreEqual("sheet1!$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));
            Assert.AreEqual("$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
            Assert.AreEqual("$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));
            Assert.AreEqual("sheet1!D:E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
            Assert.AreEqual("sheet1!D:E", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));

            ref1 = new CellRangeAddress(1, 2, -1, -1);
            Assert.AreEqual("2:3", ref1.FormatAsString());
            Assert.AreEqual("sheet1!$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
            Assert.AreEqual("sheet1!$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));
            Assert.AreEqual("$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
            Assert.AreEqual("$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));
            Assert.AreEqual("sheet1!2:3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
            Assert.AreEqual("sheet1!2:3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));

            ref1 = new CellRangeAddress(1, 1, 2, 2);
            Assert.AreEqual("C2", ref1.FormatAsString());
            Assert.AreEqual("sheet1!$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
            Assert.AreEqual("sheet1!$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));
            Assert.AreEqual("$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
            Assert.AreEqual("$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));
            Assert.AreEqual("sheet1!C2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
            Assert.AreEqual("sheet1!C2", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));

            // is this a valid Address?
            ref1 = new CellRangeAddress(-1, -1, -1, -1);
            Assert.AreEqual(":", ref1.FormatAsString());
        }

        public void TestSetArrayFormula_SingleCell()
        {
            ICell[] cells;

            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet sheet = workbook.CreateSheet();
            ICell cell = sheet.CreateRow(0).CreateCell(0);
            Assert.IsFalse(cell.IsPartOfArrayFormulaGroup);
            try
            {
                cell.GetArrayFormulaRange(/*getter*/);
                Assert.Fail("expected exception");
            }
            catch (InvalidOperationException e)
            {
                Assert.AreEqual("Cell A1 is not part of an array formula.", e.Message);
            }

            // row 3 does not yet exist
            Assert.IsNull(sheet.GetRow(2));
            CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
            cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells;
            Assert.AreEqual(1, cells.Length);
            // sheet.SetArrayFormula Creates rows and cells for the designated range
            Assert.IsNotNull(sheet.GetRow(2));
            cell = sheet.GetRow(2).GetCell(2);
            Assert.IsNotNull(cell);

            Assert.IsTrue(cell.IsPartOfArrayFormulaGroup);
            //retrieve the range and check it is the same
            Assert.AreEqual(range.FormatAsString(), cell.GetArrayFormulaRange().FormatAsString());
            //check the formula
            Assert.AreEqual("SUM(C11:C12*D11:D12)", cell.CellFormula);
        }

        public void TestRemoveArrayFormula()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet sheet = workbook.CreateSheet();

            CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2);
            Assert.AreEqual("C4:C6", range.FormatAsString());
            ICellRange<ICell> cr = sheet.SetArrayFormula("SUM(A1:A3*B1:B3)", range);
            Assert.AreEqual(3, cr.Size);

            // remove the formula cells in C4:C6
            ICellRange<ICell> dcells = sheet.RemoveArrayFormula(cr.TopLeftCell);
            // RemoveArrayFormula should return the same cells as SetArrayFormula
            Assert.IsTrue(Arrays.Equals(cr.FlattenedCells, dcells.FlattenedCells));

            foreach (ICell acell in cr)
            {
                Assert.IsFalse(acell.IsPartOfArrayFormulaGroup);
                Assert.AreEqual(CellType.BLANK, acell.CellType);
            }

            // cells C4:C6 are not included in array formula,
            // invocation of sheet.RemoveArrayFormula on any of them throws ArgumentException
            foreach (ICell acell in cr)
            {
                try
                {
                    sheet.RemoveArrayFormula(acell);
                    Assert.Fail("expected exception");
                }
                catch (ArgumentException e)
                {
                    String ref1 = new CellReference(acell).FormatAsString();
                    Assert.AreEqual("Cell " + ref1 + " is not part of an array formula.", e.Message);
                }
            }
        }