Example #1
1
        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;
        }
Example #2
0
 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);
 }
Example #3
0
        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);
        }
Example #4
0
        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);
        }
Example #5
0
        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();
        }
Example #6
0
        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));
        }
Example #7
0
        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));
        }
Example #8
0
        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();
        }
Example #9
0
        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
        }
Example #10
0
        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);
        }
        public ActionResult Template(int? tagId)
        {

            var basicInfoSheetName = ProUploadService.BASIC_SHEET;
            var moreInfoSheetName = ProUploadService.MORE_SHEET;
            var supportSheetName = "不要修改";

            var headerLabels = new Dictionary<string, dynamic>() { 
                {"商品代码",new {dataformat=0,width=10}},
                {"商品名称",new {dataformat=0,width=20}},
                {"描述",new {dataformat=0,width=50}},
                {"吊牌价",new {dataformat=2,width=8}},
            {"现价",new {dataformat=2,width=8}},
            {"品牌名",new {dataformat=0,width=20}},
            { "分类名",new {dataformat=0,width=20}},
            {"门店名",new {dataformat=0,width=20}},
            { "促销活动编码",new {dataformat=0,width=20}},
            { "专题编码(多个以,分割)",new {dataformat=0,width=20}},
             {"可销售",new {dataformat=2,width=5}},
             {"商品货号",new {dataformat=0,width=10}}
            };
            var workbook = new HSSFWorkbook();
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.BorderBottom = BorderStyle.THIN;
            headerLabelCellStyle.BorderLeft = BorderStyle.THIN;
            headerLabelCellStyle.BorderRight = BorderStyle.THIN;
            headerLabelCellStyle.BorderTop = BorderStyle.THIN;
            headerLabelCellStyle.WrapText = true;
            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
            headerLabelCellStyle.SetFont(headerLabelFont);
            //set support sheet
            var supportSheet = workbook.CreateSheet(supportSheetName);
            workbook.SetSheetHidden(workbook.GetSheetIndex(supportSheet), true);
            Func<int, dynamic, int> supportFill = (rowIndex, data) =>
            {
                var brandRow = supportSheet.CreateRow(rowIndex++);
                var brandCodeCol = brandRow.CreateCell(1);
                brandCodeCol.SetCellType(CellType.STRING);
                brandCodeCol.SetCellValue(data.Id);
                var brandNameCol = brandRow.CreateCell(2);
                brandNameCol.SetCellType(CellType.STRING);
                brandCodeCol.SetCellValue(data.Name);
                return rowIndex;
            };
            int brandRowIndex = 0;
            foreach (var brand in _brandRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
            {
                brandRowIndex = supportFill(brandRowIndex, brand);
            }
            int tagRowIndex = brandRowIndex;
            var tagLinq = _tagRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name });
            //if (tagId.HasValue)
            //    tagLinq = tagLinq.Where(t => t.Id == tagId.Value);
            foreach (var tag in tagLinq.OrderBy(t => t.Name))
            {
                tagRowIndex = supportFill(tagRowIndex, tag);
            }
            int storeRowIndex = tagRowIndex;
            foreach (var store in _storeRepo.Get(b => b.Status != (int)DataStatus.Deleted).OrderBy(b => b.Name).Select(b => new { Id = b.Id, Name = b.Name }))
            {
                storeRowIndex = supportFill(storeRowIndex, store);
            }

            //set basic sheet 
            var sheet1 = workbook.CreateSheet(basicInfoSheetName);
            //workbook.SetSheetOrder(basicInfoSheetName, 0);

            var rowFirst = sheet1.CreateRow(0);
            Action<int, string, dynamic> cellSetting = (cellindex, desc, option) =>
            {
                var cell = rowFirst.CreateCell(cellindex);
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(desc);
                cell.CellStyle = headerLabelCellStyle;
                sheet1.SetColumnWidth(cellindex, option.width * 255);

                var currentCellStyle = workbook.CreateCellStyle();
                currentCellStyle.DataFormat = (short)option.dataformat;
                sheet1.SetDefaultColumnStyle(cellindex, currentCellStyle);
            };
            int index = 0;
            foreach (var key in headerLabels.Keys)
            {
                cellSetting(index++, key, headerLabels[key]);
            }
            //set constraint
            DVConstraint brandConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B$1:$B${1}", supportSheetName, brandRowIndex));
            CellRangeAddressList brandaddressList = new CellRangeAddressList(1, 1000, 5, 5);
            HSSFDataValidation branddataValidation = new HSSFDataValidation(brandaddressList, brandConstaint);
            branddataValidation.SuppressDropDownArrow = false;
            sheet1.AddValidationData(branddataValidation);

            DVConstraint tagConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, brandRowIndex + 1, tagRowIndex));
            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);
                cell.CellStyle = headerLabelCellStyle;
                sheet2.SetColumnWidth(moreCellIndex, moreheaderLabels[key].width * 255);

                var currentCellStyle = workbook.CreateCellStyle();
                currentCellStyle.DataFormat = (short)moreheaderLabels[key].dataformat;
                sheet2.SetDefaultColumnStyle(moreCellIndex, currentCellStyle);
                moreCellIndex++;

            }
            for (int i = 3; i <= 11; i++)
            {
                var cell = morerowFirst.CreateCell(i);
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(string.Empty);
                cell.CellStyle = headerLabelCellStyle;
                sheet2.SetColumnWidth(i, 10 * 255);
            
            }


            //set merge cell for last cell
            sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 2, 11));
            //set constraint
            DVConstraint codeConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$A${1}:$A${2}", basicInfoSheetName, 2, 1000));
            CellRangeAddressList codeaddressList = new CellRangeAddressList(1, 1000, 0, 0);
            HSSFDataValidation codedataValidation = new HSSFDataValidation(codeaddressList, codeConstaint);
            codedataValidation.SuppressDropDownArrow = false;
            sheet2.AddValidationData(codedataValidation);



            for (int i = 1; i < 1000; i++)
            {
                //create hide helper cell
                sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)&\"_\"&$B${0}",i+1,basicInfoSheetName),
                            new CellRangeAddress(i,i,12,12));
                //create hide helper cell
                sheet2.SetArrayFormula(string.Format("VLOOKUP($A${0},'{1}'!$A$1:$L$1000,7,FALSE)", i + 1, basicInfoSheetName),
                            new CellRangeAddress(i, i, 13, 13));
                sheet2.SetColumnHidden(12, true);
                sheet2.SetColumnHidden(13, true);
                DVConstraint pConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($N${0})", i + 1)); // DVConstraint.CreateFormulaListConstraint(string.Format("'{0}'!$B${1}:$B${2}", supportSheetName, storeRowIndex + 1, propertyRowIndex));
                CellRangeAddressList paddressList = new CellRangeAddressList(i,i, 1, 1);
                HSSFDataValidation pdataValidation = new HSSFDataValidation(paddressList, pConstaint);
                pdataValidation.SuppressDropDownArrow = false;
                sheet2.AddValidationData(pdataValidation);
                //set constraint
                DVConstraint pvConstaint = DVConstraint.CreateFormulaListConstraint(string.Format("INDIRECT($M${0})", i + 1));
                CellRangeAddressList pvaddressList = new CellRangeAddressList(i, i, 2, 11);
                HSSFDataValidation pvdataValidation = new HSSFDataValidation(pvaddressList, pvConstaint);
                pvdataValidation.SuppressDropDownArrow = false;
                sheet2.AddValidationData(pvdataValidation);
            }


            workbook.SetActiveSheet(workbook.GetSheetIndex(sheet1));

            var ms = new MemoryStream();
            workbook.Write(ms);
            ms.Position = 0;
            var downloadName = tagId.HasValue ? string.Format("商品上传模版-{0}.xls", tagId.Value) : "商品上传模块.xls";
            return File(ms, "application/vnd.ms-excel", downloadName);

        }
Example #12
0
 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);
 }
Example #13
0
        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();
            }
            
        }
Example #14
0
        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);
        }
Example #15
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);
            }
        }
Example #16
0
        public void TestDefinedNameWithComplexFlag_bug47048()
        {
            // Mock up a spreadsheet to match the critical details of the sample
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Input");
            IName definedName = wb.CreateName();
            definedName.NameName = ("Is_Multicar_Vehicle");
            definedName.RefersToFormula = ("Input!$B$17:$G$17");

            // Set up some data and the formula
            IRow row17 = sheet.CreateRow(16);
            row17.CreateCell(0).SetCellValue(25.0);
            row17.CreateCell(1).SetCellValue(1.33);
            row17.CreateCell(2).SetCellValue(4.0);

            IRow row = sheet.CreateRow(0);
            ICell cellA1 = row.CreateCell(0);
            cellA1.CellFormula = ("SUM(Is_Multicar_Vehicle)");

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

            HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
            CellValue value;
            try
            {
                value = hsf.Evaluate(cellA1);
            }
            catch (Exception e)
            {
                if (e.Message.Equals("Don't now how to evalate name 'Is_Multicar_Vehicle'"))
                {
                    throw new AssertionException("Identified bug 47048a");
                }
                throw e;
            }

            Assert.AreEqual(CellType.Numeric, value.CellType);
            Assert.AreEqual(5.33, value.NumberValue, 0.0);
        }
Example #17
0
        public void TestNamesInFormulas()
        {
            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);
        }