Excel、SPREAD、テキストボックスからも！外部から取得した値を数式内で参照する

Excelの外部参照では、別のExcelブックにあるシートのセルを参照することができますが、SPREADでも数式の中で別のSPREADやExcelファイルのセルを参照できれば、と思った経験はないでしょうか。

2019年2月20日に発売されたSPREAD for Windows Forms 12.0JのIWorkbookSetインタフェースを使うことで、Excelの外部参照のような処理を実現することができます。

さらにExternalVariableクラスが提供する外部変数という機能を使うと、テキストボックスに設定された値などもSPREADの数式内で参照できるようになります。

【メモ】
「SPREAD for Windows Forms 12.0Jの新機能」で紹介している次の2つの機能は、いずれも「GrapeCity.Spreadsheet名前空間」のクラスやインタフェースで実装されています。

IWorkbookSetインタフェースでExcelとの連携強化
ExcelのVBA互換の新しいAPIセット

今回は、IWorkbookSetインタフェースが提供する外部参照とExternalVariableクラスが提供する外部変数を使って簡単なサンプルを作成してみましょう。（記事の最後で作成済みのサンプルも公開しています。）

サンプルの説明
外部参照（別のSPREAD）
外部参照（Excelファイル）
外部変数（テキストボックス）

1.サンプルの説明

冒頭のGIFアニメーションは、今回作成するサンプルを動作させているときのものですが、次の3種類の文字列をSPREADの数式の中で参照し、それらをCONCAT関数を使って文字列結合しています。

外部変数の機能：テキストボックスコントロールに入力した「昨日」
外部参照の機能：別のSPREADのSheet1上のA1セルに入力した「今日」
外部参照の機能：ExcelファイルのShee1上のA1セルに入力した「明日」

最初に各フォーム上に配置されているコントロールとその機能について説明します。

Form1

SPREAD：外部参照や外部変数の機能を数式内で利用します。
ボタン：Excelファイルで設定した内容をSPREADに反映・再計算します。

Form2

テキストボックス：外部変数と連携してForm1のSPREADにリンクします。
SPREAD：外部参照先のSPREAD。Form1のSPREADから参照されます。
ボタン：Form1のSPREADの再描画と、Excelファイルの起動を行います。

次に操作の順に従って各動作を確認しましょう。

1. 起動

サンプルを起動するとForm1とForm2が表示されます。

2. Form2上の操作

テキストボックスとSPREADのA1セルにそれぞれ「昨日」と「今日」という文字列を入力します。そして、ボタンをクリックすると、Form1上のSPREADが再描画され、Excelファイルが起動されます。

このときForm1上のSPREADには、テキストボックスとSPREADに入力された文字列が結合されて「昨日今日」と表示されています。

以下に、ボタンのClickイベントで実行しているコードを記載します。

private void button1_Click(object sender, EventArgs e)
{
// Form1上のfpSpread1の再描画
this.Owner.Controls["Book1"].Refresh();
// Book3.xlsxの表示
System.Diagnostics.Process process;
process = new System.Diagnostics.Process();
process.StartInfo.FileName = "..\\..\\Book3.xlsx";
process.Start();
}

3. Excelファイルでの操作

表示されたExcelファイルのA1セルに「明日」と入力し、変更内容を保存してExcelを閉じます。

注記：
Excelファイルで変更された内容は、ファイルが保存されてExcelが閉じられるまで外部参照することができません。

4. Form1上の操作

Excelファイルで変更された内容をSPREADで参照するために、ボタンをクリックします。これによって、Excelファイルで設定した内容がSPREADに反映されて、外部参照を使った数式が再計算されます。その結果、SPREADのA1セルの文字列は「昨日今日」から「昨日今日明日」へと変更されます。

2. 外部参照（別のSPREAD）

まず最初に、以下のような手順でプロジェクトとExcelファイルを作成しておきます。

新規のWindows Formsプロジェクトを作成する
Form1にfpSpread1とbutton1を配置する
プロジェクトにForm2を追加する
Form2にtextBox1、fpSpread1、button1を配置する
Form2上のfpSpread1の名称をfpSpread2に書き換える
Excelを起動してデフォルト状態のBook1.xlsxファイルを保存する
Book1.xlsxのファイル名をBook3.xlsxに変更する
Book3.xlsxをForm1.csなどと同じフォルダ内に配置する

次にForm1クラスの中に下記のコードを記述します。

// ワークブックセットの宣言
private GrapeCity.Spreadsheet.IWorkbookSet workbookSet;
private void Form1_Load(object sender, EventArgs e)
{
// Form2の表示
System.Drawing.Point pos = this.Location;
pos.Offset(this.Width - 10, 0);
Form2 f2 = new Form2() { Location = pos };
f2.Show(this);
// ワークブックセットの作成
workbookSet =
GrapeCity.Spreadsheet.Win.Factory.CreateWorkbookSet();
// fpSpread1をBook1としてワークブックセットに追加
this.AddSpreadToWorkbook(fpSpread1, "Book1");
// Form2上のfpSpread2をBook2としてワークブックセットに追加
this.AddSpreadToWorkbook(f2.Controls["fpSpread2"]
as FarPoint.Win.Spread.FpSpread, "Book2");
// ExcelファイルをBook3としてワークブックセットに追加
this.AddExcelToWorkbook("..\\..\\Book3.xlsx", "Book3");
// Form2上のtextBox1の値をfpSpread1の外部変数に設定
fpSpread1.AsWorkbook().Names.AddExternalVariable(
"tbValue", new TextBoxExternalVariable(
f2.Controls["textBox1"] as TextBox, true));
// fpSpread1に数式を設定：外部参照と外部変数の利用
fpSpread1.ActiveSheet.Cells[0, 0].Formula =
"CONCAT(tbValue,[Book2]Sheet1!A1,[Book3]Sheet1!A1)";
}
public void AddSpreadToWorkbook(
FarPoint.Win.Spread.FpSpread spread, string bookname)
{
// FpSpreadをワークブックとしてワークブックセットに追加
GrapeCity.Spreadsheet.IWorkbook workbook;
workbook = spread.AsWorkbook();
workbook.Name = bookname;
workbook.SaveLinkValues = true;
workbookSet.Workbooks.Add(workbook);
}

このコードでは、次の手順で別のSPREADのセルを外部参照しています。

ワークブックセットを作成する

外部参照元のfpSpread1をワークブックセットに追加する

外部参照先のfpSpread2をワークブックセットに追加する

外部参照元のfpSpread1に外部参照を使った数式を設定する

ここで注意していただきたいのは、ワークブックセットに追加するのは、外部参照先のfpSpread2だけでなく、外部参照元のfpSpread1も含まれる点です。また、ワークブックセットにはFpSpreadクラスそのものは追加できないので、FpSpreadクラスのAsWorkbookメソッドを使ってIWorkbookに変換したものを追加します。

最後に、数式内で[ワークブック名]シート名!セル名の形式で別のワークブックのセルを外部参照します。

    // fpSpread1に数式を設定：外部参照と外部変数の利用
fpSpread1.ActiveSheet.Cells[0, 0].Formula =
"CONCAT(tbValue,[Book2]Sheet1!A1,[Book3]Sheet1!A1)";

3. 外部参照（Excelファイル）

Excelファイルのセルを外部参照する場合も基本的な手順は同じですが、ワークブックの作成方法が異なります。IWorkbookSetのWorkbooks.Openメソッドを使って対象のExcelファイルを読み込むことでワークブックを作成します。

外部参照の機能を使ってExcelファイルの内容を参照するときには、該当するExcelファイルが閉じられた状態でIWorkbookを生成します。Excelファイルが別のアプリケーション（通常はExcel）で開かれている場合には例外が発生するので注意してください。

さらに、Excelファイルを参照するIWorkbookを生成したときには、参照元のSPREADのシートでRecalculateAllメソッドを呼び出して明示的に再計算する必要があります。

private void button1_Click(object sender, EventArgs e)
{
// Book3の更新（再設定）
this.AddExcelToWorkbook("..\\..\\Book3.xlsx", "Book3");
// Book3に関連した数式の再計算
fpSpread1.ActiveSheet.RecalculateAll();
}
private void AddExcelToWorkbook(string filename, string bookname)
{
// 既存の同名ワークブックを削除
if (workbookSet.Workbooks[bookname] != null)
{
workbookSet.Workbooks[bookname].Close();
}
// Excelファイルをワークブックとしてワークブックセットに追加
GrapeCity.Spreadsheet.IWorkbook workbook;
workbook = workbookSet.Workbooks.Open(filename);
workbook.Name = bookname;
workbook.SaveLinkValues = true;
}

外部参照を行うアプリケーションの実行中にExcelファイルの内容が変更されたときは、Excelファイルに対応したIWorkbookを再生成する必要があります。また、上記のAddExcelToWorkbookメソッドには、既存のIWorkbookを削除するコードが入っていますが、このコードがないとワークブックが重複して、例外が発生します。

4. 外部変数（テキストボックス）

最後に、外部変数を使ってテキストボックスの値をSPREADで外部参照する方法をご紹介します。

今回のサンプルで使う外部変数は、ヘルプの外部変数のテキストボックスコントロールでの使用で紹介している継承クラスをそのまま利用します。このクラスは、ExternalVariableクラスを継承して、テキストボックスとSPREADの間で値を交換する機能を追加しています。

// TextBoxコントロールのTextプロパティを外部変数に設定
public class TextBoxExternalVariable :
GrapeCity.Spreadsheet.ExternalVariable
{
private TextBox _textBox;
private bool _asInput;
public TextBoxExternalVariable(TextBox textBox, bool asInput)
{
_textBox = textBox;
if (asInput)
{
textBox.TextChanged += TextBox_TextChanged;
}
}
private void TextBox_TextChanged(object sender, EventArgs e)
{
Dirty();
}
protected override bool OnDirtying()
{
return !_asInput;
}
protected override void OnDirtied()
{
Refresh();
}
protected override void EvaluateCore(
GrapeCity.CalcEngine.IEvaluationContext context,
GrapeCity.CalcEngine.IValue result)
{
string text = _textBox.Text;
if (!string.IsNullOrEmpty(text) &&
double.TryParse(text, out double dblValue))
{
result.SetValue(dblValue);
}
else
{
result.SetValue(text);
}
}
public void Refresh()
{
if (!_asInput)
{
_textBox.Text = this.Value.GetText();
}
}
}

上記の外部変数をSPREADで利用するには、下記のようにIWorkbookのNamesコレクションのメンバであるAddExternalVariableメソッドの第2引数に継承クラスのインスタンスを設定します。これによって、AddExternalVariableメソッドの第1引数に指定した変数名がSPREADの数式で使えるようになります。

// Form2上のtextBox1の値をfpSpread1の外部変数に設定
fpSpread1.AsWorkbook().Names.AddExternalVariable(
"tbValue", new TextBoxExternalVariable(
f2.Controls["textBox1"] as TextBox, true));

【メモ】
同一SPREAD内では、数式やセル範囲に名前を付けて参照する場合にも外部変数を利用できますが、別のSPREADで設定した外部変数を参照することはできません。この場合には、数式名の作成で解説しているカスタム名を使います。

作成したサンプルはこちらで公開しています。SPREAD for Windows Forms 12.0J SP1で作成されています。

ダウンロード（zipファイル：42.7KB）

終わりに

SPREADの外部参照と外部変数には、今回ご紹介したほかにも便利な使い方があります。以下のヘルプを是非ご一読いただければと思います。

なお、2019年4月17日に公開されたSPREAD for Windows Forms 12.0JのSP1では、新元号「令和」とVisual Studio 2019に対応しました。新しい時代を迎えてますます充実していくSPREADにご期待ください。

サービスパックは以下のアップデートページよりダウンロード可能です。

＼この記事をシェアする／