[Feature Request] Add support for reading shared formulas in Excel files

[wuxianggujun]

Summary

OpenXLSX currently throws Shared formulas not supported error when attempting to read formulas from cells that use Excel's shared formula feature. This limitation significantly impacts applications that need to process Excel files with complex formulas.

Problem Description

When reading formulas from Excel cells using cell.formula().get(), OpenXLSX throws an XLFormulaError with the message "Shared formulas not supported" for cells that contain shared formulas. This forces developers to either:

1. Skip formula processing entirely
2. Implement complex workarounds
3. Manually reconstruct formulas

Steps to Reproduce

1. Create an Excel file with formulas like =IF(AND(B14="",C14=""),"",ROUND(E14*F14,2))
2. Copy this formula to multiple cells (Excel automatically creates shared formulas)
3. Try to read the formula using OpenXLSX:

auto cell = worksheet.cell(row, col);
if (cell.hasFormula()) {
    try {
        std::string formula = cell.formula().get(); // Throws exception here
    }
    catch (const OpenXLSX::XLFormulaError& ex) {
        // Error: "Shared formulas not supported"
    }
}

Expected Behavior

OpenXLSX should be able to read shared formulas and return either:

1. The expanded formula for the specific cell (preferred)
2. The master formula with appropriate cell reference adjustments
3. At minimum, the formula structure without throwing an exception

Current Workaround

Currently, we have to manually reconstruct formulas:

catch (const OpenXLSX::XLFormulaError& ex) {
    // Manual reconstruction - not ideal
    if (col == 7) { // G column
        rowInfo.fmt[col - 1].formula = "=IF(AND(B" + std::to_string(row) + 
            "=\"\",C" + std::to_string(row) + "=\"\"),\"\",ROUND(E" + 
            std::to_string(row) + "*F" + std::to_string(row) + ",2))";
    }
}

Impact

This limitation affects:

• Enterprise applications processing financial spreadsheets
• Data migration tools that need to preserve formula logic
• Spreadsheet analysis tools requiring complete formula information
• Document processing pipelines handling complex Excel files

Technical Context

Shared formulas in Excel are stored as:

• A master formula in the first cell of the range
• References to the master formula in subsequent cells
• The xl/worksheets/sheet1.xml contains <f t="shared"> elements

Suggested Implementation

1. Parse shared formula references from the worksheet XML
2. Expand formulas by adjusting cell references relative to the target cell
3. Provide API options for getting either the master formula or expanded formula
4. Maintain backward compatibility with existing non-shared formula handling

Related Libraries

Other Excel libraries handle this successfully:

• openpyxl (Python): Reads shared formulas transparently
• xlnt (C++): May have better shared formula support
• Apache POI (Java): Handles shared formulas correctly

Use Case Priority

This is a high-priority feature for applications dealing with:

• Financial reports with repeated calculation patterns
• Template-based spreadsheets
• Data processing workflows
• Business intelligence tools

Environment

• OpenXLSX Version: [Current version]
• Platform: Cross-platform (Windows/Linux/macOS)
• Compiler: Modern C++ (C++17/20)

Request for Timeline

This feature would significantly improve OpenXLSX's utility for enterprise applications. Could the maintainers provide:

1. An estimated timeline for this feature
2. Whether community contributions would be welcome
3. Technical guidance for potential contributors

Thank you for considering this enhancement. Shared formula support would make OpenXLSX much more competitive with other Excel processing libraries.

[aral-matrix]

Thank you for this feature request. I am hereby adding it to the to-do list. I hope to get around to it within the next months, but I am prioritizing packetization for linux distributions (debian for starters) this summer, so it might take a while.

Either way, I would like to congratulate you on an excellent write-up of what you need, why & hints on how to implement it. That feature request alone makes me want to work on it :)