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