数据看板

以下示例显示了如何在DataManager中使用关系以及计算字段。在这个示例中,右上方的工作簿用于显示数据表及其关系。尝试切换不同的数据表并输入列以查看数据。

这个示例介绍了 常规列, 关系列 和 计算列,你可以在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: "*"}); } } 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.fetchRows().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/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/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/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; }