c# - How to make excel auto calculate formula type cells

Question

I have an excel file that has around 40 to 50 sheets of size 8mb and will be increasing may be 15 to 20mb and many are inter-linked with multiple formulas.

So basically if one cell values changes then it will affect multiple sheets because of function and vlookup's etc.

Now I am trying to manipulate the excel sheet dynamically using c#. But I see that the are not calculating instantly.

1. I tried using ExcelLibrary's Calculate function to do this.

Problems with ExcelLibrary are:

a. It is very very slow with Calculate, ExcelPackage(Open) and Save functions**(Makes it unusable)**. b. Need to know all the dependent cell to trigger calculate on them(not preferring workbook or worksheet calculate(the debugger never comes to next line)) which is not efficient way to manage code in feature.

I believe the problem is because it works with ooxml and actually not directly with excel(not sure we can work with excel directly so that it will be like inserting data manually using code).

Note: Trying to test with Excel interop(hoping it will do the job because it opens excel file in background while manipulating.)

    private void TestExcelWithSimultaneousReadOfTwoSheetsFromFile(string fileName)
{
    try
    {
        int count = 0;
        int rowIndex = 1702;
        for (int rowCount = 0; rowCount < 3; rowCount++)
        {
            count = ReadCountFromProdReport(fileName);
            WriteRowToProd(fileName, rowIndex + rowCount);
            count = ReadCountFromProdReport(fileName);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

private void WriteRowToProd(string fileName, int rowIndex)
{
    try
    {
        string logDirectory = System.Configuration.ConfigurationManager.AppSettings["LogDirectory"].ToString().Trim();
        string filePath = logDirectory + fileName + ConfigurationManager.AppSettings["ExcelSaveFileExtension"].ToString().Trim();

        using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(filePath)))
        {
            foreach (ExcelWorksheet workSheet in excelPackage.Workbook.Worksheets)
            {
                if (workSheet.Name.Trim() == "Sample")
                {
                    workSheet.Cells["C" + rowIndex].Value = "15.05.2017";
                    workSheet.Cells["D" + rowIndex].Value = 21701503;
                    workSheet.Cells["E" + rowIndex].Value = "21701503109W";
                    workSheet.Cells["F" + rowIndex].Value = 304;
                    workSheet.Cells["G" + rowIndex].Value = 200;
                    workSheet.Cells["H" + rowIndex].Value = 1520;
                    workSheet.Cells["I" + rowIndex].Value = 11350;
                    workSheet.Cells["J" + rowIndex].Formula = "=7.85*G1701*H1701*I1701/1000000000";
                    workSheet.Cells["K" + rowIndex].Value = 27.080;
                    excelPackage.Save();
                    break;
                }
            }
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

private int ReadCountFromProdReport(string fileName)
{
    try
    {
        string logDirectory = System.Configuration.ConfigurationManager.AppSettings["LogDirectory"].ToString().Trim();
        string filePath = logDirectory + fileName + ConfigurationManager.AppSettings["ExcelSaveFileExtension"].ToString().Trim();

        using (ExcelPackage excelPackage = new ExcelPackage(new FileInfo(filePath)))
        {
            object count = null;
            foreach (ExcelWorksheet workSheet in excelPackage.Workbook.Worksheets)
            {
                if (workSheet.Name.Trim() == "Report")
                {
                    count = workSheet.Cells["E23"].Value;
                    break;
                }
            }
            return Convert.ToInt32(count);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}