C＃ Excel出力するシートを参照する別シートのセルの値が変わらない

Question

C＃でエクセル出力をしています。

シートに"sample"という名前を付けたシートに、
値をまとめたデータを上書きする処理を作成しました。

sampleシートは、同じExcelの別シート"read_sample"シートから参照しています。
例えば、sampleシートのA5セルを、read_sampleシートのセルB2で参照している。

ただ、上記のプログラム自体は動作していますが実行後にExcelを確認すると
read_sampleシートのB2セルがプログラム実行前の値になっており、更新されません。

read_sampleシートのB2セルをダブルクリックしてEnterを押すと値が更新されます。

Excelの計算方法は自動になっているのですが、
プログラムで実行し、上書きしたデータは自動更新されないのでしょうか？

また、Excelを上書きした後、全てのシートをアップデートする方法を調べましたが見つけることができず、
ご存じの方いましたらご教示お願いいたします。

参考：抜粋しているため、不足があるかもしれませんがよろしくお願いいたします

book = NPOI.SS.UserModel.WorkbookFactory.Create(file_name);

ISheet sheet = book.GetSheet("sample");

using (var save_fs = new System.IO.FileStream(save_file_name, System.IO.FileMode.Create))
{
　　writeCellString(sheet, 0, 0, "data");
　　writeCellString(sheet, 0, 1, "data");
　　writeCellString(sheet, 0, 2, "data");
　　writeCellString(sheet, 1, 1, "data");
　　writeCellString(sheet, 1, 2, "data");

   book.Write(save_fs);
}

・・・

Comments

[rtazaki]

writeCellStringの中身が無かったのですが、セルに代入するだけの処理だと考えました。(回答欄)

Answer 1

NPOI で Excel の再計算をしたいのであればいくつかの方法があります。

• 方法1: NPOI に再計算させる。ただしネット情報によるとサポートしていない関数もあるらしい。VBAで定義した関数もNG(NotImplementedException が発生する)。

book.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll()

• 方法2: 次回Excelで開いた時にExcelに再計算させるようにフラグを立てる。ただし再計算されるタイミングは Excel で開いた時なので、その前にプログラム等から読むと再計算されてない。またExcel終了時に(再計算されて内容が更新されるので)保存確認される。

var sheet2 = book.GetSheet( "read_sample" );
sheet2.ForceFormulaRecalculation = true;

Answer 2

目的がこれで達成できたか分かりませんが、やってみました。

ポイントは、ForceFormulaRecalculation = trueで、
これによって、保存したファイルをExcelで開き直すと参照先(read_sample)の値が
反映されたのですが、NPOIでファイルを開いただけでは値が変わってはいませんでした。

「npoi 再計算」でググってみたのですが、ほかに良い方法が見つかりませんでした。

using System;
using System.Linq;

namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            var file_name = "testbook.xlsx";
            var book = NPOI.SS.UserModel.WorkbookFactory.Create(file_name);
            // read_sample: Range("C6:G6") = R[5]C[2..5] = sample!B2..6への参照
            var sheet_rs = book.GetSheet("read_sample");
            // read_sampleの現在のセルの値を出力
            foreach (var c in Enumerable.Range(2, 5))
            {
                var row = sheet_rs.GetRow(5) ?? sheet_rs.CreateRow(5);
                var cell = row.GetCell(c) ?? row.CreateCell(c);
                Console.WriteLine(cell.StringCellValue);
                // data1 data2 data3 data4 data5
            }

            var sheet = book.GetSheet("sample");
            var save_file_name = "savebook.xlsx";
            using (var save_fs = new System.IO.FileStream(
                save_file_name, System.IO.FileMode.Create))
            {
                foreach (var r in Enumerable.Range(1, 5))
                {
                    var row = sheet.GetRow(r) ?? sheet.CreateRow(r);
                    var cell = row.GetCell(1) ?? row.CreateCell(1);
                    // data6..10
                    cell.SetCellValue($"data{r + 5}");
                    // ----
                    // ForceFormulaRecalculationによって、
                    // 「次回読み込み時に」「Excelによって」再計算が強制される模様。
                    // ----
                    // sheet.ForceFormulaRecalculation = true;
                }

                // NPOIに再計算させる。
                book.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

                // read_sampleが保存前に更新されたか確認する。
                // 期待: data6..10になって欲しいが、そのままでもよい。
                // 結果: data6..10
                foreach (var c in Enumerable.Range(2, 5))
                {
                    var row = sheet_rs.GetRow(5) ?? sheet_rs.CreateRow(5);
                    var cell = row.GetCell(c) ?? row.CreateCell(c);
                    Console.WriteLine(cell.StringCellValue);
                    // data6 data7 data8 data9 data10
                }
                book.Write(save_fs);
            }
            var book2 = NPOI.SS.UserModel.WorkbookFactory.Create(save_file_name);
            var sheet_srs = book2.GetSheet("read_sample");

            // 保存されたread_sampleの現在のセルの値を出力
            // 期待: data6..10になって欲しい。
            // 結果: data6..10
            foreach (var c in Enumerable.Range(2, 5))
            {
                var row = sheet_srs.GetRow(5) ?? sheet_srs.CreateRow(5);
                var cell = row.GetCell(c) ?? row.CreateCell(c);
                Console.WriteLine(cell.StringCellValue);
                // data6 data7 data8 data9 data10
            }
        }
    }
}

Comments

[seastar]

せっかく迅速に答えて頂いたにもかかわらず、返事が遅れ大変申し訳ありません。

ForceFormulaRecalculation = true
npoi 再計算などキーワードを元にベストな方法を再考させて頂きます。

C#側で苦労するなら、エクセル側でファイルオープン時に更新する細工をするなど、利用者にとっていい方法を模索しようと思います。
NPOIは、回答にある通り、エクセル側の参照を更新させることができない、もしくは難しいため、エクセル側で対応することになると思います。

ありがとうございました。