数据看板 | 表格插件 | SpreadJS JavaScript 学习指南

这个示例介绍了常规列, 关系列和计算列，你可以在SpreadJS中创建不同的tableSheet的列。常规列只需将数据字段名称作为列值，例如使用employee表中的“firstName”。如果字段值是一个对象类型，则可以使用“.”来获取子字段，例如employee表中的“address.city”。关系列在两个表之间添加关联关系之后，用户可以将关联的表作为一个字段。下面是一些简单的代码：如果字段是数组，则可以使用“.1”获取第一项，例如“orders.1.orderData”。同时可以使用“.property”从每个对象中获取属性并创建一个新的数组，例如“orders.orderDate”。计算列您可以使用以“=”开头的公式作为计算列来设置列值。使用下面的字段引用语法规则，您可以引用表中的数据。对象类型语法示例说明示例当前行字段 =[@column1] 在当前行获取字段column1的数据 =[@QtyAvailable] * [@UnitPrice] * 1.5 计算每一行的零售价所有字段 =[column1] 获取column1的所有数据 =SUM([column1]) 获取column1的总和对象字段中的属性 =[@column1.abc] 在当前行获取字段column1中“abc”的属性值 =[@name.first] & " " & [@name.last] 获取到全名数组字段中的某一项 =[@column1.0] 在当前行获取字段column1的第一个值 =[@awards.0.name] 获取第一名对象数组字段中的属性 =[@column1.abc] 工作表 =SUM([@orderDetails.weight]) 获取到订单的weight属性当前整行 =[@] 获取当前整行数据并构造为一个对象如果字段名带有空格或点，则用户应添加方括号，例如：“=[@[column 1].[a.bc]]” 将会在当前行中获取字段column1中属性“a.bc”的值。除了整行和当前行，用户还可以获取到指定的行。示例说明 [#1[column1]] 获取到第一行的column1字段的值。 [#1,#last[column1]] 获取到第一行以及最后一行的column1字段的值。 [#odd[column1]] 获取到奇数行的column1字段的值。 [#1,#even[column1]] 获取到第一行以及偶数行的column1字段的值。 [@+1[column1]] get column1 value in the next row [@-1[column1]] 获取到前一行的column1字段的值。 [@+1:#last] 获取到从下一行到最后一行之间的所有行。 [@-1:@+1] 获取到从上一行到下一行之间的所有行。 [@:#last] 获取到从当前行到最后一行之间的所有行。 [#1,@-1:@+1,#last[column1]] 获取到第一行，上一行到下一行，以及最后一行的column1字段的值。 [#1] 获取到第一行。这是示例: "=SUM([#1:@[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 name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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;
}