Beginning in OfficeWriter 9.1, ExcelApplication’s calculation engine will offer the ability to implement custom formulas. This feature should prove helpful to users whom would like to calculate their own custom formulas or calculate formulas not currently supported by ExcelWriter on the server using ExcelWriter. This tutorial will show you how to implement these formulas.
Creating Your Own Formula
- Create a class that implements an interface provided by ExcelWriter called IFunction. The IFunction interface implements a method called Calculate.
- Inside of your class, create the Calculate method with this signature:
FunctionValue Calculate(IList<FunctionValue> args, Cell currentCell)
- Inside of the Calculate Function, code the logic of the formula you would like to implement.
- The first argument of Calculate is a list of FunctionValue objects. FunctionValue objects are how we pass values in and out of formulas. FunctionValues have several properties available to them, so please see the documentation for more information about how you can use the properties in your formula logic.
- The second argument of Calculate is the cell that contains the formula. Please see the documentation for more information about Cell objects and their available properties and methods.
Registering the Formula with ExcelWriter
- Once the formula is written, register the function using Workbook.RegisterCustomFunction.
WB.RegisterCustomFunction("MyFormula", new MyClass());
Calculating the Formula and Removing it From a Workbook
Once your formula is registered, you can use WB.CalculateFormulas to have ExcelWriter calculate the value of any cells that use the formula in your Workbook. If you are generating Excel output using ExcelApplication.Save, please note that if you have implemented a custom formula that it is not recognized by Microsoft Excel, the end user will not see the calculated values in the output workbook when opened in Excel. To get around this issue, you can use Workbook.RemoveFormulas or Worksheet.RemoveFormulas to remove the formulas from the worksheet or workbook, while leaving the last calculated values in the cells.
Here is an example of what your finished code should look like:
public class sample
{
MyMainMethod()
{
ExcelApplication xla = new ExcelApplication(ExcelApplication.FileFormat.Xlsx);
Workbook WB = xla.Create(ExcelApplication.FileFormat.Xlsx);
WB[0]["A1"].Formula = "=COUNTARGUMENTS(4, 5, 6)";
WB.RegisterCustomFunction("COUNTARGUMENTS", new Formula());
WB.CalculateFormulas();
//Optionally remove all formulas from the workbook, so only values remain. This is good in case you are using a custom formula that Excel will not be able to calculate.
WB.RemoveFormulas();
xla.Save("output.xlsx");
}
}
class Formula : IFunction
{
public FunctionValue Calculate(IList<FunctionValue> args, Cell cell)
{
//Returns the number of arguments
return new FunctionValue(args.Count);
}
}
