Excel user defined functions (UDF) (C# / netframework)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\10.API\78.Excel User Defined Functions and also at https://github.com/tmssoftware/TMS-FlexCel.NET-demos/tree/master/csharp/VS2022/netframework/Modules/10.API/78.Excel User Defined Functions
Overview
Here we will explore how to handle Excel files with UDFs. FlexCel has full support for adding Excel UDFs to cells, retrieving UDFs from cells or recalculating files containing UDFs. But you need to create .NET functions that will mimic the UDF behavior, and add them to the FlexCel recalculation engine.
Make sure you read Using Excel's User-defined Functions (UDF) in the API developers guide for a conceptual explanation of what we are doing here.
Concepts
How to recalculate a sheet containing User Defined Functions (UDfs).
How to read and write UDFs from and to an Excel file.
In order to compare the results calculated by Excel and By FlexCel, this demo will save two files: one pdf (that will not use Excel recalculation) and one xls (that will be recalculated by Excel when opened).
Files
AssemblyInfo.cs
using System.Reflection;
using System.Runtime.CompilerServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("")]
[assembly: AssemblyCopyright("(c) 2002 - 2024 TMS Software")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("7.21.0.0")]
//
// In order to sign your assembly you must specify a key to use. Refer to the
// Microsoft .NET Framework documentation for more information on assembly signing.
//
// Use the attributes below to control which key is used for signing.
//
// Notes:
// (*) If no key is specified, the assembly is not signed.
// (*) KeyName refers to a key that has been installed in the Crypto Service
// Provider (CSP) on your machine. KeyFile refers to a file which contains
// a key.
// (*) If the KeyFile and the KeyName values are both specified, the
// following processing occurs:
// (1) If the KeyName can be found in the CSP, that key is used.
// (2) If the KeyName does not exist and the KeyFile does exist, the key
// in the KeyFile is installed into the CSP and used.
// (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
// When specifying the KeyFile, the location of the KeyFile should be
// relative to the project output directory which is
// %Project Directory%\obj\<configuration>. For example, if your KeyFile is
// located in the project directory, you would specify the AssemblyKeyFile
// attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
// (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
// documentation for more information on this.
//
[assembly: AssemblyDelaySign(false)]
[assembly: AssemblyKeyFile("")]
[assembly: AssemblyKeyName("")]
Form1.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using System.Text;
using FlexCel.Render;
namespace ExcelUserDefinedFunctions
{
/// <summary>
/// An example on how to recalculate user defined functions.
/// </summary>
public partial class mainForm: System.Windows.Forms.Form
{
public mainForm()
{
InitializeComponent();
}
private string PathToExe
{
get
{
return Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
}
}
/// <summary>
/// Loads the user defined functions into the Excel recalculating engine.
/// </summary>
/// <param name="Xls"></param>
private void LoadUdfs(ExcelFile Xls)
{
Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, new SumCellsWithSameColor());
Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, new IsPrime());
Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, new BoolChoose());
Xls.AddUserDefinedFunction(TUserDefinedFunctionScope.Local, TUserDefinedFunctionLocation.Internal, new Lowest());
}
private void AddData(ExcelFile Xls)
{
LoadUdfs(Xls); //Register our custom functions. As we are using a local scope, we need to register them each time.
Xls.Open(Path.Combine(PathToExe, "udfs.xls")); //Open the file we want to manipulate.
//Fill the cell range with other values so we can see how the sheet is recalculated by FlexCel.
TXlsCellRange Data = Xls.GetNamedRange("Data", -1);
for (int r = Data.Top; r < Data.Bottom; r++)
{
Xls.SetCellValue(r, Data.Left, r - Data.Top);
}
//Add an UDF to the sheet. We can enter the fucntion "BoolChoose" here because it was registered into FlexCel in LoadUDF()
//If it hadn't been registered, this line would raise an Exception of an unknown function.
string FmlaText = "=BoolChoose(TRUE,\"This formula was entered with FlexCel!\",\"It shouldn't display this\")";
Xls.SetCellValue(11, 1, new TFormula(FmlaText));
//Verify the UDF entered is correct. We can read any udf from Excel, even if it is not registered with AddUserDefinedFunction.
object o = Xls.GetCellValue(11, 1);
TFormula fm = o as TFormula;
Debug.Assert(fm != null, "The cell must contain a formula");
if (fm != null) Debug.Assert(fm.Text == FmlaText, "Error in Formula: It should be \"" + FmlaText + "\" and it is \"" + fm.Text + "\"");
//Recalc the sheet. As we are not saving it yet, we ned to make a manual recalc.
Xls.Recalc();
//Export the file to PDF so we can see the values calculated by FlexCel without Excel recalculating them.
using (FlexCelPdfExport pdf = new FlexCelPdfExport(Xls, true))
{
pdf.Export(saveFileDialog1.FileName);
}
//Save the file as xls too so we can compare.
Xls.Save(Path.ChangeExtension(saveFileDialog1.FileName, "xls"));
}
private void button1_Click(object sender, System.EventArgs e)
{
AutoRun();
}
public void AutoRun()
{
if (saveFileDialog1.ShowDialog() != DialogResult.OK) return;
ExcelFile Xls = new XlsFile(true);
AddData(Xls);
if (MessageBox.Show("Do you want to open the generated files (PDF and XLS)?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (Process p = new Process())
{
p.StartInfo.FileName = saveFileDialog1.FileName;
p.StartInfo.UseShellExecute = true;
p.Start();
}
using (Process p = new Process())
{
p.StartInfo.FileName = Path.ChangeExtension(saveFileDialog1.FileName, "xls");
p.StartInfo.UseShellExecute = true;
p.Start();
}
}
}
/// <summary>
/// This is the method that will be called by the ASP.NET front end. It returns an array of bytes
/// with the report data, so the ASP.NET application can stream it to the client.
/// </summary>
/// <returns>The generated file as a byte array.</returns>
public byte[] WebRun()
{
ExcelFile Xls = new XlsFile(true);
AddData(Xls);
using (MemoryStream OutStream = new MemoryStream())
{
Xls.Save(OutStream);
return OutStream.ToArray();
}
}
}
#region UDF definitions
/// <summary>
/// Implements a custom function that will sum the cells in a range that have the same
/// color of the source cell. This function mimics the VBA macro in the example, so when
/// recalculating the sheet with FlexCel you will get the same results as with Excel.
/// </summary>
public class SumCellsWithSameColor: TUserDefinedFunction
{
/// <summary>
/// Creates a new instance and registers the class in the FlexCel recalculating engine as "SumCellsWithSameColor".
/// </summary>
public SumCellsWithSameColor() : base("SumCellsWithSameColor")
{
}
/// <summary>
/// Returns the sum of cells in a range that have the same color as a reference cell.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 2 parameters, first the reference cell and then
/// the range in which to sum. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 2, out Err)) return Err;
//The first parameter should be a range
TXls3DRange SourceCell;
if (!TryGetCellRange(parameters[0], out SourceCell, out Err)) return Err;
//The second parameter should be a range too.
TXls3DRange SumRange;
if (!TryGetCellRange(parameters[1], out SumRange, out Err)) return Err;
#endregion
//Get the color in SourceCell. Note that if Source cell is a range with more than one cell,
//we will use the first cell in the range. Also, as different colors can have the same rgb value, we will compare the actual RGB values, not the ExcelColors
TFlxFormat fmt = arguments.Xls.GetCellVisibleFormatDef(SourceCell.Sheet1, SourceCell.Top, SourceCell.Left);
int SourceColor = fmt.FillPattern.FgColor.ToColor(arguments.Xls).ToArgb();
double Result = 0;
//Loop in the sum range and sum the corresponding values.
for (int s = SumRange.Sheet1; s <= SumRange.Sheet2; s++)
{
for (int r = SumRange.Top; r <= SumRange.Bottom; r++)
{
for (int c = SumRange.Left; c <= SumRange.Right; c++)
{
int XF = -1;
object val = arguments.Xls.GetCellValue(s, r, c, ref XF);
if (val is double) //we will only sum numeric values.
{
TFlxFormat sumfmt = arguments.Xls.GetCellVisibleFormatDef(s, r, c);
if (sumfmt.FillPattern.FgColor.ToColor(arguments.Xls).ToArgb() == SourceColor)
{
Result += (double)val;
}
}
}
}
}
return Result;
}
}
/// <summary>
/// Implements a custom function that will return true if a number is prime.
/// This function mimics the VBA macro in the example, so when
/// recalculating the sheet with FlexCel you will get the same results as with Excel.
/// </summary>
public class IsPrime: TUserDefinedFunction
{
/// <summary>
/// Creates a new instance and registers the class in the FlexCel recalculating engine as "IsPrime".
/// </summary>
public IsPrime() : base("IsPrime")
{
}
/// <summary>
/// Returns true if a number is prime.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 1 parameter with the number. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 1, out Err)) return Err;
//The parameter should be a double or a range.
double Number;
if (!TryGetDouble(arguments.Xls, parameters[0], out Number, out Err)) return Err;
#endregion
//Return true if the number is prime.
int n = Convert.ToInt32(Number);
if (n == 2) return true;
if (n < 2 || n % 2 == 0) return false;
for (int i = 3; i <= Math.Sqrt(n); i += 2)
{
if (n % i == 0) return false;
}
return true;
}
}
/// <summary>
/// Implements a custom function that will choose between two different strings.
/// This function mimics the VBA macro in the example, so when
/// recalculating the sheet with FlexCel you will get the same results as with Excel.
/// </summary>
public class BoolChoose: TUserDefinedFunction
{
/// <summary>
/// Creates a new instance and registers the class in the FlexCel recalculating engine as "BoolChoose".
/// </summary>
public BoolChoose() : base("BoolChoose")
{
}
/// <summary>
/// Chooses between 2 different strings.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 3 parameters: The first is a boolean, and the other 2 strings. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 3, out Err)) return Err;
//The first parameter should be a boolean.
bool ChooseFirst;
if (!TryGetBoolean(arguments.Xls, parameters[0], out ChooseFirst, out Err)) return Err;
//The second parameter should be a string.
string s1;
if (!TryGetString(arguments.Xls, parameters[1], out s1, out Err)) return Err;
//The third parameter should be a string.
string s2;
if (!TryGetString(arguments.Xls, parameters[2], out s2, out Err)) return Err;
#endregion
//Return s1 or s2 depending on ChooseFirst
if (ChooseFirst) return s1; else return s2;
}
}
/// <summary>
/// Implements a custom function that will choose the lowest member in an array.
/// This function mimics the VBA macro in the example, so when
/// recalculating the sheet with FlexCel you will get the same results as with Excel.
/// </summary>
public class Lowest: TUserDefinedFunction
{
/// <summary>
/// Creates a new instance and registers the class in the FlexCel recalculating engine as "Lowest".
/// </summary>
public Lowest() : base("Lowest")
{
}
/// <summary>
/// Chooses the lowest element in an array.
/// </summary>
/// <param name="arguments"></param>
/// <param name="parameters">In this case we expect 1 parameter that should be an array. We will return an error otherwise.</param>
/// <returns></returns>
public override object Evaluate(TUdfEventArgs arguments, object[] parameters)
{
#region Get Parameters
TFlxFormulaErrorValue Err;
if (!CheckParameters(parameters, 1, out Err)) return Err;
//The first parameter should be an array.
object[,] SourceArray;
if (!TryGetArray(arguments.Xls, parameters[0], out SourceArray, out Err)) return Err;
#endregion
double Result = 0;
bool First = true;
foreach (object o in SourceArray)
{
if (o is double)
{
if (First)
{
First = false;
Result = (double)o;
}
else
{
if ((double)o < Result) Result = (double)o;
}
}
else return TFlxFormulaErrorValue.ErrValue;
}
return Result;
}
}
#endregion
}
Form1.Designer.cs
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using FlexCel.Core;
using FlexCel.XlsAdapter;
using System.IO;
using System.Diagnostics;
using System.Reflection;
using System.Text;
using FlexCel.Render;
namespace ExcelUserDefinedFunctions
{
public partial class mainForm: System.Windows.Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
this.label1 = new System.Windows.Forms.Label();
this.SuspendLayout();
//
// button1
//
this.button1.Anchor = System.Windows.Forms.AnchorStyles.Bottom;
this.button1.Location = new System.Drawing.Point(132, 73);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "GO!";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// saveFileDialog1
//
this.saveFileDialog1.Filter = "Pdf Files|*.pdf";
this.saveFileDialog1.RestoreDirectory = true;
this.saveFileDialog1.Title = "The Application will save BOTH AN XLS AND A PDF file in this folder";
//
// label1
//
this.label1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.label1.BackColor = System.Drawing.Color.FromArgb(((System.Byte)(255)), ((System.Byte)(255)), ((System.Byte)(192)));
this.label1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
this.label1.Location = new System.Drawing.Point(16, 16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(312, 32);
this.label1.TabIndex = 1;
this.label1.Text = "A demo on how to handle Excel UDFs with the API.";
//
// mainForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(336, 110);
this.Controls.Add(this.label1);
this.Controls.Add(this.button1);
this.Name = "mainForm";
this.Text = "Excel User Defined Functions";
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.Button button1;
private System.Windows.Forms.SaveFileDialog saveFileDialog1;
private System.Windows.Forms.Label label1;
}
}
Program.cs
using System;
using System.Windows.Forms;
namespace ExcelUserDefinedFunctions
{
static class Program
{
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new mainForm());
}
}
}