public void TestSortTopLeftToBottomRight()
{
AreaPtg ptg = new AreaPtg("A$1:$B5");
Assert.AreEqual("A$1:$B5", ptg.ToFormulaString());
ptg.FirstColumn = (3);
Assert.AreEqual("D$1:$B5", ptg.ToFormulaString(),
"Area Ptg should not implicitly re-sort itself (except during construction)");
ptg.SortTopLeftToBottomRight();
Assert.AreEqual("$B$1:D5", ptg.ToFormulaString(),
"Area Ptg should restore itself to top-left to lower-right order when explicitly asked");
}
public void Test44410()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SingleLetterRanges.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
// =index(C:C,2,1) -> 2
IRow rowIDX = sheet.GetRow(3);
// =sum(C:C) -> 6
IRow rowSUM = sheet.GetRow(4);
// =sum(C:D) -> 66
IRow rowSUM2D = sheet.GetRow(5);
// Test the sum
ICell cellSUM = rowSUM.GetCell(0);
FormulaRecordAggregate frec = (FormulaRecordAggregate)((HSSFCell)cellSUM).CellValueRecord;
Ptg[] ops = frec.FormulaRecord.ParsedExpression;
Assert.AreEqual(2, ops.Length);
Assert.AreEqual(typeof(AreaPtg), ops[0].GetType());
Assert.AreEqual(typeof(FuncVarPtg), ops[1].GetType());
// Actually stored as C1 to C65536
// (last row is -1 === 65535)
AreaPtg ptg = (AreaPtg)ops[0];
Assert.AreEqual(2, ptg.FirstColumn);
Assert.AreEqual(2, ptg.LastColumn);
Assert.AreEqual(0, ptg.FirstRow);
Assert.AreEqual(65535, ptg.LastRow);
Assert.AreEqual("C:C", ptg.ToFormulaString());
// Will show as C:C, but won't know how many
// rows it covers as we don't have the sheet
// to hand when turning the Ptgs into a string
Assert.AreEqual("SUM(C:C)", cellSUM.CellFormula);
// But the evaluator knows the sheet, so it
// can do it properly
Assert.AreEqual(6, eva.Evaluate(cellSUM).NumberValue, 0);
// Test the index
// Again, the formula string will be right but
// lacking row count, Evaluated will be right
ICell cellIDX = rowIDX.GetCell(0);
Assert.AreEqual("INDEX(C:C,2,1)", cellIDX.CellFormula);
Assert.AreEqual(2, eva.Evaluate(cellIDX).NumberValue, 0);
// Across two colums
ICell cellSUM2D = rowSUM2D.GetCell(0);
Assert.AreEqual("SUM(C:D)", cellSUM2D.CellFormula);
Assert.AreEqual(66, eva.Evaluate(cellSUM2D).NumberValue, 0);
wb.Close();
}
public void TestAbsoluteRelativeRefs()
{
AreaPtg sca1 = new AreaPtg(4, 5, 6, 7, true, false, true, false);
AreaPtg sca2 = new AreaPtg(4, 5, 6, 7, false, true, false, true);
AreaPtg sca3 = new AreaPtg(5, 5, 7, 7, true, false, true, false);
AreaPtg sca4 = new AreaPtg(5, 5, 7, 7, false, true, false, true);
Assert.AreEqual("G5:$H$6", sca1.ToFormulaString(), "first rel., last abs.");
Assert.AreEqual("$G$5:H6", sca2.ToFormulaString(), "first abs., last rel.");
Assert.AreEqual("H6:$H$6", sca3.ToFormulaString(), "first rel., last abs.");
Assert.AreEqual("$H$6:H6", sca4.ToFormulaString(), "first abs., last rel.");
AreaPtg cla1 = CloneArea(sca1);
AreaPtg cla2 = CloneArea(sca2);
AreaPtg cla3 = CloneArea(sca3);
AreaPtg cla4 = CloneArea(sca4);
Assert.AreEqual("G5:$H$6", cla1.ToFormulaString(), "first rel., last abs.");
Assert.AreEqual("$G$5:H6", cla2.ToFormulaString(), "first abs., last rel.");
Assert.AreEqual("H6:$H$6", cla3.ToFormulaString(), "first rel., last abs.");
Assert.AreEqual("$H$6:H6", cla4.ToFormulaString(), "first abs., last rel.");
}