public void TestMultipleNamedWrite() { HSSFWorkbook wb = new HSSFWorkbook(); wb.CreateSheet("TestSheet1"); String sheetName = wb.GetSheetName(0); Assert.AreEqual("TestSheet1", sheetName); //Creating new Named Range NPOI.SS.UserModel.Name newNamedRange = wb.CreateName(); newNamedRange.NameName = ("RangeTest"); newNamedRange.RefersToFormula = (sheetName + "!$D$4:$E$8"); //Creating another new Named Range NPOI.SS.UserModel.Name newNamedRange2 = wb.CreateName(); newNamedRange2.NameName = ("AnotherTest"); newNamedRange2.RefersToFormula = (sheetName + "!$F$1:$G$6"); NPOI.SS.UserModel.Name namedRange1 = wb.GetNameAt(0); String referece = namedRange1.RefersToFormula; wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name nm = wb.GetNameAt(wb.GetNameIndex("RangeTest")); Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName); Assert.IsTrue((wb.GetSheetName(0) + "!$D$4:$E$8").Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula); nm = wb.GetNameAt(wb.GetNameIndex("AnotherTest")); Assert.IsTrue("AnotherTest".Equals(nm.NameName), "Name is " + nm.NameName); Assert.IsTrue(newNamedRange2.RefersToFormula.Equals(nm.RefersToFormula), "Reference is " + nm.RefersToFormula); }
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 TestNamedRange() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls"); //Creating new Named Range IName newNamedRange = wb.CreateName(); //Getting Sheet Name for the reference String sheetName = wb.GetSheetName(0); //Setting its name newNamedRange.NameName = "RangeTest"; //Setting its reference newNamedRange.RefersToFormula = sheetName + "!$D$4:$E$8"; //Getting NAmed Range IName namedRange1 = wb.GetNameAt(0); //Getting it sheet name sheetName = namedRange1.SheetName; // sanity check SanityChecker c = new SanityChecker(); c.CheckHSSFWorkbook(wb); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); IName nm = wb.GetNameAt(wb.GetNameIndex("RangeTest")); Assert.IsTrue("RangeTest".Equals(nm.NameName), "Name is " + nm.NameName); Assert.AreEqual(wb.GetSheetName(0) + "!$D$4:$E$8", nm.RefersToFormula); }
public void TestNamedCell_2() { // setup for this Testcase String sname = "TestSheet", cname = "TestName", cvalue = "TestVal"; HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sname); sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cvalue)); // Create named range for a single cell using cellreference NPOI.SS.UserModel.Name namedCell = wb.CreateName(); namedCell.NameName = (cname); String reference = sname + "!A1"; namedCell.RefersToFormula = (reference); // retrieve the newly Created named range int namedCellIdx = wb.GetNameIndex(cname); NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx); Assert.IsNotNull(aNamedCell); // retrieve the cell at the named range and Test its contents CellReference cref = new CellReference(aNamedCell.RefersToFormula); Assert.IsNotNull(cref); NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName); Row r = sheet.GetRow(cref.Row); Cell c = r.GetCell(cref.Col); String contents = c.RichStringCellValue.String; Assert.AreEqual(contents, cvalue, "Contents of cell retrieved by its named reference"); }
public void TestNames() { HSSFWorkbook wb = new HSSFWorkbook(); try { wb.GetNameAt(0); Assert.Fail("Fails without any defined names"); } catch (ArgumentException e) { //Assert.IsTrue(e.Message.Contains("no defined names"), e.Message); } HSSFName name = (HSSFName)wb.CreateName(); Assert.IsNotNull(name); Assert.IsNull(wb.GetName("somename")); name.NameName = ("myname"); Assert.IsNotNull(wb.GetName("myname")); Assert.AreEqual(0, wb.GetNameIndex(name)); Assert.AreEqual(0, wb.GetNameIndex("myname")); try { wb.GetNameAt(5); Assert.Fail("Fails without any defined names"); } catch (ArgumentException e) { //Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message); } try { wb.GetNameAt(-3); Assert.Fail("Fails without any defined names"); } catch (ArgumentException e) { //Assert.IsTrue(e.Message.Contains("outside the allowable range"), e.Message); } }
public void TestMultiNamedRange() { // Create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // Create a worksheet 'sheet1' in the new workbook wb.CreateSheet(); wb.SetSheetName(0, "sheet1"); // Create another worksheet 'sheet2' in the new workbook wb.CreateSheet(); wb.SetSheetName(1, "sheet2"); // Create a new named range for worksheet 'sheet1' NPOI.SS.UserModel.Name namedRange1 = wb.CreateName(); // Set the name for the named range for worksheet 'sheet1' namedRange1.NameName = ("RangeTest1"); // Set the reference for the named range for worksheet 'sheet1' namedRange1.RefersToFormula = ("sheet1" + "!$A$1:$L$41"); // Create a new named range for worksheet 'sheet2' NPOI.SS.UserModel.Name namedRange2 = wb.CreateName(); // Set the name for the named range for worksheet 'sheet2' namedRange2.NameName = ("RangeTest2"); // Set the reference for the named range for worksheet 'sheet2' namedRange2.RefersToFormula = ("sheet2" + "!$A$1:$O$21"); // Write the workbook to a file // Read the Excel file and verify its content wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); NPOI.SS.UserModel.Name nm1 = wb.GetNameAt(wb.GetNameIndex("RangeTest1")); Assert.IsTrue("RangeTest1".Equals(nm1.NameName), "Name is " + nm1.NameName); Assert.IsTrue((wb.GetSheetName(0) + "!$A$1:$L$41").Equals(nm1.RefersToFormula), "Reference is " + nm1.RefersToFormula); NPOI.SS.UserModel.Name nm2 = wb.GetNameAt(wb.GetNameIndex("RangeTest2")); Assert.IsTrue("RangeTest2".Equals(nm2.NameName), "Name is " + nm2.NameName); Assert.IsTrue((wb.GetSheetName(1) + "!$A$1:$O$21").Equals(nm2.RefersToFormula), "Reference is " + nm2.RefersToFormula); }
private NPOI.SS.UserModel.ICell GetCell(string rangeName) { var range = Book.GetNameAt(Book.GetNameIndex(ExcelNameEscape(rangeName))); var areaRef = new AreaReference(range.RefersToFormula); CellReference[] crefs = areaRef.GetAllReferencedCells(); CellReference cref = crefs[0]; return(Book.GetSheet(cref.SheetName) .GetRow(cref.Row) .GetCell(cref.Col)); }
public void TestNamedRead() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("namedinput.xls"); //Get index of the named range with the name = "NamedRangeName" , which was defined in input.xls as A1:D10 int NamedRangeIndex = wb.GetNameIndex("NamedRangeName"); //Getting NAmed Range IName namedRange1 = wb.GetNameAt(NamedRangeIndex); String sheetName = wb.GetSheetName(0); //Getting its reference String reference = namedRange1.RefersToFormula; Assert.AreEqual(sheetName + "!$A$1:$D$10", reference); IName namedRange2 = wb.GetNameAt(1); Assert.AreEqual(sheetName + "!$D$17:$G$27", namedRange2.RefersToFormula); Assert.AreEqual("SecondNamedRange", namedRange2.NameName); }
public void TestNamedCell_1() { // setup for this Testcase String sheetName = "Test Named Cell"; String cellName = "A name for a named cell"; String cellValue = "TEST Value"; HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sheetName); sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cellValue)); // Create named range for a single cell using areareference NPOI.SS.UserModel.Name namedCell = wb.CreateName(); namedCell.NameName = (cellName); String reference = "'" + sheetName + "'" + "!A1:A1"; namedCell.RefersToFormula = (reference); // retrieve the newly Created named range int namedCellIdx = wb.GetNameIndex(cellName); NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx); Assert.IsNotNull(aNamedCell); // retrieve the cell at the named range and Test its contents AreaReference aref = new AreaReference(aNamedCell.RefersToFormula); Assert.IsTrue(aref.IsSingleCell, "Should be exactly 1 cell in the named cell :'" + cellName + "'"); CellReference cref = aref.FirstCell; Assert.IsNotNull(cref); NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName); Assert.IsNotNull(s); Row r = sheet.GetRow(cref.Row); Cell c = r.GetCell(cref.Col); String contents = c.RichStringCellValue.String; Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference"); }