Getting Started with SpreadJS V10

  • 0 Comments

SpreadJS Version 10 now includes two JavaScript components: Spread.Sheets and Spread.Views. Spread.Sheets is a more traditional spreadsheet component, while Spread.Views is a highly customizable data view component. This tutorial shows how to get started with both components. To download the sample pages used in this tutorial, see here: SpreadJS Getting Started Samples

Spread.Sheets

The Spread.Sheets component can be implemented in a web page with a small amount of code in a short amount of time. All you need is the core JavaScript library and style sheet, as there are no external dependencies. In this tutorial, the hosted versions of the Spread.Sheets files are used to show how to implement an instance of the Spread.Sheets control in a web page. You can use the same links or download SpreadJS and use the files locally. Either way, the Spread.Sheets component should show up on the page. Be sure to check out the Spread.Sheets Tutorial Site for over 100 more feature tutorials and examples. To start off, add these references to the Spread.Sheets core JavaScript and CSS files to your HTML’s HEAD section:


<link href=" http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.10.0.0.css" rel="stylesheet" type="text/css"/>  
<script type="text/javascript" src=" http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.10.0.0.min.js"></script>

Then use the window.onload function to initialize the Spread.Sheets component and get the Spread object.


var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });

In the HTML code, use a DIV and the object’s ID (in this example, “ss”) to place the sheet in the page’s BODY section.


<body>  
      <div id='ss' style='width:100%; height:500px;'></div>  
</body>

That’s all there is to getting started with using Spread.Sheets on a webpage. Now you can add some data as a table to the sheet:


var data = [{ "Id": 11074, "OrderDate": 42647, "RequiredDate": 35949, "ShipVia": 2, "Freight": 18.44, "ShipName": "Simons bistro", "ShipAddress": "Vinbæltet 34", "ShipCity": "Kobenhavn", "ShipPostalCode": "1734", "ShipCountry": "Denmark", "Employee": "Laura Callahan", "Customer": "Simons bistro", "ProductId": 16, "UnitPrice": 17.45, "Quantity": 14, "Discount": 0.05 },  
{ "Id": 11075, "OrderDate": 42648, "RequiredDate": 35949, "ShipVia": 2, "Freight": 6.19, "ShipName": "Richter Supermarkt", "ShipAddress": "Starenweg 5", "ShipCity": "Genève", "ShipPostalCode": "1204", "ShipCountry": "Switzerland", "Employee": "Anne Dodsworth", "Customer": "Richter Supermarkt", "ProductId": 2, "UnitPrice": 19, "Quantity": 10, "Discount": 0.15 },  
{ "Id": 11075, "OrderDate": 42649, "RequiredDate": 35949, "ShipVia": 2, "Freight": 6.19, "ShipName": "Richter Supermarkt", "ShipAddress": "Starenweg 5", "ShipCity": "Genève", "ShipPostalCode": "1204", "ShipCountry": "Switzerland", "Employee": "Anne Dodsworth", "Customer": "Richter Supermarkt", "ProductId": 46, "UnitPrice": 12, "Quantity": 30, "Discount": 0.15 },  
{ "Id": 11075, "OrderDate": 42650, "RequiredDate": 35949, "ShipVia": 2, "Freight": 6.19, "ShipName": "Richter Supermarkt", "ShipAddress": "Starenweg 5", "ShipCity": "Genève", "ShipPostalCode": "1204", "ShipCountry": "Switzerland", "Employee": "Anne Dodsworth", "Customer": "Richter Supermarkt", "ProductId": 76, "UnitPrice": 18, "Quantity": 2, "Discount": 0.15 },  
{ "Id": 11076, "OrderDate": 42651, "RequiredDate": 35949, "ShipVia": 2, "Freight": 38.28, "ShipName": "Bon app'", "ShipAddress": "12, rue des Bouchers", "ShipCity": "Marseille", "ShipPostalCode": "13008", "ShipCountry": "France", "Employee": "Steven Buchanan", "Customer": "Bon app", "ProductId": 6, "UnitPrice": 25, "Quantity": 20, "Discount": 0.25 },  
{ "Id": 11076, "OrderDate": 42654, "RequiredDate": 35949, "ShipVia": 2, "Freight": 38.28, "ShipName": "Bon app'", "ShipAddress": "12, rue des Bouchers", "ShipCity": "Marseille", "ShipPostalCode": "13008", "ShipCountry": "France", "Employee": "Steven Buchanan", "Customer": "Bon app", "ProductId": 14, "UnitPrice": 23.25, "Quantity": 20, "Discount": 0.25 },  
{ "Id": 11076, "OrderDate": 42656, "RequiredDate": 35949, "ShipVia": 2, "Freight": 38.28, "ShipName": "Bon app'", "ShipAddress": "12, rue des Bouchers", "ShipCity": "Marseille", "ShipPostalCode": "13008", "ShipCountry": "France", "Employee": "Steven Buchanan", "Customer": "Bon app", "ProductId": 19, "UnitPrice": 9.2, "Quantity": 10, "Discount": 0.25 },  
{ "Id": 11077, "OrderDate": 42657, "RequiredDate": 35949, "ShipVia": 2, "Freight": 8.53, "ShipName": "Rattlesnake Canyon Grocery", "ShipAddress": "2817 Milton Dr.", "ShipCity": "Albuquerque", "ShipRegion": "NM", "ShipPostalCode": "87110", "ShipCountry": "USA", "Employee": "Andrew Fuller", "Customer": "Rattlesnake Canyon Grocery", "ProductId": 2, "UnitPrice": 19, "Quantity": 24, "Discount": 0.2 },  
{ "Id": 11077, "OrderDate": 42658, "RequiredDate": 35949, "ShipVia": 2, "Freight": 8.53, "ShipName": "Rattlesnake Canyon Grocery", "ShipAddress": "2817 Milton Dr.", "ShipCity": "Albuquerque", "ShipRegion": "NM", "ShipPostalCode": "87110", "ShipCountry": "USA", "Employee": "Andrew Fuller", "Customer": "Rattlesnake Canyon Grocery", "ProductId": 3, "UnitPrice": 10, "Quantity": 4, "Discount": 0 },  
{ "Id": 11077, "OrderDate": 42661, "RequiredDate": 35949, "ShipVia": 2, "Freight": 8.53, "ShipName": "Rattlesnake Canyon Grocery", "ShipAddress": "2817 Milton Dr.", "ShipCity": "Albuquerque", "ShipRegion": "NM", "ShipPostalCode": "87110", "ShipCountry": "USA", "Employee": "Andrew Fuller", "Customer": "Rattlesnake Canyon Grocery", "ProductId": 4, "UnitPrice": 22, "Quantity": 1, "Discount": 0 },  
{ "Id": 11077, "OrderDate": 42662, "RequiredDate": 35949, "ShipVia": 2, "Freight": 8.53, "ShipName": "Rattlesnake Canyon Grocery", "ShipAddress": "2817 Milton Dr.", "ShipCity": "Albuquerque", "ShipRegion": "NM", "ShipPostalCode": "87110", "ShipCountry": "USA", "Employee": "Andrew Fuller", "Customer": "Rattlesnake Canyon Grocery", "ProductId": 13, "UnitPrice": 6, "Quantity": 4, "Discount": 0 },  
{ "Id": 11077, "OrderDate": 42663, "RequiredDate": 35949, "ShipVia": 2, "Freight": 8.53, "ShipName": "Rattlesnake Canyon Grocery", "ShipAddress": "2817 Milton Dr.", "ShipCity": "Albuquerque", "ShipRegion": "NM", "ShipPostalCode": "87110", "ShipCountry": "USA", "Employee": "Andrew Fuller", "Customer": "Rattlesnake Canyon Grocery", "ProductId": 14, "UnitPrice": 23.25, "Quantity": 1, "Discount": 0.03 },  
{ "Id": 11077, "OrderDate": 42765, "RequiredDate": 35949, "ShipVia": 2, "Freight": 8.53, "ShipName": "Rattlesnake Canyon Grocery", "ShipAddress": "2817 Milton Dr.", "ShipCity": "Albuquerque", "ShipRegion": "NM", "ShipPostalCode": "87110", "ShipCountry": "USA", "Employee": "Andrew Fuller", "Customer": "Rattlesnake Canyon Grocery", "ProductId": 16, "UnitPrice": 17.45, "Quantity": 2, "Discount": 0.03 },  
{ "Id": 11038, "OrderDate": 42769, "RequiredDate": 35934, "ShippedDate": 35915, "ShipVia": 2, "Freight": 29.59, "ShipName": "Suprêmes délices", "ShipAddress": "Boulevard Tirou, 255", "ShipCity": "Charleroi", "ShipPostalCode": "B-6000", "ShipCountry": "Belgium", "Employee": "Andrew Fuller", "Customer": "Suprêmes délices", "ProductId": 40, "UnitPrice": 18.4, "Quantity": 5, "Discount": 0.2 },  
{ "Id": 11038, "OrderDate": 42771, "RequiredDate": 35934, "ShippedDate": 35915, "ShipVia": 2, "Freight": 29.59, "ShipName": "Suprêmes délices", "ShipAddress": "Boulevard Tirou, 255", "ShipCity": "Charleroi", "ShipPostalCode": "B-6000", "ShipCountry": "Belgium", "Employee": "Andrew Fuller", "Customer": "Suprêmes délices", "ProductId": 52, "UnitPrice": 7, "Quantity": 2, "Discount": 0 },  
{ "Id": 11038, "OrderDate": 42772, "RequiredDate": 35934, "ShippedDate": 35915, "ShipVia": 2, "Freight": 29.59, "ShipName": "Suprêmes délices", "ShipAddress": "Boulevard Tirou, 255", "ShipCity": "Charleroi", "ShipPostalCode": "B-6000", "ShipCountry": "Belgium", "Employee": "Andrew Fuller", "Customer": "Suprêmes délices", "ProductId": 71, "UnitPrice": 21.5, "Quantity": 30, "Discount": 0 }  
];

Then add a table with data from that data source:


sheet.tables.addFromDataSource("Table1", 0, 0, data, GC.Spread.Sheets.Tables.TableThemes.dark7);

Don’t forget to visit the SpreadJS Tutorial Site to learn how to implement and customize features. Spread.Sheets V10 Page Spread.Sheets V10 Page

Spread.Views

In this part of the tutorial, we will use the same data that was used in the Spread.Sheets tutorial, to show how both components display the data differently. The Calendar View is a great example of the different kinds of data views that can be implemented easily with Spread.Views. Most of the code for this example comes from the Sales Report demo, with a few tweaks to suit our particular data. Since the data in our sample is different from the Sales Report demo, we need to change the column definitions:


var columns = [{  
    id: 'orderDate',  
    caption: 'Order Date',  
    dataField: 'OrderDate',  
    format: 'yyyy/MM/dd'  
}, {  
    id: 'shipName',  
    caption: 'Shipping Name',  
    dataField: 'ShipName'  
}, {  
    id: 'shipAddress',  
    caption: 'Shipping Address',  
    dataField: 'ShipAddress'  
}, {  
    id: 'shipCity',  
    caption: 'Shipping City',  
    dataField: 'ShipCity'  
}, {  
    id: 'shipRegion',  
    caption: 'Shipping Region',  
    dataField: 'ShipRegion'  
}, {  
    id: 'shipCountry',  
    caption: 'Shipping Country',  
    dataField: 'ShipCountry'  
}, {  
    id: 'productId',  
    caption: 'Product ID',  
    dataField: 'ProductId'  
}, {  
    id: 'unitPrice',  
    caption: 'Unit Price',  
    dataField: 'UnitPrice',  
    format: '$#,##0'  
}, {  
    id: 'quantity',  
    caption: 'Quantity',  
    dataField: 'Quantity'  
}, {  
    id: 'sales',  
    dataField: '=[quantity]*[unitPrice]',  
    format: '$#,##0'  
}];

Then we can change the header template (which is used for the actual days in the calendar) and the row template (which is used for the “details” dialog):


var headerTemplate = '<div class="summaryCell">' +  
    '<div class="salesContainer"><span>summary</span><div class="sales">$\{{=it.eval("=sum([sales])", "#,##")}}</div></div>' +  
    '</div>';  

var rowTemplate = '<div>' +  
    '<div class="orderPropertyName">Name:</div><div class="orderProperty">\{{=it.shipName}}</div>' +  
    '<div></div>' +  
    '<div class="orderPropertyName">Product ID:</div><div class="orderProperty">\{{=it.productId}}</div>' +  
    '<div></div>' +  
    '<div class="orderPropertyName">Shipping Address:</div><div class="orderProperty">\{{=it.shipAddress}}, \{{=it.shipCity}}, \{{=it.shipCountry}}</div>' +  
    '<div></div>' +  
    '<div class="orderPropertyName">Unit Price:</div><div class="orderProperty">\{{=it.unitPrice}}</div>' +  
    '<div></div>' +  
    '<div class="orderPropertyName">Quantity:</div><div class="orderProperty">\{{=it.quantity}}</div>' +  
    '<div></div>' +  
    '<div class="orderPropertyName">Sales:</div><div class="orderProperty">\{{=it.sales}}</div>' +  
    '<div></div>' +  
    '</div>';

That’s all there is to implementing your own Calendar View with Spread.Views. A few changes to the demo results in a calendar that suits our data, with very little code that needs to be rewritten. This tutorial shows how easy it is to work with Spread.Sheets and Spread.Views. With a small amount of code, you can add a spreadsheet and data view component to your page. To see the Spread.Views Demos, make sure to check out this link: Spread.Views Demos Spread.Views V10 Page Spread.Views V10 Page

MESCIUS inc.