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

2 posts, 0 answers
  1. Richard
    Richard avatar
    147 posts
    Member since:
    Feb 2012

    Posted 28 Feb 2018 Link to this post

    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");
            }
        }
    }
  2. Deyan
    Admin
    Deyan avatar
    171 posts

    Posted 05 Mar 2018 Link to this post

    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

Back to Top