when I import an Excel-Sheet with formulas,
in very simple cases,it converts correctly into a FormulaValueCell, gives me the formula and the GetResultValueAsString() gives the computed Value.
But with more complicated formulas, this doesn't seem to work.
var value = sheet.Cells[6, 2].GetValue();
var a = value.Value.GetResultValueAsString(CellValueFormat.GeneralFormat);
var b = value.Value.GetValueAsString(CellValueFormat.GeneralFormat);
var c = value.Value.RawValue;
var d = value.Value.ValueType;
a"='[2]Übernahme 11-12'!C7"
b"='[2]Übernahme 11-12'!C7"
c"='[2]Übernahme 11-12'!C7"
d = Text
So first, it's generally wrong to consider this a text-cell
Second, in the xlsx File, there is a <v> Tag for each cell, containing the actual (numeric) value of a cell, even of formula cells.
Is there a way to access this computed value ? I can find the <v>100</v> Tag in the xlsx-File, but I don't find a way to access it.
6 Answers, 1 is accepted
Indeed, the approach you are using is the correct one and the results you are getting are weird. However, I couldn't reproduce the behavior on our side, therefore I couldn't test and find the root of it. Could you please share more details on the scenario? What is the content of the referenced cells? We would much appreciate it if you can share a sample document, which reproduces the issue. Note that in the public forums you can attach only images, so you will need to upload the document somewhere or send it in a support ticket from your telerik.com account.
Regards,
Tanya
Progress Telerik
Rank 1
As far as I understand, Telerik does not use the stored value, is produces a RadExpression and computes the value itself. This works sometimes, not always.
I figured a little for the conditions, for me it failed on cross-workbook references.
Imagine you have a workbook (xlsx-File), put the numbers 1,2,3 in Cells A1 to A3.
Put the Sum(A1:A3) in any cell of the workbook, even on other sheets, GetResultValueAsString will return "6".
But put the Sum in another workbook:
=SUM('F:\Telerik\[Mappe1.xlsx]Tabelle 1'!A1:'F:\Telerik\[Mappe1.xlsx]Tabelle 1'!A3)
will result in GetResultValueAsString returning "=SUM('[1]Tabelle 1'!A1:'[1]Tabelle 1'!A3)"
and considering the cell as a Text cell.
I guess this feature of excel is rather rarely used, and not supported by your product.
The [1] seems to be an internal index to an unresolved reference.
But beside a feature request, to support this type of reference and process the formula correctly,
it would be another feature request (or may be it's already somewhere), to access the stored value.
In the xslx File the cell looks like this:
<c r="A1"><f>SUM('[1]Tabelle 1'!A1:'[1]Tabelle 1'!A3)</f><v>6</v></c>
the "6" is there in the <v>-Tag, but I can't access it, and the <f>-Tag is considered as Text.
Excel itself stores the value, to display something useful, even if the reference is broken, f.e. if you send the single workbook to someone by e-mail.
Thank you for the additional information.
Indeed, the references between different worksheets in the same workbook are supported in the model while the ones pointing to an external workbook are currently not implemented. I logged this as a feature request and you can vote for its implementation as well as track its status using the related public item.
The values that are preserved in the XML of the document are not used by RadSpreadProcessing and implementing similar mechanism is not included in our plans since there are risks related to the customer data. Furthermore, the document is loaded at once and the stream is disposed, which means that those values should be preserved in the memory, allocating more resources. This could lead to issues when dealing with large documents.
Regards,
Tanya
Progress Telerik
Rank 1
Hello, Tanya.
I have a same issue with Holger. Did this Issue is solved?
Rank 1
It is "solved", with the result Telerik has not supported/not implemented this.
Above I had to different issues:
- the stored and (by excel itself) computed value from the xlsx file is not loaded at all. (it's recomputed by telerik)
- the recomputation is not always working, .. in the rare case of cross-workbook formulas.
Maybe there is an Telerik update meanwhile ? I haven't checked.
What I did for myself, was just not using Telerik.
There are several open source libraries to read the office-xml format, I took one named ExcelDataReader
This does not do the formula computation, it is really a rare feature Telerik offers here.
But you only have a use of this feature, if you are going to modify the spreadsheet, and you want updated results.
For just importing an Excel-File, it's more handicap than help.
The task is not scheduled for implementation at this point and I am unable to say when it might be available. Make sure to cast your vote for the implementation this functionality and follow the public item, so you can get a notification when its status changes.
Regards,
Tanya
Progress Telerik
Rank 1
Hello, Tanya.
I have a same issue of cross-worksheets formula result not shown when import selected worksheet. Did this Issue is solved?
Bhanu,
This is still not resolved. At this point, I cannot provide a timeframe. You can track its status on our feedback portal: SpreadProcessing: Add support for external references to another workbook.
I want to apologize for the inconvenience this missing functionality is causing you.