Implicit Intersection "@" sign operator does not show up in Formulas

Aspose.Cells Product Family
,
1

Consider the attached workbook, and the following code:

ExampleIntersections.zip (10.3 KB)

        var workbook = new Aspose.Workbook("ExampleIntersections.xlsx");

        // "=EntireRowNamedRange" but should be "=@EntireRowNamedRange"
        var a5Formula = workbook.GetRangeByAddress("Example!A5").Formula;

        // "=EntireRowNamedRange" 
        var a6Formula = workbook.GetRangeByAddress("Example!A6").Formula;

I would have expected the “@” sign to appear in the formula, both because it changes the actual rendered output of the workbook (see the difference between rows 5 and 6), but it also has bearing on these Excel compatibility topics:

We would like to see the “@” to help programmatically identify formulas where its presence may be an issue. For example, try editing A4 in the attached workbook to have an @ sign in front the formula, and you’ll get this error message:

image.png (17.3 KB)

Using precedent tracing and some logic of checking named range size, if Aspose were to return the “@” sign in the formula, we would be able to programmatically detect the same situation that popup is warning about.

Would it be possible for Aspose to allow getting/setting the “@” character in cell/range Formulas?

Thank you

2

@ag.canalyst,
Are you using latest version of the API as I can not find details of GetRangeByAddress method for investigating the issue.

3

Hi @ahsaniqbalsidiqui

My apologies, that method is a wrapper we have around Aspose. Here’s the equivalent code:

        Workbook w = new Workbook("ExampleIntersections.xlsx");
        var a5Formula = w.Worksheets["Example"].Cells.GetCell(4, 0).Formula;
        var a6Formula  = w.Worksheets["Example"].Cells.GetCell(5, 0).Formula;
4

@ag.canalyst,
We have observed this scenario and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-48547 - Implicit Intersection "@" sign operator does not show up in Formulas

1 Like
5

@ag.canalyst,
We have investigated this feature. To check and add the ‘@’ while building the formulas, we need to calculate every operand in the formula to check whether it represent one single value or multiple values. This means that we must calculate it first when constructing each formula string. Such kind of operation will definitely reduce the performance of getting formula significantly.

If you need this feature, we may consider to provide an option for you to denote explicitly that we need to check and add ‘@’ automatically when getting formulas. However, it is a complicated task so we cannot support it soon. We will try to support it in the forth quarter of this year.

1 Like
6

Hi @ahsaniqbalsidiqui

Thank you very much for your response. That is understandable and in that case we can manage without it for now.

7

@ag.canalyst,

You are welcome.