Cell.SetArrayFormula

SetArrayFormula(string, int, int, bool, bool)

Sets an array formula to a range of cells.

[Obsolete("Use FormulaParseOptions for more options instead.")]
[EditorBrowsable(EditorBrowsableState.Never)]
public void SetArrayFormula(string arrayFormula, int rowNumber, int columnNumber, bool isR1C1, 
    bool isLocal)
ParameterTypeDescription
arrayFormulaStringArray formula.
rowNumberInt32Number of rows to populate result of the array formula.
columnNumberInt32Number of columns to populate result of the array formula.
isR1C1Booleanwhether the formula is R1C1 formula
isLocalBooleanwhether the formula is locale formatted

Remarks

NOTE: This class is now obsolete. Instead, please use Cell.SetArrayFormula(string,int,int,FormulaParseOptions). This property will be removed 12 months later since December 2019. Aspose apologizes for any inconvenience you may have experienced.

Examples

namespace AsposeCellsExamples
{
    using Aspose.Cells;
    using System;

    public class CellMethodSetArrayFormulaWithStringInt32Int32BooleanBooleanDemo
    {
        public static void Run()
        {
            // Create a new workbook
            Workbook workbook = new Workbook();

            // Access the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            // Add some sample data
            worksheet.Cells["A1"].Value = 10;
            worksheet.Cells["A2"].Value = 20;
            worksheet.Cells["A3"].Value = 30;

            try
            {
                // Get the target cell where we want to set the array formula
                Cell targetCell = worksheet.Cells["B1"];

                // Set an array formula that multiplies values in A1:A3 by 2
                // Parameters: (formula, rowNumber=3, columnNumber=1, isR1C1=false, isLocal=false)
                targetCell.SetArrayFormula("=A1:A3*2", 3, 1, false, false);

                // Calculate the workbook to see the results
                workbook.CalculateFormula();

                // Display the results
                Console.WriteLine($"B1 value: {targetCell.Value}");
                Console.WriteLine($"B2 value: {worksheet.Cells["B2"].Value}");
                Console.WriteLine($"B3 value: {worksheet.Cells["B3"].Value}");

                // Save the workbook
                workbook.Save("SetArrayFormulaDemo.xlsx");
                Console.WriteLine("Array formula set successfully and workbook saved.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error setting array formula: {ex.Message}");
            }
        }
    }
}

See Also

SetArrayFormula(string, int, int)

Sets an array formula(legacy array formula entered via CTRL+SHIFT+ENTER in ms excel) to a range of cells.

public void SetArrayFormula(string arrayFormula, int rowNumber, int columnNumber)
ParameterTypeDescription
arrayFormulaStringArray formula.
rowNumberInt32Number of rows to populate result of the array formula.
columnNumberInt32Number of columns to populate result of the array formula.

Examples

using System;
using Aspose.Cells;

namespace AsposeCellsExamples
{
    public class CellMethodSetArrayFormulaWithStringInt32Int32Demo
    {
        public static void Run()
        {
            Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;

            Cell cell = cells["A1"];
            cell.SetArrayFormula("=FREQUENCY({\"79\",\"85\",\"78\",\"85\",\"50\",\"81\",\"95\",\"88\",\"97\"},{70,79,89})", 4, 1);

            workbook.CalculateFormula();

            for (int i = 0; i < 4; i++)
            {
                Console.WriteLine($"Cell A{i+1} value: {cells[i, 0].IntValue}");
            }
        }
    }
}

See Also

SetArrayFormula(string, int, int, FormulaParseOptions)

Sets an array formula to a range of cells.

public void SetArrayFormula(string arrayFormula, int rowNumber, int columnNumber, 
    FormulaParseOptions options)
ParameterTypeDescription
arrayFormulaStringArray formula.
rowNumberInt32Number of rows to populate result of the array formula.
columnNumberInt32Number of columns to populate result of the array formula.
optionsFormulaParseOptionsOptions for parsing the formula.

Examples

using System;
using Aspose.Cells;

namespace AsposeCellsExamples
{
    public class CellMethodSetArrayFormulaWithStringInt32Int32FormulaParseOpDemo
    {
        public static void Run()
        {
            // Create a workbook
            Workbook workbook = new Workbook();
            
            // Access first worksheet
            Worksheet worksheet = workbook.Worksheets[0];
            Cells cells = worksheet.Cells;

            // Put sample data in cells C1:E1
            cells["C1"].PutValue(10);
            cells["D1"].PutValue(20);
            cells["E1"].PutValue(30);

            // Set array formula in cell A1 that will spill to 2 rows and 3 columns
            cells["A1"].SetArrayFormula("=TRANSPOSE(C1:E1)", 2, 1, new FormulaParseOptions());

            // Calculate formulas
            workbook.CalculateFormula();

            // Output the results
            Console.WriteLine("Array formula results:");
            for (int row = 0; row < 2; row++)
            {
                for (int col = 0; col < 3; col++)
                {
                    Cell cell = cells[row, col];
                    Console.WriteLine($"Cell {cell.Name}: {cell.Value}");
                }
            }
        }
    }
}

See Also

SetArrayFormula(string, int, int, FormulaParseOptions, object[][])

Sets an array formula to a range of cells.

public void SetArrayFormula(string arrayFormula, int rowNumber, int columnNumber, 
    FormulaParseOptions options, object[][] values)
ParameterTypeDescription
arrayFormulaStringArray formula.
rowNumberInt32Number of rows to populate result of the array formula.
columnNumberInt32Number of columns to populate result of the array formula.
optionsFormulaParseOptionsOptions for parsing the formula.
valuesObject[][]values for those cells with given array formula

See Also