How to reduce the initial loading time of a large number of HOT instances? All instances are linked with HyperFormula

Tags: #<Tag:0x00007efc6b7acf38> #<Tag:0x00007efc6b7acce0>
#1

Hi friends, I have encountered a problem. I am using Handsontable 13.1.0 and need to load around 500-1000 sheets, but only one sheet is displayed at a time, similar to the Excel scenario. However, all sheets are linked with HyperFormula. On my Windows device, it takes about 10 seconds to load all the sheets. How can I optimize this?

<div class="table-wrap">
    <a-skeleton active :paragraph="{ rows: 4 }" :loading="workbookLoading">
        <template v-for="worksheet in workbookStore.worksheets" :key="worksheet.id">
            <HotTable
                  :worksheet="worksheet"
                  v-show="workbookStore.activeWorksheet?.code === worksheet.code"
                />
            </template>
    </a-skeleton>
</div>

const instance = new Handsontable(hotElement.value, {
    ...options,
    ...worksheet.value.conf,
});
hotStore.setInstance(worksheet.value.code, instance);
#2

Hi @geektry

500 sheets seems a lot. Wouldn’t it be better to hold them with Hyperformula instances being on the backend and then only render via Handsontable when the user wants the data to be in the view?

We do not have backend examples yet. But I see here https://stackoverflow.com/questions/78189082/how-to-load-data-to-and-save-data-from-a-handsontable-to-a-mysql-database there’s a similar question posted. Maybe the user would be keen on sharing some of their solutions.

#3

hi @aleksandra_budnik thanks for reply!
do you mean that i should run handsontable in backend node runtime? and keep all worksheet instances
but im not sure if someone did this before, will it cost lots of memory usage?

#4

Well, it depends on the number of calculations and cell dependencies that you have. Backend will always be faster.

But there’s also an option to create 1 table in Handsontable (on the view) and keep the rest of the sheets within the Hyperformula instances like in this demo https://codesandbox.io/p/sandbox/bold-cerf-7gp6zj?file=%2Fsrc%2FhyperformulaConfig.js (then you do not overload your DOM with too many elements that aren’t used and still the calculations are possible due to HyperFormula’s setCellContents method.

So the concept is

  1. Single Handsontable Instance :
  • You maintain only one Handsontable instance in the view (the visible table). This table is what the user interacts with directly.
  1. Multiple HyperFormula Instances :
  • You create multiple HyperFormula instances (500 as mentioned) to handle the calculations for other sheets that are not directly visible to the user.
  • HyperFormula allows you to manage complex calculations efficiently without overloading the DOM.

Communication between the sheets

  • The data and formulas in the Handsontable can be synchronized with the HyperFormula instances using methods like setCellContents .
  • When a user interacts with the Handsontable, you update the corresponding HyperFormula instances, and vice versa.
#5