setFormula2() 4700x slower with enableIterativeCalculation on large workbooks

[twentyaibot]

Summary

IRange.setFormula2() takes ~4,700ms per cell when enableIterativeCalculation is true and the workbook contains ~560K formula cells created via bulk setFormula2() on rectangular ranges. The same operation takes <1ms with iterative calculation disabled.

This makes it impossible to add SUM formulas to a totals row in a large workbook within a reasonable time — 130 SUM formulas × 4.7s = ~10 minutes.

Environment

• GcExcel Java 9.0.3
• Java 21
• Linux x86_64

Steps to Reproduce

1. Create a sheet with 5,000 rows × 17 columns of numeric data
2. Use setFormula2() on 7 rectangular ranges (16 cols × 5,000 rows each) to create ~560K formula cells
3. Enable iterative calculation: wb.getOptions().getFormulas().setEnableIterativeCalculation(true)
4. Set wb.setDeferUpdateDirtyState(true)
5. Call setFormula2("=SUM(Y4:Y5003)") on a single cell

Expected: <1ms
Actual: ~4,700ms

Minimal Reproduction Code

import com.grapecity.documents.excel.Workbook;
import com.grapecity.documents.excel.IWorksheet;

public class DsExcelBugRepro {

 public static void main(String[] args) {
 Workbook wb = buildWorkbook();

 // THIS LINE CAUSES THE 4700x SLOWDOWN:
 wb.getOptions().getFormulas().setEnableIterativeCalculation(true);

 wb.setDeferUpdateDirtyState(true);

 IWorksheet sheet = wb.getWorksheets().get(0);
 int totalsRow = 5004; // 1-based, row after 5000 data rows
 int lastRow = 5003;

 System.out.println("With enableIterativeCalculation=true:");
 for (int ci = 24; ci <= 28; ci++) { // columns Y through AC
 String col = indexToCol(ci);
 String formula = "=SUM(" + col + "4:" + col + lastRow + ")";
 long start = System.currentTimeMillis();
 sheet.getRange(col + totalsRow).setFormula2(formula);
 System.out.println(" " + col + totalsRow + " -> " + (System.currentTimeMillis() - start) + "ms");
 }
 // Output: ~4700ms per formula
 }

 static Workbook buildWorkbook() {
 Workbook wb = new Workbook();
 IWorksheet sheet = wb.getWorksheets().get(0);
 int rows = 5_000;
 int ds = 3; // data start row (0-based)
 int lastRow = ds + rows; // 5003
 String r1 = "4"; // first data row (1-based)

 // 17 columns of numeric data
 Object[][] data = new Object[rows][17];
 for (int i = 0; i < rows; i++)
 for (int c = 0; c < 17; c++)
 data[i][c] = 50.0 + (i % 500) + c * 10.0;
 sheet.getRange(ds, 5, rows, 17).setValue(data);

 // 560K formula cells via bulk setFormula2 on 16-col × 5000-row ranges
 sheet.getRange("Y4:AN" + lastRow).setFormula2("=IF(AND(F" + r1 + "=0,G" + r1 + ">0),G" + r1 + ",0)");
 sheet.getRange("AQ4:BF" + lastRow).setFormula2("=IF(AND(G" + r1 + ">F" + r1 + ",Y" + r1 + "=0),G" + r1 + "-F" + r1 + ",0)");
 sheet.getRange("BI4:BX" + lastRow).setFormula2("=IF(AND(F" + r1 + ">0,G" + r1 + "=0),F" + r1 + ",0)");
 sheet.getRange("CA4:CP" + lastRow).setFormula2("=IF(AND(G" + r1 + "<F" + r1 + ",BI" + r1 + "=0),F" + r1 + "-G" + r1 + ",0)");
 sheet.getRange("CR4:DH" + lastRow).setFormula2("=IF(F" + r1 + ">0,1,0)");
 sheet.getRange("DK4:DZ" + lastRow).setFormula2("=IF(AND(CR" + r1 + "=0,CS" + r1 + "=1),1,0)");
 sheet.getRange("EC4:ER" + lastRow).setFormula2("=IF(AND(CR" + r1 + "=1,CS" + r1 + "=0),1,0)");

 return wb;
 }

 static String indexToCol(int c) {
 StringBuilder sb = new StringBuilder();
 while (c >= 0) { sb.insert(0, (char) ('A' + c % 26)); c = c / 26 - 1; }
 return sb.toString();
 }
}

Observed Results

enableIterativeCalculation	Time per SUM formula
false	<1ms
true	~4,700ms

The slowdown is 4,700x. Cross-sheet references have no effect — tested with and without, same results.

Impact

In our application, we enable iterative calculation to handle workbooks with circular references. When our AI agent builds a financial model with 5,000 rows and then adds ~130 SUM formulas to a totals row, the total setFormula2 time is ~10 minutes, causing timeouts.

Suspected Cause

When iterative calculation is enabled, setFormula2() appears to perform an expensive dependency graph traversal (possibly checking for circular references) for each formula added. With 560K existing formula cells, this traversal takes ~4.7 seconds per formula.