Bind a Table in a Sheet to a DataSource Table

Posted by: Jean.s on 28 February 2024, 4:13 am EST

Please login to follow topic

Jean.s
- Post Options:
- Link
  Copy
Posted 28 February 2024, 4:13 am EST

Hi,

how can I in the Designer bind a Table in a Sheet to a Table defined in the Data Source Tab (Data Manager)?

Thank you
priyam.kushwaha
- Post Options:
- Link
  Copy
Posted 28 February 2024, 8:04 pm EST - Updated 28 February 2024, 8:09 pm EST

Hi,

As per my understanding, you’re looking to use a table into a sheet that’s created in a data source. However, SpreadJS doesn’t support adding a data source table like a normal table in a sheet. Instead, you can use a tableSheet, which enables the use of data source tables. You can learn more about tableSheet by referring to this article: https://developer.mescius.com/spreadjs/docs/features/tablesheet.

Please find below a GIF demonstrating how to insert a tableSheet to use a data source table, along with a demo of tableSheet functionality.

Demo: https://developer.mescius.com/spreadjs/demos/features/table-sheet/overview/purejs

Gif:

Regards,

Priyam
Jean.s
- Post Options:
- Link
  Copy
Posted 1 March 2024, 12:29 am EST

HI,

I’ll try to clarify:

I see in your demo (https://developer.mescius.com/spreadjs/demos/features/data-binding/table-binding/purejs) that we can bind the source data to a table. Is there a way to associate a table in a sheet with a table already defined in Data Manager? I think this would be a very useful feature and would unify how we can use data in various scenarios.

Thank you
priyam.kushwaha
- Post Options:
- Link
  Copy
Posted 3 March 2024, 11:09 pm EST

Hi,

Currently this feature is not supported and TableSheet is added for this functionality. Data manager table can be used in tableSheet, reportSheet and GanttSheet. TableSheet is a fast, data-bound DataTable view with grid-like behavior and a spreadsheet user interface. It includes a relational data manager and powerful structured formulas in addition to sorting, filtering, styles, column and row pinning, and cell editing. Therefore, I recommend considering TableSheet for your needs.

If TableSheet does not fully meet your requirements, please provide additional details about your specific use case. This will enable us to better understand the aspects that are not addressed by TableSheet and offer appropriate assistance to fulfill your needs.

Demo: https://developer.mescius.com/spreadjs/demos/features/table-sheet/overview/purejs

Reference:

tableSheet: https://developer.mescius.com/spreadjs/docs/features/tablesheet.

Regards,

Priyam
Jean.s
- Post Options:
- Link
  Copy
Posted 5 March 2024, 12:29 am EST
Hi Priyam, thank you for your answer.

I Know the tableshet and the data manager class and i think it is a great job!

I think that would be very useful to let a Table in a normal sheet bind to a data source for the following reasons:

In a dashboard, I normally need to expose more tables in a different zone (the best solution for this requirement would be what I have described in my other post: https://developer.mescius.com/forums/spreadjs/sheet-in-floating-object)

In a TableSheet is not possible to show data from two or more tables at the same time and is not possible to use a shape or a button to call some action

A typical Excel user does not know tablesheet but is quite familiar with a table loaded from different data source

Thank you
priyam.kushwaha
- Post Options:
- Link
  Copy
Posted 5 March 2024, 11:11 pm EST

Hi,

I’ve relayed your concern to the development team, and it is to inform you that the requested feature is already in our backlog with the internal track id is “SJS-6004”. I’ll keep you updated on any progress regarding this matter.

Regards,

Priyam
priyam.kushwaha
- Post Options:
- Link
  Copy
Posted 14 March 2024, 9:46 pm EST

Hi,

I’ve received an update, and it seems that the requested feature won’t be available until version 18 at the earliest.

In the meantime, the development team has suggested an alternative approach: you can access data manager table data using the “QUERY” function. You can learn more about the query function by referring to this demo: QUERY Function Demo. Once you have retrieved the data, you can use a camera shape to display it according to your requirements. To learn more about using camera shapes, you can refer to this demo: Camera Shape Demo.

Regards,

Priyam
Jean.s
- Post Options:
- Link
  Copy
Posted 15 March 2024, 12:20 am EST

HI Priyam,

thank you for your answer.

As for the suggested solution I partially agree. For instance how can I manage situation in witch you refresh your source and there are a different number of rows? How can I using a table format on the result of a Query formula?

Thank you
priyam.kushwaha
- Post Options:
- Link
  Copy
Posted 18 March 2024, 12:11 am EST

Hi,

We are still investigating the issue at our end. We will let you know about our findings as soon as possible.

Regards,

Priyam

priyam.kushwaha

Post Options:
Link
Copy

Posted 18 March 2024, 10:50 pm EST - Updated 19 March 2024, 4:00 pm EST

Hi,

As per my understanding, You want to update the added sheet table from the dataManager when the dataManager table data is updated, and you also want to use table formatting on the added table in the sheet.

To achieve this, you can access the dataManager table data by query funtion and getArray method together. After that, clear formula and set the headers for the table and then set the data of the table. Finally, add the table using the add method of the table manager of the sheet. You can then hide this sheet and create a camera shape to display this table in any other table. Refer attached snippet.

// event
    let sheetTable = null;
    let shape = null;
    document.getElementById("addTable").addEventListener("click", (e) => {
        sheet2.setFormula(1, 0, 'QUERY("myTable")');

        myTable.fetch().then(val => {
            setTimeout(() => {
                spread.suspendPaint();
                spread.suspendEvent();
                const usedRange = sheet2.getUsedRange(GC.Spread.Sheets.UsedRangeType.data);
                const data = sheet2.getArray(usedRange.row, usedRange.col, usedRange.rowCount, usedRange.colCount);

                // clear the formula
                sheet2.setFormula(1, 0, undefined)
                // set headers
                const headers = [["Description", "Due Date", "Priority", "Assigned to", "isFinished", "isEncourage", "Credit"]];
                sheet2.setArray(0, 0, headers)
                // adjust columns
                sheet2.setColumnWidth(0, 100)
                sheet2.setColumnWidth(1, 70)
                sheet2.setColumnWidth(2, 70)
                sheet2.setColumnWidth(3, 70)
                sheet2.setColumnWidth(4, 80)
                sheet2.setColumnWidth(5, 80)

                // adding dataManager table data 
                sheet2.setArray(1, 0, data);

                // add table
                const TableThemes = GC.Spread.Sheets.Tables.TableThemes;
                // use table to help set style then remove like convert table to range in Excel
                sheetTable = sheet2.tables.add("Table1", usedRange.row - 1, usedRange.col, usedRange.rowCount + 1, usedRange.colCount, TableThemes.light2);
                // adding shape in sheet1
                const formula = sheetTable.name() + "[#ALL]";
                shape = sheet.shapes.addCameraShape("myCameraShape1", formula, 120, 100);
                spread.resumeEvent();
                spread.resumePaint();
            }, 100)

        })

    })

If the dataManager table data is updated and you want to replicate it in a normal table in the sheet, you can follow same above steps after removing table and shape. Refer attached snippet.

[code]

document.getElementById(“update”).addEventListener(“click”, (e) => {

flag = true;

    // remove the table
    sheet2.tables.remove(sheetTable);
    sheet2.setFormula(1, 0, 'QUERY("myTable")');
    // remove existing shape and add another one
    sheet.shapes.remove(shape.name());

    myTable.fetch(true).then((val) => {

        setTimeout(() => {
            spread.suspendPaint();
            spread.suspendEvent();
            const usedRange = sheet2.getUsedRange(GC.Spread.Sheets.UsedRangeType.data);
            const data = sheet2.getArray(usedRange.row, usedRange.col, usedRange.rowCount, usedRange.colCount);

            // clear the formula
            sheet2.setFormula(1, 0, undefined)
            // set headers
            const headers = [["Description", "Due Date", "Priority", "Assigned to", "isFinished", "isEncourage", "Credit"]];
            sheet2.setArray(0, 0, headers)

            // adding dataManager table data 
            sheet2.setArray(1, 0, data);

            // add table
            const TableThemes = GC.Spread.Sheets.Tables.TableThemes;
            // use table to help set style then remove like convert table to range in Excel
            sheetTable = sheet2.tables.add("Table1", usedRange.row - 1, usedRange.col, usedRange.rowCount + 1, usedRange.colCount, TableThemes.light2);
            // adding shape in sheet1
            const formula = sheetTable.name() + "[#ALL]";
            shape = sheet.shapes.addCameraShape("myCameraShape1", formula, 120, 100);
            spread.resumeEvent();
            spread.resumePaint();
        }, 100)

    })
}

[/code])

I have attached a sample to refer. With above mentioned steps you can create normal table using dataManager table and then can use table formatting as used in normal table. This workaround is suggested by dev.

Sample: https://jscodemine.mescius.io/share/DHIdt4g7yEu_FPm8P2gpyQ/?defaultOpen={"OpenedFileName"%3A["%2Findex.html"]%2C"ActiveFile"%3A"%2Findex.html"}

References:

getArray: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#getarray

setArray: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#setarray

add: https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.Tables.TableManager#add

Regards,

Priyam

Jean.s
- Post Options:
- Link
  Copy
Posted 19 March 2024, 9:14 am EST

Hi Priyam,

I read your answer and it seems rather tricky to me.

Can you suggest a way to do this in the designer?

Thank you
priyam.kushwaha
- Post Options:
- Link
  Copy
Posted 20 March 2024, 8:29 pm EST

Hi,

As per my understanding, you wish to fulfill the requested requirement through the user interface in the designer.

As this feature is currently not supported, achieving it through the UI is not possible at the moment. Once this feature is implemented in the future, you will be able to accomplish it through the UI as well.

Regards,

Priyam

Please login to reply to thread

Need extra support?

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

Learn More

Forum Channels

ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.
ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server
Spread

Forums for all current versions of Spread .NET spreadsheets, SpreadJS JavaScript spreadsheets, and SpreadCOM spreadsheets.
Wijmo

Forums for all Wijmo products, including Wijmo Core, FinancialChart, FlexSheet, MultiRow, OLAP, and ReportViewer
- General Discussion
Document Solutions

Forums for all Document Solutions products, including Document Solutions for PDF, Word, Excel (.NET and Java), and Imaging.