Free Support Forum - aspose.com

CustomFunction params proceeding (C++/Cpp & Python via Java)

Hi,
I’m trying to use the ICustomFunction functionality but I faced the following problem when playing with example from docs: https://docs.aspose.com/display/cellscpp/Using+ICustomFunction+Feature
How can I use the params from paramsList? How can I convert them successfully? I can see great examples for .NET here: https://docs.aspose.com/display/cellsnet/Using+ICustomFunction+Feature but I am not able to apply them for Cpp code. I have searched this forum for kind of but couldn’t find any…
Could You give me some short example how to procceed the following params inside CalculateCustomFunction?

worksheet->GetICells()->GetObjectByIndex(new String(“A1”))->SetFormula(new String("=MySampleFunc(B1,B2:B3)"));

Thank You in advance,
Szymon

@Slanek,
Thank you for your query.

We were able to understand the requirement but we need to look into it more. We have logged the issue in our database for investigation and for a sample code. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSCPP-231 – Example for custom function with parameters required

Thank You, I will wait for the news.

@Slanek,
Once we have an update on it, we will let you know here.

@Slanek,
Here is an example, we hope it is useful.

class CellsCppCustomFunction : public ICustomFunction
 {
 public:
 	ObjectPtr CalculateCustomFunction(StringPtr functionName, ArrayListPtr paramsList, ArrayListPtr contextObjects)
 	{
 		double total = 0;
 		intrusive_ptr<IReferredArea firstParam = dynamic_pointer_cast<IReferredArea(paramsList-Get(0));
 		double firstParamVal = Convert::ToDouble(firstParam-GetValues());
 		intrusive_ptr<IReferredArea secondParam = dynamic_pointer_cast<IReferredArea(paramsList-Get(1));
 
 		double valC1 = Convert::ToDouble(secondParam-GetValue(0, 0));
 		double valC2 = Convert::ToDouble(secondParam-GetValue(1, 0));
 		double valC3 = Convert::ToDouble(secondParam-GetValue(2, 0));
 		double valC4 = Convert::ToDouble(secondParam-GetValue(3, 0));
 		double valC5 = Convert::ToDouble(secondParam-GetValue(4, 0));
 		total = (valC1 + valC2 + valC3 + valC4 + valC5) / firstParamVal;
 	    return new Primitive<double(total);
 	}
     
 };
 
 int main(int argc, char** argv) 
 {
 
 	intrusive_ptr<IWorkbook workbook = Factory::CreateIWorkbook();
 
 	//Obtaining the reference of the first worksheet 
 	intrusive_ptr<IWorksheet worksheet = workbook-GetIWorksheets()-GetObjectByIndex(0);
 
 	//Adding a sample value to "B1" cell 
 	worksheet-GetICells()-GetObjectByIndex(new String("B1"))-PutValue(5);
 
 	//Adding a sample value to "C1" cell 
 	worksheet-GetICells()-GetObjectByIndex(new String("C1"))-PutValue(100);
 
 	//Adding a sample value to "C2" cell 
 	worksheet-GetICells()-GetObjectByIndex(new String("C2"))-PutValue(150);
 
 	//Adding a sample value to "C3" cell 
 	worksheet-GetICells()-GetObjectByIndex(new String("C3"))-PutValue(60);
 
 	//Adding a sample value to "C4" cell 
 	worksheet-GetICells()-GetObjectByIndex(new String("C4"))-PutValue(32);
 
 	//Adding a sample value to "C5" cell 
 	worksheet-GetICells()-GetObjectByIndex(new String("C5"))-PutValue(62);
 
 	//Adding custom formula to intrusive_ptr<ICell A1 
 	workbook-GetIWorksheets()-GetObjectByIndex(0)-GetICells()-GetObjectByIndex(new String("A1"))-SetFormula(new String("=MyFunc(B1,C1:C5)"));
 
 	//Calcualting Formulas 
 	workbook-CalculateFormula(false, new CellsCppCustomFunction());
 
   //self-check
 	//double ret = Aspose::Cells::Systems::ObjectCast<Aspose::Cells::Systems::Double( workbook-GetIWorksheets()-GetObjectByIndex(0)-GetICells()-GetObjectByIndex(new String("A1"))-GetValue());
 	//EXPECT_DOUBLE_EQ(80.8, ret);
 	
 	//Assign resultant value to intrusive_ptr<ICell A1 
 	workbook-GetIWorksheets()-GetObjectByIndex(0)-GetICells()-GetObjectByIndex(new String("A1"))-PutValue(workbook-GetIWorksheets()-GetObjectByIndex(0)-GetICells()-GetObjectByIndex(new String("A1"))-GetValue());
 
 	workbook-Save(new String("D:\\DemoICustomFunction.xlsx"));
 }

Let us know your feedback.

1 Like

Perfect! This code works great! Exactly what I wanted :slight_smile:
I just had to add missing “>” to make it compliable and this is it!
Thank You very much for help!

Best regards,
Szymon

@Slanek,

Good to know that the suggested code works for your needs. 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.

I’ve got one more question to the same topic. Is there any other way to get the values from the params list without dynamic casting them or this is the only possible solution here?
In Cpp environment this works perfectly but my problem now is that I’m wrapping the aspose code to python and I’m not able to use dynamic casting there.

@Slanek,
We have logged your comments with the ticket and discussing it here. Please spare us little time to analyze your requirement and provide our feedback.

Thank You :slight_smile:
Greetings!

I just realized something I didn’t know before. You have such a product like Aspose Cells for Python via Java and maybe it could be a solution for my needs.

Could You tell me if this project is still alive? I have seen that last changes on its github is from 2016…
I also realized it is available only for python 2.7 and my question is if You have plans in the future to create a newer version for python 3.x as python 2.7 is no longer supported version at this time?
Eventually as a platform independent product Win32/x64, linux64?

As I understand from the docs it gaves an easy access to all the Aspose Cells for Java utilities , including also Custom Functions?

This sounds really like a great solution but the only thing I’m affraid is old python 2.7 version…

Best Regards,
Szymon

@Slanek,
Yes, you are right that this project is alive and is planned to be released within a month or so. We are linking the ticket here so that you may get notification whenever it is released. Regarding the python version 3.x, platform dependencies and features list, you may please wait a little bit as all this information will be accompanied with the release of this new version. We will provide you all the supporting links once this product is released. So please stay tuned to experience the exciting features of this incoming product.

Great! I will then wait for the new release of the aspose for python via java :slight_smile:

@Slanek,

For your information, our upcoming Aspose.Cells for Python via Java release will support Python3.5 and above versions on Windows, Linux and MacOS. I guess it fulfills your requirements completely. It depends upon: https://jpype.readthedocs.io/en/latest/.

Keep in touch.

1 Like

Perfect, this is what I exactly need :slight_smile:

@Slanek,

Good to know that.

Have a good day!

@Slanek,

We are pleased to inform you that Aspose.Cells for Python via Java has been released now:
https://downloads.aspose.com/cells/python
Please try the latest version Aspose.Cells for Python via Java v19.12 for your needs. Also, see the Docs here:
https://docs.aspose.com/display/cellspythonjava/Home

@Slanek,

Also, see the following example using Aspose.Cells for Python via Java for your requirements:
e.g
Sample code:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *
 
from jpype import JImplements, JOverride
from com.aspose.cells import ICustomFunction
 
@JImplements(ICustomFunction)
class CustomCalcImpl(object):
    @JOverride
    def calculateCustomFunction(self, functionName, paramsList, contextObjects):
        param1 = paramsList.get(0).getValue(0, 0)
        parma21 = paramsList.get(1).getValue(0, 0)
        parma22 = paramsList.get(1).getValue(1, 0)
        if functionName == "myAdd":
            return param1 + parma21 + parma22
 
wb = Workbook()
cells = wb.getWorksheets().get(0).getCells()
wb.getWorksheets().get(0).getCells().get("A1").putValue(20.0)
wb.getWorksheets().get(0).getCells().get("A2").putValue(40)
wb.getWorksheets().get(0).getCells().get("A3").putValue(50)
wb.getWorksheets().get(0).getCells().get("B1").setFormula("=myAdd(A1, A2:A3)")
wb.calculateFormula(False, CustomCalcImpl())
print(wb.getWorksheets().get(0).getCells().get("B1").getStringValue())
 
jpype.shutdownJVM()

Hope, this helps a bit.

Thank You very much, I will test it :slight_smile:

@Slanek,

Sure, please take your time to evaluate the new release with sample code. Hopefully it will support your needs.