search

dotnet / Open-XML-SDK

Open XML SDK by Microsoft
https://www.nuget.org/packages/DocumentFormat.OpenXml/
MIT License
4.01k stars 544 forks source link

Adding CellFormula - on opening with Excel, @ have been injected. How to avoid? #1793

Open hohae opened 1 month ago

hohae commented 1 month ago

Describe the bug I'm building up an excel sheet. I'm using CellFormula to add a formula to a cell. That works fine. When opening the file with Excel, @ characters are injected into the formula and the formula is broken.

Sample formula:

=WENN(ANZAHL2('I&C Extension'!D3:E3)>1;INDEX(D2:D1300;VERGLEICH(INDEX(A:A;ZEILE())&"OC001";A2:A1300&C2:C1300;0));Material!C9)

ends up as

=WENN(ANZAHL2('I&C Extension'!D3:E3)>1;INDEX(D2:D1300;VERGLEICH(@INDEX(A:A;ZEILE())&"OC001";@A2:A1300&@C2:C1300;0));Material!C9)

Using version 2.20.

There are hints for VBA to use CellFormula2, but this isn't available in the SDK. How to avoid this?

tomjebo commented 2 weeks ago

@hohae Can you share sample repro code for this issue and does the problem reproduce with the latest version of the SDK?

tomjebo commented 6 days ago

@hohae This behavior is in Excel and not in the SDK. The behavior you're seeing is a new feature that arrived with Dynamic Arrays. It is explained in detail here: https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

Pre-DA (dynamic arrays featured) Excel, didn't need these because the default behavior when specifying a range of cells in a formula where only one cell is required, is to use the cell that aligns with the formula's cell address either vertically or horizontally. This is called IIE or "Implicitly Intersection Evaluation" [sic].

With DA featured Excel, the default would be to apply the formula to all the cells in the range that was used to address the single cell requested by the formula. The "@" was added by Excel to indicate to someone reading it that Excel would use DA style processing called "Array Evaluation" or also known as "lifting" for that particular cell range reference.

This is not a feature of the SDK or of VBA per se but of Excel. With the SDK, you as a programmer could add "@" to your formula but would have to ensure that it followed the rules of Excel which is beyond the scope of the SDK's CellFormula processing. CellFormula is oblivious to the contents of formulas and modifying it to be aware of and process formula strings would not be desirable for the SDK as a framework. Nor should it be necessary to write SpreadsheetML successfully.

If you are seeing an error in the SDK due to this new "@" convention of DA featured Excel, then please clarify what's happening.