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