private ValueEval doFixed(
ValueEval numberParam, ValueEval placesParam,
ValueEval skipThousandsSeparatorParam,
int srcRowIndex, int srcColumnIndex)
{
try
{
ValueEval numberValueEval =
OperandResolver.GetSingleValue(
numberParam, srcRowIndex, srcColumnIndex);
decimal number = (decimal)OperandResolver.CoerceValueToDouble(numberValueEval);
ValueEval placesValueEval =
OperandResolver.GetSingleValue(
placesParam, srcRowIndex, srcColumnIndex);
int places = OperandResolver.CoerceValueToInt(placesValueEval);
ValueEval skipThousandsSeparatorValueEval =
OperandResolver.GetSingleValue(
skipThousandsSeparatorParam, srcRowIndex, srcColumnIndex);
bool?skipThousandsSeparator =
OperandResolver.CoerceValueToBoolean(
skipThousandsSeparatorValueEval, false);
// Round number to respective places.
//number = number.SetScale(places, RoundingMode.HALF_UP);
if (places < 0)
{
number = number / (decimal)Math.Pow(10, -places);
number = Math.Round(number, 0);
number = number * (decimal)Math.Pow(10, -places);
}
else
{
number = Math.Round(number, places);
}
// Format number conditionally using a thousands separator.
/*NumberFormat nf = NumberFormat.GetNumberInstance(Locale.US);
* DecimalFormat formatter = (DecimalFormat)nf;
* formatter.setGroupingUsed(!skipThousandsSeparator);
* formatter.setMinimumFractionDigits(places >= 0 ? places : 0);
* formatter.setMaximumFractionDigits(places >= 0 ? places : 0);
* String numberString = formatter.Format(number);*/
//System.Globalization.CultureInfo culture = System.Globalization.CultureInfo.CurrentCulture;
string numberString = skipThousandsSeparator != null && skipThousandsSeparator.Value ?
number.ToString(places > 0 ? "F" + places : "F0")
: number.ToString(places > 0 ? "N" + places : "N0", System.Globalization.CultureInfo.InvariantCulture);
// Return the result as a StringEval.
return(new StringEval(numberString));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public static bool EvaluateFirstArg(ValueEval arg, int srcCellRow, int srcCellCol)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, srcCellRow, srcCellCol);
bool? b = OperandResolver.CoerceValueToBoolean(ve, false);
if (b == null)
{
return(false);
}
return((bool)b);
}
private static bool EvaluateBooleanArg(ValueEval arg, OperationEvaluationContext ec)
{
ValueEval ve = OperandResolver.GetSingleValue(arg, ec.RowIndex, ec.ColumnIndex);
if (ve == BlankEval.instance || ve == MissingArgEval.instance)
{
return(false);
}
// numeric quantities follow standard bool conversion rules
// for strings, only "TRUE" and "FALSE" (case insensitive) are valid
return((bool)OperandResolver.CoerceValueToBoolean(ve, false));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, ValueEval arg2)
{
try
{
double d0 = NumericFunction.SingleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
double d1 = NumericFunction.SingleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
ValueEval ve = OperandResolver.GetSingleValue(arg2, srcRowIndex, srcColumnIndex);
bool? method = OperandResolver.CoerceValueToBoolean(ve, false);
return(new NumberEval(Evaluate(d0, d1, method != null && (bool)method)));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
bool boolArgVal;
try
{
ValueEval ve = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
bool? b = OperandResolver.CoerceValueToBoolean(ve, false);
boolArgVal = b == null ? false : (bool)b;
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(BoolEval.ValueOf(!boolArgVal));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg1, ValueEval arg2, ValueEval arg3, ValueEval arg4)
{
try
{
Double xval = evaluateValue(arg1, srcRowIndex, srcColumnIndex);
if (double.IsNaN(xval))
{
return(ErrorEval.VALUE_INVALID);
}
Double mean = evaluateValue(arg2, srcRowIndex, srcColumnIndex);
if (double.IsNaN(mean))
{
return(ErrorEval.VALUE_INVALID);
}
Double stdev = evaluateValue(arg3, srcRowIndex, srcColumnIndex);
if (double.IsNaN(stdev))
{
return(ErrorEval.VALUE_INVALID);
}
else if (stdev <= 0)
{
return(ErrorEval.NUM_ERROR);
}
var cumulative = OperandResolver.CoerceValueToBoolean(arg4, false);
if (cumulative == null)
{
return(ErrorEval.VALUE_INVALID);
}
return(new NumberEval(probability(
xval, mean, stdev, (bool)cumulative)));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex,
int srcColumnIndex)
{
if (args.Length < 2 || args.Length > 5)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
bool pAbsRow, pAbsCol;
int row = (int)NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
int col = (int)NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
int refType;
if (args.Length > 2 && args[2] != MissingArgEval.instance)
{
refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
}
else
{
refType = REF_ABSOLUTE; // this is also the default if parameter is not given
}
switch (refType)
{
case REF_ABSOLUTE:
pAbsRow = true;
pAbsCol = true;
break;
case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
pAbsRow = true;
pAbsCol = false;
break;
case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
pAbsRow = false;
pAbsCol = true;
break;
case REF_RELATIVE:
pAbsRow = false;
pAbsCol = false;
break;
default:
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
bool a1;
if (args.Length > 3)
{
ValueEval ve = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
// TODO R1C1 style is not yet supported
a1 = ve == MissingArgEval.instance ? true : OperandResolver.CoerceValueToBoolean(ve, false).Value;
}
else
{
a1 = true;
}
String sheetName;
if (args.Length == 5)
{
ValueEval ve = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
sheetName = ve == MissingArgEval.instance ? null : OperandResolver.CoerceValueToString(ve);
}
else
{
sheetName = null;
}
CellReference ref1 = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
StringBuilder sb = new StringBuilder(32);
if (sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, sheetName);
sb.Append('!');
}
sb.Append(ref1.FormatAsString());
return(new StringEval(sb.ToString()));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
private bool Calculate(ValueEval[] args)
{
bool result = InitialResultValue;
bool atleastOneNonBlank = false;
bool?tempVe;
/*
* Note: no short-circuit bool loop exit because any ErrorEvals will override the result
*/
for (int i = 0, iSize = args.Length; i < iSize; i++)
{
ValueEval arg = args[i];
if (arg is TwoDEval)
{
TwoDEval ae = (TwoDEval)arg;
int height = ae.Height;
int width = ae.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetValue(rrIx, rcIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
}
continue;
}
if (arg is RefEval)
{
ValueEval ve = ((RefEval)arg).InnerValueEval;
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
}
else if (arg is ValueEval)
{
ValueEval ve = (ValueEval)arg;
tempVe = OperandResolver.CoerceValueToBoolean(ve, false);
}
else
{
throw new InvalidOperationException("Unexpected eval (" + arg.GetType().Name + ")");
}
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
if (!atleastOneNonBlank)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return(result);
}
private bool Calculate(ValueEval[] args)
{
bool result = InitialResultValue;
bool atleastOneNonBlank = false;
/*
* Note: no short-circuit bool loop exit because any ErrorEvals will override the result
*/
foreach (ValueEval arg in args)
{
bool?tempVe;
if (arg is TwoDEval)
{
TwoDEval ae = (TwoDEval)arg;
int height = ae.Height;
int width = ae.Width;
for (int rrIx = 0; rrIx < height; rrIx++)
{
for (int rcIx = 0; rcIx < width; rcIx++)
{
ValueEval ve = ae.GetValue(rrIx, rcIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
}
continue;
}
if (arg is RefEval)
{
RefEval re = (RefEval)arg;
int firstSheetIndex = re.FirstSheetIndex;
int lastSheetIndex = re.LastSheetIndex;
for (int sIx = firstSheetIndex; sIx <= lastSheetIndex; sIx++)
{
ValueEval ve = re.GetInnerValueEval(sIx);
tempVe = OperandResolver.CoerceValueToBoolean(ve, true);
if (tempVe != null)
{
result = PartialEvaluate(result, tempVe.Value);
atleastOneNonBlank = true;
}
}
continue;
}
//else if (arg is ValueEval)
//{
// ValueEval ve = (ValueEval)arg;
// tempVe = OperandResolver.CoerceValueToBoolean(ve, false);
//}
if (arg == MissingArgEval.instance)
{
tempVe = null; // you can leave out parameters, they are simply ignored
}
else
{
tempVe = OperandResolver.CoerceValueToBoolean(arg, false);
}
if (tempVe != null)
{
result = PartialEvaluate(result, Convert.ToBoolean(tempVe, CultureInfo.InvariantCulture));
atleastOneNonBlank = true;
}
}
if (!atleastOneNonBlank)
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
return(result);
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
/*
* Must be at least three arguments:
* - delimiter Delimiter for joining text arguments
* - ignoreEmpty If true, empty strings will be ignored in the join
* - text1 First value to be evaluated as text and joined
* - text2, etc. Optional additional values to be evaluated and joined
*/
// Make sure we have at least one text value, and at most 252 text values, as documented at:
// https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c?ui=en-us&rs=en-us&ad=us
if (args.Length < 3 || args.Length > 254)
{
return(ErrorEval.VALUE_INVALID);
}
int srcRowIndex = ec.RowIndex;
int srcColumnIndex = ec.ColumnIndex;
try
{
// Get the delimiter argument
List <ValueEval> delimiterArgs = GetValues(args[0], srcRowIndex, srcColumnIndex, true);
// Get the boolean ignoreEmpty argument
ValueEval ignoreEmptyArg = OperandResolver.GetSingleValue(args[1], srcRowIndex, srcColumnIndex);
bool ignoreEmpty = (bool)OperandResolver.CoerceValueToBoolean(ignoreEmptyArg, false);
// Get a list of string values for each text argument
List <string> textValues = new List <string>();
for (int i = 2; i < args.Length; i++)
{
List <ValueEval> textArgs = GetValues(args[i], srcRowIndex, srcColumnIndex, false);
foreach (ValueEval textArg in textArgs)
{
String textValue = OperandResolver.CoerceValueToString(textArg);
// If we're not ignoring empty values or if our value is not empty, add it to the list
if (!ignoreEmpty || (textValue != null && textValue.Length > 0))
{
textValues.Add(textValue);
}
}
}
// Join the list of values with the specified delimiter and return
if (delimiterArgs.Count == 0)
{
return(new StringEval(String.Join("", textValues)));
}
else if (delimiterArgs.Count == 1)
{
String delimiter = LaxValueToString(delimiterArgs[0]);
return(new StringEval(String.Join(delimiter, textValues)));
}
else
{
//https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
//see example 3 to see why this is needed
List <string> delimiters = new List <string>();
foreach (ValueEval delimiterArg in delimiterArgs)
{
delimiters.Add(LaxValueToString(delimiterArg));
}
StringBuilder sb = new StringBuilder();
for (int i = 0; i < textValues.Count; i++)
{
if (i > 0)
{
int delimiterIndex = (i - 1) % delimiters.Count;
sb.Append(delimiters[delimiterIndex]);
}
sb.Append(textValues[i]);
}
return(new StringEval(sb.ToString()));
}
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}