public void TestHSSFSetArrayFormula_SingleCell() { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); ICell[] cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells; Assert.AreEqual(1, cells.Length); // sheet.SetArrayFormula Creates rows and cells for the designated range Assert.IsNotNull(sheet.GetRow(2)); ICell cell = sheet.GetRow(2).GetCell(2); Assert.IsNotNull(cell); Assert.IsTrue(cell.IsPartOfArrayFormulaGroup); //retrieve the range and check it is the same Assert.AreEqual(range.FormatAsString(), cell.ArrayFormulaRange.FormatAsString()); FormulaRecordAggregate agg = (FormulaRecordAggregate)(((HSSFCell)cell).CellValueRecord); Assert.AreEqual(range.FormatAsString(), agg.GetArrayFormulaRange().FormatAsString()); Assert.IsTrue(agg.IsPartOfArrayFormula); }
public void TestArrayFormulas() { int rownum = 4; int colnum = 4; FormulaRecord fr = new FormulaRecord(); fr.Row = (rownum); fr.Column = ((short)colnum); FormulaRecordAggregate agg = new FormulaRecordAggregate(fr, null, SharedValueManager.CreateEmpty()); Ptg[] ptgsForCell = { new ExpPtg(rownum, colnum) }; agg.SetParsedExpression(ptgsForCell); String formula = "SUM(A1:A3*B1:B3)"; Ptg[] ptgs = HSSFFormulaParser.Parse(formula, null, FormulaType.Array, 0); agg.SetArrayFormula(new CellRangeAddress(rownum, rownum, colnum, colnum), ptgs); Assert.IsTrue(agg.IsPartOfArrayFormula); Assert.AreEqual("E5", agg.GetArrayFormulaRange().FormatAsString()); Ptg[] ptg = agg.FormulaTokens; String fmlaSer = FormulaRenderer.ToFormulaString(null, ptg); Assert.AreEqual(formula, fmlaSer); agg.RemoveArrayFormula(rownum, colnum); Assert.IsFalse(agg.IsPartOfArrayFormula); }