@ sign is added to the index function in a smartmarker construction

I’ve observed that the smartmarkers are adding a @ sign to the formula when using a smartmarker. Why is this the case and can it influence the results? tested on .NET version 21.4

sample code :

  [TestClass]
public class UnitTest1
{
    [TestMethod]
    public void TestMethod1()
    {
        // ExStart:1
        // The path to the documents directory.
        var workbook = new Workbook();

        // Create a designer workbook

        // Workbook workbook = new Workbook();
        var worksheet = workbook.Worksheets[0];

        worksheet.Cells["A1"].PutValue("Teacher Name");
        worksheet.Cells["A2"].PutValue("&=Teacher.Name");

        worksheet.Cells["B1"].PutValue("Teacher Age");
        worksheet.Cells["B2"].PutValue("&=Teacher.Age");

        worksheet.Cells["C1"].PutValue("Student Name");
        worksheet.Cells["C2"].PutValue("&=Teacher.Students.Name");

        worksheet.Cells["D1"].PutValue("Student Age");
        worksheet.Cells["D2"].PutValue("&=Teacher.Students.Age");

        worksheet.Cells["E1"].PutValue("faulty");
        worksheet.Cells["E2"].PutValue("&=&=INDEX($D$2:D{r},{r}-1,0)");

        worksheet.Cells["F1"].PutValue("working");
        worksheet.Cells["F2"].PutValue("&=&=INDEX($D$2:D7,1,0)");

        // Apply Style to A1:D1
        var range = worksheet.Cells.CreateRange("A1:D1");
        var style = workbook.CreateStyle();
        style.Font.IsBold = true;
        style.ForegroundColor = Color.Yellow;
        style.Pattern = BackgroundType.Solid;
        var flag = new StyleFlag();
        flag.All = true;
        range.ApplyStyle(style, flag);

        // Initialize WorkbookDesigner object
        var designer = new WorkbookDesigner();

        // Load the template file
        designer.Workbook = workbook;

        var list = new List<UsingGenericList.Teacher>();

        // Create an object for the Teacher class
        var h1 = new UsingGenericList.Teacher("Mark John", 30);

        // Create the relevant student objects for the Teacher object
        h1.Students = new List<UsingGenericList.Person>();
        h1.Students.Add(new UsingGenericList.Person("Chen Zhao", 14));
        h1.Students.Add(new UsingGenericList.Person("Jamima Winfrey", 18));
        h1.Students.Add(new UsingGenericList.Person("Reham Smith", 15));

        // Create another object for the Teacher class
        var h2 = new UsingGenericList.Teacher("Masood Shankar", 40);

        // Create the relevant student objects for the Teacher object
        h2.Students = new List<UsingGenericList.Person>();
        h2.Students.Add(new UsingGenericList.Person("Karishma Jathool", 16));
        h2.Students.Add(new UsingGenericList.Person("Angela Rose", 13));
        h2.Students.Add(new UsingGenericList.Person("Hina Khanna", 15));

        // Add the objects to the list
        list.Add(h1);
        list.Add(h2);

        // Specify the DataSource
        designer.SetDataSource("Teacher", list);

        // Process the markers
        designer.Process();

        // Autofit columns
        worksheet.AutoFitColumns();

        // Save the Excel file.
        designer.Workbook.Save("output.xlsx");

        // ExEnd:1
    }
}

}

public class UsingGenericList
{
public class Person
{
public Person(string name, int age)
{
Age = age;
Name = name;
}

    public int Age { get; set; }

    public string Name { get; set; }

    // ExEnd:1
}

public class Teacher : Person
{
    public Teacher(string name, int age) : base(name, age)
    {
        Students = new List<Person>();
    }

    public IList<Person> Students { get; set; }
}

}

results in formula

e2: =@INDEX($D$2:D2;2-1;0)
f2: =@INDEX($D$2:D7;1;0)

@nexxbiz,
I could not reproduce the issue as output file is attached. Please try to use the Aspose.Cells for .NET 21.4 and let us know your feedback. Share your output file and screenshot if you still face the issue.
output.zip (6.5 KB)

actually you did reproduce it… if you look at the contents of the cells it is exactly as i described
image.png (9.7 KB)

the question is: why is there a @ sign?

and I did test it with 21.4

@nexxbiz,

Please open the Excel file we attached in the previous post into MS Excel. See the screenshot that looks fine (regarding dynamic formula) in the F2 cell.
sc_shot1.png (54.6 KB)

We still suspect either there is some issue with your code (you used) or you might be using some older version of the APIs. Could you please create a separate console application, zip the project with the output Excel file (you may exclude Aspose.Cells.Dll for version 21.4) to minimize the size and attach it here. We will open your exact project into VS.NET and execute it to produce the output if we could find your mentioned issue.

I think it is caused by the latest version of excel. I see you don’t use the latest version.

I have looked in the excel file itself. I can see that the @ sign is not present prior to opening the file. see sheet.xml in output.zip

the output file can be found in the same output.zip

output.zip (8.1 KB)

this is solution to test it.
Bug9670IndexFault.zip (86.5 KB)

@nexxbiz,

Which MS Excel version you are using? I tested it with MS Excel 2007, 2010, 2013 and 2017 using your output file and all shows correct formulas without “@” at the start.

We are still not sure where the “@” char is coming on your end and how only you are getting this. Kindly give us exact steps and complete details to reproduce the issue on our end. We will check it soon.

image.png (1.2 KB)

16.0.13901.2036 64 bit

steps are:

run unittest
open output.xlsx
see formula

@nexxbiz,
This is not an issue with Aspose.Cells but an expected behavior by MS Excel 365 as described below:
Excel 365 - Functions preceded by “@” in formulas

OK thanks for letting me know and doing the research…

sorry for the inconvenience.

it qwas a bit puzzling because with sometimes it was added and sometimes it wasn’t for me at least

@nexxbiz,
You are welcome. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Hi i did some further investigation. i found a similar issue that is nt related to the index function but still adds a @ sign to named ranges.

see note D13 on test worksheet
in this item the named ranges get prefixed with an @ sign. Which make the formula fail.

The smartmarker function:
&=&=INDEX(result,MATCH(1,(A{r}=GenderSelection)(B{r}=EducationSelection)(C{r}=NationalitySelection),0),1)

get converted in:
=INDEX(result;MATCH(1;(A13=@GenderSelection)(B13=@EducationSelection)(C13=@NationalitySelection);0);1)

so an @ character is added

In d16 you can see how it should work…

Bug9670IndexFault (match).zip (106.3 KB)

same version of excel, latest version of aspose lib.

in the zip you will find a solution, and the results of the unit test

@nexxbiz,
If you open the output file output-candidates-calculated.xlsx in MS Excel other than version 365, you will observe that formulas are shown correctly. Similarly if you analyze the sheet1.xml, you will see that formulas are correct after calling CalculateFormula(). Hence this is not an issue with Aspose.Cells but a strange behavior of MS Excel 365 that is not in our control. The Sheet1.xml file is attached here for your reference that is extracted from output-candidates-calculated.xlsx and contains correct formulas.

sheet1.xml.zip (1.3 KB)

yeah i observed it too, but what i don’t understand it the fact that the other formula’s that were in the input already seem to work find and don’t get altered.

i don’t get why, there seems that there are no differences, yet the result is different.

@nexxbiz,
We have tried to sort it out but could not find any option to create required output in MS Excel 365. An investigation ticket is logged for a detailed analysis. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-48008 - @sign is added with the functions in the output Excel file after processing the SmartMarkers

@nexxbiz,
Office 365 now support dynamic formulas, if you input =INDEX*(B13=EducationSelection)*(C13=NationalitySelection),0),1) in office 365, it will automaticaly process it as an Array Formulas. We may not support this feature in the near future.

Please change the smart marker in the template file as:
&=&=INDEX*(B{r}=EducationSelection)*(C{r}=NationalitySelection),0),1)~(arrayformula),
then we will convert this smart marker to array formula.

but i don’t want it to be an array formula. i just need to know why there is a difference between the result of the formula’s like the ones that keep on working that were in the template sheet and the smartmarker conversions that don’t work due to the @ sign.

@nexxbiz,
We have noted your feedback and will share our comments after detailed analysis.