Contents Summary Error rendering macro 'toc' : null

Using Formulas and Functions
Aspose.Cells provides a class, Workbook
, that represents a Microsoft Excel file. The Workbook
class contains a Worksheets
collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet
class. The Worksheet
class provides a Cells
collection. Each item in the Cells
collection represents an object of the Cell
class.
It is possible to apply formulas to cells using properties and methods offered by the Cell
class, discussed in more detail below.
 Using builtin functions.
 Using addin functions.
 Working with array formulas.
 Creating a R1C1 formula.
Using Builtin Functions
Builtin functions or formulas are provided as readymade functions to reduce developers' efforts and time. See a list of builtin functions. The functions are listed in alphabetical order. More functions will be supported in future.
Aspose.Cells supports most of the formulas or functions offered by Microsoft Excel. Developers can use these formulas through the API or designer spreadsheet. Aspose.Cells supports a huge set of mathematical, string, Boolean, date/time, statistical, database, lookup and reference formulas.
Use the Cell
class' Formula
property to add a formula to a cell. Complex formulas, for example
, are also supported in Aspose.Cells. When applying a formula to a cell, always begin the string with an equal sign (=) as you do when creating a formula in Microsoft Excel and use a comma (,) to delimit function parameters.
In the example below, a complex formula is applied to the first cell of a worksheet's Cells
collection. The formula uses a builtin IF function provided by Aspose.Cells.
Using Addin Functions
We can have some user defined formulas that we want to include as an excel addin. When setting the cell.Formula function builtin functions work fine however there is a need to set the custom functions or formulas using the addin functions.
Aspose.Cells provides features to register add in functions using Worksheets.RegisterAddInFunction(). Afterwards when we set cell.Formula = anyFunctionFromAddIn, the output Excel file contains the calculated value from the AddIn function.
Following XLAM file shall be downloaded for registering the add in function in the below sample code. Similarly the output file "test_udf.xlsx" can be downloaded to check the output.
Sample Code
// Create empty workbook
Workbook workbook = new Workbook();
// Register macro enabled addin along with the function name
int id = workbook.getWorksheets().registerAddInFunction(srcDir + "TESTUDF.xlam", "TEST_UDF", false);
// Register more functions in the file (if any)
workbook.getWorksheets().registerAddInFunction(id, "TEST_UDF1"); //in this way you can add more functions that are in the same file
// Access first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Access first cell
Cell cell = worksheet.getCells().get("A1");
// Set formula name present in the addin
cell.setFormula("=TEST_UDF()");
// Save workbook to output XLSX format.
workbook.save(outDir + "test_udf.xlsx", SaveFormat.XLSX);
Using Array Formula
Array formulas are formulas that work with arrays, instead of individual numbers, as arguments to the functions that make up the formula. When an array formula is displayed, it is surrounded by braces ({}) as shown below.
Setting an array formula on cell G2
Some Microsoft Excel functions return arrays of values. To calculate multiple results with an array formula, enter the array into a range of cells with the same number of rows and columns as the array arguments.
It is possible to apply an array formula to a cell by calling the Cell
class' setArrayFormula
method. The setArrayFormul
method takes the following parameters:
 Array Formula, the array formula.
 Number of Rows, the number of rows to populate result of the array formula.
 Number of Columns, the number of columns to populate result of the array formula.
Using R1C1 Formula
Apply an R1C1 reference style formula to a cell with the Cell
class' setR1C1Formula
property.