Array formula and SetValue() GetValue() - Is it possible ?

[Richard]

The spreadsheet widget supports array formulas with a manual Ctrl-Shift-Enter. (see bottom of https://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/end-user/list-of-formulas)

I want to do the same programmatically using the Spreadsheet.Model classes.

This example shows how I can set and get results of a simple calculation, however the array formula I want to use is an Error. 

Indeed, the Sample.xlsx it creates, when opened shows #N/A where the array formula is.  However, in Excel, when I edit the cell and do Ctrl-Shift-Enter, it is interpreted as an array formula and shows the proper result.  The same formula in the spreadsheet widget also works (kudos to the dev team -- array formulas can make you squirrely).

console output

Starting
B2 retrieved 1
B3 retrieved 10
b4FormulaCell: =B2+B3
b4Result.RawValue: 11
k9CellValue: Telerik.Windows.Documents.Spreadsheet.Model.RangePropertyValue`1[Telerik.Windows.Documents.Spreadsheet.Model.ICellValue]
k9FormulaCell: Telerik.Windows.Documents.Spreadsheet.Model.FormulaCellValue
k9FormulaCell.RawValue: =MATCH(MIN(ABS(K2:K4-K8)),ABS(K2:K4-K8),0)
k9FormulaCell.ResultValueType: Error
Finished

 

program

using System;

using System.IO;

using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;

using Telerik.Windows.Documents.Spreadsheet.Model;

 

namespace ConsoleApp1

{

    class Program

    {

        static void Main(string[] args)

        {

            Console.WriteLine("Starting");

 

            var workbook = new Workbook();

            var sheet = workbook.Worksheets.Add();

 

            // B2

            var b2 = sheet.Cells[1, 1];

            b2.SetValue(1);

            var b2CellValue = b2.GetValue();

            var b2NumCell = b2CellValue.Value as NumberCellValue;

            var b2Num = b2NumCell.Value;

 

            Console.WriteLine($"B2 retrieved {b2Num}");

 

            // B3

            var b3 = sheet.Cells[2, 1];

            b3.SetValue(10);

            var b3CellValue = b3.GetValue();

            var b3NumCell = b3CellValue.Value as NumberCellValue;

            var b3Num = b3NumCell.Value;

 

            Console.WriteLine($"B3 retrieved {b3Num}");

 

            // B4 = B2 + B3

            var b4 = sheet.Cells[3, 1];

            b4.SetValue("=B2+B3");

            var b4CellValue = b4.GetValue();

            var b4FormulaCell = b4CellValue.Value as FormulaCellValue;

            var b4Result = b4FormulaCell.GetResultValueAsCellValue();

 

            Console.WriteLine($"b4FormulaCell: {b4FormulaCell.RawValue}");

            Console.WriteLine($"b4Result.RawValue: {b4Result.RawValue}");

 

 

            sheet.Cells[1, 10].SetValue(6);         // K2

            sheet.Cells[2, 10].SetValue(7);         // K3

            sheet.Cells[3, 10].SetValue(8);         // K4

            sheet.Cells[4, 10].SetValue(100);       // K5

            sheet.Cells[5, 10].SetValue(1000);      // K6

            sheet.Cells[6, 10].SetValue(10000);     // K7

            sheet.Cells[7, 10].SetValue(7.1);       // K8

 

                 // https://www.extendoffice.com/documents/excel/1050-excel-find-closest-value.html
            // goal use MATCH look up to find index of value in K2:K4 nearest to K8

            var arrayDiff = "ABS(K2:K4-K8)";  // formula for computing nearness

            var matchFormula = $"=MATCH(MIN({arrayDiff}),{arrayDiff},0)"; // formula for computing index of nearest (smallest ABS diff)

 

            sheet.Cells[8, 10].SetValue(matchFormula); // K9, nearest index for 7.1 should be computed as 2, which corresponds to value of 7 in K3, which is the second item in K2:K4

 

            var k9 = sheet.Cells[8, 10];

            var k9CellValue = k9.GetValue();

            var k9FormulaCell = k9CellValue.Value as FormulaCellValue;

            // var k9Result = k9FormulaCell.GetResultValueAsCellValue();

 

            Console.WriteLine($"k9CellValue: {k9CellValue}");

            Console.WriteLine($"k9FormulaCell: {k9FormulaCell}");

            Console.WriteLine($"k9FormulaCell.RawValue: {k9FormulaCell.RawValue}");

            Console.WriteLine($"k9FormulaCell.ResultValueType: {k9FormulaCell.ResultValueType}");

            // Console.WriteLine($"k9Result.RawValue: {k9Result.RawValue}");

 

            using (var output = new FileStream(@"C:\Temp\Sample.xlsx", FileMode.Create))

            {

                new XlsxFormatProvider().Export(workbook, output);

            }

 

            Console.WriteLine("Finished");

        }

    }

}

[Deyan]

Hello Richard,

RadSpreadProcessing currently does not support Array Formulas. We have already logged a feature request in our feedback portal. You may vote for it and follow its implementation progress by following this feedback item.

If you have other questions or concerns please do not hesitate to contact us again.

Regards,
Deyan
Progress Telerik