Using ICustomFunction Feature

Introduction

This article provides an understanding of how to use the ICustomFunction feature to implement custom functions with Aspose.Cells APIs.

The ICustomFunction interface allows you to add custom formula calculation functions to extend the Aspose.Cells core calculation engine in order to meet certain requirements. This feature is useful to define custom (user defined) functions in a template file or in a code where the custom function can be implemented and evaluated using Aspose.Cells APIs like any other default Microsoft Excel function.

Using ICustomFunction Feature

The following sample code implements the ICustomFunction interface which evaluates and returns the values of the two custom functions i.e. MySampleFunc() and YourSampleFunc(). These custom functions are inside the cells A1 and A2 respectively. Then it calls the IWorkbook.CalculateFormula(false, ICustomFunction) method to invoke the implementation of ICustomFunction.CalculateCustomFunction() method. Then, it prints the values of A1 and A2 on console which are actually the values returned by ICustomFunction.CalculateCustomFunction(). Please see the Console Output of the sample code below for more help.

Sample Code

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Implement ICustomFunction interface
class CustomFunction : public ICustomFunction
{
public:
	//Evalaute and return the values of your custom functions
	intrusive_ptr<Aspose::Cells::System::Object>
		CalculateCustomFunction(
			intrusive_ptr<Aspose::Cells::System::String> functionName,
			intrusive_ptr<Aspose::Cells::System::Collections::ArrayList> paramsList,
			intrusive_ptr<Aspose::Cells::System::Collections::ArrayList> contextObjects)
	{
		if (functionName->Equals(new String("MySampleFunc")))
		{
			return new String("MY sample function was called successfully.");
		}

		if (functionName->Equals(new String("YourSampleFunc")))
		{
			return new String("YOUR sample function was called successfully.");
		}

		return NULL;
	}

};

//Using ICustomFunction Feature
void  UsingICustomFunctionFeature()
{
	//Create workbook
	intrusive_ptr<IWorkbook> wb = Factory::CreateIWorkbook();

	//Access first worksheet in the workbook
	intrusive_ptr<IWorksheet> ws = wb->GetIWorksheets()->GetObjectByIndex(0);

	//Adding custom formulas to Cell A1 and A2
	ws->GetICells()->GetObjectByIndex(new String("A1"))->SetFormula(new String("=MySampleFunc()"));
	ws->GetICells()->GetObjectByIndex(new String("A2"))->SetFormula(new String("=YourSampleFunc()"));

	// Calcualting Formulas
	intrusive_ptr<CustomFunction> custFunc = new CustomFunction();
	wb->CalculateFormula(false, custFunc);

	//Print the value of cell A1 and A2 after the calculation of custom function implemented by us.
	intrusive_ptr<String> valA1 = ws->GetICells()->GetObjectByIndex(new String("A1"))->GetStringValue();
	intrusive_ptr<String> valA2 = ws->GetICells()->GetObjectByIndex(new String("A2"))->GetStringValue();

	//Print the values on console
	StringPtr str1 = new String("Value of A1: ");
	Console::WriteLine(str1->StringAppend(valA1));
	StringPtr str2 = new String("Value of A2: ");
	Console::WriteLine(str2->StringAppend(valA2));
}

Console Output

 Value of A1: MY sample function was called successfully.

Value of A2: YOUR sample function was called successfully.