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); }