public CompileResult(eErrorType errorType)
{
Result = ExcelErrorValue.Create(errorType);
DataType = DataType.ExcelError;
}
protected CompileResult CreateResult(eErrorType errorType)
{
return(CreateResult(ExcelErrorValue.Create(errorType), DataType.ExcelError));
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 2);
var number = ArgToDecimal(arguments, 0);
var refer = arguments.ElementAt(1);
bool asc = false;
if (arguments.Count() > 2)
{
asc = base.ArgToBool(arguments, 2);
}
var l = new List <double>();
foreach (var c in refer.ValueAsRangeInfo)
{
var v = Utils.ConvertUtil.GetValueDouble(c.Value, false, true);
if (!double.IsNaN(v))
{
l.Add(v);
}
}
l.Sort();
double ix;
if (asc)
{
ix = l.IndexOf(number) + 1;
if (_isAvg)
{
int st = Convert.ToInt32(ix);
while (l.Count > st && l[st] == number)
{
st++;
}
if (st > ix)
{
ix = ix + ((st - ix) / 2D);
}
}
}
else
{
ix = l.LastIndexOf(number);
if (_isAvg)
{
int st = Convert.ToInt32(ix) - 1;
while (0 <= st && l[st] == number)
{
st--;
}
if (st + 1 < ix)
{
ix = ix - ((ix - st - 1) / 2D);
}
}
ix = l.Count - ix;
}
if (ix <= 0 || ix > l.Count)
{
return(new CompileResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError));
}
else
{
return(CreateResult(ix, DataType.Decimal));
}
}
protected CompileResult Lookup(LookupNavigator navigator, LookupArguments lookupArgs)
{
object lastValue = null;
object lastLookupValue = null;
int? lastMatchResult = null;
if (lookupArgs.SearchedValue == null)
{
throw new ExcelErrorValueException("Lookupfunction failed to lookup value", ExcelErrorValue.Create(eErrorType.NA));
}
do
{
var matchResult = IsMatch(navigator.CurrentValue, lookupArgs.SearchedValue);
if (matchResult != 0)
{
if (lastValue != null && navigator.CurrentValue == null)
{
break;
}
if (lookupArgs.RangeLookup)
{
if (lastValue == null && matchResult > 0)
{
ThrowExcelErrorValueException(eErrorType.NA);
}
if (lastValue != null && matchResult > 0 && lastMatchResult < 0)
{
return(_compileResultFactory.Create(lastLookupValue));
}
lastMatchResult = matchResult;
lastValue = navigator.CurrentValue;
lastLookupValue = navigator.GetLookupValue();
}
}
else
{
return(_compileResultFactory.Create(navigator.GetLookupValue()));
}
}while (navigator.MoveNext());
if (lookupArgs.RangeLookup)
{
return(_compileResultFactory.Create(lastLookupValue));
}
throw new ExcelErrorValueException("Lookupfunction failed to lookup value", ExcelErrorValue.Create(eErrorType.NA));
}
public ExcelErrorValueException(eErrorType errorType)
: this(ExcelErrorValue.Create(errorType))
{
}
public void OperatorMinusShouldThrowExceptionIfNonNumericOperand()
{
var result = Operator.Minus.Apply(new CompileResult(1, DataType.Integer), new CompileResult("a", DataType.String));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), result.Result);
}
public void OperatorDivideShouldReturnValueErrorIfNonNumericOperand()
{
var result = Operator.Divide.Apply(new CompileResult(1, DataType.Integer), new CompileResult("a", DataType.String));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), result.Result);
}
/// <summary>
/// Converts the given <see cref="CompileResult"/> into an <see cref="Expression"/>.
/// </summary>
/// <param name="compileResult">The <see cref="CompileResult"/> to convert.</param>
/// <returns>Returns the <see cref="Expression"/> representation of the given <see cref="CompileResult"/>.</returns>
public Expression FromCompileResult(CompileResult compileResult)
{
switch (compileResult.DataType)
{
case DataType.Integer:
return(compileResult.Result is string
?new IntegerExpression(compileResult.Result.ToString())
: new IntegerExpression(Convert.ToDouble(compileResult.Result)));
case DataType.Time:
case DataType.Decimal:
return(compileResult.Result is string
?new DecimalExpression(compileResult.Result.ToString())
: new DecimalExpression(Convert.ToDouble(compileResult.Result)));
case DataType.String:
return(new StringExpression(compileResult.Result.ToString()));
case DataType.Boolean:
return(compileResult.Result is string
?new BooleanExpression(compileResult.Result.ToString())
: new BooleanExpression((bool)compileResult.Result));
case DataType.Date:
if (compileResult.Result is DateTime dateTimeResult || DateTime.TryParse(compileResult.Result.ToString(), out dateTimeResult))
{
return(new DateExpression(dateTimeResult.ToOADate().ToString()));
}
if (double.TryParse(compileResult.Result.ToString(), out double oaDate))
{
return(new DateExpression(oaDate.ToString()));
}
return(new ExcelErrorExpression(ExcelErrorValue.Create(eErrorType.Value)));
case DataType.ExcelError:
if (compileResult.Result is ExcelErrorValue errorValueResult)
{
return(new ExcelErrorExpression(errorValueResult));
}
else if (compileResult.Result is eErrorType eErrorTypeResult)
{
return(new ExcelErrorExpression(ExcelErrorValue.Create(eErrorTypeResult)));
}
else
{
return(new ExcelErrorExpression(compileResult.Result?.ToString(), ExcelErrorValue.Parse(compileResult.Result?.ToString())));
}
case DataType.Empty:
return(new IntegerExpression(0));
case DataType.ExcelAddress:
return(new StringExpression(compileResult.Result.ToString()));
case DataType.Enumerable:
case DataType.Unknown:
default:
// Enumerable results only end up with the first item in the collection.
// The result factory will itself return an enumerable CompileResult for List<object> so
// in order to prevent infinite recursion there is an explicit check for that specific type.
// The other form of enumerable result is IRangeInfo which is safely reduced in the result factory.
var resultToProcess = compileResult.Result;
if (resultToProcess is List <object> listResult)
{
resultToProcess = listResult.FirstOrDefault();
}
var result = this.ResultFactory.Create(resultToProcess);
return(this.FromCompileResult(result));
}
}
public void MaxIfsShouldReturnNaErrorIfNoMatch()
{
_worksheet.Cells["F1"].Formula = "MAXIFS(D3:D7,C3:C7,\"P\")";
_worksheet.Calculate();
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.NA).ToString(), _worksheet.Cells["F1"].Value.ToString());
}
public void CreateWithInvalidDefaultErrorTypeThrowsException()
{
ExcelErrorValue.Create(default(eErrorType));
}
/// <summary>
/// Takes the user arguments multiplies the corresponding components in the given arguments and returns the sum of
/// those products.
/// </summary>
/// <param name="arguments">The user specified arguments, usually arrays for this function.</param>
/// <param name="context">The current context of the function.</param>
/// <returns>The sum of the products fo the corresponding components in the given array.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (this.ArgumentsAreValid(arguments, 1, out eErrorType argumentError) == false)
{
return(new CompileResult(argumentError));
}
double result = 0d;
List <List <double> > results = new List <List <double> >();
foreach (var arg in arguments)
{
results.Add(new List <double>());
var currentResult = results.Last();
if (arg.Value is IEnumerable <FunctionArgument> )
{
foreach (var val in (IEnumerable <FunctionArgument>)arg.Value)
{
this.AddValue(val.Value, currentResult);
}
}
else if (arg.Value is FunctionArgument)
{
this.AddValue(arg.Value, currentResult);
}
else if (arg.IsExcelRange)
{
var r = arg.ValueAsRangeInfo;
for (int col = r.Address._fromCol; col <= r.Address._toCol; col++)
{
for (int row = r.Address._fromRow; row <= r.Address._toRow; row++)
{
if (r.GetValue(row, col) is bool)
{
this.AddValue(0, currentResult);
}
else if (r.GetValue(row, col) is ExcelErrorValue)
{
return(new CompileResult((ExcelErrorValue)r.GetValue(row, col)));
}
else
{
this.AddValue(r.GetValue(row, col), currentResult);
}
}
}
}
else if (arg.Value is int || arg.Value is double || arg.Value is System.DateTime)
{
this.AddValue(arg.Value, currentResult);
}
else
{
return(new CompileResult(eErrorType.Value));
}
}
var arrayLength = results.First().Count;
foreach (var list in results)
{
if (list.Count != arrayLength)
{
throw new ExcelErrorValueException(ExcelErrorValue.Create(eErrorType.Value));
}
}
for (var rowIndex = 0; rowIndex < arrayLength; rowIndex++)
{
double rowResult = 1;
for (var colIndex = 0; colIndex < results.Count; colIndex++)
{
rowResult *= results[colIndex][rowIndex];
}
result += rowResult;
}
return(this.CreateResult(result, DataType.Decimal));
}
public void CreateWithInvalidErrorTypeThrowsException()
{
ExcelErrorValue.Create(0);
}
public void IfErrorWithNameErrorAndNAErrorReturnsCorrectResult()
{
var function = new IfError();
var arguments = FunctionsHelper.CreateArgs(ExcelErrorValue.Create(eErrorType.Name), ExcelErrorValue.Create(eErrorType.NA));
var result = function.Execute(arguments, this.ParsingContext);
Assert.AreEqual(eErrorType.NA, ((ExcelErrorValue)result.Result).Type);
}
/// <summary>
/// Calculates a body value in a pivot table cell.
/// </summary>
/// <param name="dataRow">The row in the backing body data.</param>
/// <param name="dataColumn">The column in the backing body data.</param>
/// <param name="backingDatas">The backing data for the pivot table body.</param>
/// <param name="grandGrandTotalValues">The backing data for the pivot table grand grand totals.</param>
/// <param name="rowGrandTotalsValuesLists">The backing data for the pivot table row grand totals.</param>
/// <param name="columnGrandTotalsValuesLists">The backing data for the pivot table column grand totals.</param>
/// <returns>An object value for the cell.</returns>
public override object CalculateBodyValue(
int dataRow, int dataColumn,
PivotCellBackingData[,] backingDatas,
PivotCellBackingData[] grandGrandTotalValues,
List <PivotCellBackingData> rowGrandTotalsValuesLists,
List <PivotCellBackingData> columnGrandTotalsValuesLists)
{
var rowHeader = base.PivotTable.RowHeaders[dataRow];
var columnHeader = base.PivotTable.ColumnHeaders[dataColumn];
var cellBackingData = backingDatas[dataRow, dataColumn];
var dataField = base.PivotTable.DataFields[base.DataFieldCollectionIndex];
// TODO: Deal with "(next)" and "(previous)" options for this setting. See task #11840
// "(next)" is stored as "1048829" and "(previous)" is "1048828".
if (dataField.BaseItem == 1048829)
{
throw new InvalidOperationException(@"'(next)' is not supported for the 'Show Data as Percent of' setting.");
}
else if (dataField.BaseItem == 1048828)
{
throw new InvalidOperationException(@"'(previous)' is not supported for the 'Show Data as Percent of' setting.");
}
var baseFieldItemTuple = new Tuple <int, int>(dataField.BaseField, dataField.BaseItem);
if (cellBackingData == null)
{
return(cellBackingData?.Result);
}
else if (rowHeader.CacheRecordIndices.Any(t => t.Equals(baseFieldItemTuple)) || columnHeader.CacheRecordIndices.Any(t => t.Equals(baseFieldItemTuple)))
{
if (cellBackingData?.Result != null)
{
if (Convert.ToDouble(cellBackingData.Result) == 0)
{
return(ExcelErrorValue.Create(eErrorType.Div0));
}
return(1); // At a row/column that contains the comparison field item which makes this 100%.
}
else
{
return(cellBackingData?.Result);
}
}
else
{
// Try to find a value that matches either the current row or column header structure.
// If a value is found, the percentage can be calculated. Otherwise, the appropriate error is written out.
if (this.TryFindMatchingHeaderIndex(rowHeader, baseFieldItemTuple, base.PivotTable.RowHeaders, out int headerIndex))
{
var baseValue = backingDatas[headerIndex, dataColumn]?.Result;
return(this.GetShowDataAsPercentOfValue(baseValue, cellBackingData?.Result));
}
else if (this.TryFindMatchingHeaderIndex(columnHeader, baseFieldItemTuple, base.PivotTable.ColumnHeaders, out headerIndex))
{
var baseValue = backingDatas[dataRow, headerIndex]?.Result;
return(this.GetShowDataAsPercentOfValue(baseValue, cellBackingData?.Result));
}
else
{
if (!base.PivotTable.RowFields.Any(f => f.Index == dataField.BaseField) &&
!base.PivotTable.ColumnFields.Any(f => f.Index == dataField.BaseField))
{
// If the dataField.BaseField is not a row or column field, all values #N/A!
return(ExcelErrorValue.Create(eErrorType.NA));
}
else if (!rowHeader.CacheRecordIndices.Any(i => i.Item1 == dataField.BaseField) &&
!columnHeader.CacheRecordIndices.Any(i => i.Item1 == dataField.BaseField))
{
// Subtotals only get a value if they are at the same depth or below the show data as field.
return(null);
}
else
{
return(ExcelErrorValue.Create(eErrorType.NA));
}
}
}
}
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
return(CreateResult(ExcelErrorValue.Create(eErrorType.NA), DataType.ExcelError));
}
public void MaxIfsShouldReturnValueErrorIfWrongSizeOnCriteriaRange()
{
_worksheet.Cells["F1"].Formula = "MAXIFS(D3:D7,C3:C7,\"F\", B3:B5, \"Mi**nda\")";
_worksheet.Calculate();
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value).ToString(), _worksheet.Cells["F1"].Value.ToString());
}
/// <summary>
/// Throws an <see cref="ArgumentException"/> if <paramref name="condition"/> evaluates to true.
/// </summary>
/// <param name="condition"></param>
/// <param name="errorType"></param>
/// <exception cref="ExcelErrorValueException"></exception>
protected void ThrowExcelErrorValueExceptionIf(Func <bool> condition, eErrorType errorType)
{
if (condition())
{
throw new ExcelErrorValueException("An excel function error occurred", ExcelErrorValue.Create(errorType));
}
}
internal virtual object ParseCell(IEnumerable <Token> tokens, string worksheet, int row, int column, out DataType dataType)
{
var rangeAddress = _parsingContext.RangeAddressFactory.Create(worksheet, column, row);
using (var scope = _parsingContext.Scopes.NewScope(rangeAddress))
{
dataType = DataType.Unknown;
var graph = _graphBuilder.Build(tokens);
if (graph.Expressions.Count() == 0)
{
return(0d);
}
try
{
var compileResult = _compiler.Compile(graph.Expressions);
// quick solution for the fact that an excelrange can be returned.
var rangeInfo = compileResult.Result as ExcelDataProvider.IRangeInfo;
if (rangeInfo == null)
{
if (compileResult.Result != null)
{
dataType = compileResult.DataType;
}
return(compileResult.Result ?? 0d);
}
else
{
if (rangeInfo.IsEmpty)
{
return(0d);
}
if (!rangeInfo.IsMulti)
{
dataType = DataType.Enumerable;
return(rangeInfo.First().Value ?? 0d);
}
// ok to return multicell if it is a workbook scoped name.
if (string.IsNullOrEmpty(worksheet))
{
return(rangeInfo);
}
if (_parsingContext.Debug)
{
var msg = string.Format("A range with multiple cell was returned at row {0}, column {1}", row, column);
_parsingContext.Configuration.Logger.Log(_parsingContext, msg);
}
if (rangeInfo.Address.Rows > rangeInfo.Address.Columns)
{
var rangeAddressRow = rangeAddress.ToRow;
var startRow = rangeInfo.Address.Start.Row;
var endRow = rangeInfo.Address.End.Row;
var startCol = rangeInfo.Address.Start.Column;
var endCol = rangeInfo.Address.End.Column;
if (startCol != endCol)
{
dataType = DataType.ExcelError;
return(ExcelErrorValue.Create(eErrorType.Value));
}
if (rangeAddressRow == startRow)
{
return(rangeInfo.Worksheet.Cells[rangeInfo.Address.Start.Row, rangeInfo.Address.End.Column].Value);
}
else if (rangeAddressRow == endRow)
{
var test = rangeInfo.Address.End.Row;
var secondTest = rangeInfo.Address.End.Column;
return(rangeInfo.Worksheet.Cells[rangeInfo.Address.End.Row, rangeInfo.Address.End.Column].Value);
}
else if (rangeAddressRow > startRow && rangeAddressRow < endRow)
{
return(rangeInfo.Worksheet.Cells[rangeAddress.FromRow, rangeInfo.Address.Start.Column].Value);
}
}
else
{
var rangeAddressCol = rangeAddress.ToCol;
var startCol = rangeInfo.Address.Start.Column;
var endCol = rangeInfo.Address.End.Column;
var startRow = rangeInfo.Address.Start.Row;
var endRow = rangeInfo.Address.End.Row;
if (startRow != endRow)
{
dataType = DataType.ExcelError;
return(ExcelErrorValue.Create(eErrorType.Value));
}
if (rangeAddressCol == startCol)
{
return(rangeInfo.Worksheet.Cells[rangeInfo.Address.Start.Row, rangeAddress.FromCol].Value);
}
else if (rangeAddressCol == endCol)
{
return(rangeInfo.Worksheet.Cells[rangeInfo.Address.Start.Row, rangeAddress.FromCol].Value);
}
else if (rangeAddressCol > startCol && rangeAddressCol < endCol)
{
return(rangeInfo.Worksheet.Cells[rangeInfo.Address.Start.Row, rangeAddress.FromCol].Value);
}
}
dataType = DataType.ExcelError;
return(ExcelErrorValue.Create(eErrorType.Value));
}
}
catch (ExcelErrorValueException ex)
{
if (_parsingContext.Debug)
{
_parsingContext.Configuration.Logger.Log(_parsingContext, ex);
}
dataType = DataType.ExcelError;
return(ex.ErrorValue);
}
}
}
public void OperatorDivideShouldReturnDivideByZeroIfRightOperandIsZero()
{
var result = Operator.Divide.Apply(new CompileResult(1d, DataType.Decimal), new CompileResult(0d, DataType.Decimal));
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Div0), result.Result);
}
public virtual ExcelFunction GetFunction(string name)
{
if (!_functions.ContainsKey(name.ToLower()))
{
//throw new InvalidOperationException("Non supported function: " + name);
throw new ExcelErrorValueException("Non supported function: " + name, ExcelErrorValue.Create(eErrorType.Name));
}
return(_functions[name.ToLower()]);
}
/// <summary>
/// Throws an <see cref="ExcelErrorValueException"/> with the given <paramref name="errorType"/> set.
/// </summary>
/// <param name="errorType"></param>
protected void ThrowExcelErrorValueException(eErrorType errorType)
{
throw new ExcelErrorValueException("An excel function error occurred", ExcelErrorValue.Create(errorType));
}
public void MatchInvalidParameterCount()
{
this.Worksheet.Cells["A4"].Formula = "MATCH(3)";
this.Worksheet.Calculate();
Assert.AreEqual(ExcelErrorValue.Create(eErrorType.Value), this.Worksheet.Cells["A4"].Value);
}