public void TestRepeatingRowsAndColumsNames() { // First Test that Setting RR&C for same sheet more than once only Creates a // single Print_Titles built-in record HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("FirstSheet"); // set repeating rows and columns twice for the first sheet for (int i = 0; i < 2; i++) { wb.SetRepeatingRowsAndColumns(0, 0, 0, 0, 3 - 1); sheet.CreateFreezePane(0, 3); } Assert.AreEqual(1, wb.NumberOfNames); IName nr1 = wb.GetNameAt(0); Assert.AreEqual("Print_Titles", nr1.NameName); if (false) { #if !HIDE_UNREACHABLE_CODE // TODO - full column references not rendering properly, absolute markers not present either Assert.AreEqual("FirstSheet!$A:$A,FirstSheet!$1:$3", nr1.RefersToFormula); #endif } else { Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula); } // Save and re-open HSSFWorkbook nwb = HSSFTestDataSamples.WriteOutAndReadBack(wb); Assert.AreEqual(1, nwb.NumberOfNames); nr1 = nwb.GetNameAt(0); Assert.AreEqual("Print_Titles", nr1.NameName); Assert.AreEqual("FirstSheet!A:A,FirstSheet!$A$1:$IV$3", nr1.RefersToFormula); // check that Setting RR&C on a second sheet causes a new Print_Titles built-in // name to be Created sheet = (HSSFSheet)nwb.CreateSheet("SecondSheet"); nwb.SetRepeatingRowsAndColumns(1, 1, 2, 0, 0); Assert.AreEqual(2, nwb.NumberOfNames); IName nr2 = nwb.GetNameAt(1); Assert.AreEqual("Print_Titles", nr2.NameName); Assert.AreEqual("SecondSheet!B:C,SecondSheet!$A$1:$IV$1", nr2.RefersToFormula); //if (false) { // // In case you fancy Checking in excel, to ensure it // // won't complain about the file now // File tempFile = File.CreateTempFile("POI-45126-", ".xls"); // FileOutputStream fout = new FileOutputStream(tempFile); // nwb.Write(fout); // fout.close(); // Console.WriteLine("check out " + tempFile.GetAbsolutePath()); //} }
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 TestSheetLevelFormulas() { HSSFWorkbook wb = new HSSFWorkbook(); IRow row; ISheet sh1 = wb.CreateSheet("Sheet1"); IName nm1 = wb.CreateName(); nm1.NameName = ("sales_1"); nm1.SheetIndex = (0); nm1.RefersToFormula = ("Sheet1!$A$1"); row = sh1.CreateRow(0); row.CreateCell(0).SetCellValue(3); row.CreateCell(1).SetCellFormula("sales_1"); row.CreateCell(2).SetCellFormula("sales_1*2"); ISheet sh2 = wb.CreateSheet("Sheet2"); IName nm2 = wb.CreateName(); nm2.NameName = ("sales_1"); nm2.SheetIndex = (1); nm2.RefersToFormula = ("Sheet2!$A$1"); row = sh2.CreateRow(0); row.CreateCell(0).SetCellValue(5); row.CreateCell(1).SetCellFormula("sales_1"); row.CreateCell(2).SetCellFormula("sales_1*3"); //check that NamePtg refers to the correct NameRecord Ptg[] ptgs1 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 0); NamePtg nPtg1 = (NamePtg)ptgs1[0]; Assert.AreSame(nm1, wb.GetNameAt(nPtg1.Index)); Ptg[] ptgs2 = HSSFFormulaParser.Parse("sales_1", wb, FormulaType.Cell, 1); NamePtg nPtg2 = (NamePtg)ptgs2[0]; Assert.AreSame(nm2, wb.GetNameAt(nPtg2.Index)); //check that the formula evaluator returns the correct result HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); Assert.AreEqual(3.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0); Assert.AreEqual(6.0, evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0); Assert.AreEqual(5.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0); Assert.AreEqual(15.0, evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.0); }
public void TestDiscontinousReference() { Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("44167.xls"); HSSFWorkbook wb = new HSSFWorkbook(is1); InternalWorkbook 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.IName 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); } }