This sample shows regular column, related column and calculated column, you can create different types of TableSheet columns in SpreadJS.
Regular column
Just use the data field name as column value, such as "firstName" in the employee table.
If the field value is an object, can use the "." to get the sub-field, such as "address.city" in the employee table.
Related column
After add relationship between two tables, user can use the related table as a field. Here is the sample code:
If the fields is an array, can use the ".1" to get the first item, e.g. "orders.1.orderDate". And use the ".property" to get the property form every object and get a new array, e.g. "orders.orderDate".
Calculated column
You can set the column value with a formula start with "=" as calculated column. With the field reference syntax rules in the following, you can reference the data in the table.
Object Type
Syntax Examples
Explain
Sample
Current Row Field
=[@column1]
get the data in the corrent row of field column1
=[@QtyAvailable] * [@UnitPrice] * 1.5
calculate the RetailValue of each row
Whole field
=[column1]
get all the data in the column1
=SUM([column1])
get the total sum of column1.
Attribute of Object Field
=[@column1.abc]
get the attribute value of "abc" in the current row of field column1
=[@name.first] & " " & [@name.last]
get the name string
Item of Array Field
=[@column1.0]
get the first value in the corrent row of field column1
=[@awards.0.name]
get the first award name
Attribute of Object Array Field
=[@column1.abc]
worksheet
=SUM([@orderDetails.weight])
get the weight of the order.
Current Whole Row
=[@]
Get the current row data as an object.
If the field name have space or dot, user should add the square brackets, such as: "=[@[column 1].[a.bc]]" will get the attribute value of "a.bc" in the current row of field 'column 1'
Besides the whole row and current row, user can get the specified row:
Sample
Explain
[#1[column1]]
get column1 value in first row.
[#1,#last[column1]]
get column1 values in first and last rows
[#odd[column1]]
get the column1 values in odd rows.
[#1,#even[column1]]
get the column1 values in first row and even rows.
[@+1[column1]]
get column1 value in the next row
[@-1[column1]]
get column1 value in the previous row.
[@+1:#last]
get the rows from next to the last row
[@-1:@+1]
get the rows from previous row to the next row
[@:#last]
get the rows from current row to the last row
[#1,@-1:@+1,#last[column1]]
get column1 values in first row, previous row to the next row, and the last row.
[#1]
get first row.
Here is the sample:
"=SUM([#1:@[change]])" will show the cumulative change.
var tables = {};
var selectedTable = "order";
window.onload = function () {
var mainSpread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
var dataManager = mainSpread.dataManager();
// addTable
tables.product = dataManager.addTable("productTable", {
data: data.products
});
tables.supplier = dataManager.addTable("supplierTable", {
data: data.suppliers
});
tables.category = dataManager.addTable("categoryTable", {
data: data.categories
});
tables.order = dataManager.addTable("orderTable", {
data: data.orders
});
tables.detail = dataManager.addTable("orderDetailTable", {
data: data.orderDetails
});
tables.shipper = dataManager.addTable("shipperTable", {
data: data.shippers
});
tables.employee = dataManager.addTable("employeeTable", {
data: data.employees
});
tables.customer = dataManager.addTable("customerTable", {
data: data.customers
});
// addRelationship
dataManager.addRelationship(tables.product, "supplierId", "supplier", tables.supplier, "id", "products");
dataManager.addRelationship(tables.product, "categoryId", "category", tables.category, "id", "products");
dataManager.addRelationship(tables.order, "customerId", "customer", tables.customer, "id", "orders");
dataManager.addRelationship(tables.order, "shipVia", "shipper", tables.shipper, "id", "orders");
dataManager.addRelationship(tables.order, "id", "details", tables.detail, "orderId", "order");
dataManager.addRelationship(tables.order, "employeeId", "employee", tables.employee, "id", "orders");
dataManager.addRelationship(tables.product, "id", "soldDetail", tables.detail, "productId", "product");
dataManager.addRelationship(tables.employee, "reportsTo", "manager", tables.employee, "id", "subordinate");
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("sampleDiv2"));
spread.fromJSON(ssjson);
spread.options.scrollbarMaxAlign = true;
var columnsSpread = new GC.Spread.Sheets.Workbook(document.getElementById("sampleDiv3"));
var sheet = columnsSpread.getActiveSheet();
columnsSpread.options.tabStripVisible = false;
columnsSpread.options.showHorizontalScrollbar = false;
columnsSpread.options.scrollbarMaxAlign = true;
sheet.setColumnCount(2);
sheet.setRowCount(12);
sheet.setColumnWidth(1, "*");
sheet.setText(0,0,"Caption",1);
sheet.setText(0,1,"Value",1);
sheet.getRange(-1,1,-1,1).formatter("@");
document.getElementById("sampleDiv2").addEventListener("click", function (e) {
var x = e.offsetX;
var y = e.offsetY;
var target = spread.getActiveSheet().hitTest(x, y);
if (target.shapeHitInfo == null && x > 700) {
y = y - 300;
target = spread.getActiveSheet().hitTest(x, y);
}
if (target.shapeHitInfo) {
var shape = target.shapeHitInfo.shape;
var style = shape.style();
if (style.fill) { // skip the connect line
style.fill.color = '#'+Math.floor(Math.random()*0xA00000 + 0x100000).toString(16); //random color
shape.style(style);
var text = shape.text();
switchTable(text);
}
}
}, false);
document.getElementById("setButton").addEventListener("click", function (e) {
setView();
}, false);
switchTable("order");
};
function switchTable (tableName) {
selectedTable = tableName;
setTimeout(function() {
var json = JSON.stringify(tables[selectedTable].get(0),null , 2); // get is internal API
document.getElementById("datasample").innerHTML = selectedTable + ".0:\n" + json;
}, 0);
var spread = GC.Spread.Sheets.findControl( document.getElementById("sampleDiv3"));
var sheet = spread.getActiveSheet();
sheet.clear(0, 0, sheet.getRowCount(), sheet.getColumnCount(), GC.Spread.Sheets.SheetArea.viewport,GC.Spread.Sheets.StorageType.data);
sheet.setArray(0,0,dataColumns[tableName]);
sheet.autoFitColumn(0);
setView();
}
function setView () {
var spread = GC.Spread.Sheets.findControl( document.getElementById("sampleDiv3"));
var sheet = spread.getActiveSheet();
var rowCount = sheet.getRowCount();
var fields = [];
for (var i = 0; i < rowCount; i++) {
var value = sheet.getValue(i, 1);
if (value) {
fields.push({value: value, caption: sheet.getValue(i, 0), width: 200});
}
}
var view = tables[selectedTable].addView("selectedTable"+(new Date()).valueOf(), fields);
var spread = GC.Spread.Sheets.findControl( document.getElementById("ss"));
spread.clearSheets();
spread.clearSheetTabs();
var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
sheet.options.allowAddNew = false;
sheet.actionColumn.options({ visible: false });
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
view.fetch().then(function(args) {
sheet.suspendPaint();
sheet.setDataView(view);
sheet.resumePaint();
});
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill -->
<script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/northwind-data.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/temple-shape.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<div class="option-row">
<div class="inputContainer">
<p>You can click the table.</p>
<div id="sampleDiv2" style="height: 143px;"></div>
</div>
<div class="inputContainer">
<p>You can edit the columns then press the "Set" button.</p>
<div id="sampleDiv3" style="height: 200px;"></div>
<div class="col-xs-4">
<input id="setButton" class="button" type="button" value="Set"/>
</div>
</div>
<div class="inputContainer">
<p>First data in the data table</p>
<pre id="datasample">
</pre>
</div>
</div>
</div>
</div>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 500px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 500px;
padding: 3px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.sample-options {
z-index: 1000;
}
.inputContainer {
width: 100%;
height: auto;
border: 1px solid #eee;
padding: 6px 12px;
margin-bottom: 10px;
box-sizing: border-box;
}
.input {
font-size: 14px;
border: 0;
outline: none;
background: transparent;
}
.button {
height: 30px;
padding: 6px 6px;
width: 80px;
margin-top: 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
