using Syncfusion.XlsIO;
using System.Diagnostics;

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    FileStream inputStream = new FileStream(Path.GetFullPath(@"../../../Data/da3a85848be04e4081f250fffac0f1ee_UM.xlsx"), FileMode.Open, FileAccess.Read);
    IWorkbook workbook = application.Workbooks.Open(inputStream);
    IWorksheet sheet1 = workbook.Worksheets["Input Parameters"];
    
    Stopwatch sw = new Stopwatch();
    sw.Start();
    IRange range = sheet1.Range["A2:I295"];
    sheet1.EnableSheetCalculations();
    sheet1.CalcEngine.AllowShortCircuitIFs = true;
    sheet1.CalcEngine.AlwaysComputeDuringRefresh = true;
    sheet1.CalcEngine.EnableLookupTableCaching = Syncfusion.XlsIO.Calculate.LookupCachingMode.Both;
    sheet1.CalcEngine.ForceRefreshCall = false;
    sheet1.CalcEngine.MaximumRecursiveCalls = 100;
    sheet1.CalcEngine.ThrowCircularException = true;

    sheet1[32, 2].Value2 = 200000;
    
    for (int row = range.Row; row <= range.LastRow; row++)
    {
        for (int col = range.Column; col <= range.LastColumn; col++)
        {

            var calVal = sheet1.Range[row, col].CalculatedValue;

        }
    }

    IWorksheet secondSheet = workbook.Worksheets[32]; //sheet 32 "Output Paramater"

    IRange secondRange = secondSheet.Range["D2:D7"];

    for (int row = secondRange.Row; row <= secondRange.LastRow; row++)
    {
        for (int col = secondRange.Column; col <= secondRange.LastColumn; col++)
        {
            if(secondSheet.Range[row, col].HasFormula)
            {
                //hangs here trying to get calculated value
                var calVal2 = secondSheet.Range[row, col].CalculatedValue;
            }
            
        }
    }

    //check computed value using named range 
    IWorksheet output = workbook.Worksheets["Output Parameters"];
    var out2 = workbook.Names["outputValue"];
    //we get a stack overflow and hangs getting calculated value

    Console.WriteLine($"outputValue value: {out2.RefersToRange.CalculatedValue}");

    //system hangs even trying to get a single cell calculated value or displayText
    IRange total = output.Range["D2:D2"];
    Console.WriteLine($"Computed displayText: {total.DisplayText}");
    Console.WriteLine( $"Computed CalculatedValue: {total.CalculatedValue}");
    //delete if already exist
    if (File.Exists(@"../../../Data/output.xlsx")) File.Delete(@"../../../Data/output.xlsx");

    workbook.SaveAs(@"../../../Data/output.xlsx");
    sw.Stop();
    Console.WriteLine(sw.Elapsed);

}