C# HSSFWorkbook.CreateName方法代码示例

        private static HSSFWorkbook CreateWBA()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet("Sheet1");
            ISheet sheet2 = wb.CreateSheet("Sheet2");
            ISheet sheet3 = wb.CreateSheet("John's sales");

            CreateDataRow(sheet1, 0, 11, 12, 13, 14);
            CreateDataRow(sheet1, 1, 21, 22, 23, 24);
            CreateDataRow(sheet1, 2, 31, 32, 33, 34);

            CreateDataRow(sheet2, 0, 50, 55, 60, 65);
            CreateDataRow(sheet2, 1, 51, 56, 61, 66);
            CreateDataRow(sheet2, 2, 52, 57, 62, 67);

            CreateDataRow(sheet3, 0, 30, 31, 32);
            CreateDataRow(sheet3, 1, 33, 34, 35);

            IName name1 = wb.CreateName();
            name1.NameName = ("sales1");
            name1.RefersToFormula = ("Sheet1!A1:D1");

            IName name2 = wb.CreateName();
            name2.NameName = ("sales2");
            name2.RefersToFormula = ("Sheet2!B1:C3");

            IRow row = sheet1.CreateRow(3);
            row.CreateCell(0).SetCellValue("sales1");  //A4
            row.CreateCell(1).SetCellValue("sales2");  //B4

            return wb;
        }

 public void TestFormulaRelAbs_bug46174()
 {
     // perhaps this Testcase belongs on TestHSSFName
     IWorkbook wb = new HSSFWorkbook();
     IName name = wb.CreateName();
     wb.CreateSheet("Sheet1");
     name.NameName = (/*setter*/"test");
     name.RefersToFormula = (/*setter*/"Sheet1!$B$3");
     if (name.RefersToFormula.Equals("Sheet1!B3"))
     {
         throw new AssertionException("Identified bug 46174");
     }
     Assert.AreEqual("Sheet1!$B$3", name.RefersToFormula);
 }

        public void TestWithNamedRange()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            ISheet s = workbook.CreateSheet("Foo");
            s.CreateRow(0).CreateCell((short)0).SetCellValue(1.1);
            s.CreateRow(1).CreateCell((short)0).SetCellValue(2.3);
            s.CreateRow(2).CreateCell((short)2).SetCellValue(3.1);

            IName name = workbook.CreateName();
            name.NameName = ("testName");
            name.RefersToFormula = ("A1:A2");

            ConfirmParseFormula(workbook);

            // Now make it a single cell
            name.RefersToFormula = ("C3");
            ConfirmParseFormula(workbook);

            // And make it non-contiguous
            name.RefersToFormula = ("A1:A2,C3");
            ConfirmParseFormula(workbook);
        }

        public void TestSetSheetOrderHSSF()
        {
            IWorkbook wb = new HSSFWorkbook();
            ISheet s1 = wb.CreateSheet("first sheet");
            ISheet s2 = wb.CreateSheet("other sheet");

            IName name1 = wb.CreateName();
            name1.NameName = (/*setter*/"name1");
            name1.RefersToFormula = (/*setter*/"'first sheet'!D1");

            IName name2 = wb.CreateName();
            name2.NameName = (/*setter*/"name2");
            name2.RefersToFormula = (/*setter*/"'other sheet'!C1");


            IRow s1r1 = s1.CreateRow(2);
            ICell c1 = s1r1.CreateCell(3);
            c1.SetCellValue(30);
            ICell c2 = s1r1.CreateCell(2);
            c2.CellFormula = (/*setter*/"SUM('other sheet'!C1,'first sheet'!C1)");

            IRow s2r1 = s2.CreateRow(0);
            ICell c3 = s2r1.CreateCell(1);
            c3.CellFormula = (/*setter*/"'first sheet'!D3");
            ICell c4 = s2r1.CreateCell(2);
            c4.CellFormula = (/*setter*/"'other sheet'!D3");

            // conditional formatting
            ISheetConditionalFormatting sheetCF = s1.SheetConditionalFormatting;

            IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(
                    ComparisonOperator.Between, "'first sheet'!D1", "'other sheet'!D1");

            IConditionalFormattingRule[] cfRules = { rule1 };

            CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5
        };
            sheetCF.AddConditionalFormatting(regions, cfRules);

            wb.SetSheetOrder("other sheet", 0);

            // names
            Assert.AreEqual("'first sheet'!D1", wb.GetName("name1").RefersToFormula);
            Assert.AreEqual("'other sheet'!C1", wb.GetName("name2").RefersToFormula);

            // cells
            Assert.AreEqual("SUM('other sheet'!C1,'first sheet'!C1)", c2.CellFormula);
            Assert.AreEqual("'first sheet'!D3", c3.CellFormula);
            Assert.AreEqual("'other sheet'!D3", c4.CellFormula);

            // conditional formatting
            IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);
            Assert.AreEqual("'first sheet'!D1", cf.GetRule(0).Formula1);
            Assert.AreEqual("'other sheet'!D1", cf.GetRule(0).Formula2);
        }

        public void TestNamesWithUnderscore()
        {
            HSSFWorkbook wb = new HSSFWorkbook(); //or new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet("NamesWithUnderscore");

            IName nm;

            nm = wb.CreateName();
            nm.NameName = ("DA6_LEO_WBS_Number");
            nm.RefersToFormula = ("33");

            nm = wb.CreateName();
            nm.NameName = ("DA6_LEO_WBS_Name");
            nm.RefersToFormula = ("33");

            nm = wb.CreateName();
            nm.NameName = ("A1_");
            nm.RefersToFormula = ("22");

            nm = wb.CreateName();
            nm.NameName = ("_A1");
            nm.RefersToFormula = ("11");

            nm = wb.CreateName();
            nm.NameName = ("A_1");
            nm.RefersToFormula = ("44");

            nm = wb.CreateName();
            nm.NameName = ("A_1_");
            nm.RefersToFormula = ("44");

            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);

            cell.CellFormula = ("DA6_LEO_WBS_Number*2");
            Assert.AreEqual("DA6_LEO_WBS_Number*2", cell.CellFormula);

            cell.CellFormula = ("(A1_*_A1+A_1)/A_1_");
            Assert.AreEqual("(A1_*_A1+A_1)/A_1_", cell.CellFormula);

            cell.CellFormula = ("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))");
            Assert.AreEqual("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))", cell.CellFormula);

            wb.Close();
        }

        public void TestZeroRowRefs()
        {
            String badCellRef = "B0"; // bad because zero is not a valid row number
            String leadingZeroCellRef = "B000001"; // this should get parsed as "B1"
            HSSFWorkbook wb = new HSSFWorkbook();

            try
            {
                HSSFFormulaParser.Parse(badCellRef, wb);
                throw new AssertionException("Identified bug 47312b - Shouldn't be able to parse cell ref '"
                        + badCellRef + "'.");
            }
            catch (FormulaParseException e)
            {
                // expected during successful Test
                ConfirmParseException(e, "Specified named range '"
                        + badCellRef + "' does not exist in the current workbook.");
            }

            Ptg[] ptgs;
            try
            {
                ptgs = HSSFFormulaParser.Parse(leadingZeroCellRef, wb);
                Assert.AreEqual("B1", ((RefPtg)ptgs[0]).ToFormulaString());
            }
            catch (FormulaParseException e)
            {
                ConfirmParseException(e, "Specified named range '"
                        + leadingZeroCellRef + "' does not exist in the current workbook.");
                // close but no cigar
                throw new AssertionException("Identified bug 47312c - '"
                        + leadingZeroCellRef + "' should parse as 'B1'.");
            }

            // create a defined name called 'B0' and try again
            IName n = wb.CreateName();
            n.NameName = ("B0");
            n.RefersToFormula = ("1+1");
            ptgs = HSSFFormulaParser.Parse("B0", wb);
            ConfirmTokenClasses(ptgs, typeof(NamePtg));
        }

        public void TestParseComplexName()
        {

            // Mock up a spreadsheet to match the critical details of the sample
            HSSFWorkbook wb = new HSSFWorkbook();
            wb.CreateSheet("Sheet1");
            IName definedName = wb.CreateName();
            definedName.NameName = ("foo");
            definedName.RefersToFormula = ("Sheet1!B2");

            // Set the complex flag - POI doesn't usually manipulate this flag
            NameRecord nameRec = TestHSSFName.GetNameRecord(definedName);
            nameRec.OptionFlag = ((short)0x10); // 0x10 -> complex

            Ptg[] result;
            try
            {
                result = HSSFFormulaParser.Parse("1+foo", wb);
            }
            catch (FormulaParseException e)
            {
                if (e.Message.Equals("Specified name 'foo' is not a range as expected."))
                {
                    throw new AssertionException("Identified bug 47078c");
                }
                throw e;
            }
            ConfirmTokenClasses(result, typeof(IntPtg), typeof(NamePtg), typeof(AddPtg));
        }

        public void TestBackSlashInNames()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            IName name = wb.CreateName();
            name.NameName = ("POI\\2009");
            name.RefersToFormula = ("Sheet1!$A$1");

            ISheet sheet = wb.CreateSheet();
            IRow row = sheet.CreateRow(0);

            ICell cell_C1 = row.CreateCell(2);
            cell_C1.CellFormula = ("POI\\2009");
            Assert.AreEqual("POI\\2009", cell_C1.CellFormula);

            ICell cell_D1 = row.CreateCell(2);
            cell_D1.CellFormula = ("NOT(POI\\2009=\"3.5-final\")");
            Assert.AreEqual("NOT(POI\\2009=\"3.5-final\")", cell_D1.CellFormula);

            wb.Close();
        }

        public void TestNamedRangeThatLooksLikeCell()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IName name = wb.CreateName();
            name.RefersToFormula = ("Sheet1!B1");
            name.NameName = ("pfy1");

            Ptg[] ptgs;
            try
            {
                ptgs = HSSFFormulaParser.Parse("count(pfy1)", wb);
            }
            catch (ArgumentException e)
            {
                if (e.Message.Equals("Specified colIx (1012) is out of range"))
                {
                    throw new AssertionException("Identified bug 45354");
                }
                throw e;
            }
            ConfirmTokenClasses(ptgs, typeof(NamePtg), typeof(FuncVarPtg));

            ICell cell = sheet.CreateRow(0).CreateCell(0);
            cell.CellFormula = ("count(pfy1)");
            Assert.AreEqual("COUNT(pfy1)", cell.CellFormula);
            try
            {
                cell.CellFormula = ("count(pf1)");
                throw new AssertionException("Expected formula parse execption");
            }
            catch (FormulaParseException e)
            {
                ConfirmParseException(e,
                        "Specified named range 'pf1' does not exist in the current workbook.");
            }
            cell.CellFormula = ("count(fp1)"); // plain cell ref, col is in range
        }

        public void TestNamesInFormulas()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");

            IWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");

            IName name1 = wb.CreateName();
            name1.NameName = "aConstant";
            name1.RefersToFormula = "3.14";

            IName name2 = wb.CreateName();
            name2.NameName = "aFormula";
            name2.RefersToFormula = "SUM(Sheet1!$A$1:$A$3)";

            IName name3 = wb.CreateName();
            name3.NameName = "aSet";
            name3.RefersToFormula = "Sheet1!$A$2:$A$4";

            IRow row0 = sheet.CreateRow(0);
            IRow row1 = sheet.CreateRow(1);
            IRow row2 = sheet.CreateRow(2);
            IRow row3 = sheet.CreateRow(3);
            row0.CreateCell(0).SetCellValue(2);
            row1.CreateCell(0).SetCellValue(5);
            row2.CreateCell(0).SetCellValue(3);
            row3.CreateCell(0).SetCellValue(7);

            row0.CreateCell(2).SetCellFormula("aConstant");
            row1.CreateCell(2).SetCellFormula("aFormula");
            row2.CreateCell(2).SetCellFormula("SUM(aSet)");
            row3.CreateCell(2).SetCellFormula("aConstant+aFormula+SUM(aSet)");

            IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
            Assert.AreEqual(3.14, fe.Evaluate(row0.GetCell(2)).NumberValue);
            Assert.AreEqual(10.0, fe.Evaluate(row1.GetCell(2)).NumberValue);
            Assert.AreEqual(15.0, fe.Evaluate(row2.GetCell(2)).NumberValue);
            Assert.AreEqual(28.14, fe.Evaluate(row3.GetCell(2)).NumberValue);
        }

//.........这里部分代码省略.........
            CellRangeAddressList tagaddressList = new CellRangeAddressList(1, 1000, 6, 6);
            HSSFDataValidation tagdataValidation = new HSSFDataValidation(tagaddressList, tagConstaint);
            tagdataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(tagdataValidation);

            DVConstraint storeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, tagRowIndex + 1, storeRowIndex));
            CellRangeAddressList storeaddressList = new CellRangeAddressList(1, 1000, 7, 7);
            HSSFDataValidation storedataValidation = new HSSFDataValidation(storeaddressList, storeConstaint);
            storedataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(storedataValidation);

            DVConstraint is4saleConstaint = DVConstraint.CreateExplicitListConstraint(new string[] { ProUploadService.IS_4SALE_YES, ProUploadService.IS_4SALE_NO });
            CellRangeAddressList is4saleaddressList = new CellRangeAddressList(1, 1000, 10, 10);
            HSSFDataValidation is4saledataValidation = new HSSFDataValidation(is4saleaddressList, is4saleConstaint);
            is4saledataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(is4saledataValidation);

            //set sheet2
            //create property value sheet
            var propertyLinq = _propertyRepo.Get(b => b.Status != (int)DataStatus.Deleted)
                            .Join(Context.Set<TagEntity>(), o => o.CategoryId, i => i.Id, (o, i) => new { P = o, C = i })
                            .OrderBy(b => b.P.CategoryId)
                            .ThenBy(b => b.P.PropertyDesc);
            int propertyRowIndex = storeRowIndex;
            foreach (var tag in tagLinq)
            {
                var propertyFromIndex = propertyRowIndex;
                foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id).Select(p=>new {Id = p.P.Id,Name=p.P.PropertyDesc}))
                {
                    propertyRowIndex = supportFill(propertyRowIndex, property);
                }
                if (propertyRowIndex > propertyFromIndex)
                {
                    var rName = workbook.CreateName();
                    rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, propertyFromIndex + 1, propertyRowIndex);
                    rName.NameName = tag.Name;
                }
                foreach (var property in propertyLinq.Where(p => p.C.Id == tag.Id))
                {
                    int valueFromIndex = propertyRowIndex;
                    foreach (var pvalue in _valueRepo.Get(b => b.Status != (int)DataStatus.Deleted && b.PropertyId == property.P.Id).Select(p => new { Id = p.Id, Name = p.ValueDesc }))
                    {
                        propertyRowIndex = supportFill(propertyRowIndex, pvalue);
                    }
                    if (propertyRowIndex > valueFromIndex)
                    {
                        var rName = workbook.CreateName();
                        rName.RefersToFormula = string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, valueFromIndex + 1, propertyRowIndex);
                        rName.NameName = string.Format("{0}_{1}", property.C.Name, property.P.PropertyDesc);
                    }
                }
            }

            var moreheaderLabels = new Dictionary<string, dynamic>() { 
                {"商品代码",new {dataformat=0,width=10}},
                 {"属性名",new {dataformat=0,width=20}},
                 {"属性值",new {dataformat=0,width=10}}
                };
            var sheet2 = workbook.CreateSheet(moreInfoSheetName);
            var morerowFirst = sheet2.CreateRow(0);
            int moreCellIndex = 0;
            foreach (var key in moreheaderLabels.Keys)
            {
                var cell = morerowFirst.CreateCell(moreCellIndex);
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(key);

 public void TestFormulaGeneral()
 {
     // perhaps this Testcase belongs on TestHSSFName
     IWorkbook wb = new HSSFWorkbook();
     IName name = wb.CreateName();
     wb.CreateSheet("Sheet1");
     name.NameName = (/*setter*/"test");
     name.RefersToFormula = (/*setter*/"Sheet1!A1+Sheet1!A2");
     Assert.AreEqual("Sheet1!A1+Sheet1!A2", name.RefersToFormula);
     name.RefersToFormula = (/*setter*/"5*6");
     Assert.AreEqual("5*6", name.RefersToFormula);
 }

        public void TestFormulasWithUnderscore()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            try
            {
                IName nm1 = wb.CreateName();
                nm1.NameName = ("_score1");
                nm1.RefersToFormula = ("A1");

                IName nm2 = wb.CreateName();
                nm2.NameName = ("_score2");
                nm2.RefersToFormula = ("A2");

                ISheet sheet = wb.CreateSheet();
                ICell cell = sheet.CreateRow(0).CreateCell(2);
                cell.CellFormula = ("_score1*SUM(_score1+_score2)");
                Assert.AreEqual("_score1*SUM(_score1+_score2)", cell.CellFormula);
            }
            finally
            {
                wb.Close();
            }
            
        }

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