Accessing computed values of formula cells

7 posts, 0 answers
  1. Holger
    Holger avatar
    23 posts
    Member since:
    Jan 2015

    Posted 12 Jun 2017 Link to this post

    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.

  2. Tanya
    Admin
    Tanya avatar
    718 posts

    Posted 15 Jun 2017 Link to this post

    Hi Holger,

    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

  3. Holger
    Holger avatar
    23 posts
    Member since:
    Jan 2015

    Posted 15 Jun 2017 in reply to Tanya Link to this post

    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.

  4. Tanya
    Admin
    Tanya avatar
    718 posts

    Posted 20 Jun 2017 Link to this post

    Hello Holger,

    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

  5. HYUKJUN
    HYUKJUN avatar
    1 posts
    Member since:
    Mar 2018

    Posted 23 Mar in reply to Tanya Link to this post

    Hello, Tanya.

    I have a same issue with Holger. Did this Issue is solved?

  6. Holger
    Holger avatar
    23 posts
    Member since:
    Jan 2015

    Posted 23 Mar in reply to HYUKJUN Link to this post

    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.

     

     

  7. Tanya
    Admin
    Tanya avatar
    718 posts

    Posted 28 Mar Link to this post

    Hi all,

    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

Back to Top