Things You Didn't Know About Spread for Windows Forms: The Formula Provider Control

If you have done any development with Spread for Windows Forms you've probably come to realize by now that there is so much packed into this product that you may never fully grasp what all it can do. With that in mind we've decided to start blogging on some of the lesser known features that you might not yet have uncovered and show you some more of what Spread can do for you.

For our first blog we decided to talk about the Formula Provider control. Now if this is the first time you have heard about the Formula Provider control don't feel bad because you aren't the only one. Spread for Windows Forms is full of these little surprises. When I was first approached to write this blog posting I believe my response was "The Formula what control?"

So what then exactly is the Formula Provider control? The Formula Provider is a component which allows us to create a Formula property as an extended property for controls. After the formula expression is assigned, it’ll be calculated automatically. Now what does that mean exactly? Well, let's take a look by walking through a simple project, so if you haven't already started Visual Studio to look for this mysterious component do so now. If you've already installed Spread for Windows Forms you should be able to locate the Formula Provider control in the Spread Components group. Drag both a Spread and a Formula Provider to your form. Since the Formula Provider is a non-GUI control it will not display on your form directly. However, what you should now notice in the property grid for the Spread control are two new properties, Formula and FormulaTriggerEvent. You should also drag a NumericUpDown control to your form and initialize it as follows:

NumericUpDown1.Increment = .25

NumericUpDown1.Minimum = .25

NumericUpDown1.DecimalPlaces = 2

NumericUpDown1.Maximum = 4

NumericUpDown1.Value = 1

FpSpread1.Height = 200

FpSpread1.Width = 400

Now the idea for this example is simply to alter the Spread's ZoomFactor and its dimensions based on the value of the NumericUpDown control. This is a simple and fairly common feature you might have added to one of your projects already. To accomplish this with the Formula Provider control all we need to do is set the Formula property in the property grid for the Spread control to the following:

ZoomFactor=NumericUpDown1.Value

What you should notice now is an arrow drawn from the NumericUpDown control to the Spread control signifying that the two controls are bound together. Compile and run your project. What you should see is that when you change the value in the NumericUpDown control the Spread will zoom in and out.

The other property added to the Spread was the FormulaTriggerEvent. This property specifies the event on the control which will trigger Formula recalculations. Each control has a Default event which will trigger the recalculation. For the NumericUpDown control that is the ValueChanged event. But suppose we didn't want the Formula to be recalculated while the mouse is held down over the spin buttons in the NumericUpDown control? Simple enough. Just go to the property grid for the NumericUpDown control and changed the value of the FormulaTriggerEvent to MouseUp. This tells the Formula Provider that formulas dependent on the NumericUpDown control should not recalculate until the mouse is released over the NumericUpDown control. If you now compile and run your project you should notice that the Spread's ZoomFactor does not change until you stop spinning the NumericUpDown control.

Now up to this point we have only worked on changing the ZoomFactor for the Spread but have not yet altered its dimensions. Here is where we get to really show off what this control can do. If we go back to the property grid for Spread and look at the Formula property added by the Formula Provider control we see how we effected the change to the ZoomFactor property but how would we alter multiple properties at once? And how would we calculate a Height and Width for the Spread based on the value of the NumericUpDown control?

What is so powerful about the Formula Provider control is that it has access to the FarPoint CalcEngine. So in writing out our Formulas we can use Spread's CalcEngine formulas, custom names and even custom functions to accomplish our tasks. So let's alter our Formula now to take advantage of this new found knowledge.

Change the Formula property in the property grid for Spread from

ZoomFactor=NumericUpDown1.Value

to

ZoomFactor=NumericUpDown1.Value;Width=Product(START_WIDTH,NumericUpDown1.Value);Height=Product(START_HEIGHT,NumericUpDown1.Value)

Upon closer inspection we see that we now are modifying three properties of the Spread control, ZoomFactor, Width and Height. Each property affected is separated by a semi-colon in the formula string . We are also using two custom names START_WIDTH and START_HEIGHT which we will need to define later on. Finally, we are using the Product formula from the Spread CalcEngine formulas to calculate the Width and Height.

Before testing our project we will need to add some code in our Form_Load and in one new event we need to add for the Formula Provider. First, lets go to the Form_Load event and add the following code:

FormulaProvider1.BeginInit()

FormulaProvider1.AddQueryValueName("START_HEIGHT")

FormulaProvider1.AddQueryValueName("START_WIDTH")

FormulaProvider1.EndInit()

This code will add the custom names, START_HEIGHT and START_WIDTH into the Formula Provider so they can be queried as needed. The BeginInit and EndInit methods must be used when adding any custom values or functions.

We will also need to handle the QueryValue event for the FormulaProvider control so we can supply the Formula Provider with the values for our custom names. Add the following code.

Private Sub FormulaProvider1_QueryValue(ByVal sender As Object, ByVal arg As FarPoint.Win.Spread.QueryValueEventArgs) Handles FormulaProvider1.QueryValue

If arg.CustomName = "START_HEIGHT" Then

arg.Result = 200

ElseIf arg.CustomName = "START_WIDTH" Then

arg.Result = 400

End If

End Sub

Now if we compile and run the project we should see that our control sizes along with the ZoomFactor whenever the NumericUpDown control's value is changed.

So there you have it. The Formula Provider control. A nifty little component tucked away in your toolbox and dusted off courtesy of FarPoint Technologies. Keep an eye out on this blog in the future for more tips on some of the lesser known features of Spread for Windows Forms.