C# HSSFWorkbook.GetNameAt方法代码示例

        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);
            }
        }