Issue with Formulas (presence of @ before function name

Aspose.Cells Product Family
1

Hi guys,

Maybe some of you can help me find a solution to my case.

I generate some algorithm with default constructor to generate a workbook with sheets.
(for a french version of Excel)

In one column of the sheet named Data, I try to put a formula which do the following :
result = “=SOMME.SI.ENS(Prix!$R:$R;Prix!$A:$A;@CELLULE(”“contenu”“;INDIRECT(”“A”“&LIGNE())))”

For an unexpected reason this result in the following when I open the generated Workbook :
=@SOMME.SI.ENS(Prix!$R:$R;Prix!$A:$A;@CELLULE(“contenu”;INDIRECT(“A”&@LIGNE())))

I’ve read some things around aggregating functions or so, but in this case each of the subfunction only returns one result, so no reason to be replaced by an @version of this specific version.

Is there any way to prevent Excel or Aspose to add this @ in the resulting formula ?

I’ll really appreciate any help on this.
I could also provide the resulting file.

Many thanks for your feedbacks

Best
Louis

2

@Hellmer

Hi Louis,

The issue you’re encountering with the “@” symbol being added before your formula functions in Excel is due to how Excel interprets structured references, particularly in tables. The “@” symbol indicates that the formula is using implicit intersection, which is Excel’s method for handling references in a table context.

Cause: The “@” symbol appears when Excel interprets your formula as a structured reference within a table.

Solution: To prevent Excel from adding the “@” symbol, follow these steps:

  1. Check the Formula Context: Ensure that the formula is not being interpreted as a structured reference. If you’re using a table, consider switching to regular cell references instead.

  2. Modify the Formula: Instead of using the @CELLULE function, directly reference the cell without the “@”.

  3. Use Aspose.Cells API: When setting the formula in Aspose.Cells, ensure that you are using the correct syntax. Here’s a code snippet to set the formula without the “@” symbol:

    Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
worksheet.getCells().get("A1").putValue(1);
worksheet.getCells().get("A2").putValue(2);
worksheet.getCells().get("A3").putValue(3);
worksheet.getCells().get("A4").setFormula("=SOMME.SI.ENS(Prix!$R:$R;Prix!$A:$A;CELLULE(\"contenu\";INDIRECT(\"A\"&LIGNE())))");
workbook.calculateFormula();
workbook.save("output.xlsx");

  4. Review Excel Settings: Sometimes, Excel settings can affect how formulas are interpreted. Ensure your Excel version is up to date and check the settings related to formula calculation.

Next Steps: If these steps do not resolve the issue, please share the generated file, and I will take a closer look.

Let me know if this helps!

3

@Hellmer

It is ms excel’s behavior to do with references or functions that may produce multiple values from when it introduced the new kind of array formula: dynamic array formula. To remove the ‘@’, you may try to set the formula as dynamic array formula instead: Cell.SetDynamicArrayFormula