Class CalcQuickBase

A class that allows you to quickly add calculation support for controls on a form, or usercontrol.

Inheritance

System.Object

CalcQuickBase

Implements

ISheetData

ICalcData

System.IDisposable

Namespace: Syncfusion.XlsIO.Calculate

Assembly: Syncfusion.XlsIO.NET.dll

Syntax

public class CalcQuickBase : Object, ISheetData, ICalcData, IDisposable

Remarks

To use CalcQuick, you instantiate an instance of the class. Then just by indexing the class object with string names to identify a formula object, you can have calculation support in your form. Alternatively, you can add a collection of Control-derived objects and the CalcQuick object will bind the Control.Text property allowing you to use the Control/Name property to reference other controls in a formula.

Examples

Here is code that uses three TextBoxes, the first showing a value for an angle in degrees, and the other two displaying the sine and cosine of this angle. In this code, the calculations are done on the click of a button:

    CalcQuick calculator = null;
       private void AngleForm_Load(object sender, System.EventArgs e)
       {
           //TextBox Angle = new TextBox();
           this.Angle.Name = "Angle";
           this.Angle.Text = "30";
           //cosTB = new TextBox();
           this.cosTB.Name = "cosTB";
           this.cosTB.Text = "= cos([Angle] * pi() / 180) ";
           //sinTB = new TextBox();
           this.sinTB.Name = "sinTB";
           this.sinTB.Text = "= sin([Angle] * pi() / 180) ";
           // Instantiate the CalcQuick object:
           this.calculator = new CalcQuick();
       }
       // Perform a manual calculation:
       private void ComputeButton_Click(object sender, System.EventArgs e)
       {
           // Let the calculator know the values/formulas
           // by using an indexer on the calculator object.
           // Here we are using the TextBox.Name as the indexer key
           // provided to the calculator object. This is not required.
           // The only restriction for the indexer key values is that they
           // be unique nonempty strings:
           this.calculator["Angle"] = this.Angle.Text;
           this.calculator["cosTB"] = this.cosTB.Text;
           this.calculator["sinTB"] = this.sinTB.Text;
           // Mark the calculator dirty:
           this.calculator.SetDirty();
           // Now as the values are retrieved from the calculator, they
           // will be the newly calculated values:
           this.cosTB.Text = this.calculator["cosTB"];
           this.sinTB.Text = this.calculator["sinTB"];
       }

Dim calculator As CalcQuick = Nothing
   Private Sub AngleForm_Load(sender As Object, e As System.EventArgs)
       'TextBox Angle = new TextBox();
       Me.Angle.Name = "Angle"
       Me.Angle.Text = "30"
       'cosTB = new TextBox();
       Me.cosTB.Name = "cosTB"
       Me.cosTB.Text = "= cos([Angle] * pi() / 180) "

       'sinTB = new TextBox();
       Me.sinTB.Name = "sinTB"
       Me.sinTB.Text = "= sin([Angle] * pi() / 180) "

       'Instantiate the CalcQuick object:
       Me.calculator = New CalcQuick()
       End Sub 'AngleForm_Load

   'Perform a manual calculation:
   Private Sub ComputeButton_Click(sender As Object, e As System.EventArgs)
       'Let the calculator know the values/formulas
       'by using an indexer on the calculator object.
       'Here we are using the TextBox.Name as the indexer key
       'provided to the calculator object. This is not required.
       'The only restriction for the indexer key values is that they
       'be unique nonempty strings:
       Me.calculator("Angle") = Me.Angle.Text
       Me.calculator("cosTB") = Me.cosTB.Text
       Me.calculator("sinTB") = Me.sinTB.Text

       'Mark the calculator dirty:
       Me.calculator.SetDirty()

       'Now as the values are retrieved from the calculator, they
       'will be the newly calculated values:
       Me.cosTB.Text = Me.calculator("cosTB")
       Me.sinTB.Text = Me.calculator("sinTB")
   End Sub 'ComputeButton_Click

Here is code that uses the same three TextBoxes as above, but this time the code is set up to automatically compute things as you change the value in the Angle TextBox. There is no longer a need for a button handler to trigger setting / getting values.

    CalcQuick calculator = null;

       private void AngleForm_Load(object sender, System.EventArgs e)
       {
           //TextBox Angle = new TextBox();
           this.Angle.Name = "Angle";
           this.Angle.Text = "30";

           //cosTB = new TextBox();
           this.cosTB.Name = "cosTB";
           this.cosTB.Text = "= cos([Angle] * pi() / 180) ";

           //sinTB = new TextBox();
           this.sinTB.Name = "sinTB";
           this.sinTB.Text = "= sin([Angle] * pi() / 180) ";

           // Instantiate the CalcQuick object:
           this.calculator = new CalcQuick();
       }

       // Perform a manual calculation:
       private void ComputeButton_Click(object sender, System.EventArgs e)
       {
           // Let the calculator know the values/formulas
           // by using an indexer on the calculator object.
           // Here we are using the TextBox.Name as the indexer key
           // provided to the calculator object. This is not required.
           // The only restriction for the indexer key values is that they
           // be unique nonempty strings:
           this.calculator["Angle"] = this.Angle.Text;
           this.calculator["cosTB"] = this.cosTB.Text;
           this.calculator["sinTB"] = this.sinTB.Text;

           // Mark the calculator dirty:
           this.calculator.SetDirty();

           // Now as the values are retrieved from the calculator, they
           // will be the newly calculated values:
           this.cosTB.Text = this.calculator["cosTB"];
           this.sinTB.Text = this.calculator["sinTB"];
       }

Dim calculator As CalcQuick = Nothing

   Private Sub AngleForm_Load(sender As Object, e As System.EventArgs)
       'TextBox Angle = new TextBox();
       Me.Angle.Name = "Angle"
       Me.Angle.Text = "30"

       'cosTB = new TextBox();
       Me.cosTB.Name = "cosTB"
       Me.cosTB.Text = "= cos([Angle] * pi() / 180) "

       'sinTB = new TextBox();
       Me.sinTB.Name = "sinTB"
       Me.sinTB.Text = "= sin([Angle] * pi() / 180) "

       'Instantiate the CalcQuick object:
       Me.calculator = New CalcQuick()
       End Sub 'AngleForm_Load

   'Perform a manual calculation:
   Private Sub ComputeButton_Click(sender As Object, e As System.EventArgs)
       'Let the calculator know the values/formulas
       'by using an indexer on the calculator object.
       'Here we are using the TextBox.Name as the indexer key
       'provided to the calculator object. This is not required.
       'The only restriction for the indexer key values is that they
       'be unique nonempty strings:
       Me.calculator("Angle") = Me.Angle.Text
       Me.calculator("cosTB") = Me.cosTB.Text
       Me.calculator("sinTB") = Me.sinTB.Text

       'Mark the calculator dirty:
       Me.calculator.SetDirty()

       'Now as the values are retrieved from the calculator, they
       'will be the newly calculated values:
       Me.cosTB.Text = Me.calculator("cosTB")
       Me.sinTB.Text = Me.calculator("sinTB")
   End Sub 'ComputeButton_Click

Constructors

CalcQuickBase()

Default constructor:

Declaration

public CalcQuickBase()

Remarks

Use this constructor when you want to have several CalcQuick objects that access the same static members of the CalcEngine.

CalcQuickBase(Boolean)

Constructor that resets the CalcEngine object.

Declaration

public CalcQuickBase(bool resetStaticMembers)

Parameters

Type	Name	Description
System.Boolean	resetStaticMembers	Indicates whether the static members of the CalcEngine class will be cleared.

Fields

ignoreChanges

Returns the value as false

Declaration

protected bool ignoreChanges

Field Value

Type
System.Boolean

Properties

AutoCalc

A property that gets/sets the auto calculation mode of the CalcQuick.

Declaration

public bool AutoCalc { get; set; }

Property Value

Type
System.Boolean

Remarks

By default, the CalcQuick will not update other values when you change a FormulaInfo object. By default, you explicitly call SetDirty() of the CalcQuick instance to force calculations to be done the next time they are required. Setting AutoCalc to True tells the CalcQuick to maintain the dependency information necessary to automatically update dependent formulas when values that affect these formulas change.

CheckKeys

Gets or sets whether formulas should be checked for syntax during key substitutions. Default is true.

Declaration

public bool CheckKeys { get; set; }

Property Value

Type
System.Boolean

Remarks

Prior to version 4.4, no syntax checking was performed during the initial parsing process of substituting for keys (variable names enclosed in square brackets). This early syntax checking support has been added to catch cases where a keys was not preceded (or followed) properly in the formula. This CheckKeys property is available for backward compatibility. To maintain the exact parsing algorithm found in versions prior to 4.4, set this property to false.

ControlModifiedFlags

Maintains a set of modified flags indicating whether any control has had a value changed.

Declaration

protected Dictionary<object, object> ControlModifiedFlags { get; }

Property Value

Type
System.Collections.Generic.Dictionary<System.Object, System.Object>

DataStore

Maintains a collection of FormulaInfo objects.

Declaration

protected FormulaInfoHashtable DataStore { get; }

Property Value

Type
FormulaInfoHashtable

Remarks

This Hashtable serves as the data store for the CalcQuick instance. The keys are the strings used to identify formulas and the values are FormulaInfo objects that hold the information on each formula or value.

DisposeEngineResource

Determines whether the CalcEngine object of this CalcQuick should be disposed on disposing this object.

Default value is true.

Declaration

public bool DisposeEngineResource { get; set; }

Property Value

Type
System.Boolean

Engine

A read-only property that gets the reference to the CalcEngine object being used by this CalcQuick instance.

Declaration

public CalcEngine Engine { get; }

Property Value

Type
CalcEngine

FormulaCharacter

A property that gets/sets character by which string starts with, are treated as formulas when indexing a CalcQuick object.

Declaration

public char FormulaCharacter { get; set; }

Property Value

Type
System.Char

Remarks

If you use the technique of indexing the CalcQuick object to set a varaible value, then you indicate that the value should be a formula by starting the string with this character. If you do not want to require your formulas to start with this character, then you will not be able to use the indexing technique. Instead, you will have to call ParseAndCompute directly to handle formulas not starting with this character.

Item[String]

Gets / sets formula values for CalcQuick.

Declaration

public string this[string key] { get; set; }

Parameters

Type	Name	Description
System.String	key	The indexer used to identify the formula.

Property Value

Type
System.String

Remarks

Using an indexer on the CalcQuick instance is the primary method of setting a value to be used in a CalcQuick object. The string used as the indexer is the key that you use to reference this formula value in other formulas used in this CalcQuick instance.

KeyToRowsMap

Maintains a mapping between the string key and the row used in a CalcSheet to identify a FormulaInfo object.

Declaration

protected Dictionary<object, object> KeyToRowsMap { get; }

Property Value

Type
System.Collections.Generic.Dictionary<System.Object, System.Object>

KeyToVectors

Maintains a mapping between the string key and a vector of numbers entered using a brace expression.

Declaration

protected Dictionary<object, object> KeyToVectors { get; }

Property Value

Type
System.Collections.Generic.Dictionary<System.Object, System.Object>

NameToControlMap

Maintains a mapping between the string key and the control which is being used to identify a FormulaInfo object.

Declaration

protected Dictionary<object, object> NameToControlMap { get; }

Property Value

Type
System.Collections.Generic.Dictionary<System.Object, System.Object>

RowsToKeyMap

Maintains a mapping between the row used in a CalcSheet and the string key used to identify a FormulaInfo object.

Declaration

protected Dictionary<object, object> RowsToKeyMap { get; }

Property Value

Type
System.Collections.Generic.Dictionary<System.Object, System.Object>

ThrowCircularException

Gets / sets whether the CalcQuick should throw an exception when a circular calculation is encountered.

Declaration

public bool ThrowCircularException { get; set; }

Property Value

Type
System.Boolean

Remarks

If this property is True, the CalcQuick will throw an exception when it detects a circular calculation. If ThrowCircularException is False, then no exception is thrown and the calculation will loop recursively until Engine.MaximumRecursiveCalls is exceeded.

Methods

add_ValueChanged(ValueChangedEventHandler)

Declaration

public void add_ValueChanged(ValueChangedEventHandler value)

Parameters

Type	Name	Description
ValueChangedEventHandler	value

add_ValueSet(QuickValueSetEventHandler)

Declaration

public void add_ValueSet(QuickValueSetEventHandler value)

Parameters

Type	Name	Description
QuickValueSetEventHandler	value

CreateEngine()

Creates the CalcEngine object used by this CalQuick object.

Declaration

public virtual CalcEngine CreateEngine()

Returns

Type	Description
CalcEngine	Returns an instance of a CalcEngine object.

Remarks

You can override this method and return a derived CalcEngine object use by the derived CalcQuick object.

Dispose()

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

Declaration

public void Dispose()

GetColumnCount()

This API supports the .NET Framework infrastructure and is not intended to be used directly from your code

Declaration

public int GetColumnCount()

Returns

Type
System.Int32

GetFirstColumn()

Gets the first column index.

Declaration

public int GetFirstColumn()

Returns

Type	Description
System.Int32	Index of first column

GetFirstRow()

Get the idex of the first row in UsedRange

Declaration

public int GetFirstRow()

Returns

Type	Description
System.Int32	index of first row

GetFormula(String)

A method that returns the formula string associated with the key passed in from a FormulaInfo object.

Declaration

public string GetFormula(string key)

Parameters

Type	Name	Description
System.String	key	The Hashtable key associated with the FormulaInfo object.

Returns

Type	Description
System.String	The formula string may be the empty string if no formula is stored with this key.

GetLastColumn()

Gets the last column index / column count.

Declaration

public int GetLastColumn()

Returns

Type	Description
System.Int32	Index of last column

GetLastRow()

get the index of the last row in UsedRange

Declaration

public int GetLastRow()

Returns

Type	Description
System.Int32	index of last row

GetRowCount()

This API supports the .NET Framework infrastructure and is not intended to be used directly from your code

Declaration

public int GetRowCount()

Returns

Type
System.Int32

GetValueRowCol(Int32, Int32)

A method to get the value of the cell referred. For internal CalcQuick use only.

Declaration

public object GetValueRowCol(int row, int col)

Parameters

Type	Name	Description
System.Int32	row	Row index.
System.Int32	col	Column index.

Returns

Type	Description
System.Object	(row, col) data.

Remarks

CalcQuick does not expose a (row, col) data access model. But since CalcEngine requires such a model, CalcQuick uses a row, col access model internally, but only exposes the formula Key model to access values.

InitCalcQuick(Boolean)

Initializes any structures needed by this instance.

Declaration

protected void InitCalcQuick(bool resetStaticMembers)

Parameters

Type	Name	Description
System.Boolean	resetStaticMembers	Indicates whether the static members of the CalcEngine class will be cleared.

ParseAndCompute(String)

A method that parses and computes a well-formed algebraic expression passed in.

Declaration

public string ParseAndCompute(string formulaText)

Parameters

Type	Name	Description
System.String	formulaText	The text of the formula.

Returns

Type	Description
System.String	The computed value.

Remarks

You would use this method if you have a formula string which contains only constants or library function references. Such formulas do not depend upon other values. If you have registered a variable through an indexer, then that variable can be used in a formula expression passed into this method.

RefreshAllCalculations()

A method that recompute any formulas stored in the CalcQuick instance.

Declaration

public void RefreshAllCalculations()

Remarks

This method only has is used when AutoCalc is False. It loops through all FormulaInfo objects stored in the CalcQuick object and recomputes any formulas.

remove_ValueChanged(ValueChangedEventHandler)

Declaration

public void remove_ValueChanged(ValueChangedEventHandler value)

Parameters

Type	Name	Description
ValueChangedEventHandler	value

remove_ValueSet(QuickValueSetEventHandler)

Declaration

public void remove_ValueSet(QuickValueSetEventHandler value)

Parameters

Type	Name	Description
QuickValueSetEventHandler	value

ResetKeys()

A method to reset all the keys registered with CalcQuickBase object.

Declaration

public void ResetKeys()

SetDirty()

A method to force all calculations to be performed the next time the CalcQuick object is accessed with an indexer requesting the value.

Declaration

public void SetDirty()

Remarks

Each FormulaInfo object contained in the CalcQuick instance has a calculation index that is checked any time the computed value is needed. If this index is current, no calculation is done, and the last computed value is returned. If this index is not current, the calculation is redone before the value is returned. Calling this method guarantees that no FormulaInfo object's calculation indexes will be current.

SetValueRowCol(Object, Int32, Int32)

A method to set value to the specified cell. For internal CalcQuick use only.

Declaration

public void SetValueRowCol(object value, int row, int col)

Parameters

Type	Name	Description
System.Object	value
System.Int32	row
System.Int32	col

TryParseAndCompute(String)

A method that parses and computes a well-formed algebraic expression passed in.

Declaration

public string TryParseAndCompute(string formulaText)

Parameters

Type	Name	Description
System.String	formulaText	The text of the formula.

Returns

Type	Description
System.String	The computed value.

Remarks

You would use this method if you have a formula string which contains only constants or library function references. Such formulas do not depend upon other values. If you have registered a variable through an indexer, then that variable can be used in a formula expression passed into this method. This method will return the Exception text if an exception is thrown during the computation.

UpdateDependencies(String)

Loops through and updates all formula items that depend on the FormulaInfo object pointed to by the key.

Declaration

public void UpdateDependencies(string key)

Parameters

Type	Name	Description
System.String	key	Identifies FormulaInfo object that triggered the update.

WireParentObject()

A method that wires the ParentObject after the CalcQuick object is created. For internal CalcQuick use only.

Declaration

public void WireParentObject()

Events

ValueChanged

For internal CalcQuick use only.

Declaration

public event ValueChangedEventHandler ValueChanged

Event Type

Type
ValueChangedEventHandler

ValueSet

Occurs when one of the FormulaInfo objects being maintained by the CalcQuick instance has changed.

Declaration

public event QuickValueSetEventHandler ValueSet

Event Type

Type
QuickValueSetEventHandler

Implements

ISheetData

ICalcData

System.IDisposable