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

Spreadsheet ToJSON breaks external references

1 Answer 153 Views

Spreadsheet

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

MikeWiese asked on 18 Aug 2016, 05:38 AM

All of our spreadsheets contain external references. That is, cells that retrieve values from other worksheets in the same workbook using formulae similar to =SomeOtherWorksheet!B13.

Our application workflow is pretty basic. We load a customer spreadsheet into the client-side kendoSpreadsheet control using the native "Load" button in the toolbar. We then save a representation of that workbook (from the toJSON() method) back to the server.

Later on, in server-side code, we Load() that json string into a Telerik.Web.Spreadsheet.Workbook and convert it to a Telerik.Windows.Documents.Spreadsheet.Model.Workbook. We then retrieve the value of certain cells with CellSelection.GetValue();

Unfortunately, when a cell formula refers to a cell on another worksheet, it fails. To be precise, cell.GetValue().Value.GetResultValueAsString(new CellValueFormat("")) comes back as "#REF!"

This only happens after the spreadsheet has been serialised/deserialised via JSON. If we load the exact same spreadsheet via
new XlsxFormatProvider().Import(myExcelFileStream)
then those external references work just fine.

Am I doing something wrong? I could not find anything in this forum or User Voice that relate to this bug. Is there a workaround?

1 Answer, 1 is accepted

Accepted