Spread.Sheets Documentation
Using Spread.Sheets with the Wijmo DataView

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 Spread.Sheets.

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.10.x.x.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 Spread.Sheets.

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 Spread.Sheets 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 http://wijmo.com/wiki/index.php/Data.

 

 

Copyright © GrapeCity, inc. All rights reserved.

Send comments on this topic.