Search Results for

    Show / Hide Table of Contents

    User defined functions (C# / netframework)

    Note

    This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\csharp\VS2022\netframework\20.Reports\95.User Defined Functions and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​NET-​demos/​tree/​master/​csharp/​VS2022/​netframework/​Modules/​20.​Reports/​95.​User Defined Functions

    Overview

    Here we will see how to define a user function to calculate the number of orders by employee and shipper. This example is similar to the Pivot Tables example, but using only static normal cells.

    Concepts

    • How to define user functions for non-standard needs. By the way: Try to define only as little user functions as you really need. User functions are on code, and a change on them means that you have to recompile the code.

    • We defined two ranges here, one horizontal and one vertical. The user defined function here will return a value for a couple of those values, allowing us to create a "static" pivot table.

    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 - 2025 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.24.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 System.IO;
    using System.Diagnostics;
    using System.Reflection;
    using FlexCel.Core;
    using FlexCel.XlsAdapter;
    using FlexCel.Report;
    using FlexCel.Demo.SharedData;
    
    
    namespace UserDefinedFunctions
    {
        public partial class mainForm: System.Windows.Forms.Form
        {
    
            public mainForm()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, System.EventArgs e)
            {
                AutoRun();
            }
    
            public void AutoRun()
            {
                using (FlexCelReport ordersReport = SharedData.CreateReport())
                {
                    ordersReport.SetValue("Date", DateTime.Now);
                    ordersReport.SetUserFunction("Orders", new OrdersImp(SharedData.GetOrders()));
    
                    string DataPath = Path.Combine(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), ".."), "..") + Path.DirectorySeparatorChar;
    
                    if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        ordersReport.Run(DataPath + "User Defined Functions.template.xls", saveFileDialog1.FileName);
    
                        if (MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {
                            using (Process p = new Process())
                            {               
                                p.StartInfo.FileName = saveFileDialog1.FileName;
                                p.StartInfo.UseShellExecute = true;
                                p.Start();
                            }
                        }
                    }
                }
            }
    
    
            private void btnCancel_Click(object sender, System.EventArgs e)
            {
                Close();
            }
    
        }
    
        #region Orders Implementation
        class OrdersImp: TFlexCelUserFunction
        {
            DataView OrdersDv;
    
            public OrdersImp(DataTable OrdersTable)
            {
                OrdersDv = new DataView(OrdersTable, String.Empty, "EmployeeID,ShipVia", DataViewRowState.CurrentRows);
            }
            public override object Evaluate(object[] parameters)
            {
                if (parameters == null || parameters.Length != 2)
                    throw new ArgumentException("Bad parameter count in call to Orders() user-defined function");
    
                int Count = OrdersDv.FindRows(parameters).Length;
                if (Count > 0) return Count; else return null;
            }
        }
        #endregion
    
    }
    

    Form1.Designer.cs

    using System;
    using System.Drawing;
    using System.Collections;
    using System.ComponentModel;
    using System.Windows.Forms;
    using System.Data;
    using System.IO;
    using System.Diagnostics;
    using System.Reflection;
    using FlexCel.Core;
    using FlexCel.XlsAdapter;
    using FlexCel.Report;
    using FlexCel.Demo.SharedData;
    namespace UserDefinedFunctions
    {
        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.btnCancel = new System.Windows.Forms.Button();
                this.SuspendLayout();
                // 
                // button1
                // 
                this.button1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
                this.button1.BackColor = System.Drawing.Color.Green;
                this.button1.ForeColor = System.Drawing.Color.White;
                this.button1.Location = new System.Drawing.Point(152, 88);
                this.button1.Name = "button1";
                this.button1.Size = new System.Drawing.Size(112, 23);
                this.button1.TabIndex = 0;
                this.button1.Text = "GO!";
                this.button1.UseVisualStyleBackColor = false;
                this.button1.Click += new System.EventHandler(this.button1_Click);
                // 
                // saveFileDialog1
                // 
                this.saveFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All files|*.*";
                this.saveFileDialog1.RestoreDirectory = true;
                // 
                // label1
                // 
                this.label1.Location = new System.Drawing.Point(24, 24);
                this.label1.Name = "label1";
                this.label1.Size = new System.Drawing.Size(288, 24);
                this.label1.TabIndex = 2;
                this.label1.Text = "A Crosstab report using an user defined function.";
                // 
                // btnCancel
                // 
                this.btnCancel.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
                this.btnCancel.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(192)))), ((int)(((byte)(0)))), ((int)(((byte)(0)))));
                this.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel;
                this.btnCancel.ForeColor = System.Drawing.Color.White;
                this.btnCancel.Location = new System.Drawing.Point(272, 88);
                this.btnCancel.Name = "btnCancel";
                this.btnCancel.Size = new System.Drawing.Size(112, 23);
                this.btnCancel.TabIndex = 3;
                this.btnCancel.Text = "Cancel";
                this.btnCancel.UseVisualStyleBackColor = false;
                this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
                // 
                // mainForm
                // 
                this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
                this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
                this.ClientSize = new System.Drawing.Size(416, 133);
                this.Controls.Add(this.btnCancel);
                this.Controls.Add(this.label1);
                this.Controls.Add(this.button1);
                this.Name = "mainForm";
                this.Text = "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;
            private System.Windows.Forms.Button btnCancel;
        }
    }
    

    Program.cs

    using System;
    using System.Windows.Forms;
    
    namespace UserDefinedFunctions
    {
        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());
            }
        }
    }
    
    In This Article
    Back to top FlexCel Studio for the .NET Framework v7.24.0.0
    © 2002 - 2025 tmssoftware.com