Status bar SUM does not match worksheet SUM formula when using text formatted cells

When working with formulas and the Status Bar in SpreadJS, you may encounter a situation where the value displayed by the worksheet SUM formula does not match the SUM value shown in the bottom Status Bar. This behavior can occur after changing a cell to Text format, entering a text value, and then replacing that text with a numeric-looking value while keeping the cell formatted as Text.

For example, if a range contains the values 1, 2, 3, and 4, both the worksheet SUM formula and the Status Bar correctly display a total of 10. If one of the cells is then formatted as Text and its value is changed to a non-numeric string, both the worksheet formula and the Status Bar update to 7, as expected. However, after replacing non-numeric string with "3" while the cell remains formatted as Text, the worksheet SUM formula returns 10 while the Status Bar continues to display 7.

Cause 

The Status Bar is functioning as designed. The difference originates from the way SpreadJS evaluates number-like text values in worksheet formulas.

By default, SpreadJS maintains backward compatibility with earlier calculation behavior and may interpret text values containing numeric characters differently from Microsoft Excel. As a result, a value such as "3" stored in a Text-formatted cell can still be included in the worksheet SUM calculation, causing the formula result to differ from Excel.

The Status Bar calculation uses a separate internal mechanism and already behaves consistently with Excel when determining aggregate values for a selected range. Because of this, the Status Bar excludes the Text-formatted value and continues to display 7, while the worksheet formula displays 10.

Solution

If your application requires formula calculations to match Excel behavior, enable Excel-compatible calculation mode in SpreadJS:

GC.Spread.CalcEngine.ExcelCompatibleCalcMode = true;

After enabling this setting, worksheet formulas will evaluate Text-formatted numeric values in a manner that more closely aligns with Microsoft Excel. In the scenario described above, the worksheet SUM formula will return the same result as Excel, eliminating the discrepancy between the formula calculation and the expected Excel behavior.