Question on how to execute addins

Question

Hello,I'm new to Syncfusion so I was wondering if someone can point me to information on how Excel Addins work in Syncfusion.It seems that Excel Addins are the way to run my own custom functions using Syncfusion but I'm having trouble finding how it works?I found this article that says I need to use them instead of VBA Macros, given they are not supported by Syncfusion:https://www.syncfusion.com/forums/110910/using-xlsio-to-call-macro-in-excel...and I was able to find examples of how to REGISTER my addin with a spreadsheet:https://help.syncfusion.com/file-formats/xlsio/working-with-formulas?_ga=2.111452676.1242965561.1614201214-1859188818.1614201214#add-in-functions...what isn't clear to me is how to execute them?Will addin functions be automatically executed by Syncfunction if they are registered with the spreadsheet?OR are they only executed when opened in Microsoft Excel?IF they are only executed when using Microsoft Excel, is there ANY WAY we can add our own custom code / functions to a spreadsheet and have Syncfusion execute them programmatically when loading the spreadsheet?Thank you,Luis Silva

Shamini Kiruba Sobers · Accepted Answer

Hi Luis, 

Greetings from Syncfusion support. 





Query 

Response 


It seems that Excel Addins are the way to run my own custom functions using Syncfusion but I'm having trouble finding how it works? 
 
...and I was able to find examples of how to REGISTER my addin with a spreadsheet: 
https://help.syncfusion.com/file-formats/xlsio/working-with-formulas?_ga=2.111452676.1242965561.1614201214-1859188818.1614201214#add-in-functions 

IF they are only executed when using Microsoft Excel, is there ANY WAY we can add our own custom code / functions to a spreadsheet and have Syncfusion execute them programmatically when loading the spreadsheet? 

This UG link probably contains all the answers for your queries. 
https://help.syncfusion.com/file-formats/xlsio/working-with-formulas?_ga=2.111452676.1242965561.1614201214-1859188818.1614201214#add-in-functions 

However, we will summarize them in simple steps for your understanding. 

Create a .xlam file (say AddIn.xlam) with your own custom function.
Register the AddIn in Microsoft Excel by adding the above file to Excel Application by locating the .xlam file through the menu (Developer -> Add-ins -> Browse).
Use the below code snippet to execute the Add-in programmatically.
 




using (ExcelEngine excelEngine = new ExcelEngine()) 
{ 
    IApplication application = excelEngine.Excel; 
    application.DefaultVersion = ExcelVersion.Excel2013; 
    IWorkbook workbook = application.Workbooks.Create(1); 
    IWorksheet sheet = workbook.Worksheets[0]; 
 
    IAddInFunctions unknownFunctions = workbook.AddInFunctions; 
 
    //Adding the XLAM file reference to AddIn functions 
    //NOTE: The add-in name must be same as the function name 
    unknownFunctions.Add(@"D:\AddIn.xlam", "AddInFunction"); 
 
    //Use the function. The expected result is 30 
    sheet.Range["A3"].Formula = "AddInFunction(10,20)"; 
 
    string fileName = "AddIn.xlsx"; 
    workbook.Version = ExcelVersion.Excel2010; 
    workbook.SaveAs(fileName); 
} 
 





I found this article that says I need to use them instead of VBA Macros, given they are not supported by Syncfusion: 
https://www.syncfusion.com/forums/110910/using-xlsio-to-call-macro-in-excel 


Syncfusion now supports to create and edit Macros in an Excel document. Kindly look into the following UG link. 
https://help.syncfusion.com/file-formats/xlsio/working-with-macros 

But still, we cannot run Macros in the existing Excel file. 


Will addin functions be automatically executed by Syncfunction if they are registered with the spreadsheet? 
OR are they only executed when opened in Microsoft Excel? 


Add-in functions will be automatically executed by Syncfusion if they are registered with the Excel. 

Kindly let us know if the information helps. 

Regards, 
Shamini

Luis Silva · Answer

Excellent, thank you so much for your help, Shamini!

Shamini Kiruba Sobers · Answer

Hi Luis, 

We are glad that you are happy with the provided information. 

Regards, 
Shamini

Nate Seeley · Answer

Is there a way to read the calculated value of a cell that uses a Visual Basic Add-In from within my C# program? I would like my C# program to open an Excel file, insert data into some input cells, trigger calculations that require an Add-In, and read the resulting values from output cells. This will allow me to store the results from the output cells in a database. So I need to be able to programatically get the calculated value from a cell in my C# code.

I create a simple Visual Basic Add-In function called AddSeven() that simply adds 7 to the input value.

Function AddSevenFunction(someValue As Double) As Double

AddSevenFunction = someValue + 7

End Function

I registered it with Excel and saved it as

D:\AddSeven.xlam

I created an spreadsheet using the Excel Desktop application where the formula for cell B1 looks like this

=@AddSevenFunction(A1)

While I am in the Excel Desktop application the formula works.

- If I put 100 into A1 I see 107 in B1

- If I put 200 into A1 I see 207 in B1

- If I put 300 into A1 I see 307 in B1

I then close the Excel Desktop Application and open the spreadsheet in my C# program using SyncFusion. I put 500 into A1

using (ExcelEngine excelEngine = new ExcelEngine()) {

IWorkbook myWorkbook = excelEngine.Excel.Workbooks.Open(path);

myWorkbook.Version = ExcelVersion.Excel2016;

IWorksheet sheet = myWorkbook.Worksheets[0];

sheet.EnableSheetCalculations();

sheet.Range["A1"].Number = 500; //---------------------- set A1 to 500

I make sure it worked

Console.WriteLine("A1 value is " + sheet["A1"].CalculatedValue);

This prints "500" to the console as expected. I then try to read both the value and formula of B1

Console.WriteLine("B1 formula is " + sheet["B1"].Formula);

Console.WriteLine("B1 value is " + sheet["B1"].CalculatedValue);

Here is what gets printed to the console

B1 formula is =[1]!AddSevenFunction(A1)

B1 value is #NAME?

Why is SyncFusion returning the calculated value of B1 as #NAME? instead of 507? I am running the C# program on the same machine as the same user as when I was able to successfully open the Excel file. The path to the AddSeven.xlam file has not changed and the permissions are the same as when it worked in the Excel Desktop application. I made the add-in consistent with the function name as is shown in the example above. How can I read cell B1 programatically?

Shamini Kiruba Sobers · Answer

Hi Nate, 

Greetings from Syncfusion support. 

We are validating your query and we will update the details in a business day on March 8th, 2021. 

Regards, 
Shamini

Shamini Kiruba Sobers · Answer

Hi Nate, 

Thanks for the patience. 

Currently we don’t have support for Add-in functions in CalcEngine. Hence, we request you to utilize the Custom Function supported by CalcEngine as an alternative solution for this (not through Add-ins). Kindly refer the following UG link for the same. 
https://help.syncfusion.com/windowsforms/calculation-engine/custom-function#add-custom-function 
 
We have prepared a simple sample to demonstrate the use of custom function, which can be downloaded from the following link. 
https://www.syncfusion.com/downloads/support/forum/162964/ze/F1629641211196844 
 
Kindly let us know if it helps. 

Regards, 
Shamini

Nate Seeley · Answer

Thank your for taking the time to create the sample project. I compiled and ran the sample example from https://www.syncfusion.com/downloads/support/forum/162964/ze/F1629641211196844 At first it seemed like it was working. Sample.xlsx had the following values

A1 = 100

B1 = 200

When I clicked the pop up I saw 300, which is the sum of A1 and B1.

However, on closer inspection, it does not seem like the CustomSum() function is actually getting. If I change the CustomSum function and add 1000 to the sum

worksheet.CalcEngine.AddFunction("Sum", new CalcEngine.LibraryFunction(CustomSum));

worksheet["C1"].Value = worksheet.CalcEngine.ParseAndComputeFormula("=SUM(A1,B1)");

MessageBox.Show(worksheet["C1"].Value);

...

public string CustomSum(string args)

{

double sum = 0;

double d;

var splitArgs = args.Split(new char[] { CalcEngine.ParseArgumentSeparator });

foreach (string s in splitArgs)

{

if (double.TryParse(s, NumberStyles.Number | NumberStyles.AllowExponent, null, out d))

sum += d;

}

sum += 1000; //<--------------- Test to see if this function is getting called

return sum.ToString();

}

I expect to see a pop up that shows 1300. But the pop up still shows 300. So I think that Excel's sum() function is still getting called and the CustomSum() function is getting ignored. So registered the function under a different name that would not conflict with Excel's built in function names.

worksheet.CalcEngine.AddFunction("Sum2", new CalcEngine.LibraryFunction(CustomSum));

worksheet["C1"].Value = worksheet.CalcEngine.ParseAndComputeFormula("=SUM2(A1,B1)");

When I ran it this time 1000 showed in the pop up window instead of the 1300 that I expected to see. CustomSum was getting called but args received "A1,B1" and the double.TryParse() failed since "A1" and "B1" are not double values. I was able to make this work by saving workbook as a class variable

private IWorksheet worksheet;

and then using the worksheet to get the values out of "A1" and "B1" before trying to convert to a double value.

string cellValue = worksheet[s].Value;

if (double.TryParse(cellValue, NumberStyles.Number | NumberStyles.AllowExponent, null, out d)) {

The problem with doing this is that the code containing the custom function needs a reference to the IWorksheet. We wanted to allow our customers to write DLLs containing their own set of custom functions. This means that when our customers write their DLLs they would have to import the SyncFusion libraries so they could import the IWorksheet class. If that is the case would each of our customers need their own SyncFusion license? Or is there another way for the custom funtion to get the value out of cell "A1" and "B1" without access to to the IWorksheet object?

Shamini Kiruba Sobers · Answer

Hi Nate, 

Sorry for the inconvenience. 

We are checking the feasibility to achieve your requirement. We will update further details on tomorrow (March 10th, 2021). 

Regards, 
Shamini

Shamini Kiruba Sobers · Answer

Hi Nate, 

We have prepared a working sample to use custom functions as per your requirement, which can be downloaded from the following link. 
https://www.syncfusion.com/downloads/support/forum/162964/ze/UseCustomFunction-221185483 

Kindly let us know if this helps. 

Regards, 
Shamini