IXlCell.SetFormula(String) Method

Assigns the specified formula string to a cell.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v23.2.Core.dll

NuGet Package: DevExpress.Printing.Core

Declaration

C#

void SetFormula(
    string formula
)

VB.NET

Sub SetFormula(
    formula As String
)

Parameters

Name	Type	Description
formula	String	A string that is a formula contained in the cell.

Remarks

A spreadsheet formula is an equation that performs a calculation on data contained in worksheet cells. A string specified as the worksheet formula should conform to the formula syntax rules and contain only supported elements (refer to the Excel Export and Spreadsheet Document API Feature Comparison document for a list of supported features).

Before a formula is assigned to a cell, it is analyzed with a formula parser specified in the XlExport.CreateExporter method. If the use of named ranges (defined names) is detected or other unsupported elements are found, an exception is thrown.

If the parser is not specified, the export to the Excel 97-2003 (.xls) format fails because this format requires a parsed formula to assign to a cell. Export to OpenXml (.xlsx) format without a parser leaves the string formula unvalidated.

Example

Note

A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples

FormulaActions.cs

// Create data rows using string formulas.
for (int i = 0; i < 4; i++) {
    using (IXlRow row = sheet.CreateRow()) {
        using (IXlCell cell = row.CreateCell()) {
            cell.Value = product[i];
        }
        using (IXlCell cell = row.CreateCell()) {
            cell.Value = qty[i];
        }
        using (IXlCell cell = row.CreateCell()) {
            cell.Value = price[i];
        }
        using (IXlCell cell = row.CreateCell()) {
            // Set the formula to calculate the amount per product.
            cell.SetFormula(String.Format("B{0}*C{0}", i + 2));
        }
    }
}

FormulaActions.vb

' Create data rows using string formulas.
For i As Integer = 0 To 3
    Using row As IXlRow = sheet.CreateRow()
        Using cell As IXlCell = row.CreateCell()
            cell.Value = product(i)
        End Using
        Using cell As IXlCell = row.CreateCell()
            cell.Value = qty(i)
        End Using
        Using cell As IXlCell = row.CreateCell()
            cell.Value = price(i)
        End Using
        Using cell As IXlCell = row.CreateCell()
            ' Set the formula to calculate the amount per product.
            cell.SetFormula(String.Format("B{0}*C{0}", i + 2))
        End Using
    End Using
Next i

Related GitHub Examples

The following code snippet (auto-collected from DevExpress Examples) contains a reference to the SetFormula(String) method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

excel-export-library-generate-an-invoice/CS/XLExportExample/Form1.cs#L526

// Set the formula to calculate the total amount.
cell.SetFormula(string.Format("SUM(H{0}:H{1})", firstDataRowIndex + 1, row.RowIndex - 1));
// Set the cell background color.

VB.NET

excel-export-library-generate-an-invoice/VB/XLExportExample/Form1.vb#L540

' Set the formula to calculate the total amount.
cell.SetFormula(String.Format("SUM(H{0}:H{1})", firstDataRowIndex + 1, row.RowIndex - 1))
' Set the cell background color.