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

Slanek · February 12, 2020, 6:40pm

Hi,
I’m trying to use the ICustomFunction functionality but I faced the following problem when playing with example from docs: Using ICustomFunction Feature|Documentation
How can I use the params from paramsList? How can I convert them successfully? I can see great examples for .NET here: Using ICustomFunction Feature|Documentation 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

ahsaniqbalsidiqui · February 13, 2020, 1:53am

@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

Slanek · February 13, 2020, 7:22am

Thank You, I will wait for the news.

ahsaniqbalsidiqui · February 13, 2020, 9:42am

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

ahsaniqbalsidiqui · February 14, 2020, 7:12am

@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.

Slanek · February 14, 2020, 9:18am

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

Best regards,
Szymon

amjad.sahi · February 14, 2020, 11:13am

@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.

Slanek · February 17, 2020, 2:03pm

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.

ahsaniqbalsidiqui · February 17, 2020, 5:17pm

@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.

Slanek · February 18, 2020, 6:52am

Thank You
Greetings!

Slanek · February 18, 2020, 12:30pm

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

ahsaniqbalsidiqui · February 18, 2020, 2:24pm

@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.

Slanek · February 19, 2020, 8:51am

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

amjad.sahi · February 20, 2020, 8:59am

@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: JPype documentation — JPype 1.5.0_dev0 documentation.

Keep in touch.

Slanek · February 20, 2020, 11:40am

Perfect, this is what I exactly need

amjad.sahi · February 20, 2020, 12:08pm

@Slanek,

Good to know that.

Have a good day!

amjad.sahi · February 24, 2020, 9:00am

@Slanek,

We are pleased to inform you that Aspose.Cells for Python via Java has been released now:

Please try the latest version Aspose.Cells for Python via Java v19.12 for your needs. Also, see the Docs here:

amjad.sahi · February 24, 2020, 9:53am

@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.

Slanek · February 25, 2020, 8:16am

Thank You very much, I will test it

amjad.sahi · February 25, 2020, 8:38am

@Slanek,

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