Skip to main content Skip to footer

Custom Function using Parallel.For to Optimize Returning Array Result

This blog post will demonstrate many useful things:

  1. How to implement a custom function for Spread.NET 12 Windows Forms that works just like the built-in functions in cell formulas
  2. How to make a custom function return an array of values and how to use it in an array formula to show those values in a range of cells
  3. How to make use of Parallel.For safely with Spread.NET 12 Windows Forms to optimize performance
  4. How to port a legacy custom function using FarPoint.CalcEngine.FunctionInfo to a new custom function using GrapeCity.CalcEngine.Function
  5. How to use GrapeCity.Spreadsheet.CalculationEngine to calculate formulas in code, outside the context of a worksheet

The Riemann Zeta Function ζ(s) is the Most Interesting Function in the World

To illustrate the custom function feature of Spread.NET 12 Windows Forms, I wanted to choose a really cool example, so I picked the most awesome function possible, the Riemann Zeta Function ζ(s):

Figure 1 The Riemann Zeta Function ζ(s) converges for all s > 1.

This function is interesting for many reasons – here are just a few:

  1. It can be expressed as an infinite sum or as an infinite product over the prime numbers – this was demonstrated by Leonhard Euler:

Figure 2 Riemann Zeta Function can be expressed as a sum over integers and a product over primes.

  1. Bernhard Riemann used analytic continuation to extend ζ(s) to cover Complex Plane, converges everywhere except for a single pole at x = 1: Figure 3 Riemann Zeta Function expressed using Dirichlet series extends domain to s > 0.

Figure 4 Riemann Zeta Function extended to domain s≠1.

  1. The Riemann Hypothesis is an unproven mathematical conjecture about the zeros of ζ(s) on the line x = ½ – there is a pending $1M Millenium Prize for proving that conjecture.

  2. Voronin's Universality Theorem (which is proven, unlike the Riemann Hypothesis) states that if the Riemann Hypothesis is true, then ζ(s) can approximate any analytical function arbitrarily well somewhere inside the strip between x = ½ and x = 1 – essentially, ζ(s) "contains" all analytic functions (including itself) like a fractal and all possible patterns of continuously varying complex values are represented in that region.

This blog will describe in detail how to create a Spread.NET custom function which calculates the Riemann Zeta Function for a specified set of points along the line x = ½ – where the nontrivial roots are located – and highlight the values calculated that are closest to the roots using a conditional format.

Spread.NET Custom Functions

When you need a formula function that isn't available in Spread.NET, then you can implement a custom function class to calculate that function. In Spread.NET 12 Windows Forms, the base class for custom functions is GrapeCity.CalcEngine.Function. In earlier releases (before version 11), the base class for custom functions was FarPoint.CalcEngine.FunctionInfo, and that class is still supported for backwards compatibility, but it is better to refactor the code for the custom function to use the new Function class instead. This blog will show the ZETA function implemented both ways, to illustrate how easily a custom function class can be ported to Spread.NET 12 Windows Forms.

You can reuse custom functions between projects, and even create libraries of custom functions to use in your Spread.NET workbooks. Creating a custom function requires the following steps:

  1. Decide exactly how you want the function to operate:
    • What are the arguments for the function?
    • Which arguments will accept references, and which will require values?
    • For the arguments which accept references, which types of references are allowed?
      • Will the argument accept only a single-cell reference like INDIRECT()?
      • Will the argument allow a range reference like SUM() and use the number values in the whole range?
      • Will the argument allow a range reference like ABS(), which normally operates only on a single cell, and return an array of values with ABS() applied on each value individually, like the internal releases of Excel 2019 which support "spilling" array values?
  2. Implement the custom function – this requires writing a new class inheriting from GrapeCity.CalcEngine.Function.
  3. Add the function to the workbook using FpSpread.AddCustomFunction:
    [VB] 
    fpSpread1.AddCustomFunction(New ZetaFunction()) 
    
    [C#] 
    fpSpread1.AddCustomFunction(new ZetaFunction()); 
    

The ZETA Custom Function

The example custom function for this blog is focused on calculating the ZETA function values along the critical line x = ½ in search of the non-trivial roots. The calculation uses the equation in Figure 3 above, where the ZETA function is analytically extended to s > 0. This implementation of ZETA requires 4 arguments, with an optional fifth argument:

ZETA(real_part, imaginary_part, count, increment [, iterations=10000]) 

The function returns an array of values with count rows and 2 columns, with the real component of the complex value in the first column and the complex component in the second column.

The real_part argument is the real value component of the complex number s for which ZETA will be computed – this value is always 0.5 since we're looking for the non-trivial root values, but the implementation of the function can work using any value > 0.

The imaginary_part argument is the imaginary component of the complex number s for which ZETA will be computed – this value is specified in a text box (textBoxStart) at the top of the form.

The count argument is the number of ZETA points to calculate and return – this value is specified in a text box (textBoxCount) at the top of the form. The default value for count is 1000. The function is designed to calculate the ZETA function for count points and return the function values as an array of values with count rows and 2 columns. Each row in the array contains a complex number value of ZETA computed for the point, with the real part in the first column and the imaginary part in the second column.

The increment argument is the distance between each ZETA point calculated and determines the resolution of the root search – this value is specified in a text box (textBoxIncrement) at the top of the form. The function is designed to calculate ZETA for the initial complex number s plus increment, then calculate the value for the complex number s + 2 increment, etc. until count* values have been computed. The default value for increment is 0.1 and searches for root values every tenth, and this is recommended for optimal efficiency. Changing the increment will change the resolution of the search – for example, using increment of 0.01 will search for root values every hundredth and thus finding more decimal places in the root value.

The iterations argument is the number of partial sums to compute for the infinite series – this value is specified in a text box (textboxIterations) at the top of the form. The default value for iterations is 10000 and this should be enough to calculate ZETA accurately to 2 decimal places for values of s < 1000. If calculating ZETA to more decimal places (e.g. changing increment to 0.01 or 0.001) or if calculating ZETA for large values of s (> 1000), then it might be necessary to increase iterations to 10000 (this will affect the performance of the calculation). In that case, you can compute ZETA for fewer points by decreasing the value for count in textBoxCount.

The ZetaFunction Class

The ZetaFunction class inherits from GrapeCity.CalcEngine.Function and overrides the protected virtual Evaluate method with logic to handle calculating the ZETA function. This example also implements some logic to track the number of calls to the function and time the calculation. The array formula applied to calculate the ZETA function and produce the table of results is calculated just once to produce the array of results in the table. Since each point calculated in the array is independent of the other points being calculated, the loop calculating the point values can run in parallel, so Parallel.For is used to optimize calculation performance. This requires creating a Task which contains the Parallel.For logic inside a lambda function, and executing the Task synchronously (otherwise you get recurrence and stack overflow).

[VB]
Imports System.Threading
Imports GrapeCity.CalcEngine

Namespace SpreadNETZetaCalculator
        Friend NotInheritable Class ZetaFunction
        Inherits [Function]
        ' keep count of function calls
        Friend Shared cnt As Integer = 0
        ' time calculation
        Friend Shared t1 As DateTime
        Friend Shared t2 As DateTime

        Public Sub New()
            MyBase.New("ZETA", 4, 5, FunctionAttributes.Array)
        End Sub

        Protected Overrides Sub Evaluate(ByVal args As IArguments, ByVal result As IValue)
            cnt += 1 ' increment function call counter
            t1 = DateTime.Now ' start timing evaluation
            Dim l As Integer = 10000 ' default to 10K iterations if not specified
            Dim d As Double = args(0).GetPrimitiveValue(args.EvaluationContext).GetNumber() ' real part
            Dim d1 As Double = args(1).GetPrimitiveValue(args.EvaluationContext).GetNumber() ' imaginary part
            Dim count As Integer = CInt(args(2).GetPrimitiveValue(args.EvaluationContext).GetNumber()) ' number of points to calculate
            Dim incr As Double = args(3).GetPrimitiveValue(args.EvaluationContext).GetNumber() ' increment between points
            ' iterations is optional 
            If args.Count > 4 Then l = CInt(args(4).GetPrimitiveValue(args.EvaluationContext).GetNumber())
            Dim array As IValuesArray = result.CreateValuesArray(CInt(count), 2) ' create array to return values
            Dim arr As Double(,) = New Double(count - 1, 1) {} ' create local array to calculate values (can't set values to IValuesArray from another thread)
            '  do the ZETA calculation in a Task using Parallel.For for better performance
            Dim t As Task = New Task(
                Sub()
                    Parallel.[For](0, count, Sub(i)
                        Dim d2 As Double = 0.0
                        Dim d3 As Double = 0.0
                        Dim d4 As Double = 0.0
                        Dim d5 As Double = 0.0
                        Dim dd1 As Double = d1 + i * incr

                        For l1 As Integer = 1 To l ' calculate Dirichlet Eta series partial sum of l iterations
                            Dim d6 As Double = 2 * l1 - 1
                            Dim d7 As Double = 2 * l1
                            Dim d14 As Double = 0.0
                            Dim d15 As Double = 0.0
                            Dim d16 As Double = 0.0
                            Dim d8 As Double = (dd1 * Math.Log(d6)) Mod 6.2831853071795862
                            Dim d9 As Double = (dd1 * Math.Log(d7)) Mod 6.2831853071795862
                            Dim d10 As Double = -d
                            Dim d11 As Double = Math.Pow(d6, d10)
                            Dim d12 As Double = Math.Pow(d7, d10)
                            d2 += d11 * Math.Cos(d8) - d12 * Math.Cos(d9)
                            d3 += d12 * Math.Sin(d9) - d11 * Math.Sin(d8)
                            d4 += Math.Pow(d6, -0.5) * Math.Cos(d8) - Math.Pow(d7, -0.5) * Math.Cos(d9)
                            d14 = Math.Pow(d7, -0.5) * Math.Sin(d9)
                            d15 = Math.Pow(d6, -0.5)
                            d16 = Math.Sin(d8)
                            d5 += d14 - d15 * d16
                        Next
                        ' calculate Zeta from Eta value
                        Dim d17 As Double = 1.0 - Math.Pow(2, 1.0 - d) * Math.Cos(d1 * Math.Log(2))
                        Dim d18 As Double = Math.Pow(2, 1.0 - d) * Math.Sin(d1 * Math.Log(2))
                        Dim d19 As Double = (d17 * d2 + d18 * d3) / (Math.Pow(d17, 2) + Math.Pow(d18, 2))
                        Dim d20 As Double = (d17 * d3 – d18 * d2) / (Math.Pow(d17, 2) + Math.Pow(d18, 2))
                        Interlocked.Exchange(arr(i, 0), d19)
                        Interlocked.Exchange(arr(i, 1), d20)
                    End Sub)
                End Sub)
            t.RunSynchronously() ' need to run this task synchronously on the UI thread
            ' copy values from arr to the IValuesArray
            For i As Integer = 0 To count - 1
                array(i, 0).SetValue(arr(i, 0))
                array(i, 1).SetValue(arr(i, 1))
            Next
            ' set result -- this is important! If you don't return a result, then the cells remain marked dirty and Evaluate will be called again...
            result.SetValue(array)
            t2 = DateTime.Now
        End Sub
    End Class
End Namespace
 [C#]
using System;
using System.Threading;
using System.Threading.Tasks;
using GrapeCity.CalcEngine;

namespace SpreadNETZetaCalculator
{
    internal sealed class ZetaFunction : Function
    {   // keep count of function calls
        internal static int cnt = 0;
        // time calculation
        internal static DateTime t1;
        internal static DateTime t2;
        public ZetaFunction() : base("ZETA", 4, 5, FunctionAttributes.Array)
        { // minimum args 4, maximum args 5, returns array value
        }
        protected override void Evaluate(IArguments args, IValue result)
        {
            cnt++; // increment function call counter
            t1 = DateTime.Now; // start timing evaluation
            long l = 10000L; // default to 10K iterations if not specified
            // get function arguments
            double d = args[0].GetPrimitiveValue(args.EvaluationContext).GetNumber();  // real part
            double d1 = args[1].GetPrimitiveValue(args.EvaluationContext).GetNumber(); // imaginary part
            long count = (long)args[2].GetPrimitiveValue(args.EvaluationContext).GetNumber(); // number of points to calculate
            double incr = args[3].GetPrimitiveValue(args.EvaluationContext).GetNumber(); // increment between points
            if (args.Count > 4) // iterations is optional
                l = (long)args[4].GetPrimitiveValue(args.EvaluationContext).GetNumber();
            IValuesArray array = result.CreateValuesArray((int)count, 2); // create array to return values
            double[,] arr = new double[count, 2]; // create local array to calculate values (can't set values to IValuesArray from another thread)
            // do the ZETA calculation in a Task using Parallel.For for better performance
            Task t = new Task(delegate
            {
                Parallel.For(0, count, i =>
                    {
                    var d2 = 0.0;
                    var d3 = 0.0;
                    var d4 = 0.0;
                    var d5 = 0.0;
                    var dd1 = d1 + i * incr;
                    for (var l1 = 1; l1 <= l; l1++)
                    { // calculate Dirichlet Eta series partial sum of l iterations
                        var d6 = 2 * l1 - 1;
                        var d7 = 2 * l1;
                        var d14 = 0.0;
                        var d15 = 0.0;
                        var d16 = 0.0;
                        var d8 = (dd1 * Math.Log(d6)) % 6.2831853071795862;
                        var d9 = (dd1 * Math.Log(d7)) % 6.2831853071795862;
                        var d10 = -d;
                        var d11 = Math.Pow(d6, d10);
                        var d12 = Math.Pow(d7, d10);
                        d2 += d11 * Math.Cos(d8) - d12 * Math.Cos(d9);
                        d3 += d12 * Math.Sin(d9) - d11 * Math.Sin(d8);
                        d4 += Math.Pow(d6, -0.5) * Math.Cos(d8) - Math.Pow(d7, -0.5) * Math.Cos(d9);
                        d14 = Math.Pow(d7, -0.5) * Math.Sin(d9);
                        d15 = Math.Pow(d6, -0.5);
                        d16 = Math.Sin(d8);
                        d5 += d14 - d15 * d16;
                    }
                    // calculate Zeta from Eta value
                    var d17 = 1.0 - Math.Pow(2, 1.0 - d) * Math.Cos(d1 * Math.Log(2));
                    var d18 = Math.Pow(2, 1.0 - d) * Math.Sin(d1 * Math.Log(2));
                    var d19 = (d17 * d2 + d18 * d3) / (Math.Pow(d17, 2) + Math.Pow(d18, 2));
                    var d20 = (d17 * d3 – d18 * d2) / (Math.Pow(d17, 2) + Math.Pow(d18, 2));
                    Interlocked.Exchange(ref arr[i, 0], d19);
                    Interlocked.Exchange(ref arr[i, 1], d20);
                });
            });
            t.RunSynchronously();  // need to run this task synchronously on the UI thread
            // copy values from arr to the IValuesArray
            for (int i = 0; i < count; i++)
            {
                array[i, 0].SetValue(arr[i, 0]);
                array[i, 1].SetValue(arr[i, 1]);
            }
            result.SetValue(array); // set result -- this is important! If you don't return a result, then the cells remain marked dirty and Evaluate will be called again...
            t2 = DateTime.Now; // time calculation
        }
    }
}

The override for Evaluate uses the IArguments passed into the function to get the number of arguments specified and to get the value of each argument using IValue.GetPrimitiveValue, specifying the IArguments.EvaluationContext for relative reference resolution and context-sensitive functions. The IValuesArray array is created using IValue.CreateValuesArray on result – note that the logic does not directly call IValuesArray.SetValue inside the Parallel.For code, because calls from other threads are not allowed. Instead the Parallel.For executes synchronously, using Interlocked.Exchange to set the results to a captured temporary array variable, blocks the main UI thread until all calculations are complete, and then safely copies the results from the temporary array of double to array.

At the end of this post, I will show the same custom function implemented using the old FarPoint.CalcEngine.FunctionInfo, which is still supported for backwards compatibility. If you have custom functions using this class created for earlier versions of Spread.NET, then you can use that as a guide to help with porting your custom functions to using the new GrapeCity.CalcEngine.Function class instead.

The Spread.NET Zeta Function Calculator

Figure 5 Spread.NET Zeta Calculator Form.

The main form for the calculator has controls in a panel docked across the top and the spreadsheet control docked below. Simply clicking the Calculate button using the default values in the text box will find the first nontrivial root near the start value 14:

Figure 6 Spread.NET Zeta Calculator Form after calculating using default text box values.

The first column A in the result shows the imaginary values bi along the line x = ½ for which ζ will be calculated. The second and third columns B and C show the calculated result of ζ(0.5+bi), with the real part in column B and the imaginary part in column C. The fourth column D shows these values combined into one imaginary value in column D using the COMPLEX function, and the fifth column E shows the complex absolute value using the IMABS function. The complex absolute value is the distance between the complex value and the origin value 0.

Root values are highlighted with a conditional format when the function value is closer to the origin than increment. After calculating a set of values, the Filter Roots Only button becomes enabled and you can use it to show only the root values:

Figure 7 Spread.NET Zeta Calculator after filtering roots only using default text box values.

The calculator can also perform a custom goal-seek operation to find the root value to 4 decimal places using the calculation engine to manually calculate values for ZETA by selecting a root value (move the active cell to a highlighted row) and using the GoalSeek Root button:

Figure 8 Spread.NET Zeta Calculator performing custom GoalSeek for a root value.

The Form Code

The initialization code handles adding the custom ZetaFunction for calculations using AddCustomFunction, attaching the FormulaTextBox control using Attach, and showing the spreadsheet status bar using StatusBarVisible:

[VB]
        Public Sub New()
            InitializeComponent()
            fpSpread1.AddCustomFunction(New ZetaFunction())
            formulaTextBox1.Attach(fpSpread1)
            fpSpread1.StatusBarVisible = True
        End Sub
[C#]
        public ZetaCalculatorForm()
        {
            InitializeComponent();
            fpSpread1.AddCustomFunction(new ZetaFunction());
            formulaTextBox1.Attach(fpSpread1);
            fpSpread1.StatusBarVisible = true;
        }

A helper function GetInputValues() handles getting the input values from the text box controls:

[VB]
        Private Function GetInputValues(ByRef initval As Double, ByRef incr As Double, ByRef rc As Integer, ByRef iter As Long) As Boolean
            If Not Double.TryParse(textBoxStart.Text, initval) Then
                MessageBox.Show("Please enter a double value for Start Value.")
                textBoxStart.Focus()
                Return False
            End If
            If Not Double.TryParse(textBoxIncrement.Text, incr) Then
                MessageBox.Show("Please enter a double value for Increment.")
                textBoxIncrement.Focus()
                Return False
            End If
            If Not Integer.TryParse(textBoxCount.Text, rc) Then
                MessageBox.Show("Please enter an integer value for Count.")
                textBoxCount.Focus()
                Return False
            End If
            Long.TryParse(textBoxIter.Text, iter)
            Return True
        End Function
[C#]
        private bool GetInputValues(ref double initval, ref double incr, ref int rc, ref long iter)
        {
            if (!double.TryParse(textBoxStart.Text, out initval))
            {
                MessageBox.Show("Please enter a double value for Start Value.");
                textBoxStart.Focus();
                return false;
            }
            if (!double.TryParse(textBoxIncrement.Text, out incr))
            {
                MessageBox.Show("Please enter a double value for Increment.");
                textBoxIncrement.Focus();
                return false;
            }
            if (!int.TryParse(textBoxCount.Text, out rc))
            {
                MessageBox.Show("Please enter an integer value for Count.");
                textBoxCount.Focus();
                return false;
            }
            long.TryParse(textBoxIter.Text, out iter);
            return true;
        }

Another helper function creates the number format string to format the values in the first column to show the number of decimal places used in the increment value:

[VB]
        Private Function GetIncrementNumberFormat() As String
            Dim incr As Double = 0.1
            If Not Double.TryParse(textBoxIncrement.Text, incr) Then Return "0.0"
            Dim sb As New StringBuilder
            sb.Append("0.")
            While incr < 1
                sb.Append("0")
                incr *= 10
            End While
            Return sb.ToString()
        End Function
[C#]
        private string GetIncrementNumberFormat()
        {
            double incr = 0.1;
            if (!double.TryParse(textBoxIncrement.Text, out incr))
                return "0.0";
            StringBuilder sb = new StringBuilder();
            sb.Append("0.");
            while (incr < 1)
            {
                sb.Append("0");
                incr *= 10;
            }
            return sb.ToString();
        }

The event handler for the Calculate button click contains the most important code that generates the table of results. First the input values are retrieved to local variables using GetInputValues:

[VB]
        Private Sub buttonCalculate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles buttonCalculate.Click
            Dim initval As Double = 0.0
            Dim incr As Double = 0.1
            Dim rc As Integer = 1000
            Dim iter As Long = 10000
            If Not GetInputValues(initval, incr, rc, iter) Then Return
[C#]
        private void buttonCalculate_Click(object sender, EventArgs e)
        {
            double initval = 0.0;
            double incr = 0.01;
            int rc = 12000;
            long iter = 10000;
            if (!GetInputValues(ref initval, ref incr, ref rc, ref iter))
                return;

Before starting the calculation, the spreadsheet layout is suspended using SuspendLayout to prevent repainting during the operation, and the cursor is updated to show the wait cursor and form text updated to indicate a calculation is happening:

[VB]
            fpSpread1.SuspendLayout()
            Cursor = Cursors.WaitCursor
            Text = "Calculating, please wait..."
[C#]
            fpSpread1.SuspendLayout();
            Cursor = Cursors.WaitCursor;
            Text = "Calculating, please wait...";

The sheet is initialized for a new calculation with the table headings using Reset, ColumnCount, RowCount, ReferenceStyle, Cell.Text and Cell.HorizontalAlignment:

[VB]
            ' inialize sheet with Zeta calculation table headers
            Dim sheet As SheetView = fpSpread1.ActiveSheet
            sheet.Reset()
            sheet.RowCount = rc + 1
            sheet.ColumnCount = 5
            sheet.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
            sheet.Cells(0, 0).Text = "bi"
            sheet.Cells(0, 1).Text = "Re( ζ(0.5+bi) )"
            sheet.Cells(0, 2).Text = "Im( ζ(0.5+bi) )"
            sheet.Cells(0, 3).Text = "ζ(0.5+bi)"
            sheet.Cells(0, 4).Text = "IMABS( ζ(0.5+bi) )"
            sheet.Cells(0, 0, 0, 4).HorizontalAlignment = CellHorizontalAlignment.Center
[C#]
            // inialize sheet with Zeta calculation table headers
            SheetView sheet = fpSpread1.ActiveSheet;
            sheet.Reset();
            sheet.RowCount = rc + 1;
            sheet.ColumnCount = 5;
            sheet.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            sheet.Cells[0, 0].Text = "bi";
            sheet.Cells[0, 1].Text = "Re( ζ(0.5+bi) )";
            sheet.Cells[0, 2].Text = "Im( ζ(0.5+bi) )";
            sheet.Cells[0, 3].Text = "ζ(0.5+bi)";
            sheet.Cells[0, 4].Text = "IMABS( ζ(0.5+bi) )";
            sheet.Cells[0, 0, 0, 4].HorizontalAlignment = CellHorizontalAlignment.Center;

The table headings use built-in cell styles, which are applied using IRange.ApplyStyle:

[VB]
            ' apply built-in styles for table header cells
            Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
            workbook.ActiveSheet.Cells(0, 0, 0, 4).ApplyStyle(BuiltInStyle.Percent20Accent3)
            workbook.ActiveSheet.Cells(0, 0, 0, 4).ApplyStyle(BuiltInStyle.Heading3)
[C#]
            // apply built-in styles for table header cells
            IWorkbook workbook = fpSpread1.AsWorkbook();
            workbook.ActiveSheet.Cells[0, 0, 0, 4].ApplyStyle(BuiltInStyle.Percent20Accent3);
            workbook.ActiveSheet.Cells[0, 0, 0, 4].ApplyStyle(BuiltInStyle.Heading3);

The values to be calculated are set into the cells in the first column and formatted to show the number of decimal places appropriate for the increment value using IRange.NumberFormat:

[VB]
            ' initialize the values to be calculated
            For r As Integer = 1 To rc
                sheet.Cells(r, 0).Value = initval + incr * r
            Next
            ' format the cells to show the number decimal places appropriate for increment
            fpSpread1.AsWorkbook().Worksheets(0).Cells(1, 0, rc, 0).NumberFormat = GetIncrementNumberFormat()
 [C#]
            // initialize the values to be calculated
            for (int r = 1; r <= rc; r++)
                sheet.Cells[r, 0].Value = initval + incr * r;
            // format the cells to show the number decimal places appropriate for increment
            fpSpread1.AsWorkbook().Worksheets[0].Cells[1, 0, rc, 0].NumberFormat = GetIncrementNumberFormat();

The formula to calculate the ZETA function for the specified set of points is an array formula, set using Cell.FormulaArray, and the formulas to calculate the COMPLEX and IMABS functions are regular formulas set using Cell.Formula:

[VB]
            ' create the formula to calculate the ZETA function along the critical line Re = 0.5 for the
            ' set of points starting with the initial value and using the specified increment and iterations
            Dim formula As String = "ZETA(0.5,RC1," + rc.ToString().Trim() + "," + incr.ToString().Trim() + "," + iter.ToString().Trim() + ")"
            sheet.Cells(1, 1, rc, 2).FormulaArray = formula
            ' apply the COMPLEX function to the two values returned for the real (in column 1) and imaginary (in column 2) parts to get the resulting complex number calculated
            sheet.Cells(1, 3, rc, 3).Formula = "COMPLEX(RC2,RC3)"
            ' apply IMABS function to resulting complex number -- this is equivalent to SQRT(RC2*RC2+RC3*RC3) and computes the length of the complex vector
            ' IMABS values less than increment are considered "close" to a root and highlighted if the value is a local minimum
            sheet.Cells(1, 4, rc, 4).Formula = "IMABS(RC4)"
 [C#]
            // create the formula to calculate the ZETA function along the critical line Re = 0.5 for the
            // set of points starting with the initial value and using the specified increment and iterations
            string formula = "ZETA(0.5,RC1," + rc.ToString().Trim() + "," + incr.ToString().Trim() + "," + iter.ToString().Trim() + ")";
            sheet.Cells[1, 1, rc, 2].FormulaArray = formula;
            // apply the COMPLEX function to the two values returned for the real (in column 1) and imaginary (in column 2) parts to get the resulting complex number calculated
            sheet.Cells[1, 3, rc, 3].Formula = "COMPLEX(RC2,RC3)";
            // apply IMABS function to resulting complex number -- this is equivalent to SQRT(RC2*RC2+RC3*RC3) and computes the length of the complex vector
            // IMABS values less than increment are considered "close" to a root and highlighted if the value is a local minimum
            sheet.Cells[1, 4, rc, 4].Formula = "IMABS(RC4)";

The column widths for the results table are set using SetColumnWidth and GetPreferredColumnWidth, which returns the width required to show all the text in the column:

[VB]
            ' set column widths
            sheet.SetColumnWidth(0, CInt(sheet.GetPreferredColumnWidth(0)))
            sheet.SetColumnWidth(1, 150)
            sheet.SetColumnWidth(2, 150)
            sheet.SetColumnWidth(3, CInt(sheet.GetPreferredColumnWidth(3)))
            sheet.SetColumnWidth(4, CInt(sheet.GetPreferredColumnWidth(4)))
[C#]
            // set column widths
            sheet.SetColumnWidth(0, (int)sheet.GetPreferredColumnWidth(0));
            sheet.SetColumnWidth(1, 150);
            sheet.SetColumnWidth(2, 150);
            sheet.SetColumnWidth(3, (int)sheet.GetPreferredColumnWidth(3));
            sheet.SetColumnWidth(4, (int)sheet.GetPreferredColumnWidth(4));

The filter and sort indicators are added using the new IRange.AutoFilter API – since the formula used in columns B and C is an array formula, and array formulas are not supported in table cells, this example cannot use a table for the results. Instead of using a table for filter and sort, a range filter can be set on the target range to make its contents filterable and sortable by the user:

[VB]
            ' enable auto filter on the range
            workbook.ActiveSheet.Cells(0, 0, rc, 4).AutoFilter()
[C#]
            // enable auto filter on the range
            workbook.ActiveSheet.Cells[0, 0, rc, 4].AutoFilter();

The top row in the results table showing the table header cells should not scroll vertically with the sheet, so the top row is frozen using FrozenRowCount, and the row header cells are renumbered using StartingRowNumber to adjust for the extra header row for the table and make the table rows start at 1 instead of 2 (the text of the first row header cell is also cleared so it doesn’t show "0"):

[VB]
            ' freeze first row from scrolling to keep table headers visible
            sheet.FrozenRowCount = 1
            ' renumber row headings 
            sheet.StartingRowNumber = 0
            ' clear the text in the first row header
            sheet.RowHeader.Cells(0, 0).Text = " "
[C#]
            // freeze first row from scrolling to keep table headers visible
            sheet.FrozenRowCount = 1;
            // renumber row headings 
            sheet.StartingRowNumber = 0;
            // clear the text in the first row header
            sheet.RowHeader.Cells[0, 0].Text = " ";

To ensure that the clipboard shortcut keys for Copy – Ctrl+C and Ctrl+Insert – are handled by the worksheet to copy only the visible cells and skip the filtered-out cells, those shortcut keys are mapped to the appropriate SpreadActions using GetInputMap, InputMapMode, and InputMap.Put:

[VB]
            ' map the clipboard shortcut keys to special actions that copy only visible values as text
            ' (so the sheet can be filtered and copy/paste works as expected)
            fpSpread1.GetInputMap(InputMapMode.WhenFocused).Put(New Keystroke(Keys.C, Keys.Control), SpreadActions.ClipboardCopyAsStringSkipHidden)
            fpSpread1.GetInputMap(InputMapMode.WhenFocused).Put(New Keystroke(Keys.Insert, Keys.Control), SpreadActions.ClipboardCopyAsStringSkipHidden)
[C#]
            // map the clipboard shortcut keys to special actions that copy only visible values as text
            // (so the sheet can be filtered and copy/paste works as expected)
            fpSpread1.GetInputMap(InputMapMode.WhenFocused).Put(new Keystroke(Keys.C, Keys.Control), SpreadActions.ClipboardCopyAsStringSkipHidden);
            fpSpread1.GetInputMap(InputMapMode.WhenFocused).Put(new Keystroke(Keys.Insert, Keys.Control), SpreadActions.ClipboardCopyAsStringSkipHidden);

To highlight the root values, a FormulaConditionalFormattingRule is applied to the cells which highlights the row when the IMABS value in column E is a local minimum (less than both preceding and succeeding values) that is less than the specified increment, using SetConditionalFormatting:

[VB]
            ' create a conditional format to highlight the root (local minima IMABS < 1) values with yellow
            Dim conditionformula As String = "AND(RC5<" + incr.ToString() + ",RC5<R[1]C5,RC5<R[-1]C5)"
            Dim formulaRule As FormulaConditionalFormattingRule = New FormulaConditionalFormattingRule(conditionformula)
            formulaRule.BackColor = System.Drawing.Color.Yellow
            sheet.SetConditionalFormatting(1, 0, rc, 5, New IConditionalFormattingRule() {formulaRule})
[C#]
            // create a conditional format to highlight the root (local minima IMABS < increment) values with yellow
            string conditionformula = "AND(RC5<" + incr.ToString() + ",RC5<R[1]C5,RC5<R[-1]C5)";
            FormulaConditionalFormattingRule formulaRule = new FormulaConditionalFormattingRule(conditionformula);
            formulaRule.BackColor = System.Drawing.Color.Yellow;
            sheet.SetConditionalFormatting(1, 0, rc, 5, new IConditionalFormattingRule[] { formulaRule });

The last bit of code handles cleanup, resuming layout with ResumeLayout, resetting the cursor and updating the form title bar with statistics about the function call count and timing of the calculations, and enabling the Filter Roots Only button:

[VB]
            ' cleanup, update window text with function call count and timing stats, enable filter roots button
            fpSpread1.ResumeLayout()
            Cursor = Cursors.[Default]
            If checkBoxFunctionInfo.Checked Then
                Text = "Zeta Calculation Complete! [ZetaFunctionInfo Call #" + ZetaFunctionInfo.cnt.ToString() + " in " & (ZetaFunctionInfo.t2 - ZetaFunctionInfo.t1).ToString() & "]"
            Else
                Text = "Zeta Calculation Complete! [ZetaFunction Call #" + ZetaFunction.cnt.ToString() + " in " & (ZetaFunction.t2 - ZetaFunction.t1).ToString() & "]"
            End If
            buttonFilterRootsOnly.Enabled = True
        End Sub
 [C#]
            // cleanup, update window text with function call count and timing stats, enable filter roots button'
            fpSpread1.ResumeLayout();
            Cursor = Cursors.Default;
            if (checkBoxFunctionInfo.Checked)
                Text = "Zeta Calculation Complete! [ZetaFunctionInfo Call #" + ZetaFunctionInfo.cnt.ToString() + " in " + (ZetaFunctionInfo.t2 - ZetaFunctionInfo.t1).ToString() + "]";
            else
                Text = "Zeta Calculation Complete! [ZetaFunction Call #" + ZetaFunction.cnt.ToString() + " in " + (ZetaFunction.t2 - ZetaFunction.t1).ToString() + "]";
            buttonFilterRootsOnly.Enabled = true;
        }

The code for the Filter Roots Only button click event uses the new IRange.AutoFilter method with a yellow Fill specified, which has the same effect as the user opening the filter menu for the first column and selecting Filter By Color -> Filter By Cell Color :

[VB]
        Private Sub buttonFilterRootsOnly_Click(ByVal sender As Object, ByVal e As EventArgs) Handles buttonFilterRootsOnly.Click
            Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
            Dim worksheet As IWorksheet = workbook.ActiveSheet
            Dim fill As Fill = New Fill(GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow))
            worksheet.Cells(0, 0, worksheet.RowCount - 1, 4).AutoFilter(0, fill)
        End Sub
[C#]
        private void buttonFilterRootsOnly_Click(object sender, EventArgs e)
        {
            IWorkbook workbook = fpSpread1.AsWorkbook();
            IWorksheet worksheet = workbook.ActiveSheet;
            Fill fill = new Fill(GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow));
            worksheet.Cells[0, 0, worksheet.RowCount - 1, 4].AutoFilter(0, fill);
        }

The code for the Use ZetaFunctionInfo check box CheckChanged event handler switches the active function calculating the ZETA function between the GrapeCity.CalcEngine.Function and the FarPoint.CalcEngine.FunctionInfo implementations (which calculate the same thing using different APIs):

[VB]
        Private Sub checkBoxFunctionInfo_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles checkBoxFunctionInfo.CheckedChanged
            If checkBoxFunctionInfo.Checked Then
                ' this method is marked obsolete and deprecated -- you should port your old FunctionInfo class to a Function class
                fpSpread1.ActiveSheet.AddCustomFunction(New ZetaFunctionInfo())
            Else
                fpSpread1.AddCustomFunction(New ZetaFunction())
            End If
        End Sub
[C#]
        private void checkBoxFunctionInfo_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBoxFunctionInfo.Checked)
                // this method is marked obsolete and deprecated -- you should port your old FunctionInfo class to a Function class
                fpSpread1.ActiveSheet.AddCustomFunction(new ZetaFunctionInfo());
            else
                fpSpread1.AddCustomFunction(new ZetaFunction());
        }

Note that the SheetView.AddCustomFunction method is now marked obsolete and deprecated – custom functions using FarPoint.CalcEngine.FunctionInfo should be ported to using the new GrapeCity.CalcEngine.Function class instead.

The event handler for the LeaveCell event handles checking the whether the cell becoming active is a highlighted root value and enabling or disabling the Goal Seek Root button:

[VB]
        Private Sub fpSpread1_LeaveCell(ByVal sender As Object, ByVal e As LeaveCellEventArgs) Handles fpSpread1.LeaveCell
            Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
            ' get the interior color of the cell becoming active, enable goal seek button if it's a highlighted root value
            Dim c As GrapeCity.Spreadsheet.Color = workbook.ActiveSheet.Cells(e.NewRow, e.NewColumn).DisplayFormat.Interior.Color
            If c.Equals(GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow)) Then
                buttonGoalSeek.Enabled = True
            Else
                buttonGoalSeek.Enabled = False
            End If
        End Sub
[C#]
        private void fpSpread1_LeaveCell(object sender, LeaveCellEventArgs e)
        {
            IWorkbook workbook = fpSpread1.AsWorkbook();
            // get the interior color of the cell becoming active, enable goal seek button if it's a highlighted root value
            GrapeCity.Spreadsheet.Color c = workbook.ActiveSheet.Cells[e.NewRow, e.NewColumn].DisplayFormat.Interior.Color;
            if (c.Equals(GrapeCity.Spreadsheet.Color.FromKnownColor(GrapeCity.Core.KnownColor.Yellow)))
                buttonGoalSeek.Enabled = true;
            else
                buttonGoalSeek.Enabled = false;
        }

The code handling the Goal Seek Root button click implements logic to call into the CalculationEngine and perform manual calculations of formulas in code. First the code sets up variables for the calculations and gets the input values in the text box controls, overriding the iterations to use 100000 for improved accuracy finding root values:

[VB]
        Private Sub buttonGoalSeek_Click(ByVal sender As Object, ByVal e As EventArgs) Handles buttonGoalSeek.Click
            Dim t2 As DateTime, t1 As DateTime = DateTime.Now ' for timing the goal-seek
            Dim wb As IWorkbook = fpSpread1.AsWorkbook()
            Dim calcengine As CalculationEngine = wb.WorkbookSet.CalculationEngine
            Dim ws As IWorksheet = wb.ActiveSheet
            Dim sheet As SheetView = fpSpread1.ActiveSheet
            Dim initval As Double = 0.0
            Dim incr As Double = 0.1
            Dim rc As Integer = 1000
            Dim iter As Long = 10000
            Dim row As Integer = sheet.ActiveRowIndex
            Dim cnt As Integer ' for counting the number of ZETA function calls performed during goal-seek
            Dim cnt2 As Integer
            Dim maxchange As Double ' current decimal place of search
            Dim formula, formulaNext As String ' formula for value and formula for next value to be tested
            Dim result, resultNext As IValue ' calculated result of formula for value and formula for next value
            ' temp variables for calculating IMABS of result and resultNext
            Dim temp, temp1, temp2, tempNext, tempNext1, tempNext2 As Double
            Dim nextDecimalPlace As Boolean ' flag for moving to next decimal place
            Dim value As Double ' current value being tested
            ' save initial ZETA call count
            If checkBoxFunctionInfo.Checked Then
                cnt = ZetaFunctionInfo.cnt
            Else
                cnt = ZetaFunction.cnt
            End If
            If Not GetInputValues(initval, incr, rc, iter) Then Return
            ' force 100K interations for improved accuracy
            iter = 100000
[C#]
        private void buttonGoalSeek_Click(object sender, EventArgs e)
        {
            DateTime t2, t1 = DateTime.Now; // for timing the goal-seek
            IWorkbook wb = fpSpread1.AsWorkbook();
            CalculationEngine calcengine = wb.WorkbookSet.CalculationEngine;
            IWorksheet ws = wb.ActiveSheet;
            SheetView sheet = fpSpread1.ActiveSheet;
            double initval = 0.0;
            double incr = 0.01;
            int rc = 12000;
            long iter = 10000;
            int row = sheet.ActiveRowIndex;
            int cnt, cnt2; // for counting the number of ZETA function calls performed during goal-seek
            double maxchange; // current decimal place of search
            string formula, formulaNext; // formula for value and formula for next value to be tested
            IValue result, resultNext; // calculated result of formula for value and formula for next value
            // temp variables for calculating IMABS of result and resultNext
            double temp, temp1, temp2, tempNext, tempNext1, tempNext2;
            bool nextDecimalPlace; // flag for moving to next decimal place
            double value; // current value being tested
            // save initial ZETA call count
            if (checkBoxFunctionInfo.Checked) 
                cnt = ZetaFunctionInfo.cnt;
            else
                cnt = ZetaFunction.cnt;
            if (!GetInputValues(ref initval, ref incr, ref rc, ref iter))
                return;
            // force 100K iterations for improved accuracy
            iter = 100000;

Then the actual goal seek is performed using CalculationEngine.Evaluate and CellReference to evaluate ZETA for value and refine that value to 5 decimal places, then discard the last decimal place:

[VB]
            ' do goal seek on the cell (row, 0):
            value = Math.Round(CDbl(ws.Cells(row, 0).Value), 1) + 0.04
            ws.Cells(row, 0).NumberFormat = "0.0000"
            Cursor = Cursors.WaitCursor
            maxchange = 0.01 ' start changing the hundredths place, then move on to each sucessive decimal place until maxchange = 1e-5, then discard the last (unreliable) decimal place
            Do
                nextDecimalPlace = False ' reset flag
                ' calculate value
                formula = "ZETA(0.5," + value.ToString().Trim() + ",1,0," + iter.ToString().Trim() + ")"
                result = calcengine.Evaluate(formula, ws, New GrapeCity.Spreadsheet.CellReference(row, 1))
                temp1 = result.GetValuesArray()(0, 0).GetNumber()
                temp2 = result.GetValuesArray()(0, 1).GetNumber()
                ' calculate absolute values of results
                temp = Math.Sqrt(temp1 * temp1 + temp2 * temp2)
                Do
                    ' calculate next value (value + maxchange) using CalculationEngine.Evaluate
                    formulaNext = "ZETA(0.5," + (value + maxchange).ToString().Trim() + ",1,0," + iter.ToString().Trim() + ")"
                    resultNext = calcengine.Evaluate(formulaNext, ws, New GrapeCity.Spreadsheet.CellReference(row, 1))
                    tempNext1 = resultNext.GetValuesArray()(0, 0).GetNumber()
                    tempNext2 = resultNext.GetValuesArray()(0, 1).GetNumber()
                    ' calculate absolute values of results
                    tempNext = Math.Sqrt(tempNext1 * tempNext1 + tempNext2 * tempNext2)
                    If tempNext < temp Then
                        value += maxchange ' next value is closer, increment value and continue checking next higher value
                        temp = tempNext
                    Else ' next value is not closer, check previous value(s)
                        Do
                            ' calculate previous value
                            formulaNext = "ZETA(0.5," + (value - maxchange).ToString().Trim() + ",1,0," + iter.ToString().Trim() + ")"
                            resultNext = calcengine.Evaluate(formulaNext, ws, New GrapeCity.Spreadsheet.CellReference(row, 1))
                            tempNext1 = resultNext.GetValuesArray()(0, 0).GetNumber()
                            tempNext2 = resultNext.GetValuesArray()(0, 1).GetNumber()
                            ' calculate absolute value
                            tempNext = Math.Sqrt(tempNext1 * tempNext1 + tempNext2 * tempNext2)
                            If tempNext < temp Then
                                ' previous value is closer, decrement value and continue checking next previous value
                                value -= maxchange
                                temp = tempNext
                            Else
                                ' previous value is not closer, current value is closest -- continue to next decimal place
                                nextDecimalPlace = True
                                value = value + 4 * (maxchange / 10)
                            End If
                        Loop Until nextDecimalPlace
                    End If
                Loop Until nextDecimalPlace
                maxchange *= 0.1
            Loop While maxchange > 0.00001
            ' discard last digit
            value = Math.Truncate(value * 10000) / 10000
 [C#]
            // do goal seek on the cell [row, 0]:
            value = Math.Round((double)ws.Cells[row, 0].Value, 1) + 0.04; // start with value XXX.X4
            ws.Cells[row, 0].NumberFormat = "0.0000"; // format cell for 4 decimal places
            Cursor = Cursors.WaitCursor;
            for (maxchange = 0.01; maxchange > 1e-5; maxchange *= 0.1)
            { // start changing the hundredths place, then move on to each sucessive decimal place until maxchange = 1e-5, then discard the last (unreliable) decimal place
                nextDecimalPlace = false; // reset flag 
                // calculate value
                formula = "ZETA(0.5," + value.ToString().Trim() + ",1,0," + iter.ToString().Trim() + ")";
                result = calcengine.Evaluate(formula, ws, new GrapeCity.Spreadsheet.CellReference(row, 1));
                temp1 = result.GetValuesArray()[0, 0].GetNumber();
                temp2 = result.GetValuesArray()[0, 1].GetNumber();
                // calculate absolute values of results
                temp = Math.Sqrt(temp1 * temp1 + temp2 * temp2);
                do
                { // next value (value + maxchange) using CalculationEngine.Evaluate
                    formulaNext = "ZETA(0.5," + (value + maxchange).ToString().Trim() + ",1,0," + iter.ToString().Trim() + ")";
                    resultNext = calcengine.Evaluate(formulaNext, ws, new GrapeCity.Spreadsheet.CellReference(row, 1));
                    tempNext1 = resultNext.GetValuesArray()[0, 0].GetNumber();
                    tempNext2 = resultNext.GetValuesArray()[0, 1].GetNumber();
                    // calculate absolute values of results
                    tempNext = Math.Sqrt(tempNext1 * tempNext1 + tempNext2 * tempNext2);
                    if (tempNext < temp)
                    {
                        value += maxchange; // next value is closer, increment value and continue checking next higher value
                        temp = tempNext;
                    }
                    else
                    { // next value is not closer, check previous value(s)
                        do
                        { // calculate previous value
                            formulaNext = "ZETA(0.5," + (value - maxchange).ToString().Trim() + ",1,0," + iter.ToString().Trim() + ")";
                            resultNext = calcengine.Evaluate(formulaNext, ws, new GrapeCity.Spreadsheet.CellReference(row, 1));
                            tempNext1 = resultNext.GetValuesArray()[0, 0].GetNumber();
                            tempNext2 = resultNext.GetValuesArray()[0, 1].GetNumber();
                            // calculate absolute value
                            tempNext = Math.Sqrt(tempNext1 * tempNext1 + tempNext2 * tempNext2);
                            if (tempNext < temp)
                            { // previous value is closer, decrement value and continue checking next previous value
                                value -= maxchange; 
                                temp = tempNext;
                            }
                            else
                            { // previous value is not closer, current value is closest -- continue to next decimal place
                                nextDecimalPlace = true; 
                                value = value + 4 * (maxchange / 10);
                            }
                        } while (!nextDecimalPlace);
                    }
                } while (!nextDecimalPlace);
            }
            // discard last digit
            value = Math.Truncate(value * 10000) / 10000;

After the calculations are complete, the logic sets the root value found to the cell, using Cell.Value, resets the column width to show the whole value, and shows a message box with statistics about how many ZETA function calls were required and the timing of the operation:

[VB]
            ' cleanup
            t2 = DateTime.Now
            Cursor = Cursors.[Default]
            If checkBoxFunctionInfo.Checked Then
                cnt2 = ZetaFunctionInfo.cnt
            Else
                cnt2 = ZetaFunction.cnt
            End If
            sheet.Cells(row, 0).Value = value
            sheet.Columns(0).Width = sheet.GetPreferredColumnWidth(0)
            MessageBox.Show("Zero Value: " + value.ToString() + vbCrLf + "Calculation completed in " + (t2 - t1).ToString() + " with " + (cnt2 - cnt).ToString() + " calls to ZETA function.")
        End Sub
[C#]
            // cleanup
            t2 = DateTime.Now;
            Cursor = Cursors.Default;
            if (checkBoxFunctionInfo.Checked)
                cnt2 = ZetaFunctionInfo.cnt;
            else
                cnt2 = ZetaFunction.cnt;
            sheet.Cells[row, 0].Value = value;
            sheet.Columns[0].Width = sheet.GetPreferredColumnWidth(0);
            MessageBox.Show("Zero Value: " + value.ToString() + "\r\nCalculation completed in " + (t2 - t1).ToString() + " with " + (cnt2 - cnt).ToString() + " calls to ZETA function.");

This is certainly not the most optimal implementation for goal seek – there are much better ways to do that I'm sure – but the purpose of this code is really to show how easily you can call into the calculation engine to calculate formulas in code using the Spread.NET API.

The ZetaFunctionInfo Class

For customers who have old custom function implementations using the FarPoint.CalcEngine.FunctionInfo class and need to port those implementations to using the new GrapeCity.CalcEngine.Function class, here is an implementation of the ZETA function using the old API for reference:

[VB]
Imports System.Threading
Imports FarPoint.CalcEngine

Namespace SpreadNETZetaCalculator
    Friend Class ZetaFunctionInfo
        Inherits FunctionInfo
        ' keep count of function calls
        Friend Shared cnt As Integer = 0
        ' time calculation
        Friend Shared t1 As DateTime
        Friend Shared t2 As DateTime
        ' required overrides for FunctionInfo
        Public Overrides ReadOnly Property Name As String
            Get
                Return "ZETA"
            End Get
        End Property
        Public Overrides ReadOnly Property MinArgs As Integer
            Get
                Return 4
            End Get
        End Property
        Public Overrides ReadOnly Property MaxArgs As Integer
            Get
                Return 5
            End Get
        End Property
        Public Overrides Function Evaluate(ByVal args As Object()) As Object
            cnt += 1
            t1 = DateTime.Now
            Dim l As Integer = 10000
            Dim d As Double = CalcConvert.ToDouble(args(0))
            Dim d1 As Double = CalcConvert.ToDouble(args(1))
            Dim count As Integer = CalcConvert.ToInt(args(2))
            Dim incr As Double = CalcConvert.ToDouble(args(3))
            If args.Length > 4 Then l = CalcConvert.ToInt(args(4))
            Dim arr As Double(,) = New Double(count - 1, 1) {}
            Dim t As Task = New Task(
                Sub()
                    Parallel.[For](0, count, Sub(i)
                        Dim d2 As Double = 0.0
                        Dim d3 As Double = 0.0
                        Dim d4 As Double = 0.0
                        Dim d5 As Double = 0.0
                        Dim dd1 As Double = d1 + i * incr

                        For l1 As Integer = 1 To l
                            Dim d6 As Double = 2 * l1 - 1
                            Dim d7 As Double = 2 * l1
                            Dim d14 As Double = 0.0
                            Dim d15 As Double = 0.0
                            Dim d16 As Double = 0.0
                            Dim d8 As Double = (dd1 * Math.Log(d6)) Mod 6.2831853071795862
                            Dim d9 As Double = (dd1 * Math.Log(d7)) Mod 6.2831853071795862
                            Dim d10 As Double = -1 * d
                            Dim d11 As Double = -1 * 0.5
                            Dim d12 As Double = Math.Pow(d6, d10)
                            Dim d13 As Double = Math.Pow(d7, d10)
                            d2 += d12 * Math.Cos(d8) - d13 * Math.Cos(d9)
                            d3 += d13 * Math.Sin(d9) - d12 * Math.Sin(d8)
                            d4 += Math.Pow(d6, d11) * Math.Cos(d8) - Math.Pow(d7, d11) * Math.Cos(d9)
                            d14 = Math.Pow(d7, d11) * Math.Sin(d9)
                            d15 = Math.Pow(d6, d11)
                            d16 = Math.Sin(d8)
                            d5 += d14 - d15 * d16
                        Next
                        Dim d17 As Double = 1.0 - Math.Pow(2, 1.0 - d) * Math.Cos(d1 * Math.Log(2))
                        Dim d18 As Double = Math.Pow(2, 1.0 - d) * Math.Sin(d1 * Math.Log(2))
                        Dim d19 As Double = (d17 * d2 + d18 * d3) / (Math.Pow(d17, 2) + Math.Pow(d18, 2))
                        Dim d20 As Double = (d17 * d3 – d18 * d2) / (Math.Pow(d17, 2) + Math.Pow(d18, 2))
                        Interlocked.Exchange(arr(i, 0), d19)
                        Interlocked.Exchange(arr(i, 1), d20)
                   End Sub)
                End Sub)
            t.RunSynchronously()
            t2 = DateTime.Now
            Return New ArrayResult(arr)
        End Function
    End Class
    Public Class ArrayResult
        Inherits CalcArray

        Private values As Double(,)
        Public Sub New(ByVal values As Double(,))
            If values Is Nothing Then values = New Double(-1, -1) {}
            Me.values = values
        End Sub
        Public Overrides ReadOnly Property RowCount As Integer
            Get
                Return values.GetUpperBound(0) - values.GetLowerBound(0) + 1
            End Get
        End Property
        Public Overrides ReadOnly Property ColumnCount As Integer
            Get
                Return values.GetUpperBound(1) - values.GetLowerBound(1) + 1
            End Get
        End Property
        Public Overrides Function GetValue(ByVal row As Integer, ByVal column As Integer) As Object
            Return values(row, column)
        End Function
    End Class
End Namespace
[C#]
using System;
using System.Threading;
using System.Threading.Tasks;
using FarPoint.CalcEngine;

namespace SpreadNETZetaCalculator
{
    internal sealed class ZetaFunctionInfo : FunctionInfo
    {   // keep count of function calls
        internal static int cnt = 0;
        // time calculation
        internal static DateTime t1;
        internal static DateTime t2;
        // required overrides for FunctionInfo
        public override string Name => "ZETA";
        public override int MinArgs => 4;
        public override int MaxArgs => 5;
        public override object Evaluate(object[] args)
        {
            cnt++;
            t1 = DateTime.Now;
            int l = 10000;
            double d = CalcConvert.ToDouble(args[0]);  // real part
            double d1 = CalcConvert.ToDouble(args[1]); // imaginary part
            int count = CalcConvert.ToInt(args[2]); // number of points to calculate
            double incr = CalcConvert.ToDouble(args[3]); // increment between points
            if (args.Length > 4)
                l = CalcConvert.ToInt(args[4]); // iterations
            double[,] arr = new double[count, 2]; // create array to return values
            // do the ZETA calculation in a Task using Parallel.For for better performance
            Task t = new Task(delegate
            {
                Parallel.For(0, count, i =>
                {
                    var d2 = 0.0;
                    var d3 = 0.0;
                    var d4 = 0.0;
                    var d5 = 0.0;
                    var dd1 = d1 + i * incr;
                    for (var l1 = 1; l1 <= l; l1++)
                    {
                        var d6 = 2 * l1 - 1;
                        var d7 = 2 * l1;
                        var d14 = 0.0;
                        var d15 = 0.0;
                        var d16 = 0.0;
                        var d8 = (dd1 * Math.Log(d6)) % 6.2831853071795862;
                        var d9 = (dd1 * Math.Log(d7)) % 6.2831853071795862;
                        var d10 = -1 * d;
                        var d11 = -1 * 0.5;
                        var d12 = Math.Pow(d6, d10);
                        var d13 = Math.Pow(d7, d10);
                        d2 += d12 * Math.Cos(d8) - d13 * Math.Cos(d9);
                        d3 += d13 * Math.Sin(d9) - d12 * Math.Sin(d8);
                        d4 += Math.Pow(d6, d11) * Math.Cos(d8) - Math.Pow(d7, d11) * Math.Cos(d9);
                        d14 = Math.Pow(d7, d11) * Math.Sin(d9);
                        d15 = Math.Pow(d6, d11);
                        d16 = Math.Sin(d8);
                        d5 += d14 - d15 * d16;
                    }
                    var d17 = 1.0 - Math.Pow(2, 1.0 - d) * Math.Cos(d1 * Math.Log(2));
                    var d18 = Math.Pow(2, 1.0 - d) * Math.Sin(d1 * Math.Log(2));
                    var d19 = (d17 * d2 + d18 * d3) / (Math.Pow(d17, 2) + Math.Pow(d18, 2));
                    var d20 = (d17 * d3 – d18 * d2) / (Math.Pow(d17, 2) + Math.Pow(d18, 2));
                    Interlocked.Exchange(ref arr[i, 0], d19);
                    Interlocked.Exchange(ref arr[i, 1], d20);
                });
            });
            t.RunSynchronously(); // need to run this task synchronously on the UI thread
            t2 = DateTime.Now;
            return new ArrayResult(arr); // return as ArrayResult (see below)
        }
    }
    public class ArrayResult : CalcArray
    {
        double[,] values;
        public ArrayResult(double[,] values)
        {
            if (values == null)
                values = new double[0, 0];
            this.values = values;
        }
        public override int RowCount => values.GetUpperBound(0) - values.GetLowerBound(0) + 1;
        public override int ColumnCount => values.GetUpperBound(1) - values.GetLowerBound(1) + 1;
        public override object GetValue(int row, int column)
        {
            return values[row, column];
        }
    }
}

Note that the code inside the Task is identical, and only the outside code has changed – the logic which gets the function arguments and returns the array of results. The old FunctionInfo API required implementing a custom class inheriting from CalcArray to return the array of results; this is no longer required using the new API.

VB sample code | C# sample code

In another article, we show you how to implement advanced formula tracing and auditing for spreadsheets.

Thanks for following along, happy coding!

 

Download Now!<%/if%>

 

Sean Lawyer

Sean Lawyer

Product Manager
comments powered by Disqus