Sharing data between SpreadJS instances

Posted by: marek on 9 March 2019, 6:57 am EST

    • Post Options:
    • Link

    Posted 9 March 2019, 6:57 am EST

    Hello,

    I need to have multiple instances of SpreadJS on a single page. What would be the best approach to have data shared between those instances so that formulas can reference each other between Workbooks? An important thing is also a smart autocomplete with FormulaTextBox with the support of Structured Reference Formulas (https://help.grapecity.com/spread/SpreadSheets12/webframe.html#structref.html).

    I’ve been trying with using custom names but that doesn’t really work well with tables.

    One more question - I’m currently evaluating the trial, does the full paid version come with more readable source code? Currently everything is minified and obfuscated and it’s a bit hard to work with it.

    Best Regards,

    Marek

  • Posted 10 March 2019, 4:52 pm EST

    TL;DR - I think this is what I’m looking for, although it relates to WinForms I feel like the situation might be similar with JS:

    The spreadsheet supplies a DefaultSheetDataModel class. (…) This class provides internal storage for cell values, cell formulas, cell dependency links, and cell recalculation lists. This class also has a DataSource property that allows the cell values to be stored outside the class. The DataSource property supports some of the more common iterfaces such as IList. When you call the DefaultSheetDataModel’s SetValue method, the specified value is put into the internal cell value storage (or the external DataSource object). The cell dependency links are checked for dependent cells. The dependent cells are added to the recalculation list. The formulas for the cells on the recaluation list are evaluated. The formulas results are put into the internal cell value storage (or the external DataSource object).

    Credits to bobbyo from this thread https://www.grapecity.com/en/forums/spread-winforms/custom-data-model-amp-circ.

    Unfortunately I couldn’t find the JS equivalent. Does it really work in the same way as in WinForm?

    Here is a little bit more detailed explanation of my use-case:

    Let’s assume there are 2 spreadsheets on a page

    when editing cell in spreadsheet 2, I would also like to access data (mainly tables / named ranges) from the spreadsheet no.1, provided with the help of autocomplete (which works amazingly good btw.)

    any changes in one spreadsheet should lead to changes in other spreadsheets if there are referenced cells (and vice versa)

    side note: spreadsheet here is more like a table, in a sense that table contains rather structured and organized data. Those “spread-tables” also have meaningful names for reference. Referencing will be by table’s and column’s names, i.e. formula

    = SUM(Expenses.Marketing)
    will sum up the “Marketing” column in “Expenses” table. Basically spreadsheet is a named table and as such will be displayed (no way to add new sheets etc.).

    After some thinking, I believe it’s hard to fit this use case into Workbook / Worksheets model (and I’d like to avoid sheets names in formulas like

    =Sheet1!...
    ).

    My first idea was to store a hidden Workbook + Worksheet for the whole page and replicate it for each spreadsheet. Then in such spreadsheet part of the data (not belonging to the owning spreadsheet) was hidden with grouping and collapsing. The goal was to be able to access external data while still looking like a table. In the end I dropped this solution as it felt too hacky.

    Now I think a better solution would be to try out directly with the CalcEngine - for example to override some sheets methods like

    getValue
    or
    getCustomNames
    - I know that CalcEngine calls them. I could just put in those methods a check if it’s trying to retrieve external data, if yes I will provide it, if no I will just pass it through.

    The most perfect solution would be to make use of CalcEngine for evaluations but to do it without any interaction with Workbooks / Worksheets at all, so to just insert data directly to the engine. Is this possible?

  • Posted 10 March 2019, 7:48 pm EST

    Hi Marek,

    We have responded to the case on the SupportOne portal. Please continue the discussion there.

    Regards

    Sharad

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels