GetResultValueAsString wrong with komplex formular

[Harald]

Hallo,

when i read the result of a cell with a formular how has a function (like SUM()) or a referenz (sheetname!Range) linked to a worksheet (itself or another) in the workbook the result (.GetResultValueAsString ) ist "0" or a text (the formular itself) and not the result. Simple formulars related to cells in the worksheet are ok.

Example:

The cell formular looks like this:

1. =SUMME(L23:L30) ==> GetResultValueAsString result = "0"

2. {=WENNFEHLER(INDEX(Bestellliste!E:E;KKLEINSTE(WENN(Bestellliste!$A$1:$A$1131="x";ZEILE($1:$991));ZEILE(A3)));"")} ==> GetResultValueAsString result = "=WENNFEHLER(INDEX(Bestellliste!E:E;KKLEINSTE(WENN(Bestellliste!$A$1:$A$1131="x";ZEILE($1:$991));ZEILE(A3)));"")"

VB code:
Dim iCellValue As ICellValue
iCellValue = TryCast(workbook.ActiveWorksheet.Cells(row, col).GetValue().Value, ICellValue)
If iCellValue IsNot Nothing Then
    Dim format As CellValueFormat = workbook.ActiveWorksheet.Cells(row, col).GetFormat().Value
    Dim valueAsString As String = iCellValue.GetValueAsString(format)
    'valueAsString
    Dim resultValueAsString As String = iCellValue.GetResultValueAsString(format)
    'resultAsString
    Dim valueType As CellValueType = iCellValue.ValueType
    'valueType = Formula      ' ==> allways Text{4} with komplex formular
    'resultValueType = Number
    Dim resultValueType As CellValueType = iCellValue.ResultValueType
End If

DLL Version: 2015.2.728.40

What's wrong? Are their any restriction to resolve formulars?

 

Regards

Harald

 

 

 

[Martin Ivanov]

Hello Harald,

I am not sure that I understand your concern. The functions mentioned in your reply are not part of the default list of functions in the spreadprocessing library.  This is why I will guess they are custom made as demonstrated in the Custom Functions help article. If so, you can also take a look at the related SDK example.

Additionally, I noticed that your second function starts with the "{" character. It is probably a typo, but keep in mind that in order for a function to be properly evaluated the expression should start with "=".

Regards,
Martin
Telerik by Progress

[Harald]

Hi Martin,

thanks for your response!
With other words: My problem is to read results in an Excel (sheet) (Excel 2013)  from my customer where some results are computed with standard Excel formulas (=SUM(A3:A10) or like shown in my question. Standard value results with simple formulas (=123+456) get the right result. But it seems with functions or linked cells in the formula it doesn’t work.
The ’{‘ is not part of the formula – sorry.

Or - are there other ways to read the computed result of a cell?

Best Regards
Harald

[Deyan]

Hello Harald,

The custom functions functionality allows you to either define Excel function that is currently not supported by RadSpreadProcessing or define a totally custom function that is not even defined in Excel. If you are using function that originally is not available in Excel, then when open the exported XLSX file in Excel the function will not be evaluated. In order to visualize the result of such custom function there are two possibilities:

1. You may visualize the XLSX file by using RadSpreadsheet control for WPF or RadSpreadsheet control for Silverlight instead of using Excel. As these controls use RadSpreadProcessing model, the custom functions evaluation comes out of the box. This approach may be seen in this CustomFunctions SDK example.
2. The other approach requires implementing the same custom functions in so that they are visualized correctly. A quick google search me to this article showing how to create custom Excel functions.

I hope this is helpful.

Regards,
Deyan
Telerik by Progress