Handling formula compatibility between SpreadJS and Excel with Dynamic Arrays

When working with formulas in SpreadJS, you may notice that some formulas behave differently after exporting to Excel. Functions that return arrays may show errors or unexpected results in Excel. 

This article explains how SpreadJS handles dynamic arrays and what settings you need to adjust to ensure compatibility with Excel 365. By default, SpreadJS has allowDynamicArray set to false. This configuration mimics the behavior of Excel 2019, which does not support dynamic arrays.

Example: If you import a file into SpreadJS and recalculate, formulas like =INDEX() may display #N/A.

After exporting to Excel: In Excel 2019, this behavior is expected. In Excel 365, Excel may automatically prefix certain formulas with @ to maintain backward compatibility using implicit intersection.

To match the behavior of Excel 365 and ensure formulas export correctly you should:

1. Enable dynamic array support in SpreadJS:

spread.options.allowDynamicArray = true;

2. Trigger a full recalculation:

spread.calculate(GC.Spread.Sheets.CalculationType.rebuild);

When opened in Excel 365, the formulas and their results will match what you see in SpreadJS.