Excel formulas for array values

[Alex B]

Hi syncfusion

I have a question.
Your documentation says how to use Array of Formula

But it's not clear for me, how can I apply SUM/MIN/MAX and other formulas to array.

Can you provide me an example, please?
I have created the next method with following parameters I need:

  public static string EvaluareExcelFormula<T>(List<T> dataArray, string formula)

        {

            try

            {

                using (ExcelEngine excelEngine = new ExcelEngine())

                {

                    IApplication application = excelEngine.Excel;

                    application.DefaultVersion = ExcelVersion.Excel2016;

                    IWorkbook workbook = application.Workbooks.Create(1);

                    IWorksheet sheet = workbook.Worksheets.Create();

                    sheet.EnableSheetCalculations();

                    sheet.ImportArray(dataArray.Cast<object>().ToArray(), 1, 1, true);

                    sheet.Names.Add("ArrayRange", sheet.Range[1, 1, dataArray.Count(), 1]);

                    //Assign formula array with named range

                    sheet["A2"].FormulaArray = $"{formula}ArrayRange";

                    string calculatedValue = sheet["A2"].CalculatedValue;

                  

                    return calculatedValue;

                }

            }

            catch (Exception ex)

            {              

                return null;

            }

        }

[Shamini Kiruba Sobers]

Hi Alex, 

 

Greetings from Syncfusion support. 

 

Kindly look into the following code snippet which shows how to apply SUM/MIN/MAX formulas to array. 

 

Code snippet: 

 

//Create an instance of ExcelEngine 

using (ExcelEngine excelEngine = new ExcelEngine()) 

{ 

    IApplication application = excelEngine.Excel; 

 

    application.DefaultVersion = ExcelVersion.Excel2016; 

 

    //Create a workbook 

    IWorkbook workbook = application.Workbooks.Create(1); 

    IWorksheet sheet = workbook.Worksheets[0]; 

 

    //Assign array formula 

    sheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}"; 

 

    //Adding a named range for the range A1 to D1 

    sheet.Names.Add("ArrayRange", sheet.Range["A1:D1"]); 

 

    //Assign formula array with named range 

    sheet.Range["A2"].Text = "SUM"; 

    sheet.Range["B2"].FormulaArray = "Sum(ArrayRange)"; 

    sheet.Range["A3"].Text = "MIN"; 

    sheet.Range["B3"].FormulaArray = "Min(ArrayRange)"; 

    sheet.Range["A4"].Text = "MAX"; 

    sheet.Range["B4"].FormulaArray = "Max(ArrayRange)"; 

 

    //Saving the Excel to the MemoryStream  

    MemoryStream stream = new MemoryStream(); 

 

workbook.SaveAs(stream); 

} 

 

The working sample with the above code can be downloaded from the following link. 

https://www.syncfusion.com/downloads/support/forum/159877/ze/F159877-746249578 

 

Please let us know if it helps. 

 

Regards, 

Shamini 

[Alex B]

Hi Alex, 

 

Greetings from Syncfusion support. 

 

Kindly look into the following code snippet which shows how to apply SUM/MIN/MAX formulas to array. 

 

Code snippet: 

 

//Create an instance of ExcelEngine 

using (ExcelEngine excelEngine = new ExcelEngine()) 

{ 

    IApplication application = excelEngine.Excel; 

 

    application.DefaultVersion = ExcelVersion.Excel2016; 

 

    //Create a workbook 

    IWorkbook workbook = application.Workbooks.Create(1); 

    IWorksheet sheet = workbook.Worksheets[0]; 

 

    //Assign array formula 

    sheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}"; 

 

    //Adding a named range for the range A1 to D1 

    sheet.Names.Add("ArrayRange", sheet.Range["A1:D1"]); 

 

    //Assign formula array with named range 

    sheet.Range["A2"].Text = "SUM"; 

    sheet.Range["B2"].FormulaArray = "Sum(ArrayRange)"; 

    sheet.Range["A3"].Text = "MIN"; 

    sheet.Range["B3"].FormulaArray = "Min(ArrayRange)"; 

    sheet.Range["A4"].Text = "MAX"; 

    sheet.Range["B4"].FormulaArray = "Max(ArrayRange)"; 

 

    //Saving the Excel to the MemoryStream  

    MemoryStream stream = new MemoryStream(); 

 

workbook.SaveAs(stream); 

} 

 

The working sample with the above code can be downloaded from the following link. 

https://www.syncfusion.com/downloads/support/forum/159877/ze/F159877-746249578 

 

Please let us know if it helps. 

 

Regards, 

Shamini 

Hi Shamini 

Thanks for example, that helped me

[Shamini Kiruba Sobers]

Hi Alex, 

We are glad that the provided example helped you. 

Thanks, 

Shamini