Use custom function to implement INDIRECT in Spread

INDIRECT is an useful function, it returns the reference specified by a text string, and the references are immediately evaluated to display their contents. Currently Spread doesn't support INDIRECT yet, but Spread provides powerful customization ability, you may use custom function to implement a simple INDIRECT function. Here is a sample shows how to do it. Firstly, we need to define a custom function, there is FunctionInfo which is base class of custom function, we create a class named IndirectFunctionInfo, and inherited from FunctionInfo class. [Serializable()] class IndirectFunctionInfo : FunctionInfo And create a constructor with a parameter which type is DefaultSheetDataModel. DefaultSheetDataModel Model; public IndirectFunctionInfo(DefaultSheetDataModel model) { Model = model; } And define some information for the function through override methods, such as name, number of arguments... Note, here needs to return true in IsVolatile method, so that the function will be evaluated immediately when any cell formula in sheet is calculated. public override string Name { get { return "INDIRECT"; } } public override int MinArgs { get { return 1; } } public override int MaxArgs { get { return 1; } } public override bool AcceptsReference(int i) { return i == 0; } public override bool IsVolatile() { return true; } And then implement Evaluate method for calculation of the function. Here uses DefaultSheetDataModel to parse formula string text to an expression. public override object Evaluate(object[] args) { CalcReference range = args[0] as CalcReference; object value = range != null ? range.GetValue(range.Row, range.Column) : args[0]; if (value == null) return null; CellExpression expression = Model.ParseFormula(0, 0, value.ToString()) as CellExpression; if (expression == null) return null; return new CellCalcReference(Model, expression); } Because the function need to return a reference, here we need to create a CalcReference class as return value. So we define a CellCalcReference which implement from CalcReference class, and here use DefaultSheetDataModel to return value of the reference. class CellCalcReference : CalcReference { DefaultSheetDataModel dataModel; CellExpression expression; public CellCalcReference(DefaultSheetDataModel model, CellExpression ex) { dataModel = model; expression = ex; } public override int Column { get { return expression.Column; } } public override int ColumnCount { get { return expression.ColumnCount; } } public override CalcReference GetSource() { return null; } public override object GetValue(int row, int column) { return dataModel.GetValue(row, column); } public override bool IsSubtotal(int row, int column) { return false; } public override int Row { get { return expression.Row; } } public override int RowCount { get { return expression.RowCount; } } } OK, now we done the custom function definition, we may use it in Spread, call AddCustomFunction to add this INDIRECT custom function into Spread, and add formulas into some cells. private void Form1_Load(object sender, EventArgs e) { var sheet = this.fpSpread1.ActiveSheet; sheet.AddCustomFunction(new IndirectFunctionInfo(sheet.Models.Data as DefaultSheetDataModel)); sheet.Cells[0, 0].Value = 987; sheet.Cells[1, 1].Value = 123; sheet.Cells[2, 2].Value = "A1"; sheet.Cells[3, 3].Formula = "INDIRECT(\"B2\")"; sheet.Cells[4, 4].Formula = "INDIRECT(C3)"; } Run the application, you will see D4, E5 shows value of B2 and A1, and you may also change value of A1, B2 to see the formulas calculate immediately. Note, This sample code is for Spread for WinForms, you can also use it in Spread for ASP.NET, because they are same API. Here is sample code you can download and try, and you need to install Spread Studio for .NET before run the sample, you may go to our web site to download trial version.