SpreadJS 13
In This Topic
    SpreadJS with the Wijmo DataView
    In This Topic

    You can use the Wijmo DataView source (which implements the IDataView interface) to implement paging, filtering, and sorting functions.

    Use the following steps to use the DataView source with SpreadJS.

    1. Add the js reference.

    JavaScript
    Copy Code
    <!--JS Reference-->
     ....
     <script type="text/javascript" src="../XXX/globalize.min.js"></script>
     <script type="text/javascript" src="../XXX/knockout-2.1.0.js"></script>
     <script type="text/javascript" src="../XXX/knockout-2.1.0.js"></script>
     <script type="text/javascript" src="../XXX/gc.spread.sheets.all.13.0.0.min.js"></script>
     ....
    

    2. Define the view mode and implement the IDataView interface.

    JavaScript
    Copy Code
    var viewModel;
             function sourceData() {
                 return [
                     { "Product_ID": 1, "Supplier_ID": 1, "Category_ID": 1, "Product_Name": "Chai", "English_Name": "Dharamsala Tea", "Quantity_Per_Unit": "10 boxes x 20 bags", "Unit_Price": 18.0, "Units_In_Stock": 39, "Units_On_Order": 0, "Reorder_Level": 10, "Discontinued": false }, { "Product_ID": 2, "Supplier_ID": 1, "Category_ID": 1, "Product_Name": "Chang", "English_Name": "Tibetan Barley Beer", "Quantity_Per_Unit": "24 - 12 oz bottles", "Unit_Price": 19.0, "Units_In_Stock": 17, "Units_On_Order": 40, "Reorder_Level": 25, "Discontinued": false }, { "Product_ID": 24, "Supplier_ID": 10, "Category_ID": 1, "Product_Name": "Guaraná Fantástica", "English_Name": "Guaraná Fantástica Soft Drink", "Quantity_Per_Unit": "12 - 355 ml cans", "Unit_Price": 4.5, "Units_In_Stock": 20, "Units_On_Order": 0, "Reorder_Level": 0, "Discontinued": true }, { "Product_ID": 34, "Supplier_ID": 16, "Category_ID": 1, "Product_Name": "Sasquatch Ale", "English_Name": "Sasquatch Ale", "Quantity_Per_Unit": "24 - 12 oz bottles", "Unit_Price": 14.0, "Units_In_Stock": 111, "Units_On_Order": 0, "Reorder_Level": 15, "Discontinued": false }, { "Product_ID": 35, "Supplier_ID": 16, "Category_ID": 1, "Product_Name": "Steeleye Stout", "English_Name": "Steeleye Stout", "Quantity_Per_Unit": "24 - 12 oz bottles", "Unit_Price": 18.0, "Units_In_Stock": 20, "Units_On_Order": 0, "Reorder_Level": 15, "Discontinued": false }, { "Product_ID": 38, "Supplier_ID": 18, "Category_ID": 1, "Product_Name": "Côte de Blaye", "English_Name": "Côte de Blaye (Red Bordeaux wine)", "Quantity_Per_Unit": "12 - 75 cl bottles", "Unit_Price": 263.5, "Units_In_Stock": 17, "Units_On_Order": 0, "Reorder_Level": 15, "Discontinued": false }, { "Product_ID": 39, "Supplier_ID": 18, "Category_ID": 1, "Product_Name": "Chartreuse verte", "English_Name": "Green Chartreuse (Liqueur)", "Quantity_Per_Unit": "750 cc per bottle", "Unit_Price": 18.0, "Units_In_Stock": 69, "Units_On_Order": 0, "Reorder_Level": 5, "Discontinued": false }, { "Product_ID": 43, "Supplier_ID": 20, "Category_ID": 1, "Product_Name": "Ipoh Coffee", "English_Name": "Malaysian Coffee", "Quantity_Per_Unit": "16 - 500 g tins", "Unit_Price": 46.0, "Units_In_Stock": 17, "Units_On_Order": 10, "Reorder_Level": 25, "Discontinued": false }, { "Product_ID": 67, "Supplier_ID": 16, "Category_ID": 1, "Product_Name": "Laughing Lumberjack Lager", "English_Name": "Laughing Lumberjack Lager", "Quantity_Per_Unit": "24 - 12 oz bottles", "Unit_Price": 14.0, "Units_In_Stock": 52, "Units_On_Order": 0, "Reorder_Level": 10, "Discontinued": false }, { "Product_ID": 70, "Supplier_ID": 7, "Category_ID": 1, "Product_Name": "Outback Lager", "English_Name": "Outback Lager", "Quantity_Per_Unit": "24 - 355 ml bottles", "Unit_Price": 15.0, "Units_In_Stock": 15, "Units_On_Order": 10, "Reorder_Level": 30, "Discontinued": false }, { "Product_ID": 75, "Supplier_ID": 12, "Category_ID": 1, "Product_Name": "Rhönbräu Klosterbier", "English_Name": "Rhönbräu Beer", "Quantity_Per_Unit": "24 - 0.5 l bottles", "Unit_Price": 7.75, "Units_In_Stock": 125, "Units_On_Order": 0, "Reorder_Level": 25, "Discontinued": false }, { "Product_ID": 76, "Supplier_ID": 23, "Category_ID": 1, "Product_Name": "Lakkalikööri", "English_Name": "Cloudberry Liqueur", "Quantity_Per_Unit": "500 ml", "Unit_Price": 18.0, "Units_In_Stock": 57, "Units_On_Order": 0, "Reorder_Level": 20, "Discontinued": false },
                     { "Product_ID": 3, "Supplier_ID": 1, "Category_ID": 2, "Product_Name": "Aniseed Syrup", "English_Name": "Licorice Syrup", "Quantity_Per_Unit": "12 - 550 ml bottles", "Unit_Price": 10.0, "Units_In_Stock": 13, "Units_On_Order": 70, "Reorder_Level": 25, "Discontinued": false }, { "Product_ID": 4, "Supplier_ID": 2, "Category_ID": 2, "Product_Name": "Chef Anton's Cajun Seasoning", "English_Name": "Chef Anton's Cajun Seasoning", "Quantity_Per_Unit": "48 - 6 oz jars", "Unit_Price": 22.0, "Units_In_Stock": 53, "Units_On_Order": 0, "Reorder_Level": 0, "Discontinued": false }, { "Product_ID": 5, "Supplier_ID": 2, "Category_ID": 2, "Product_Name": "Chef Anton's Gumbo Mix", "English_Name": "Chef Anton's Gumbo Mix", "Quantity_Per_Unit": "36 boxes", "Unit_Price": 21.35, "Units_In_Stock": 0, "Units_On_Order": 0, "Reorder_Level": 0, "Discontinued": true }, { "Product_ID": 6, "Supplier_ID": 3, "Category_ID": 2, "Product_Name": "Grandma's Boysenberry Spread", "English_Name": "Grandma's Boysenberry Spread", "Quantity_Per_Unit": "12 - 8 oz jars", "Unit_Price": 25.0, "Units_In_Stock": 120, "Units_On_Order": 0, "Reorder_Level": 25, "Discontinued": false }, { "Product_ID": 8, "Supplier_ID": 3, "Category_ID": 2, "Product_Name": "Northwoods Cranberry Sauce", "English_Name": "Northwoods Cranberry Sauce", "Quantity_Per_Unit": "12 - 12 oz jars", "Unit_Price": 40.0, "Units_In_Stock": 6, "Units_On_Order": 0, "Reorder_Level": 0, "Discontinued": false }, { "Product_ID": 15, "Supplier_ID": 6, "Category_ID": 2, "Product_Name": "Genen Shouyu", "English_Name": "Lite Sodium Soy Sauce", "Quantity_Per_Unit": "24 - 250 ml bottles", "Unit_Price": 15.5, "Units_In_Stock": 39, "Units_On_Order": 0, "Reorder_Level": 5, "Discontinued": false }, { "Product_ID": 44, "Supplier_ID": 20, "Category_ID": 2, "Product_Name": "Gula Malacca", "English_Name": "Malacca Dark Brown Sugar", "Quantity_Per_Unit": "20 - 2 kg bags", "Unit_Price": 19.45, "Units_In_Stock": 27, "Units_On_Order": 0, "Reorder_Level": 15, "Discontinued": false }, { "Product_ID": 61, "Supplier_ID": 29, "Category_ID": 2, "Product_Name": "Sirop d'érable", "English_Name": "Maple Syrup", "Quantity_Per_Unit": "24 - 500 ml bottles", "Unit_Price": 28.5, "Units_In_Stock": 113, "Units_On_Order": 0, "Reorder_Level": 25, "Discontinued": false }, { "Product_ID": 63, "Supplier_ID": 7, "Category_ID": 2, "Product_Name": "Vegie-spread", "English_Name": "Vegetable Sandwich Spread", "Quantity_Per_Unit": "15 - 625 g jars", "Unit_Price": 43.9, "Units_In_Stock": 24, "Units_On_Order": 0, "Reorder_Level": 5, "Discontinued": false }, { "Product_ID": 65, "Supplier_ID": 2, "Category_ID": 2, "Product_Name": "Louisiana Fiery Hot Pepper Sauce", "English_Name": "Louisiana Fiery Hot Pepper Sauce", "Quantity_Per_Unit": "32 - 8 oz bottles", "Unit_Price": 21.05, "Units_In_Stock": 76, "Units_On_Order": 0, "Reorder_Level": 0, "Discontinued": false }, { "Product_ID": 66, "Supplier_ID": 2, "Category_ID": 2, "Product_Name": "Louisiana Hot Spiced Okra", "English_Name": "Louisiana Hot Spiced Okra", "Quantity_Per_Unit": "24 - 8 oz jars", "Unit_Price": 17.0, "Units_In_Stock": 4, "Units_On_Order": 100, "Reorder_Level": 20, "Discontinued": false }, { "Product_ID": 77, "Supplier_ID": 12, "Category_ID": 2, "Product_Name": "Original Frankfurter grüne Soße", "English_Name": "Original Frankfurter Green Sauce", "Quantity_Per_Unit": "12 boxes", "Unit_Price": 13.0, "Units_In_Stock": 32, "Units_On_Order": 0, "Reorder_Level": 15, "Discontinued": false }
                 ];
             }
     
             function ViewModel(sessionId) {
                 var self = this;
                 this.currentPositionChangedhandler = function(context) {
                     self.activeProduct(self.products.currentItem());
                 };
                 //ArrayDataView is a js type which implement IDataView Interface.
                 var productView = new wijmo.data.ArrayDataView(sourceData());//, {pageSize: 10});
     
                 this.products = productView;
     
                 this.products.currentPosition(0);
                 this.products.currentPosition.subscribe(this.currentPositionChangedhandler, null, null);
     
                 this.activeProduct = ko.observable(this.products.currentItem());
     
                 //IDataView Filter function.
                 this.clearFilter = function () {
                     productView.filter(null);
                 };
                 this.filterBeverages = function () {
                     productView.filter({ Category_ID: 1 });
                };
                 this.filterCondiments = function () {
                     productView.filter({ Category_ID: 2 });
                 };
     
                 //IDataView Sort function.
                 this.sortId = function () {
                     productView.sort("Product_ID");
                 };
                 this.sortPrice = function () {
                     productView.sort("Unit_Price desc, Product_Name");
                 };
     
                 //IDataView Paging function
                 this.clearPaging = function () {
                     productView.pageSize(0);
                 };
                 this.setPaging = function () {
                     productView.pageSize(10);
                 };
                 this.prevPage = function () {
                     productView.prevPage();
                 };
                 this.nextPage = function () {
                     productView.nextPage();
                 };
     
                 //IDataView Add function.
                 this.add = function () {
                     productView.add({
                         Product_ID: 100,
                         Product_Name: "Tomato",
                         Category_ID: 1,
                         Unit_Price: 5
                     });
                     productView.commitEdit();
                 };
                
                 //IDataView Delete function.
                 this.deleteCurrent = function () {
                     productView.remove();
                 };
                
                 //IDataView Edit Value in source function.
                 this.doublePrice = function () {
                     productView.editItem();
                     productView.currentEditItem().Unit_Price *= 2;
                     productView.commitEdit();
                 };
             }
     
             $(document).ready(function () {
                 if (!viewModel) viewModel = new ViewModel();
                 ko.applyBindings(viewModel);
             });
    

    3. Bind to SpreadJS.

    JavaScript
    Copy Code
    <div id="ss" data-bind="gcspread-sheets: { sheetCount:1,
                     activeSheetIndex:0,
                     tabEditable:false,
                     newTabVisible:false,
                     sheets:[
                         {
                             data: products, columns: [
                                 { displayName: 'ID', name: 'Product_ID', width:60 },
                                 { displayName: 'Product name', name: 'Product_Name', width:260 },
                                 { displayName: 'Category ID', name: 'Category_ID', width:100 },
                                 { displayName: 'Price', name: 'Unit_Price', width:60 }
                             ]
                         }
                     ]
                 }" style="width:850px; height:300px; border: 1px solid black;"></div>
    

    4. Invoke the IDataView API to refresh SpreadJS and display the result.

    JavaScript
    Copy Code
    <button data-bind="click:clearPaging">pageSize = 0</button>
    <button data-bind="click:setPaging">pageSize = 10</button>
    <button data-bind="click:prevPage">prev</button>
    <button data-bind="click:nextPage">next</button>
    

    For more information about binding with Wijmo, refer to https://help.grapecity.com/wijmo/3/webframe.html#Data.html.