public void TestDiscontinousReference() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls"); HSSFWorkbook wb = new HSSFWorkbook(is1); NPOI.HSSF.Model.Workbook workbook = wb.Workbook; HSSFEvaluationWorkbook eb = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual(1, wb.NumberOfNames); String sheetName = "Tabelle1"; String rawRefA = "$C$10:$C$14"; String rawRefB = "$C$16:$C$18"; String refA = sheetName + "!" + rawRefA; String refB = sheetName + "!" + rawRefB; String ref1 = refA + "," + refB; // Check the low level record NameRecord nr = workbook.GetNameRecord(0); Assert.IsNotNull(nr); Assert.AreEqual("test", nr.NameText); Ptg[] def = nr.NameDefinition; Assert.AreEqual(4, def.Length); MemFuncPtg ptgA = (MemFuncPtg)def[0]; Area3DPtg ptgB = (Area3DPtg)def[1]; Area3DPtg ptgC = (Area3DPtg)def[2]; UnionPtg ptgD = (UnionPtg)def[3]; Assert.AreEqual("", ptgA.ToFormulaString()); Assert.AreEqual(refA, ptgB.ToFormulaString(eb)); Assert.AreEqual(refB, ptgC.ToFormulaString(eb)); Assert.AreEqual(",", ptgD.ToFormulaString()); Assert.AreEqual(ref1, NPOI.HSSF.Model.HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // Check the high level definition int idx = wb.GetNameIndex("test"); Assert.AreEqual(0, idx); NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(idx); // Should have 2 references Assert.AreEqual(ref1, aNamedCell.RefersToFormula); // Check the parsing of the reference into cells Assert.IsFalse(AreaReference.IsContiguous(aNamedCell.RefersToFormula)); AreaReference[] arefs = AreaReference.GenerateContiguous(aNamedCell.RefersToFormula); Assert.AreEqual(2, arefs.Length); Assert.AreEqual(refA, arefs[0].FormatAsString()); Assert.AreEqual(refB, arefs[1].FormatAsString()); for (int i = 0; i < arefs.Length; i++) { AreaReference ar = arefs[i]; ConfirmResolveCellRef(wb, ar.FirstCell); ConfirmResolveCellRef(wb, ar.LastCell); } }
public void TestToFormulaString() { Area3DPtg target = new Area3DPtg("A1:B1", (short)0); string sheetName = "my sheet"; HSSFWorkbook wb = CreateWorkbookWithSheet(sheetName); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.Create(wb); Assert.AreEqual("'my sheet'!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "Sheet1"); Assert.AreEqual("Sheet1!A1:B1", target.ToFormulaString(book)); wb.SetSheetName(0, "C64"); Assert.AreEqual("'C64'!A1:B1", target.ToFormulaString(book)); }