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;
            }
        }
    }
}