public void TestSortTopLeftToBottomRight() { AreaPtg ptg = new AreaPtg("A$1:$B5"); Assert.AreEqual("A$1:$B5", ptg.ToFormulaString()); ptg.FirstColumn = (3); Assert.AreEqual("D$1:$B5", ptg.ToFormulaString(), "Area Ptg should not implicitly re-sort itself (except during construction)"); ptg.SortTopLeftToBottomRight(); Assert.AreEqual("$B$1:D5", ptg.ToFormulaString(), "Area Ptg should restore itself to top-left to lower-right order when explicitly asked"); }
public void Test44410() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls"); NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0); HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb); // =index(C:C,2,1) -> 2 IRow rowIDX = sheet.GetRow(3); // =sum(C:C) -> 6 IRow rowSUM = sheet.GetRow(4); // =sum(C:D) -> 66 IRow rowSUM2D = sheet.GetRow(5); // Test the sum ICell cellSUM = rowSUM.GetCell(0); FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord; Ptg[] ops = frec.FormulaRecord.ParsedExpression; Assert.AreEqual(2, ops.Length); Assert.AreEqual(typeof(AreaPtg), ops[0].GetType()); Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType()); // Actually stored as C1 to C65536 // (last row is -1 === 65535) AreaPtg ptg = (AreaPtg)ops[0]; Assert.AreEqual(2, ptg.FirstColumn); Assert.AreEqual(2, ptg.LastColumn); Assert.AreEqual(0, ptg.FirstRow); Assert.AreEqual(65535, ptg.LastRow); Assert.AreEqual("C:C", ptg.ToFormulaString()); // Will show as C:C, but won't know how many // rows it covers as we don't have the sheet // to hand when turning the Ptgs into a string Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula); // But the evaluator knows the sheet, so it // can do it properly Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0); // Test the index // Again, the formula string will be right but // lacking row count, Evaluated will be right ICell cellIDX = rowIDX.GetCell(0); Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula); Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0); // Across two colums ICell cellSUM2D = rowSUM2D.GetCell(0); Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula); Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0); wb.Close(); }
public void TestAbsoluteRelativeRefs() { AreaPtg sca1 = new AreaPtg(4, 5, 6, 7, true, false, true, false); AreaPtg sca2 = new AreaPtg(4, 5, 6, 7, false, true, false, true); AreaPtg sca3 = new AreaPtg(5, 5, 7, 7, true, false, true, false); AreaPtg sca4 = new AreaPtg(5, 5, 7, 7, false, true, false, true); Assert.AreEqual("G5:$H$6", sca1.ToFormulaString(), "first rel., last abs."); Assert.AreEqual("$G$5:H6", sca2.ToFormulaString(), "first abs., last rel."); Assert.AreEqual("H6:$H$6", sca3.ToFormulaString(), "first rel., last abs."); Assert.AreEqual("$H$6:H6", sca4.ToFormulaString(), "first abs., last rel."); AreaPtg cla1 = CloneArea(sca1); AreaPtg cla2 = CloneArea(sca2); AreaPtg cla3 = CloneArea(sca3); AreaPtg cla4 = CloneArea(sca4); Assert.AreEqual("G5:$H$6", cla1.ToFormulaString(), "first rel., last abs."); Assert.AreEqual("$G$5:H6", cla2.ToFormulaString(), "first abs., last rel."); Assert.AreEqual("H6:$H$6", cla3.ToFormulaString(), "first rel., last abs."); Assert.AreEqual("$H$6:H6", cla4.ToFormulaString(), "first abs., last rel."); }