void CustomFunction_CellReference()
{
FormulaExtension.CustomFunctions["Div200"] = (cell, args) =>
{
// this function requires at least one argument
if (args.Length < 1)
{
return(null);
}
// the first argument must be address in string
if (!(args[0] is string))
{
return(null);
}
var addr = (string)args[0];
if (!CellPosition.IsValidAddress(addr))
{
// the address is not valid
return(null);
}
// get position
CellPosition pos = new CellPosition(addr);
double value = 0;
// try get value from cell
if (unvell.ReoGrid.Utility.CellUtility.TryGetNumberData(cell.Worksheet.GetCellData(pos), out value))
{
// get value is successful
return(value / 200);
}
else
{
// cell value might not a number
return(0);
}
};
// set cell value
worksheet["E2"] = 400;
// set address referece to E2 is ADDRESS(2, 5)
worksheet["F2"] = "=Div200(ADDRESS(2, 5))";
// check result
AssertSame(worksheet["F2"], 2);
}
public static FormulaValue Indirect(Cell cell, FormulaValue[] args)
{
if (args[0].type != FormulaValueType.String)
{
throw new FormulaTypeMismatchException(cell);
}
string address = (string)args[0].value;
if (CellPosition.IsValidAddress(address))
{
var pos = new CellPosition(address);
return(cell == null || cell.Worksheet == null ? null : Evaluator.CreateFormulaValue(cell.Worksheet.GetCell(pos)));
}
else if (RangePosition.IsValidAddress(address))
{
return(new RangePosition(address));
}
else
{
throw new FormulaTypeMismatchException(cell);
}
}
public void ValidRelativeAddress()
{
// cell
AssertEquals(CellPosition.IsValidAddress("x1"), false, "x1");
AssertEquals(CellPosition.IsValidAddress("-x1"), false);
AssertEquals(CellPosition.IsValidAddress("A"), false);
AssertEquals(CellPosition.IsValidAddress("1"), false);
AssertEquals(CellPosition.IsValidAddress("B1:"), false);
AssertEquals(CellPosition.IsValidAddress(":A1"), false);
AssertEquals(CellPosition.IsValidAddress("A1"), true);
AssertEquals(CellPosition.IsValidAddress(" ZZZ777 "), true);
AssertEquals(CellPosition.IsValidAddress("AZHL1048576"), true);
// range address
AssertEquals(RangePosition.IsValidAddress("x1:C1"), false, "x1:C1");
AssertEquals(RangePosition.IsValidAddress("-x1:C2"), false);
AssertEquals(RangePosition.IsValidAddress("A:B"), true);
AssertEquals(RangePosition.IsValidAddress("1"), false);
AssertEquals(RangePosition.IsValidAddress("1:2"), true, "1:2");
AssertEquals(RangePosition.IsValidAddress("1:X2"), false, "1:X2");
AssertEquals(RangePosition.IsValidAddress("-A1:X2"), false);
AssertEquals(RangePosition.IsValidAddress("$A1:X2"), true);
AssertEquals(RangePosition.IsValidAddress("B1:"), false);
AssertEquals(RangePosition.IsValidAddress(":A1"), false);
AssertEquals(RangePosition.IsValidAddress("A1:C3"), true);
AssertEquals(RangePosition.IsValidAddress("A1:A1"), true);
AssertEquals(RangePosition.IsValidAddress(" A1:D5 "), true);
AssertEquals(RangePosition.IsValidAddress("A1:AZHL1048576"), true);
AssertEquals(RangePosition.IsValidAddress("A1"), true);
AssertEquals(RangePosition.IsValidAddress(" A1 "), true);
AssertEquals(RangePosition.IsValidAddress("AZHL1048576 "), true);
}
public static bool TryGetPosFromArgs(Worksheet sheet, object[] args, out CellPosition pos)
{
if (args.Length == 0)
{
pos = CellPosition.Empty;
return(false);
}
else if (args.Length == 1)
{
if (args[0] is CellPosition)
{
pos = (CellPosition)args[0];
return(true);
}
else if (args[0] is string || args[0] is System.Text.StringBuilder)
{
var addressOrName = Convert.ToString(args[0]);
if (CellPosition.IsValidAddress(addressOrName))
{
pos = new CellPosition(addressOrName);
return(true);
}
NamedRange namedRange;
if (sheet.TryGetNamedRange(addressOrName, out namedRange))
{
pos = namedRange.StartPos;
return(true);
}
pos = CellPosition.Empty;
return(false);
}
else if (args[0] is ObjectValue)
{
var obj = (ObjectValue)args[0];
pos = new CellPosition(ScriptRunningMachine.GetIntValue(obj["row"]),
ScriptRunningMachine.GetIntValue(obj["col"]));
return(true);
}
}
else //if (arr.Length == 2)
{
pos = new CellPosition(ScriptRunningMachine.GetIntValue(args[0], 0),
ScriptRunningMachine.GetIntValue(args[1], 0));
return(true);
}
//ReoGridPos pos = ReoGridPos.Empty;
//if (args.Length == 1)
//{
// ObjectValue obj = args[0] as ObjectValue;
// if (obj == null) return ReoGridPos.Empty;
// pos.Row = ScriptRunningMachine.GetIntValue(obj["row"]);
// pos.Col = ScriptRunningMachine.GetIntValue(obj["col"]);
//}
//else
//{
// pos.Row = Convert.ToInt32(args[0]);
// if (args.Length > 1) pos.Col = Convert.ToInt32(args[1]);
//}
//return pos;
pos = CellPosition.Empty;
return(false);
}
