/**
* For a given database returns the column number for a column heading.
*
* @param db Database.
* @param name Column heading.
* @return Corresponding column number.
* @If it's not possible to turn all headings into strings.
*/
private static int GetColumnForString(AreaEval db, String name)
{
int resultColumn = -1;
int width = db.Width;
for (int column = 0; column < width; ++column)
{
ValueEval columnNameValueEval = ResolveReference(db, 0, column);
if (columnNameValueEval is BlankEval)
{
continue;
}
if (columnNameValueEval is ErrorEval)
{
continue;
}
String columnName = OperandResolver.CoerceValueToString(columnNameValueEval);
if (name.Equals(columnName))
{
resultColumn = column;
break;
}
}
return(resultColumn);
}
private static Double evaluateValue(ValueEval arg, int srcRowIndex, int srcColumnIndex)
{
ValueEval veText = OperandResolver.GetSingleValue(arg, srcRowIndex, srcColumnIndex);
String strText1 = OperandResolver.CoerceValueToString(veText);
return(OperandResolver.ParseDouble(strText1));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval inumberVE)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(inumberVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string iNumber = OperandResolver.CoerceValueToString(veText1);
//Matcher m = COMPLEX_NUMBER_PATTERN.matcher(iNumber);
//bool result = m.matches();
System.Text.RegularExpressions.Match m = COMPLEX_NUMBER_PATTERN.Match(iNumber);
bool result = m.Success && m.Groups[0].Length > 0;
string imaginary = "";
if (result == true)
{
string imaginaryGroup = m.Groups[5].Value;
bool hasImaginaryPart = imaginaryGroup.Equals("i") || imaginaryGroup.Equals("j");
if (imaginaryGroup.Length == 0)
{
return(new StringEval(Convert.ToString(0)));
}
if (hasImaginaryPart)
{
string sign = "";
string imaginarySign = m.Groups[(GROUP3_IMAGINARY_SIGN)].Value;
if (imaginarySign.Length != 0 && !(imaginarySign.Equals("+")))
{
sign = imaginarySign;
}
string groupImaginaryNumber = m.Groups[(GROUP4_IMAGINARY_INTEGER_OR_DOUBLE)].Value;
if (groupImaginaryNumber.Length != 0)
{
imaginary = sign + groupImaginaryNumber;
}
else
{
imaginary = sign + "1";
}
}
}
else
{
return(ErrorEval.NUM_ERROR);
}
return(new StringEval(imaginary));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval inumberVE)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(inumberVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string iNumber = OperandResolver.CoerceValueToString(veText1);
System.Text.RegularExpressions.Match m = Imaginary.COMPLEX_NUMBER_PATTERN.Match(iNumber);
//bool result = m.matches();
bool result = m.Success && !string.IsNullOrEmpty(m.Groups[0].Value);
string real = "";
if (result == true)
{
string realGroup = m.Groups[(2)].Value;
bool hasRealPart = realGroup.Length != 0;
if (realGroup.Length == 0)
{
return(new StringEval(Convert.ToString(0)));
}
if (hasRealPart)
{
string sign = "";
string realSign = m.Groups[(Imaginary.GROUP1_REAL_SIGN)].Value;
if (realSign.Length != 0 && !(realSign.Equals("+")))
{
sign = realSign;
}
string groupRealNumber = m.Groups[(Imaginary.GROUP2_IMAGINARY_INTEGER_OR_DOUBLE)].Value;
if (groupRealNumber.Length != 0)
{
real = sign + groupRealNumber;
}
else
{
real = sign + "1";
}
}
}
else
{
return(ErrorEval.NUM_ERROR);
}
return(new StringEval(real));
}
static StringEval ConvertToStringEval(ValueEval eval)
{
if (eval is StringEval)
{
return((StringEval)eval);
}
String sv = OperandResolver.CoerceValueToString(eval);
return(new StringEval(sv));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE)
{
String octal = OperandResolver.CoerceValueToString(numberVE);
try
{
return(new NumberEval(BaseNumberUtils.ConvertToDecimal(octal, OCTAL_BASE, MAX_NUMBER_OF_PLACES)));
}
catch (ArgumentException)
{
return(ErrorEval.NUM_ERROR);
}
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE)
{
String number = OperandResolver.CoerceValueToString(numberVE);
if (number.Length > 10)
{
return(ErrorEval.NUM_ERROR);
}
String unsigned;
//If the leftmost bit is 0 -- number is positive.
bool isPositive;
if (number.Length < 10)
{
unsigned = number;
isPositive = true;
}
else
{
unsigned = number.Substring(1);
isPositive = number.StartsWith("0");
}
String value;
int sum;
if (isPositive)
{
//bit9*2^8 + bit8*2^7 + bit7*2^6 + bit6*2^5 + bit5*2^4+ bit3*2^2+ bit2*2^1+ bit1*2^0
sum = getDecimalValue(unsigned);
value = sum.ToString();
}
else
{
//The leftmost bit is 1 -- this is negative number
//Inverse bits [1-9]
String inverted = toggleBits(unsigned);
// Calculate decimal number
sum = getDecimalValue(inverted);
//Add 1 to obtained number
sum++;
value = "-" + sum.ToString();
}
return(new NumberEval(long.Parse(value)));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg1, ValueEval arg2)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(arg1, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText1 = OperandResolver.CoerceValueToString(veText1);
Double number1 = OperandResolver.ParseDouble(strText1);
if (double.IsNaN(number1))
{
return(ErrorEval.VALUE_INVALID);
}
ValueEval veText2;
try
{
veText2 = OperandResolver.GetSingleValue(arg2, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText2 = OperandResolver.CoerceValueToString(veText2);
Double number2 = OperandResolver.ParseDouble(strText2);
if (double.IsNaN(number2))
{
return(ErrorEval.VALUE_INVALID);
}
//int result = new BigDecimal(number1).CompareTo(new BigDecimal(number2));
int result = NumberComparer.Compare(number1, number2);
return(result == 0 ? ONE : ZERO);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0)
{
ValueEval veText;
try
{
veText = OperandResolver.GetSingleValue(arg0, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText = OperandResolver.CoerceValueToString(veText);
Double result = ConvertTextToNumber(strText);
if (Double.IsNaN(result))
{
return(ErrorEval.VALUE_INVALID);
}
return(new NumberEval(result));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval dateTextArg)
{
try
{
String dateText = OperandResolver.CoerceValueToString(
OperandResolver.GetSingleValue(dateTextArg, srcRowIndex, srcColumnIndex));
if (string.IsNullOrEmpty(dateText))
{
return(BlankEval.instance);
}
foreach (Format format in Format.Values())
{
var m = format.pattern.Match(dateText);
if (m.Success)
{
var matchGroups = m.Groups;
List <String> groups = new List <string>();
for (int i = 1; i <= matchGroups.Count; ++i)
{
groups.Add(matchGroups[i].Value);
}
int year = format.hasYear
? int.Parse(groups[format.yearIndex])
: DateTime.Now.Year;
int month = parseMonth(groups[format.monthIndex]);
int day = int.Parse(groups[format.dayIndex]);
return(new NumberEval(DateUtil.GetExcelDate(new DateTime(year, month, day))));
}
}
}
catch (FormatException e)
{
return(ErrorEval.VALUE_INVALID);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(ErrorEval.VALUE_INVALID);
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval text, ValueEval number_times)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(text, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText1 = OperandResolver.CoerceValueToString(veText1);
double numberOfTime = 0;
try
{
numberOfTime = OperandResolver.CoerceValueToDouble(number_times);
}
catch (EvaluationException e)
{
return(ErrorEval.VALUE_INVALID);
}
int numberOfTimeInt = (int)numberOfTime;
StringBuilder strb = new StringBuilder(strText1.Length * numberOfTimeInt);
for (int i = 0; i < numberOfTimeInt; i++)
{
strb.Append(strText1);
}
if (strb.ToString().Length > 32767)
{
return(ErrorEval.VALUE_INVALID);
}
return(new StringEval(strb.ToString()));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval textArg)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(textArg, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String text = OperandResolver.CoerceValueToString(veText1);
if (text.Length == 0)
{
return(ErrorEval.VALUE_INVALID);
}
int code = (int)text[0];
return(new StringEval(code.ToString()));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE)
{
String hex;
if (numberVE is RefEval)
{
RefEval re = (RefEval)numberVE;
hex = OperandResolver.CoerceValueToString(re.GetInnerValueEval(re.FirstSheetIndex));
}
else
{
hex = OperandResolver.CoerceValueToString(numberVE);
}
try
{
return(new NumberEval(BaseNumberUtils.ConvertToDecimal(hex, HEXADECIMAL_BASE, MAX_NUMBER_OF_PLACES)));
}
catch (ArgumentException)
{
return(ErrorEval.NUM_ERROR);
}
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
if (args.Length < 1)
{
return(ErrorEval.VALUE_INVALID);
}
bool isA1style;
String text;
try
{
ValueEval ve = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec
.ColumnIndex);
text = OperandResolver.CoerceValueToString(ve);
switch (args.Length)
{
case 1:
isA1style = true;
break;
case 2:
isA1style = EvaluateBooleanArg(args[1], ec);
break;
default:
return(ErrorEval.VALUE_INVALID);
}
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
return(EvaluateIndirect(ec, text, isA1style));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval number, ValueEval places)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(number, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String strText1 = OperandResolver.CoerceValueToString(veText1);
Double number1 = OperandResolver.ParseDouble(strText1);
//If this number argument is non numeric, this function returns the #VALUE! error value.
if (double.IsNaN(number1))
{
return(ErrorEval.VALUE_INVALID);
}
//If number < -549,755,813,888 or if number > 549,755,813,887, this function returns the #NUM! error value.
if (number1 < MinValue || number1 > MaxValue)
{
return(ErrorEval.NUM_ERROR);
}
int placesNumber = 0;
if (number1 < 0)
{
placesNumber = DEFAULT_PLACES_VALUE;
}
else if (places != null)
{
ValueEval placesValueEval;
try
{
placesValueEval = OperandResolver.GetSingleValue(places, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
String placesStr = OperandResolver.CoerceValueToString(placesValueEval);
Double placesNumberDouble = OperandResolver.ParseDouble(placesStr);
//non numeric value
if (double.IsNaN(placesNumberDouble))
{
return(ErrorEval.VALUE_INVALID);
}
//If this argument Contains a decimal value, this function ignores the numbers to the right side of the decimal point.
placesNumber = (int)placesNumberDouble;
if (placesNumber < 0)
{
return(ErrorEval.NUM_ERROR);
}
}
String hex = "";
if (placesNumber != 0)
{
hex = String.Format("{0:X" + placesNumber + "}", (int)number1);
}
else
{
hex = String.Format("{0:X}", (int)number1);
}
if (number1 < 0)
{
hex = "FF" + hex.Substring(2);
}
return(new StringEval(hex.ToUpper()));
}
protected override String ConvertToString(ValueEval other)
{
return(OperandResolver.CoerceValueToString(other));
}
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());
}
}
private String LaxValueToString(ValueEval eval)
{
return((eval is MissingArgEval) ? "" : OperandResolver.CoerceValueToString(eval));
}
/**
*
*
* @param nameValueEval Must not be a RefEval or AreaEval. Thus make sure resolveReference() is called on the value first!
* @param db
* @return
* @throws EvaluationException
*/
private static int GetColumnForName(ValueEval nameValueEval, AreaEval db)
{
String name = OperandResolver.CoerceValueToString(nameValueEval);
return(GetColumnForString(db, name));
}
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());
}
}
/**
* Checks a row in a database against a condition database.
*
* @param db Database.
* @param row The row in the database to Check.
* @param cdb The condition database to use for Checking.
* @return Whether the row matches the conditions.
* @If references could not be Resolved or comparison
* operators and operands didn't match.
*/
private static bool FullFillsConditions(AreaEval db, int row, AreaEval cdb)
{
// Only one row must match to accept the input, so rows are ORed.
// Each row is made up of cells where each cell is a condition,
// all have to match, so they are ANDed.
int height = cdb.Height;
for (int conditionRow = 1; conditionRow < height; ++conditionRow)
{
bool matches = true;
int width = cdb.Width;
for (int column = 0; column < width; ++column) // columns are ANDed
{
// Whether the condition column matches a database column, if not it's a
// special column that accepts formulas.
bool columnCondition = true;
ValueEval condition = null;
// The condition to apply.
condition = ResolveReference(cdb, conditionRow, column);
// If the condition is empty it matches.
if (condition is BlankEval)
{
continue;
}
// The column in the DB to apply the condition to.
ValueEval targetHeader = ResolveReference(cdb, 0, column);
if (!(targetHeader is StringValueEval))
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
if (GetColumnForName(targetHeader, db) == -1)
{
// No column found, it's again a special column that accepts formulas.
columnCondition = false;
}
if (columnCondition == true)
{ // normal column condition
// Should not throw, Checked above.
ValueEval value = ResolveReference(db, row, GetColumnForName(targetHeader, db));
if (!testNormalCondition(value, condition))
{
matches = false;
break;
}
}
else
{ // It's a special formula condition.
// TODO: Check whether the condition cell contains a formula and return #VALUE! if it doesn't.
if (string.IsNullOrEmpty(OperandResolver.CoerceValueToString(condition)))
{
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
throw new NotImplementedException(
"D* function with formula conditions");
}
}
if (matches == true)
{
return(true);
}
}
return(false);
}
/**
* Test a value against a simple (< > <= >= = starts-with) condition string.
*
* @param value The value to Check.
* @param condition The condition to check for.
* @return Whether the condition holds.
* @If comparison operator and operands don't match.
*/
private static bool testNormalCondition(ValueEval value, ValueEval condition)
{
if (condition is StringEval)
{
String conditionString = ((StringEval)condition).StringValue;
if (conditionString.StartsWith("<"))
{ // It's a </<= condition.
String number = conditionString.Substring(1);
if (number.StartsWith("="))
{
number = number.Substring(1);
return(testNumericCondition(value, Operator.smallerEqualThan, number));
}
else
{
return(testNumericCondition(value, Operator.smallerThan, number));
}
}
else if (conditionString.StartsWith(">"))
{ // It's a >/>= condition.
String number = conditionString.Substring(1);
if (number.StartsWith("="))
{
number = number.Substring(1);
return(testNumericCondition(value, Operator.largerEqualThan, number));
}
else
{
return(testNumericCondition(value, Operator.largerThan, number));
}
}
else if (conditionString.StartsWith("="))
{ // It's a = condition.
String stringOrNumber = conditionString.Substring(1);
if (string.IsNullOrEmpty(stringOrNumber))
{
return(value is BlankEval);
}
// Distinguish between string and number.
bool itsANumber = false;
try
{
int.Parse(stringOrNumber);
itsANumber = true;
}
catch (FormatException)
{ // It's not an int.
try
{
Double.Parse(stringOrNumber);
itsANumber = true;
}
catch (FormatException)
{ // It's a string.
itsANumber = false;
}
}
if (itsANumber)
{
return(testNumericCondition(value, Operator.equal, stringOrNumber));
}
else
{ // It's a string.
String valueString = value is BlankEval ? "" : OperandResolver.CoerceValueToString(value);
return(stringOrNumber.Equals(valueString));
}
}
else
{ // It's a text starts-with condition.
if (string.IsNullOrEmpty(conditionString))
{
return(value is StringEval);
}
else
{
String valueString = value is BlankEval ? "" : OperandResolver.CoerceValueToString(value);
return(valueString.StartsWith(conditionString));
}
}
}
else if (condition is NumericValueEval)
{
double conditionNumber = ((NumericValueEval)condition).NumberValue;
Double?valueNumber = GetNumberFromValueEval(value);
if (valueNumber == null)
{
return(false);
}
return(conditionNumber == valueNumber);
}
else if (condition is ErrorEval)
{
if (value is ErrorEval)
{
return(((ErrorEval)condition).ErrorCode == ((ErrorEval)value).ErrorCode);
}
else
{
return(false);
}
}
else
{
return(false);
}
}
public static String EvaluateStringArg(ValueEval eval, int srcRow, int srcCol)
{
ValueEval ve = OperandResolver.GetSingleValue(eval, srcRow, srcCol);
return(OperandResolver.CoerceValueToString(ve));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval real_num, ValueEval i_num, ValueEval suffix)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(real_num, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
double realNum = 0;
try
{
realNum = OperandResolver.CoerceValueToDouble(veText1);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
ValueEval veINum;
try
{
veINum = OperandResolver.GetSingleValue(i_num, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
double realINum = 0;
try
{
realINum = OperandResolver.CoerceValueToDouble(veINum);
}
catch (EvaluationException)
{
return(ErrorEval.VALUE_INVALID);
}
string suffixValue = OperandResolver.CoerceValueToString(suffix);
if (suffixValue.Length == 0)
{
suffixValue = DEFAULT_SUFFIX;
}
if (suffixValue.Equals(DEFAULT_SUFFIX.ToUpper()) || suffixValue.Equals(SUPPORTED_SUFFIX.ToUpper()))
{
return(ErrorEval.VALUE_INVALID);
}
if (!(suffixValue.Equals(DEFAULT_SUFFIX) || suffixValue.Equals(SUPPORTED_SUFFIX)))
{
return(ErrorEval.VALUE_INVALID);
}
StringBuilder strb = new StringBuilder("");
if (realNum != 0)
{
if (isDoubleAnInt(realNum))
{
strb.Append((int)realNum);
}
else
{
strb.Append(realNum);
}
}
if (realINum != 0)
{
if (strb.Length != 0)
{
if (realINum > 0)
{
strb.Append("+");
}
}
if (realINum != 1 && realINum != -1)
{
if (isDoubleAnInt(realINum))
{
strb.Append((int)realINum);
}
else
{
strb.Append(realINum);
}
}
strb.Append(suffixValue);
}
return(new StringEval(strb.ToString()));
}
public override ValueEval Evaluate(int srcRowIndex, int srcColumnIndex, ValueEval numberVE, ValueEval placesVE)
{
ValueEval veText1;
try
{
veText1 = OperandResolver.GetSingleValue(numberVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string strText1 = OperandResolver.CoerceValueToString(veText1);
Double number = OperandResolver.ParseDouble(strText1);
//If this number argument is non numeric, this function returns the #VALUE! error value.
if (double.IsNaN(number))
{
return(ErrorEval.VALUE_INVALID);
}
//If number < -512 or if number > 512, this function returns the #NUM! error value.
if (number < MinValue || number > MaxValue)
{
return(ErrorEval.NUM_ERROR);
}
int placesNumber;
if (number < 0 || placesVE == null)
{
placesNumber = DEFAULT_PLACES_VALUE;
}
else
{
ValueEval placesValueEval;
try
{
placesValueEval = OperandResolver.GetSingleValue(placesVE, srcRowIndex, srcColumnIndex);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
string placesStr = OperandResolver.CoerceValueToString(placesValueEval);
Double placesNumberDouble = OperandResolver.ParseDouble(placesStr);
//non numeric value
if (double.IsNaN(placesNumberDouble))
{
return(ErrorEval.VALUE_INVALID);
}
//If this argument Contains a decimal value, this function ignores the numbers to the right side of the decimal point.
placesNumber = (int)Math.Floor(placesNumberDouble);
if (placesNumber < 0 || placesNumber == 0)
{
return(ErrorEval.NUM_ERROR);
}
}
string binary = Convert.ToString((int)Math.Floor(number), 2);
if (binary.Length > DEFAULT_PLACES_VALUE)
{
binary = binary.Substring(binary.Length - DEFAULT_PLACES_VALUE);
}
//If DEC2BIN requires more than places characters, it returns the #NUM! error value.
if (binary.Length > placesNumber)
{
return(ErrorEval.NUM_ERROR);
}
return(new StringEval(binary));
}
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec)
{
string decSep = Thread.CurrentThread.CurrentCulture.NumberFormat.NumberDecimalSeparator;
string groupSep = Thread.CurrentThread.CurrentCulture.NumberFormat.NumberGroupSeparator;
String text = null;
Double result = Double.NaN;
ValueEval v1 = null;
ValueEval v2 = null;
ValueEval v3 = null;
try
{
if (args.Length == 1)
{
v1 = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex);
text = OperandResolver.CoerceValueToString(v1);
}
else if (args.Length == 2)
{
v1 = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex);
v2 = OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex);
text = OperandResolver.CoerceValueToString(v1);
decSep = OperandResolver.CoerceValueToString(v2).Substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
}
else if (args.Length == 3)
{
v1 = OperandResolver.GetSingleValue(args[0], ec.RowIndex, ec.ColumnIndex);
v2 = OperandResolver.GetSingleValue(args[1], ec.RowIndex, ec.ColumnIndex);
v3 = OperandResolver.GetSingleValue(args[2], ec.RowIndex, ec.ColumnIndex);
text = OperandResolver.CoerceValueToString(v1);
decSep = OperandResolver.CoerceValueToString(v2).Substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
groupSep = OperandResolver.CoerceValueToString(v3).Substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used.
}
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
if (text == "")
{
text = "0"; //If an empty string ("") is specified as the Text argument, the result is 0.
}
text = text.Replace(" ", ""); //Empty spaces in the Text argument are ignored, even in the middle of the argument. For example, " 3 000 " is returned as 3000.
String[] parts = text.Split(new string[] { decSep }, StringSplitOptions.RemoveEmptyEntries);
String sigPart = "";
String decPart = "";
if (parts.Length > 2)
{
return(ErrorEval.VALUE_INVALID); //If a decimal separator is used more than once in the Text argument, NUMBERVALUE returns the #VALUE! error value.
}
if (parts.Length > 1)
{
sigPart = parts[0];
decPart = parts[1];
if (decPart.Contains(groupSep))
{
return(ErrorEval.VALUE_INVALID); //If the group separator occurs after the decimal separator in the Text argument, NUMBERVALUE returns the #VALUE! error value.
}
sigPart = sigPart.Replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.
text = sigPart + "." + decPart;
}
else if (parts.Length > 0)
{
sigPart = parts[0];
sigPart = sigPart.Replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored.
text = sigPart;
}
// If the Text argument ends in one or more percent signs(%), they are used in the calculation of the result.
//Multiple percent signs are additive if they are used in the Text argument just as they are if they are used in a formula.
//For example, =NUMBERVALUE("9%%") returns the same result (0.0009) as the formula =9%%.
int countPercent = 0;
while (text.EndsWith("%"))
{
countPercent++;
text = text.Substring(0, text.Length - 1);
}
try
{
result = Double.Parse(text);
result = result / Math.Pow(100, countPercent); //If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result.
checkValue(result);
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
catch (Exception)
{
return(ErrorEval.VALUE_INVALID); //If any of the arguments are not valid, NUMBERVALUE returns the #VALUE! error value.
}
return(new NumberEval(result));
}