Calculate dirty cells on demand

Calculate dirty cells on demand

Posted by: chomaratj on 1 December 2025, 12:25 pm EST

• 

  chomaratj

  • Post Options:
  • Link
    Copy

  Posted 1 December 2025, 12:25 pm EST

  Hello,

  I’m making some tests with your library and I have this use case :

  I need to load a xlsm file as fast as possible, put some inputs data in a worksheet, calculate the dirty cells, get the outputs.

  To speed up opening I’m using this options

  var options = new XlsmOpenOptions
      { DoNotRecalculateAfterOpened = true, DoNotAutoFitAfterOpened = true, ImportFlags = ImportFlags.Data | ImportFlags.Formulas };

  To calculate on demand, I’m toggling EnableCalculation property.

  But it’s not clear for me how the Calculate() method, the EnableCalculation property and the CalculationMode are working.

  What would be the best way to recalculate on demand the dirty cells ?

• 

  chirag.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 1 December 2025, 11:34 pm EST

  Hi Julien,

  In DsExcel, the calculation pipeline has three separate controls, and it helps to understand what each one actually does:

  1. EnableCalculation: This is a global switch. When set to false, the engine will not evaluate formulas during editing operations or file loading. Dirty flags will still accumulate, but no recalculation is triggered. When you set it back to true, the engine becomes eligible to calculate again, but it does not automatically recalculate all pending cells.
  2. CalculationMode (Automatic / Manual): This controls whether DsExcel recalculates after changes when calculation is enabled.
     
     Automatic: Formulas recalculate whenever they are dirty.
     
     Manual: The engine keeps track of dirty cells, but only recalculates when the Calculate() method is explicitly called, or the cell containing the formula is accessed.
  3. Calculate(): This is the explicit recalculation trigger. This method recalculates the workbook when calculation is enabled (the default option).

  Given your use case (fast load => write inputs => calculate only dirty cells => read outputs), the recommended setup is:

  1. Load using your current XlsmOpenOptions with DoNotRecalculateAfterOpened = true.
  2. Set:

        workbook.EnableCalculation = false;
        workbook.Options.Calculation.CalculationMode = CalculationMode.Manual;

  3. After writing input values:

        workbook.EnableCalculation = true;
        workbook.Calculate();   // recalculates only dirty cells

  4. Read results normally.

  This pattern ensures:

  • No unnecessary recalculation during load,
  • No recalculation while writing inputs,
  • Only dirty cells + their dependencies are recalculated on demand.

  Please let us know if you encounter any issues or require additional assistance.

  Kind Regards,
  
  Chirag

  References:

  1. Calculate method: https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.Workbook.Calculate.html
  2. XlsmOpenOptions: https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.XlsmOpenOptions.html#GrapeCity_Documents_Excel_XlsmOpenOptions
  3. DoNotRecalculateAfterOpened: https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.XlsxOpenOptions.DoNotRecalculateAfterOpened.html#GrapeCity_Documents_Excel_XlsxOpenOptions_DoNotRecalculateAfterOpened_
  4. Enable or Disable Calculation Engine: https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ManageWorkbook/EnableDisableCalculationEngine#enable-calculation-engine
  5. Calculation Mode: https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ManageFormulas/CalculationMode#site_main_content-doc-content_title