IXlCell.SetSharedFormula(XlExpression, XlCellRange) Method

Assigns the specified formula expression to the given cell range to create a shared formula.

Namespace: DevExpress.Export.Xl

Assembly: DevExpress.Printing.v24.1.Core.dll

NuGet Package: DevExpress.Printing.Core

Declaration

C#

void SetSharedFormula(
    XlExpression formula,
    XlCellRange range
)

VB.NET

Sub SetSharedFormula(
    formula As XlExpression,
    range As XlCellRange
)

Parameters

Name	Type	Description
formula	XlExpression	An XlExpression object used to specify a shared formula.
range	XlCellRange	An XlCellRange object that is the worksheet cell range in which each cell will contain a shared formula.

Example

It takes two steps to build a shared formula. First, add a formula to the first cell in a range - it is the host formula. Next, add a reference to the host formula to any other cell of the range. The IXlCell.SetSharedFormula method with different parameters serves both steps, as illustrated in the code below.

Note

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

FormulaActions.cs

// Create data rows.
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()) {
            // Use the shared formula to calculate the amount per product. 
            if (i == 0)
                cell.SetSharedFormula("B2*C2", XlCellRange.FromLTRB(3, 1, 3, 4));
            else
                cell.SetSharedFormula(new XlCellPosition(3, 1));
        }
    }
}

FormulaActions.vb

' Create data rows.
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()
            ' Use the shared formula to calculate the amount per product. 
            If i = 0 Then
                cell.SetSharedFormula("B2*C2", XlCellRange.FromLTRB(3, 1, 3, 4))
            Else
                cell.SetSharedFormula(New XlCellPosition(3, 1))
            End If
        End Using
    End Using
Next i