Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Creating and Using External Variable / Using External Variables with Text Box Control
In This Topic
    Using External Variables with Text Box Control
    In This Topic

    The following example code shows how to create and use external variables with the standard textbox control.

    In the example:

    C#
    Copy Code
    // Creating and defining the external variable
    public class TextBoxExternalVariable : ExternalVariable
    {
        private TextBox _textBox;
        private bool _asInput;
        public TextBoxExternalVariable(TextBox textBox, bool asInput)
        {
            _textBox = textBox;
            if (asInput)
            {
                textBox.TextChanged += TextBox_TextChanged;
            }
        }
        private void TextBox_TextChanged(object sender, EventArgs e)
        {
            Dirty();
        }
       
        protected override bool OnDirtying()
        {
            return !_asInput;
        }
      
        protected override void OnDirtied()
        {
            Refresh();
        }
       
        protected override void EvaluateCore(IEvaluationContext context,
         IValue result)
        {
            string text = _textBox.Text;
            if (!string.IsNullOrEmpty(text) && double.TryParse(text,
                 out double dblValue))
            {
                result.SetValue(dblValue);
            }
            else
            {
                result.SetValue(text);
            }
        }
       
        public void Refresh()
        {
            if (!_asInput)
            {
                _textBox.Text = this.Value.GetText();
            }
        }
    } 
    // Using the external variable with text box control
    private void Form2_Load(object sender, EventArgs e)
    {
     var workbook = fpSpread1.AsWorkbook();
     var activeSheet = workbook.ActiveSheet;
     activeSheet.Cells["A1"].Value = "Factor";
     activeSheet.Cells["B1"].Value = 2;
       
     // Adding Ext. Variable - x referring to textbox1
     workbook.Names.AddExternalVariable("x",
     new TextBoxExternalVariable(textBox1, true));
       
     // Adding Ext. Variable "y" referring to textbox2 with formula "Sheet1!B1 * x"
     var extVariable2 = new TextBoxExternalVariable(textBox2, false);
     workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x");
     extVariable2.Refresh();
    } 
    

    VB
    Copy Code
    'Creating and defining the external variable
    Public Class TextBoxExternalVariable Inherits ExternalVariable
    Private _textBox As TextBox
    Private _asInput As Boolean
    Public Sub New(ByVal textBox As TextBox, ByVal asInput As Boolean)
    _textBox = textBox
    If asInput Then
    textBox.TextChanged += AddressOf TextBox_TextChanged
    End If
    End Sub
    Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
    Dirty()
    End Sub
    Protected Overrides Function OnDirtying() As Boolean
    Return Not _asInput
    End Function
    Protected Overrides Sub OnDirtied()
    Refresh()
    End Sub
    Protected Overrides Sub EvaluateCore(ByVal context As IEvaluationContext, ByVal result As IValue)
    Dim text As String = _textBox.Text
    Dim dblValue As Double = Nothing
    If Not String.IsNullOrEmpty(text) AndAlso Double.TryParse(text, dblValue) Then
    result.SetValue(dblValue)
    Else
    result.SetValue(text)
    End If
    End Sub
    Public Sub Refresh()
    If Not _asInput Then
    _textBox.Text = Me.Value.GetText()
    End If
    End Sub
    End Class    
    
    'Using the external variable with text box control
    Private Sub Form2_Load(ByVal sender As Object, ByVal e As EventArgs)
    Dim workbook = fpSpread1.AsWorkbook()
    Dim activeSheet = workbook.ActiveSheet
    activeSheet.Cells("A1").Value = "Factor"
    activeSheet.Cells("B1").Value = 2
    workbook.Names.AddExternalVariable("x", New TextBoxExternalVariable(textBox1, True))
    Dim extVariable2 = New TextBoxExternalVariable(textBox2, False)
    workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x")
    extVariable2.Refresh()
    End Sub