This blog post will demonstrate many useful things:
- How to implement a custom function for Spread.NET 12 Windows Forms that works just like the built-in functions in cell formulas
- 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
- How to make use of Parallel.For safely with Spread.NET 12 Windows Forms to optimize performance
- How to port a legacy custom function using FarPoint.CalcEngine.FunctionInfo to a new custom function using GrapeCity.CalcEngine.Function
- 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):
This function is interesting for many reasons – here are just a few:
- It can be expressed as an infinite sum or as an infinite product over the prime numbers – this was demonstrated by Leonhard Euler:
- Bernhard Riemann used analytic continuation to extend ζ(s) to cover Complex Plane, converges everywhere except for a single pole at x = 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.
-
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:
- 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?
- Implement the custom function – this requires writing a new class inheriting from GrapeCity.CalcEngine.Function.
- 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
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:
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:
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:
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!
