public static FormulaValue CountIf(Cell cell, FormulaValue[] args)
{
if (cell == null || cell.Worksheet == null)
{
return(null);
}
double count = 0;
RangePosition evalRange;
if (args[0].type == FormulaValueType.Range)
{
evalRange = (RangePosition)args[0].value;
}
else
{
throw new FormulaTypeMismatchException(cell);
}
STValueNode leftExp = new STValueNode(null);
STNode compExp;
FormulaValue compVal = args[1];
if (args[1].type == FormulaValueType.String)
{
compExp = Parser.ParseInterCompareExp(cell, (string)compVal.value);
}
else
{
compExp = new STNode(STNodeType.EQUALS, 0, 0, new List <STNode>(2)
{
leftExp, new STValueNode(args[1])
});
}
cell.Worksheet.IterateCells(evalRange, (r, c, inCell) =>
{
leftExp.Value = Evaluator.CreateFormulaValue(inCell);
compExp[0] = leftExp;
var result = (Evaluator.Evaluate(cell, compExp));
if (result.type == FormulaValueType.Boolean && ((bool)result.value))
{
count++;
}
return(true);
});
return(count);
}
public static FormulaValue CountIf(Cell cell, FormulaValue[] args)
{
if (cell == null || cell.Worksheet == null)
{
return(null);
}
double count = 0;
ReferenceRange evalRange = RangeFrom(cell, args[0]);
Worksheet evalWorksheet = WorksheetFrom(cell, evalRange);
STValueNode leftExp = new STValueNode(null);
STNode compExp;
FormulaValue compVal = args[1];
if (args[1].type == FormulaValueType.String)
{
compExp = Parser.ParseInterCompareExp(cell, (string)compVal.value);
}
else
{
compExp = new STNode(STNodeType.EQUALS, 0, 0, new List <STNode>(2)
{
leftExp, new STValueNode(args[1])
});
}
evalWorksheet.IterateCells(evalRange, (r, c, inCell) =>
{
leftExp.Value = Evaluator.CreateFormulaValue(inCell);
compExp[0] = leftExp;
var result = (Evaluator.Evaluate(cell, compExp));
if (result.type == FormulaValueType.Boolean && ((bool)result.value))
{
count++;
}
return(true);
});
return(count);
}
public static FormulaValue SumIf(Cell cell, FormulaValue[] args)
{
if (cell == null || cell.Worksheet == null)
{
return(null);
}
if (args[1].type != FormulaValueType.String)
{
// todo: support not only string
return(null);
}
double val = 0;
double data;
RangePosition evalRange, sumRange = RangePosition.Empty;
if (args[0].type == FormulaValueType.Range)
{
evalRange = (RangePosition)args[0].value;
}
else
{
throw new FormulaTypeMismatchException(cell);
}
if (args.Length > 2)
{
if (args[2].type != FormulaValueType.Range)
{
throw new FormulaTypeMismatchException(cell);
}
sumRange = (RangePosition)(args[2].value);
}
string expStr = (string)args[1].value;
STValueNode leftExp = new STValueNode(null);
STNode compExp = Parser.ParseInterCompareExp(cell, expStr);
int rows = cell.Worksheet.Rows;
int cols = cell.Worksheet.Columns;
cell.Worksheet.IterateCells(evalRange, (r, c, inCell) =>
{
leftExp.Value = Evaluator.CreateFormulaValue(inCell);
compExp[0] = leftExp;
var result = (Evaluator.Evaluate(cell, compExp));
if (result.type == FormulaValueType.Boolean && ((bool)result.value))
{
if (sumRange.IsEmpty)
{
if (CellUtility.TryGetNumberData(inCell.InnerData, out data))
{
val += data;
}
}
else
{
int tr = sumRange.Row + r - evalRange.Row;
int tc = sumRange.Col + c - evalRange.Col;
if (tr < rows && tc < cols)
{
var sumCell = cell.Worksheet.GetCell(tr, tc);
if (sumCell != null && sumCell.InnerData != null &&
CellUtility.TryGetNumberData(sumCell.InnerData, out data))
{
val += data;
}
}
}
}
return(true);
});
return(val);
}
public static FormulaValue AverageIf(Cell cell, FormulaValue[] args)
{
if (cell == null || cell.Worksheet == null)
{
return(null);
}
if (args[1].type != FormulaValueType.String)
{
// todo: support not only string
return(null);
}
double val = 0, count = 0;
double data;
ReferenceRange evalRange = RangeFrom(cell, args[0]);
Worksheet evalWorksheet = WorksheetFrom(cell, evalRange);
ReferenceRange sumRange = null;
Worksheet sumWorksheet = null;
if (args.Length > 2)
{
sumRange = RangeFrom(cell, args[2]);
sumWorksheet = WorksheetFrom(cell, sumRange);
}
string expStr = (string)args[1].value;
STValueNode leftExp = new STValueNode(null);
STNode compExp = Parser.ParseInterCompareExp(cell, expStr);
int rows = cell.Worksheet.RowCount;
int cols = cell.Worksheet.ColumnCount;
evalWorksheet.IterateCells(evalRange.Position, (r, c, inCell) =>
{
leftExp.Value = Evaluator.CreateFormulaValue(inCell);
compExp[0] = leftExp;
var result = Evaluator.Evaluate(cell, compExp);
if (result.type == FormulaValueType.Boolean && ((bool)result.value))
{
if (sumRange == null)
{
if (CellUtility.TryGetNumberData(evalWorksheet, inCell, out data))
{
val += data;
count++;
}
}
else
{
int tr = sumRange.Row + r - evalRange.Row;
int tc = sumRange.Col + c - evalRange.Col;
if (tr < rows && tc < cols)
{
var sumCell = sumWorksheet.GetCell(tr, tc);
if (sumCell != null && sumCell.InnerData != null &&
CellUtility.TryGetNumberData(sumWorksheet, sumCell, out data))
{
val += data;
count++;
}
}
}
}
return(true);
});
return(count > 0 ? (FormulaValue)(val / count) : FormulaValue.Nil);
}