DioDocs for Excel
DioDocs for Excel
カスタム関数
機能 > カスタム関数

DioDocs for Excelは、カスタム関数の作成に対応しており、ユーザーは独自の演算ロジックをスプレッドシートにて実装することができます。カスタム関数は、MS Excelのネイティブ関数と同様のもので、主要なOS（Windows、Mac、Mobile OS）やOffice（オンラインおよびオフライン）を含むすべてのExcelプラットフォームで使用することができ、非常に高速に実行され、Webサービスの呼び出しを行うこともできます。

例えば、スプレッドシート内の複雑な計算を処理するために、企業独自の関数を使用したり、カスタム関数にて入れ子になった数式を適用したり、標準の組み込み関数を組み合わせて使用したりすることができます。

DioDocs for Excel でカスタム関数を実装するには、CustomFunction クラスから派生クラスを作成し、その新しいクラスで、関数名、戻り値の型、パラメータとともにカスタム関数を宣言する必要があります。

また、このトピックの「例5」のとおり、カスタム関数の中でカスタムオブジェクトを使用することもできます。オーバーロードされた Parameter メソッドの1つのパラメータを FunctionValueType.Object に設定し、acceptCustomObjects を True に設定すると、カスタムオブジェクトを使用できます。同様に、戻り値の型が FunctionValueType.Object の場合、関数はカスタムオブジェクトを返すことができます。

カスタム関数でのキャッシュ

同じ列にあるカスタム関数は、結果の値をキャッシュとして保存します。したがって、ある列のカスタム関数が前回と同じパラメータで呼び出された場合、カスタム関数は再計算する代わりにキャッシュされた値を使用します。この機能は、特に1つの列でカスタム関数を繰り返し使用する場合に、パフォーマンスを最適化するのに役立ちます。

ただし、カスタム関数のキャッシュ動作を制御するために、DioDocs for Excelでは CustomFunction クラスから継承したクラスに IsVolatile プロパティを提供します。このプロパティを使用して、同じパラメータを持つ列のカスタム関数を毎回再計算するか、キャッシュされた結果を使用するかを選択することができます。デフォルト値は false で、1つの列に適用されるカスタム関数は独自のキャッシュを維持し、繰り返し呼び出す際にそれを再使用します。実装については、「例6：揮発性カスタム関数の作成」を参照してください。

コードの場合

以下は、カスタム関数が必要とする複雑な計算作業の例です。  

例1：条件付き合計式

スプレッドシートでカスタム条件付き合計数式を作成して使用するには、次のコード例を参照してください。 この数式は、特定の表示形式または表示スタイル（たとえば、背景色が赤のセルなど）に基づいてセルの値を合計できます。

C#
コードのコピー 
// 手順1-カスタム関数「MyConditionalSum」を定義します
// CustomFunctionクラスを継承して、新しいクラス「MyConditionalSumFunctionX」を作成します
public class MyConditionalSumFunctionX : CustomFunction
{
    public MyConditionalSumFunctionX() : base("MyConditionalSum", FunctionValueType.Number, CreateParameters())
    {
    }
    private static Parameter[] CreateParameters()
    {
        Parameter[] parameters = new Parameter[254];
        for (int i = 0; i < 254; i++)
        {
            parameters[i] = new Parameter(FunctionValueType.Object, true);
        }
        return parameters;
    }
    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        double sum = 0d;
        foreach (var argument in arguments)
        {
            foreach (var item in Enumerate(argument))
            {
                if (item is CalcError)
                {
                    return item;
                }
                if (item is double)
                {
                    sum += (double)item;
                }
            }
        }
        return sum;
    }
    private static IEnumerable<object> Enumerate(object obj)
    {
        if (obj is IEnumerable<object>)
        {
            foreach (var item in obj as IEnumerable<object>)
            {
                foreach (var item2 in Enumerate(item))
                {
                    yield return item2;
                }
            }
        }
        else if (obj is object[,])
        {
            var array = obj as object[,];
            int rowCount = array.GetLength(0);
            int colCount = array.GetLength(1);
            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < colCount; j++)
                {
                    yield return array[i, j];
                }
            }
        }
        else if (obj is CalcReference)
        {
            foreach (var item in Enumerate(obj as CalcReference))
            {
                yield return item;
            }
        }
        yield return obj;
    }
    private static IEnumerable<object> Enumerate(CalcReference reference)
    {
        foreach (var range in reference.GetRanges())
        {
            int rowCount = range.Rows.Count;
            int colCount = range.Columns.Count;
            for (int i = 0; i < rowCount; i++)
            {
                for (int j = 0; j < colCount; j++)
                {
                    if (range.Cells[i, j].DisplayFormat.Interior.Color == System.Drawing.Color.Red)
                    {
                        yield return range.Cells[i, j].Value;
                    }
                }
            }
        }
    }
}
C#
コードのコピー 
// 手順2：AddCustomFunction()メソッドを使用してカスタム関数を登録します
var workbook = new GrapeCity.Documents.Excel.Workbook();
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];

// 手順3-カスタム関数を実装します
worksheet.Range["A1:A10"].Value = new object[,] { { 1 }, { 2 }, { 3 }, { 4 }, { 5 }, { 6 }, { 7 }, { 8 }, { 9 }, { 10 } };
IFormatCondition cellValueRule = worksheet.Range["A1:A10"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5) as IFormatCondition;
cellValueRule.Interior.Color = System.Drawing.Color.Red;
// 背景色が赤になっているセルの値を合計します
worksheet.Range["C1"].Formula = "=MyConditionalSum(A1:A10)";
// Range["C1"]の値は「40」になります
var result = worksheet.Range["C1"].Value;
// セルE2に結果を表示します
worksheet.Range["E2"].Value = result;

例2：カスタム連結数式

スプレッドシートでカスタム連結数式を作成して使用する方法については、次のサンプルコードを参照してください。

C#
コードのコピー 
// 手順1-カスタム関数「MyConcatenate」を定義します
// CustomFunctionクラスを継承して、新しいクラス「MyConcatenateFunctionX」を作成します
public class MyConcatenateFunctionX : CustomFunction
{
    public MyConcatenateFunctionX() : base("MyConcatenate", FunctionValueType.Text, CreateParameters())
    {
    }
    private static Parameter[] CreateParameters()
    {
        Parameter[] parameters = new Parameter[254];
        for (int i = 0; i < 254; i++)
        {
            parameters[i] = new Parameter(FunctionValueType.Variant);
        }
        return parameters;
    }
    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        StringBuilder sb = new StringBuilder();
        string result = string.Empty;
        foreach (var argument in arguments)
        {
            if (argument is CalcError)
            {
                return argument;
            }
            if (argument is string || argument is double)
            {
                sb.Append(argument);
            }
        }
        return sb.ToString();
    }
}
C#
コードのコピー 
// 手順2：AddCustomFunction()メソッドを使用してカスタム関数を登録します
var workbook = new GrapeCity.Documents.Excel.Workbook();
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConcatenateFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
        
// 手順3-カスタム関数を実装します
worksheet.Range["A1"].Formula = "=MyConcatenate(\"I\", \" \", \"work\", \" \", \"with\", \" \", \"GcExcel\", \".\")";
worksheet.Range["A2"].Formula = "=MyConcatenate(A1, \"Documents.\")";        
//セルA1の値は「I work with GcExcel.」になります
var resultA1 = worksheet.Range["A1"].Value;
//セルC1に結果を表示します
worksheet.Range["C1"].Value = resultA1;
//セルA2の値は「I work with GcExcel Documents.」になります
var resultA2 = worksheet.Range["A2"].Value;
//セルC2に結果を表示します
worksheet.Range["C2"].Value = resultA2;

例3：結合範囲数式

スプレッドシートでカスタムマージ範囲数式を作成して使用する方法については、次のサンプルコードを参照してください。

C#
コードのコピー 
// 手順1- カスタム関数「MyIsMergedRange」を定義します
// CustomFunctionクラスを継承して、新しいクラスMyIsMergedRangeFunctionXを作成します
public class MyIsMergedRangeFunctionX : CustomFunction
{
    public MyIsMergedRangeFunctionX()
        : base("MyIsMergedRange", FunctionValueType.Boolean, new Parameter[] { new Parameter(FunctionValueType.Object, true) })
    {
    }
    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        if (arguments[0] is CalcReference)
        {
            IEnumerable<IRange> ranges = (arguments[0] as CalcReference).GetRanges();

            foreach (var range in ranges)
            {
                return range.MergeCells;
            }
        }
        return false;
    }
}
C#
コードのコピー 
// 手順2：AddCustomFunction()メソッドを使用してカスタム関数を登録します
var workbook = new GrapeCity.Documents.Excel.Workbook();
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyIsMergedRangeFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];

// 手順3-カスタム関数を実装します
worksheet.Range["A1:B2"].Merge();
worksheet.Range["C1"].Formula = "=MyIsMergedRange(A1)";
worksheet.Range["C2"].Formula = "=MyIsMergedRange(H2)";
// セルA1は結合され、Range["C1"]の値はtrueになります
var resultC1 = worksheet.Range["C1"].Value;
// セルD1に結果を表示します
worksheet.Range["D1"].Value = resultC1;
// セルH2はマージされず、Range["C2"]の値はfalseになります
var resultC2 = worksheet.Range["C2"].Value;
// セルC2に結果を表示します
worksheet.Range["D2"].Value = resultC2;

例4：エラー検出数式

スプレッドシートでカスタムエラー検出数式を作成して使用する方法については、次のサンプルコードを参照してください。

C#
コードのコピー 
//手段1-カスタム関数の定義：MyIsError 
//CustomFunctionクラスを継承して新しいMyIsErrorFunctionXクラスを作成します
public class MyIsErrorFunctionX : CustomFunction
    {
        public MyIsErrorFunctionX()
            : base("MyIsError", FunctionValueType.Boolean, new Parameter[] { new Parameter(FunctionValueType.Variant) })
        {
        }
        public override object Evaluate(object[] arguments, ICalcContext context)
        {
            if (arguments[0] is CalcError)
            {
                if ((CalcError)arguments[0] != CalcError.None && (CalcError)arguments[0] != CalcError.GettingData)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            return false;
        }
    }
C#
コードのコピー 
//手段2：AddCustomFunction（）メソッドを使用してカスタム関数を登録します
var workbook = new Workbook();
Workbook.AddCustomFunction(new MyIsErrorFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
        
//手段3：カスタム関数を実装します
worksheet.Range["A1"].Value = CalcError.Num;
worksheet.Range["A2"].Value = 100;
worksheet.Range["B1"].Formula = "=MyIsError(A1)";
worksheet.Range["B2"].Formula = "=MyIsError(A2)";
//範囲[B1]の値はTrueです
var resultB1 = worksheet.Range["B1"].Value;
//セルC1に結果を表示します
worksheet.Range["C1"].Value = resultB1;
//範囲[B2]の値はFalseです
var resultB2 = worksheet.Range["B2"].Value;
//セルC2に結果を表示します
worksheet.Range["C2"].Value = resultB2;

例5: カスタムオブジェクトを使用した最大公約数関数

BigInteger関数を作成して、最大公約数を計算するのに使用する方法については、次のサンプルコードを参照してください。

C#
コードのコピー 
//手段1.1）カスタム関数の定義：BigIntegerMultiplyFunction
internal class BigIntegerMultiplyFunction : CustomFunction
{
public BigIntegerMultiplyFunction() : base("BIG.INTEGER.MULT", FunctionValueType.Object, new[]
    {
              new Parameter(FunctionValueType.Text),
              new Parameter(FunctionValueType.Text)
          })
    {
    }

public override object Evaluate(object[] arguments, ICalcContext context)
{
    if (!(arguments[0] is string) || !(arguments[1] is string))
    {
        return CalcError.Value;
    }
    var leftNumber = (string)arguments[0];
    var rightNumber = (string)arguments[1];
    try
    {
        return BigInteger.Parse(leftNumber) * BigInteger.Parse(rightNumber);
    }
    catch (FormatException)
    {
        return CalcError.Value;
    }
    catch (ArgumentException)
    {
        return CalcError.Value;
    }
}
        
}
//手段1.2）カスタム関数の定義：BigIntegerMultiplyFunction
internal class BigIntegerPowFunction : CustomFunction
{
public BigIntegerPowFunction() : base("BIG.INTEGER.POW", FunctionValueType.Object, new[]
{
              new Parameter(FunctionValueType.Text),
              new Parameter(FunctionValueType.Number)
          })
    {
    }

public override object Evaluate(object[] arguments, ICalcContext context)
{
    if (!(arguments[0] is string) || !(arguments[1] is double))
    {
        return CalcError.Value;
    }
    var number = (string)arguments[0];
    var exp = (double)arguments[1];
    if (exp > int.MaxValue || exp < int.MinValue)
    {
        return CalcError.Value;
    }
    var iExp = Convert.ToInt32(exp);
    try
    {
        return BigInteger.Pow(BigInteger.Parse(number), iExp);
    }
    catch (FormatException)
    {
        return CalcError.Value;
    }
    catch (ArgumentException)
    {
        return CalcError.Value;
    }
}
}
//手段1.3）カスタム関数の定義：BigIntegerMultiplyFunction
internal class GreatestCommonDivisionFunction : CustomFunction
{
public GreatestCommonDivisionFunction() : base("BIG.INTEGER.GCD", FunctionValueType.Object, new[] {
              new Parameter(FunctionValueType.Object, false, true),
              new Parameter(FunctionValueType.Object, false, true)
          })
    {
    }

public override object Evaluate(object[] arguments, ICalcContext context)
{
    if (!(arguments[0] is BigInteger) || !(arguments[1] is BigInteger))
    {
        return CalcError.Value;
    }
    var leftNumber = (BigInteger)arguments[0];
    var rightNumber = (BigInteger)arguments[1];
    try
    {
        return BigInteger.GreatestCommonDivisor(leftNumber, rightNumber);
    }
    catch (ArgumentException)
    {
        return CalcError.Value;
    }
  
}   
}
C#
コードのコピー 
//新しいワークブックを作成します
var workbook = new GrapeCity.Documents.Excel.Workbook();

try
{
    //手段2.1）AddCustomFunction()メソッドを使用してカスタム関数を登録します
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new BigIntegerPowFunction());
}
catch (Exception)
{
    // Function was added
} // End Try
try
{
    //手段2.2）AddCustomFunction()メソッドを使用してカスタム関数を登録します       
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new BigIntegerMultiplyFunction());
}
catch (Exception)
{
    //機能が追加されました
}
try
{
    //手段2.3）AddCustomFunction()メソッドを使用してカスタム関数を登録します     
    GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GreatestCommonDivisionFunction());
}
catch (Exception)
{
    //機能が追加されました
}
        
//BigIntegerを使用して結果を計算します
IWorksheet worksheet = workbook.ActiveSheet;
//手段3）カスタム関数を実装します
worksheet.Range["A1"].Value = "154382190 ^ 3 = ";
worksheet.Range["A2"].Value = "1643590 * 166935 = ";
worksheet.Range["A3"].Value = "Greatest common division = ";
worksheet.Range["B1"].Formula = "=BIG.INTEGER.POW(\"154382190\", 3)";
worksheet.Range["B2"].Formula = "=BIG.INTEGER.MULT(\"1643590\", \"166935\")";
worksheet.Range["B3"].Formula = "=BIG.INTEGER.GCD(B1,B2)";

//調整
worksheet.Columns[0].AutoFit();
worksheet.Columns[1].ColumnWidth = worksheet.Range["B1"].Text.Length + 1;

//PDFファイルに保存します
workbook.Save("customobjectincustomfunction.pdf");

例6：揮発性カスタム関数の作成

GUIDを生成するためのカスタム関数を作成する方法については、次のサンプルコードを参照してください。毎回一意なGUIDを生成するために、カスタム関数はキャッシュを使用しないようにします。よって、サンプルコードでは、IsVolatile プロパティを true に設定し、呼び出すたびに新しいGUIDが生成されるようにしています。

C#
コードのコピー 
//手順1：CustomFunctionクラスを継承して新しいクラスGeneralIDを作成します
internal class GeneralID : CustomFunction
{
    public GeneralID() : base("GeneralID", FunctionValueType.Object)
    {
        //IsVolatileをtrueに設定して、キャッシュされた値を使用しないようにします
        this.IsVolatile = true;
    }

    public override object Evaluate(object[] arguments, ICalcContext context)
    {
        return Guid.NewGuid().ToString("N");
    }
}
C#
コードのコピー 
//手順2：AddCustomFunction()メソッドを使用してカスタム関数を登録します
//新しいワークブックを作成します
var workbook = new GrapeCity.Documents.Excel.Workbook();

GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new GeneralID());

IWorksheet worksheet = workbook.Worksheets[0];

//手順3：カスタム関数を実装します
worksheet.Range["A1"].Formula = "GeneralID()";
var valueA1Before = worksheet.Range["A1"].Value;

Console.WriteLine(valueA1Before);
worksheet.Range["A2"].Formula = "GeneralID()";

//キャッシュされた値を使用していないため、A1 の値が変更されます
var valueA1After = worksheet.Range["A1"].Value;
Console.WriteLine(valueA1After);

例 7: 非同期カスタム関数の作成

非同期関数とは、結果を非同期または同時に配信する関数です。 非同期関数はノンブロッキング アーキテクチャを備えているため、あるタスクの実行は別のタスクに依存しません。タスクは同時に実行できます。 非同期関数を実行すると、複数の計算を同時に実行できるため、パフォーマンスが向上します。DsExcel では、関数を AsyncCustomFunction クラスから派生することで、非同期計算を実行できます。EvaluateAsync メソッドを使用すると、関数が非同期的に計算を実行します。 また、DsExcelには、セルが非同期数式を計算していることを示す CalcError 列挙値の列挙値 「Busy」 があります。

カスタム非同期関数を追加して使用する方法については、次のサンプルコードを参照してください。

C#
コードのコピー 
internal class Program
{
    static void Main(string[] args)
    {
        //　非同期カスタム関数を登録します。
        Workbook.AddCustomFunction(new MyAddFunction());

        // 非同期カスタム関数を実装します。
        Workbook workbook = new Workbook();
        var worksheet = workbook.Worksheets[0];
        worksheet.Range["A1"].Value = 1;
        worksheet.Range["B1"].Value = 2;

        // セルの値を追加します。
        worksheet.Range["C1"].Formula = "=MyAdd(A1,B1)";
        var value = worksheet.Range["C1"].Value;

        // 果を表示します。 結果は「Busy」になります。
        Console.WriteLine($"get value first time:{value}");
        Thread.Sleep(2000);
        value = worksheet.Range["C1"].Value;

        // 結果を表示します。 結果は「3」になります。
        Console.WriteLine($"get value second time:{value}");
    }
}
//　非同期カスタム関数 MyAddFunction を定義します。
public sealed class MyAddFunction : AsyncCustomFunction
{
    public MyAddFunction()
        : base("MyAdd", FunctionValueType.Number, new Parameter[] { new Parameter(FunctionValueType.Number), new Parameter(FunctionValueType.Number) })
    {

    }

    async protected override Task<object> EvaluateAsync(object[] arguments, ICalcContext context)
    {
        await Task.Delay(1000);
        return (double)arguments[0] + (double)arguments[1];
    }
}

制限事項

AsyncCustomFunction のパラメータは参照を受け入れません。非同期関数は別のスレッドで実行される可能性があり、参照を使用すると複数のスレッドの競合が発生します。同様に、IWorksheet や IWorkbook などのオブジェクト使用することもできません。

 

 

© MESCIUS inc. All rights reserved.