How to Use SpreadJS DataManager to Bind Tablesheets to Multiple Views

June 22 2022
0 Comments

The SpreadJS Data Manager is a powerful data engine API built for performance that makes connecting and interacting with TableSheet's bound data faster and easier than ever with data relationships and views. The Data Manager allows you to load entire data tables and easily create multiple views using specific fields from that data source, giving you much faster performance with greater flexibility than just using the standard data binding.

In this blog, I will show you how to create a basic Data Manager, add some data to it, and then bind different views to the TableSheet and display them in a SpreadJS workbook. To follow along, you can download the sample for this blog.

Data Manager

Before we begin, we need to install the required SpreadJS files that we need for this application using NPM:

npm install @grapecity/spread-sheets @grapecity/spread-sheets-tablesheet

Creating the Data Manager

The first step is to create the DataManager. With SpreadJS, this is as simple as creating a new instance of Spread, getting the data manager, and then adding tables to it:

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
var dataManager = spread.dataManager();
var orderTable = dataManager.addTable("orderTable", {
    remote: {
        read: {
            url: "./Order.json"
        }
    }
});
var customerTable = dataManager.addTable("customerTable", {
    remote: {
        read: {
            url: "./Customer.json"
        }
    }
});
var employeeTable = dataManager.addTable("employeeable", {
    remote: {
        read: {
            url: "./Employee.json"
        }
    }
});

In the case of this blog, we are just using simple JSON files that contain the data, but you could bind to your data source or read from a specific URL.

Before we can start with creating styles we will want to add relationships between the tables. To do this, we can choose specific fields from different tables and create a connection between them:

dataManager.addRelationship(orderTable, "CustomerId", "customer", customerTable, "Id", "orders");
dataManager.addRelationship(orderTable, "EmployeeId", "employee", employeeTable, "Id", "orders");

Creating Views

Before we create our views of the Data Manager, we can start by defining some styles for some of the fields, specifically combining the different shipping address fields as well as the company and contact name for the customer:

// Create special styles for relationships
var addressStyle = {
    formatter: 'Ship To: {{=CONCAT(Property(@, "ShipAddress"), ", ", PROPERTY(@, "ShipCity"))}}'
};
var customerStyle = {
    formatter: '{{=CONCAT(PROPERTY(@, "CompanyName"), ", ", PROPERTY(@, "ContactName"))}}'//,
};

var dateStyle = { formatter: 'MM/dd/yyyy' };

Multiple views can be defined and bound to individual TableSheets, and in this blog, we will create three views:

Orders by Customer
Orders by Employee
General Orders

We will start with the Orders by Customer. To begin, add a TableSheet to the SpreadJS instance, and hide the new row and action column:

var customerOrderSheet = spread.addSheetTab(0, "Orders by Customer", GC.Spread.Sheets.SheetType.tableSheet);
customerOrderSheet.options.allowAddNew = false; //Hide new row option
customerOrderSheet.actionColumn.options({ visible: false }); //Hide action column

Now we can add a view to the table that we created earlier, utilizing the styles we made for the related data fields:

var customerOrderView = orderTable.addView("customerOrderView", [
    { value: "Id", width: 100 },
    { value: "OrderDate", width: 120, style: dateStyle, caption: "Order Date"},
    { value: "ShippedDate", width: 120, caption: "Shipped Date" },
    /**
     * customer.companyName - this is a related field from the customer table
     * update the customerId in orderTable by select from customer table.
     */
    { value: "customer", width: 350, caption: "Customer", style: customerStyle },
    /**
     * [=@] this column will contain the full row as a value, the formatter formula will extract the shipping address from the full row.
     * Format functions can be used to customize column display value
     * use star-size "2*" to set column width relative to the viewport width
     */
    { value: "=[@]", caption: "Address", style: addressStyle, width:320}
]);

Finally, we can set that data view in the TableSheet:

customerOrderView.fetch().then(function() {
    customerOrderSheet.setDataView(customerOrderView);
});

Similar code can be used to create the orders by employee view, and we can use a simple view for the default orders:

//Initialize a table sheet
var orderSheet = spread.addSheetTab(2, "Orders", GC.Spread.Sheets.SheetType.tableSheet);
orderSheet.options.allowAddNew = false; //Hide new row option
orderSheet.actionColumn.options({ visible: false }); //Hide action column
var defaultOrderView = orderTable.addView("defaultOrderView", [
    { value: "Id", width: 80 },
    { value: "CustomerId", width: 100 },
    { value: "ShipName", width: 100 },
    { value: "EmployeeId", width: 120 },
    { value: "OrderDate", width: 100, style: dateStyle },
    { value: "RequiredDate", width: 120, style: dateStyle },
    { value: "ShippedDate", width: 120, style: dateStyle },
    { value: "ShipVia", width: 80 },
    { value: "Freight", width: 80 },
    { value: "ShipAddress", width: 120 },
    { value: "ShipCity", width: 100 },
    { value: "ShipRegion", width: 100 },
    { value: "ShipPostalCode", width: 140 },
    { value: "ShipCountry", width: 120 }
]);
defaultOrderView.fetch().then(function() {
    orderSheet.setDataView(defaultOrderView);
});

That’s all you need to do to create multiple views from a Data Manager. To give this feature a try, make sure to download a trial of SpreadJS today. Take a look at our demos running live on our website.

Tags:

Related Blogs

Try Our JavaScript Spreadsheet Components

SpreadJS Resources

Learn More About SpreadJS

Release Information

Pricing and Licensing

Product Documentation

Kevin Ashley

Product Manager