Manage formulas of Excel files

Introduction

One of Microsoft Excel’s compelling features is its ability to process data with formulas and functions. Microsoft Excel provides a set of built-in functions and formulas that help users perform complex calculations quickly. Aspose.Cells for Python via .NET also provides a huge set of built-in functions and formulas that help developers compute values easily. Aspose.Cells for Python via .NET also supports add‑in functions. Moreover, Aspose.Cells for Python via .NET supports array and R1C1 formulas.

How to Use Formulas and Functions

Aspose.Cells for Python via .NET 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 built-in functions.
  • Using add‑in functions.
  • Working with array formulas.
  • Creating an R1C1 formula.

How to Use Built-in Functions

Built-in functions or formulas are provided as ready‑made functions to reduce developers' effort and time. See a list of built-in functions supported by Aspose.Cells for Python via .NET. The functions are listed in alphabetical order. More functions will be supported in the future.

Aspose.Cells for Python via .NET supports most of the formulas or functions offered by Microsoft Excel. Developers can use these formulas through the API or designer spreadsheet. Aspose.Cells for Python via .NET 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

 = H7*(1+IF(P7 = $L$3,$M$3, (IF(P7=$L$4,$M$4,0))))

are also supported in Aspose.Cells for Python via .NET. 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 commas (,) 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 built-in IF function provided by Aspose.Cells for Python via .NET.

How to Use Add-in Functions

We can have some user‑defined formulas that we want to include as an Excel add‑in. When setting the cell.Formula, built‑in functions work fine; however, there is a need to set custom functions or formulas using add‑in functions.

Aspose.Cells for Python via .NET provides features to register add‑in functions using worksheets.register_add_in_function(). Afterwards, when we set cell.Formula = anyFunctionFromAddIn, the output Excel file contains the calculated value from the add‑in function.

The following XLAM file should be downloaded for registering the add‑in function in the sample code below. Similarly, the output file test_udf.xlsx can be downloaded to view the result.

TestUDF.xlam

test_udf.xlsx

How to Use Array Formula

Array formulas are formulas that take 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 ({}).

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' set_array_formula method. The set_array_formula method takes the following parameters:

  • Array Formula – the array formula.
  • Number of Rows – the number of rows to populate the result of the array formula.
  • Number of Columns – the number of columns to populate the result of the array formula.

How to Use R1C1 Formula

Add an R1C1 reference‑style formula to a cell with the Cell class' r1c1_formula property.

Advanced topics

Format cells Manage Comments and Notes