C#：EPPLUSで数式設定変更＆値検索

C#：EPPLUSで数式設定変更＆値検索

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using OfficeOpenXml;

namespace EpplusTest
{
    public class Program
    {
        public static void Main(string[] args)
        {
            //ModifiedExcel();
            SelectExcel();
        }

        public static void ModifiedExcel()
        {
            var path = string.Format(@"{0}\テスト.xlsx", TEMP_FOLDER);

            FileInfo file = new FileInfo(path);

            using (var package = new ExcelPackage(file))
            {
                ExcelWorkbook workBook = package.Workbook;
                ExcelWorksheet currentWorksheet = workBook.Worksheets.SingleOrDefault(w => w.Name == "SheetName");

                int totalRows = currentWorksheet.Dimension.End.Row;
                int totalCols = currentWorksheet.Dimension.End.Column;

                // 数式設定
                currentWorksheet.Cells["W5"].Formula = "=IFERROR($S$5*$V5/$U$5,0)";
                currentWorksheet.Cells["W6"].Formula = "=IFERROR($S$6*$V6/$U$6,0)";

                // 数式設定をした後、その値を取得して再利用する場合(=数式設定の値を確定させる)
                // (https://github.com/JanKallman/EPPlus/wiki/Formula-Calculation)

                // 指定セルに数式設定
                currentWorksheet.Cells["AM7"].Formula = "INT(AK7)";
                currentWorksheet.Cells["AM8"].Formula = "INT(AK8)";
                currentWorksheet.Cells["AM9"].Formula = "INT(AK9)";
                currentWorksheet.Cells["AM10"].Formula = "INT(AK10)";
                currentWorksheet.Cells["AM11"].Formula = "INT(AK11)";
                currentWorksheet.Cells["AM12"].Formula = "INT(AK12)";
                currentWorksheet.Cells["AM13"].Formula = "INT(AK13)";

                // ファイル単位、シート単位、セル単位でCalculate()を呼び出して値を確定させる。
                //currentWorksheet.Cells["AM7"].Calculate(); // セル単位でCalculate()
                workBook .Calculate();//ファイル単位でCalculate()
                currentWorksheet.Calculate(); // シート単位でCalculate()
                var amVal = currentWorksheet.Cells["AM8"].Value;
                var amCell = currentWorksheet.Cells["AM8"];
                var aqCell = currentWorksheet.Cells["AQ3"];


                package.Save();
            }
        }

        public static void SelectExcel()
        {
            var path = string.Format(@"{0}\テスト.xlsx", TEMP_FOLDER);

            FileInfo file = new FileInfo(path);

            using (var package = new ExcelPackage(file))
            {
                ExcelWorkbook workBook = package.Workbook;
                ExcelWorksheet currentWorksheet = workBook.Worksheets.SingleOrDefault(w => w.Name == "SheetName");

                int totalRows = currentWorksheet.Dimension.End.Row;
                int totalCols = currentWorksheet.Dimension.End.Column;

                // 指定範囲のセルの中で指定の値を持つセルを抽出
                var query =
                    from cell in currentWorksheet.Cells["H4:H558"]
                    where cell.Value?.ToString() == "例外"
                    select cell;

                // 抽出したセルからセル住所(ex)"H123"を取得して、同じ行の別カラムの値を変更
                foreach(var cell in query)
                {
                    string adr = cell.Address;
                    currentWorksheet.Cells[adr.Replace("H", "W")].Formula = "=iferror($s$1*$v5/$u$1,0)";
                }

                // ## 日付が設定されているセルの扱い方
                var Cell = currentWorksheet.Cells["D5"];
                // 日付形式はvalueからdatetimeに変換
                long dateNum = long.Parse(ahCell.Value.ToString());
                DateTime result = DateTime.FromOADate(dateNum);
                string strResult = result.ToString("yyyy/MM/dd");


                // ## 既存のエクセルファイルの値を変更した後、それを基準に検索して再度値を変更
                currentWorksheet.Cells["H500"].Value = "テスト";

                // package.Save()前の変更内容も検索可能
                var query2 =
                    from cell in currentWorksheet.Cells["H4:H558"]
                    where cell.Value?.ToString() == "テスト"
                    select cell;

                foreach (var cell in query2)
                {
                    string adr = cell.Address;
                    currentWorksheet.Cells[adr.Replace("H", "W")].Formula = "=iferror($s$1111*$v1111/$u$1111,0)";
                }

                package.Save();
            }
        }

        public static string TEMP_FOLDER
        {
            get
            {
                string logFolder = string.Format(@"{0}\{1}", Environment.CurrentDirectory, "Temp");
                if (!Directory.Exists(logFolder)) { Directory.CreateDirectory(logFolder); }
                return logFolder;
            }
        }
    }
}

参考

github.com