This is a migrated thread and some comments may be shown as answers.

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

1 Answer 597 Views

SpreadProcessing

This is a migrated thread and some comments may be shown as answers.

Richard asked on 28 Feb 2018, 10:44 PM

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");

}

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

1 Answer, 1 is accepted