Forum Discussion

meghnabilimoria-insightsoftware's avatar
meghnabilimoria-insightsoftware
Copper Contributor
Jun 23, 2025

HFM formula does not work in Automatic Calculation options mode

We are encountering an issue when saving Excel workbooks that contain HFM (Hyperion Financial Management) formulas using Aspose.Cells. The behavior varies depending on the calculation mode configured within the workbook:

  • Manual Calculation Mode: The HFM formulas are preserved correctly upon saving. The data remains visible, although in some cases, it appears with strikethrough formatting.
  • Automatic Calculation Mode: The formulas are either lost or fail to retain their expected values after saving, resulting in missing or incorrect data.

We have attached two sample workbooks for your reference:

  • One opened in Excel 2016, where the data displays correctly.
  • The same file opened in Office 365, where the HFM data is not displayed as expected.

Here is an example of one of the HFM formulas used in the file:

This formula is used to retrieve data from HFM via Smart View. In Manual mode, the formula persists and displays data (albeit sometimes with strikethrough). However, in Automatic mode, the formula either disappears or does not function as intended.

We would appreciate any guidance or insights you can provide on how to preserve these formulas correctly across different calculation modes and Office versions when using Aspose.Cells.

Both excels are in this shared drive, https://drive.google.com/drive/folders/1WgTKSL-W4tuBT1U4nqJW18xUHzJ4K73o?usp=sharing

We also observed Automatic Calculation options work or not work in this combination

developer
excel
office 365

2 Replies

  • indrakumar's avatar
    indrakumar
    Copper Contributor
    Jun 25, 2025

    Hi,

    Thanks for your response and the suggestion to use manual mode before saving:
    workbook.Settings.FormulaSettings.CalculationMode = CalcModeType.Manual; workbook.Save("output.xlsx");

    However, our use case requires the workbook to retain Automatic calculation mode, as this is critical for the expected behavior. Switching to manual mode is not a viable solution for us, as our customers rely on formulas recalculating automatically — and they would not accept a manual calculation setup.

    During our investigation, we found that the presence of the ca attribute in the Excel XML is linked to this issue. When we restrict or remove this attribute, the formulas begin to work as expected — even in Automatic mode. This change allows the data to display properly without needing to switch to manual mode.

    Our customers are using Excel 2016, so we would like to confirm whether the behavior of the ca attribute is consistent across both Excel 2016 and Office 365.

    We’d appreciate your insights on the following:

    1. What is the actual significance of the ca attribute in Excel files?
    2. If we remove or limit this attribute, could it cause any issues or impact other Excel functionalities?
    3. Will the behavior of this attribute be the same in both Excel 2016 and Office 365?

    Looking forward to your response.

    Thanks,
    Indra

     

     

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Jun 24, 2025

    Any chance to force manual mode before save:

     

    workbook.Settings.FormulaSettings.CalculationMode = CalcModeType.Manual;
workbook.Save("output.xlsx");

     

Resources