Custom Formulas

Aspose.Cells Product Family
1

Is it possible to create your own custom functions in Aspose.Cells?


I’d like to my custom function for specifc math operations. Is that possible, if so how?
2

Hi,


Please see the document/ article on how to implement user defined functions in Aspose.Cells for your reference:
http://www.aspose.com/docs/display/cellsnet/Using+ICustomFunction+feature

Thank you.

3

if it also possible to remove functions aspose has defined?

4

Hi,


Well, the custom functions are created (via ICustomFunction interface) dynamically and so it set at runtime, so you may or may not set the custom function/ formula as per your needs. For example, you may simply use Cell.PutValue() to replace the formula string by its calculated value, you may insert null into your desired formula cells accordingly. If you need to remove all the formulas at once and you want to replace all the formulas should be replaced by its calculated values, you may simply use Cells.RemoveFormulas() method.

Hope, this helps a bit.

Thank you.
5

Lets say i want to change the way the SUM function works. Aspose created that function. How would i remove the Aspose implemented version of SUM and replace it with my own?

6

Hi,


Well, you have to change/update your own custom function (on the back end i.e., in the implementation of interface) or recreate another one for your needs.

Thank you.

7

Sure, i’ll create my own definition of the SUM function via the interface. How does the calc engine know to call the one i made versus the aspose has in the box.


What if i just want to remove the SUM function?
8

Hi,


Well, you may not update or change the functionality of the built-in MS Excel function (e.g SUM) as after all the Excel files are opened into MS Excel for view. The best way is you should use your own name which should not be matched with the built-in MS Excel functions/formulas.

Aspose.Cells will identify the function via its name and calculate its value (based on its custom implementation you defined).

//Adding custom formula to Cell A1

       workbook.Worksheets[0].Cells[“A1”].Formula = “=MyFunc(B1,C1:C5)”;

    <span class="code-comment" style="color: rgb(128, 128, 128); background-color: inherit;">//Calcualting Formulas



        workbook.CalculateFormula(false, new CustomFunction());


Thank you.