数式計算

ReoGrid では、Excel と同様の数式計算機能を提供しています。スプレッドシートを Excel ファイルから読み込んだ場合、計算式も自動的に読み込まれます。計算式が参照するセルの値が更新された場合、計算式も自動的に実行されます。

計算式を手入力する

Excel と同様にセルのデータを編集する際に、先頭に「=」を入れるとそのあとのテキストを計算式とみなします。

プログラムで計算式を設定する

プログラムで計算式を設定する方法は、以下のいずれかです。

１．セルのデータとして設定します。計算式の先頭に「=」を付けます

worksheet["E3"] = "=(A3+B3)\*C3";

２．セルインスタンスのFormulaプロパティに計算式を設定します。計算式の先頭に「=」を付けません。

// セルインスタンスを取得
var cell = worksheet.Cells["E3"];

// セルの計算式を設定
cell.Formula = "(A3+B3)\*C3";

計算式を実行

自動実行

計算式が設定された後、参照セルや範囲のデータが変化した場合自動的に実行されます。自動実行の機能を禁止するには、後述する「自動実行を禁止する」をご覧ください。

強制実行

計算式を強制的に再計算したい場合、ワークシートのメソッド RecalcCell を利用します。

// ワークシートのインスタンスを取得
var sheet = reoGridControl.Worksheet[0];

// セルE3を再計算する
sheet.RecalcCell("E3");

すべてのセルの計算式を再実行

ワークシートにあるすべての計算式を再実行したい場合、ワークシートのメソッド Recalculate を利用します。

sheet.Recalculate();

自動実行を禁止する

参照セルや範囲のデータが変化しても計算式を実行させたくない場合、ワークシートのメソッド SuspendFormulaReferenceUpdates を利用します。このメソッドは計算式の自動実行機能を一時停止します。

sheet.SuspendFormulaReferenceUpdates();

自動実行を再開したい場合、以下のメソッドを利用します。

sheet.ResumeFormulaReferenceUpdates();

またはワークシートの設定を変更します。メソッドの呼び出しと同様の機能です。

// 自動実行を一時停止
sheet.SetSettings(WorksheetSettings.Formula_AutoUpdateReferenceCell, false);

// 自動実行を再開
sheet.SetSettings(WorksheetSettings.Formula_AutoUpdateReferenceCell, true);

計算式を取得

計算式の取得は、以下いずれかの方法でできます。

方法１： ワークシートの GetCellFormula メソッドを利用します

var formula = worksheet.GetCellFormula("C1");

方法２：セルインスタンスの Formula プロパティを利用します

// セルインスタンスを取得
var cell = worksheet.Cells["C1"];

// 計算式を取得
string formula = cell.Formula;

セルに計算式があるかを確認するには、セルインスタンスの HasFormula を利用します。

bool hasFormula = cell.HasFormula;

計算結果を取得

計算式の計算結果は、セルのデータとして設定されます。計算結果を取得するには、セルデータを取得します。

ワークシートからセルデータを取得します。

var data = sheet["E3"];

またはセルインスタンスの Data プロパティより取得します。

var cell = sheet.Cells["E3"];
var data = cell.Data;

計算結果ステータスを確認

ReoGrid では、計算式解析の際にエラーが発生した場合、Excel のように画面に #ERR などの文言を表示せず、セルの FormulaStatus プロパティにエラーの種類を設定します。このプロパティの値は FormulaStatus 列挙体です。

エラー処理のサンプルコードは以下の通りです。

// セル B2 に計算式 A1+B2 を設定
var cell = sheet.Cells["B2"];
cell.Formula = "A1+B2";

// 計算式の結果を確認
switch (cell.FormulaStatus)
{
  default:
  case Formula.FormulaStatus.Normal:
    break;

  case Formula.FormulaStatus.CircularReference:
    MessageBox.Show("循環参照が検出されました");
    break;
}

計算式を削除

セルの計算式を削除するには、セルデータに null を設定するか、セルインスタンスの Formula プロパティに null を設定すると削除できます。

指定した範囲からすべてのセルの計算式を削除したい場合、ワークシートのメソッド ClearRangeContent を利用します。

// ワークシートを取得
var sheet = reoGridControl.CurrentWorksheet;

// A1:B5範囲にあるすべての計算式を削除
sheet.ClearRangeContent("A1:B5", CellElementFlags.Formula);

数式計算におけるデータ型

ReoGrid 内部では常に数値のデータを double 型として取り扱っています。int 型、long 型、float 型などをセルのデータに設定した場合、自動的に double 型に転換されます。数値のみを含む文字列をセルに設定した場合、文字列から抽出した数値がデータとして設定されます。以下は数式計算における利用する型の一覧です。

例えば計算結果が数値の場合、double 型のデータが取得できます。

// E3セルの計算式を設定
worksheet["E3"] = "=(A3+B3)\*C3";

// テスト、結果が true
System.Diagnostics.Debug.Assert(worksheet["E3"] is double);

// double 型のデータを取得
double value = (double)worksheet["E3"];

文字列に含む数値は、数式計算の際に自動的に double 型に転換されます。

worksheet["A1"] = "=10";

object val = worksheet["A1"];
MessageBox.Show(val.GetType().Name); // 結果：Double

計算式をデータの文字列として設定

セルの先頭に「＝」が含まれる文字列をデータとして設定する場合、文字列は計算式として処理されます。この現象を回避するためには、Excel と同様に文字列の先頭に「'」を付けます。ReoGrid は、「'」を除く文字列をセルのデータに設定します。

worksheet["A1"] = "'=10";

A1セルのデータは、文字列「=10」です。

セルと範囲の参照

ReoGrid では Excel と同様に、計算式にセルや範囲への参照を利用できます。参照には絶対位置と相対位置があります。

セル参照

参照アドレスの文法は「列のコード＋行数目」で構成されます。

D3セルはC3セルの相対位置に参照しています。

計算結果：

範囲参照

範囲の参照文法は、「開始セルの参照:終了セルの参照」で構成されます。

結果：

命名範囲参照

計算式に命名範囲の参照を利用できます。命名範囲の作成について詳しくは、「命名範囲」をご覧ください。

// 命名範囲を定義
sheet.DefineNamedRange("myrange", "A1:B5");

// 命名範囲を計算式で利用
sheet.Cells["E10"] = "=SUM(myrange)";

計算式の参照一覧を取得

計算式にあるセルや範囲への参照は、ワークシートのメソッド GetCellFormulaReferenceRanges を利用すると取得できます。

// H8セルに計算式を設定
worksheet["H8"] = "=A1+B1-SUM(A1:C3)+AVERAGE(D1:H5)";

// H8セルの参照リストを取得
var rangeList = worksheet.GetCellFormulaReferenceRanges("H8");

// 取得結果照会
AssertTrue(rangeList != null);
AssertEquals(rangeList[0].Range, new RangePosition("A1"));
AssertEquals(rangeList[1].Range, new RangePosition("B1"));
AssertEquals(rangeList[2].Range, new RangePosition("A1:C3"));
AssertEquals(rangeList[3].Range, new RangePosition("D1:H5"));

画面に参照元と参照先の関係を表示

C5セルの計算式は「=C2+C3」となり、C2とC3セルを参照しています。

この参照関係を画面に表示したい場合、セルインスタンスの TraceFormulaPrecedent プロパティを true に設定します。

var sheet = workbook.CurrentWorksheet;

sheet["C2"] = 10;
sheet["C3"] = 5;

ReoGridCell cell = sheet.Cells["C5"];
cell.Formula = "C2+C3";
cell.TraceFormulaPrecedents = true;

または、ワークシートのメソッド TraceCellPrecedents を利用します。メソッドのパラメータはセルのアドレスです。

sheet.TraceCellPrecedents("C5");

参照元と参照先のデータが変更された場合、参照リストがリセットされるので、参照関係が画面から消えます。常に参照関係を表示したい場合、セルのデータが変更した際のイベントを処理して、再度参照関係の表示を true に設定します。

sheet.CellDataChanged += (s, e) => e.Cell.TraceFormulaPrecedents = true;

数式計算用関数一覧

数式計算用関数一覧は、「数式計算用関数一覧」をご覧ください。

カスタマイズした関数

カスタマイズした関数の作成について詳しくは、「数式計算用のカスタマイズした関数」をご覧ください。