Cross Column Panel

TableSheet has a cross column panel to help users in creating cross columns.

A TableSheet has an interactive TableSheet Panel that supports setting up cross columns. The following are some steps that show how the TableSheet Panel can be used to add/update/remove cross columns: Add a TableSheet Panel Add multiple tables with the columns option (the lookup property should be set) in the data source schema Specify the primary key of the tables Add a relationship between the tables Add a custom view with the column options that have the lookup field Click the lookup field, and drag the another field to the cross area, a new cross column will be added Click the existing cross field to update or remove it Create a TableSheet Panel: This sample code shows that the lookup field is the table name of the relationship: The sample code shows that the lookup field is the values of an array:
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ var baseApiUrl = getBaseApiUrl(); var baseTableName = "Cross_Column_"; window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; spread.clearSheets(); //init a data manager var dataManager = spread.dataManager(); let panelObject = {panel: undefined}; initStudentGradeTableSheet(spread, dataManager, panelObject); initPaymentTableSheet(spread, dataManager); spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanged, function () { let activeSheetTab = spread.getActiveSheetTab(); if (activeSheetTab && panelObject.panel) { panelObject.panel.detach(); panelObject.panel.attach(activeSheetTab); } }); spread.resumePaint(); } function initStudentGradeTableSheet(spread, dataManager, panelObject) { // enable allowDynamicArray to using FILTER formula for cross column spread.options.allowDynamicArray = true; var dataSource = prepareData(); var studentTable = dataManager.addTable("Students", { autoSync: true, remote: { read: fakeRead(dataSource.students), update: fakeUpdate(dataSource.students), create: fakeCreate(dataSource.students), delete: fakeDelete(dataSource.students), }, schema: { columns: { ID: { dataType: "number" }, Name: { dataType: "string" }, } } }); studentTable.primaryKey("ID"); var workItemTable = dataManager.addTable("WorkItems", { autoSync: true, remote: { read: fakeRead(dataSource.workItems), update: fakeUpdate(dataSource.workItems), create: fakeCreate(dataSource.workItems), delete: fakeDelete(dataSource.workItems), }, schema: { columns: { ID: { dataType: "number" }, Date: { dataType: "date" }, Description: { dataType: "string" }, TotalPoints: { dataType: "number" }, Type: { dataType: "string" }, } } }); workItemTable.primaryKey("ID"); var gradeTable = dataManager.addTable("Grades", { autoSync: true, remote: { read: fakeRead(dataSource.grades), update: fakeUpdate(dataSource.grades, ['StudentID', 'WorkItemID']), create: fakeCreate(dataSource.grades), delete: fakeDelete(dataSource.grades, ['StudentID', 'WorkItemID']), }, schema: { columns: { StudentID: { dataType: "number" }, WorkItemID: { dataType: "number", lookup: "workItem" }, Grade: { dataType: "number" } } } }); gradeTable.primaryKey("StudentID,WorkItemID"); dataManager.addRelationship(gradeTable, "StudentID", "student", studentTable, "ID", "grades"); dataManager.addRelationship(gradeTable, "WorkItemID", "workItem", workItemTable, "ID", "grades"); var gradeSheet = spread.addSheetTab(0, "Grade Book", GC.Spread.Sheets.SheetType.tableSheet); gradeSheet.options.allowAddNew = true; var rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions; var options = gradeSheet.rowActionOptions(); options.push( rowActions.removeRow, rowActions.saveRow, rowActions.resetRow, ); gradeSheet.rowActionOptions(options); var gradeView = studentTable.addView("gradeView", [ { value: 'Name', width: 150 }, { value: 'grades', width: 150 }, { value: "grades.Grade", cross: { over: 'grades.WorkItemID', attributes: ['grades.workItem.Type', 'grades.workItem.TotalPoints', { value: 'grades.workItem.Date', formatter: 'dd-MMM' }], filter: '=FILTER([grades.workItem.ID],[grades.workItem.Description]<>"HW 20")', } }, ], undefined, { defaultColumnWidth: 80 } ); gradeView.fetch().then(function () { gradeSheet.setDataView(gradeView); if (!panelObject.panel) { var host = document.getElementById("panel"); panelObject.panel = new GC.Spread.Sheets.TableSheet.TableSheetPanel("myPanel", gradeSheet, host, { showSource: GC.Spread.Sheets.TableSheet.ShowSourceOptions.all }); } }); } function initPaymentTableSheet(spread, dataManager) { var paymentTable = dataManager.addTable("Payments", { data: [ { "CustomerID": "1", "CustomerName": "Overbees Stocks", "PmtDate": "2/10/2019", "PmtMethod": "ACH", "Amount": 2000 }, { "CustomerID": "2", "CustomerName": "Lincoln Construction", "PmtDate": "3/15/2029", "PmtMethod": "Cash", "Amount": 3900 }, { "CustomerID": "3", "CustomerName": "Excelton Foods", "PmtDate": "3/18/2019", "PmtMethod": "CC", "Amount": 3500 }, { "CustomerID": "4", "CustomerName": "Cheasepeak inc", "PmtDate": "4/10/2019", "PmtMethod": "Cash", "Amount": 2300 } ], schema: { columns: { CustomerID: { dataType: "string" }, CustomerName: { dataType: "string" }, PmtDate: { dataType: "date" }, PmtMethod: { dataType: "string", lookup: ["Cash", "Check", "ACH", "CC"] // specify the values can lookup, and could be used for cross columns }, Amount: { dataType: "number" } } }, }); paymentTable.primaryKey('CustomerID'); var paymentSheet = spread.addSheetTab(1, "Payments Ledger", GC.Spread.Sheets.SheetType.tableSheet); paymentSheet.options.allowAddNew = true; var rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions; var options = paymentSheet.rowActionOptions(); options.push( rowActions.removeRow, rowActions.saveRow, rowActions.resetRow, ); paymentSheet.rowActionOptions(options); var paymentView = paymentTable.addView("paymentView", [ { value: 'CustomerName', width: 150 }, { value: 'PmtDate', width: 120, style: { formatter: 'MM/dd/yyyy' } }, { value: 'PmtMethod', width: 120, }, { value: 'Amount', width: 120, }, { value: "Amount", cross: { over: 'PmtMethod', caption: 'Payment Method', }, style: { formatter: '$#,##0' } }, ], undefined, { defaultColumnWidth: 100 }); paymentView.fetch().then(function () { paymentSheet.setDataView(paymentView); }); } function prepareData() { var dataSource = {}; var students = [ { "ID": 1, "Name": "Ellen Robinson" }, { "ID": 2, "Name": "Jerry Williams" }, { "ID": 3, "Name": "Steven Kunes" }, { "ID": 4, "Name": "Lisa Williamsburg" }, { "ID": 5, "Name": "Donald Draglin" } ]; var workItems = [ { "ID": 1, "Date": "9/12/2020", "Description": "Know your numbers", "TotalPoints": 10, "Type": "Homework" }, { "ID": 2, "Date": "10/10/2020", "Description": "Add numbers", "TotalPoints": 10, "Type": "Homework" }, { "ID": 3, "Date": "10/15/2020", "Description": "Addition", "TotalPoints": 25, "Type": "Quiz" }, { "ID": 4, "Date": "11/5/2020", "Description": "Subtract Numbers", "TotalPoints": 10, "Type": "Homework" }, { "ID": 5, "Date": "11/30/2020", "Description": "Subtraction", "TotalPoints": 25, "Type": "Quiz" }, { "ID": 6, "Date": "12/10/2020", "Description": "Mid-term", "TotalPoints": 100, "Type": "Exam" }, { "ID": 7, "Date": "2/2/2020", "Description": "HW 20", "TotalPoints": 10, "Type": "Homework" }, { "ID": 8, "Date": "2/22/2022", "Description": "HW 20", "TotalPoints": 20, "Type": "Homework" } ]; var grades = [ { "StudentID": 1, "WorkItemID": 1, "Grade": 4 }, { "StudentID": 2, "WorkItemID": 1, "Grade": 9 }, { "StudentID": 3, "WorkItemID": 1, "Grade": 8 }, { "StudentID": 4, "WorkItemID": 1, "Grade": 9 }, { "StudentID": 5, "WorkItemID": 1, "Grade": 6 }, { "StudentID": 1, "WorkItemID": 2, "Grade": 7 }, { "StudentID": 2, "WorkItemID": 2, "Grade": 5 }, { "StudentID": 3, "WorkItemID": 2, "Grade": 7 }, { "StudentID": 4, "WorkItemID": 2, "Grade": 8 }, { "StudentID": 5, "WorkItemID": 2, "Grade": 9 }, { "StudentID": 1, "WorkItemID": 3, "Grade": 18 }, { "StudentID": 2, "WorkItemID": 3, "Grade": 23 }, { "StudentID": 3, "WorkItemID": 3, "Grade": 15 }, { "StudentID": 4, "WorkItemID": 3, "Grade": 19 }, { "StudentID": 5, "WorkItemID": 3, "Grade": 6 }, { "StudentID": 1, "WorkItemID": 4, "Grade": 5 }, { "StudentID": 2, "WorkItemID": 4, "Grade": 8 }, { "StudentID": 3, "WorkItemID": 4, "Grade": 9 }, { "StudentID": 4, "WorkItemID": 4, "Grade": 8 }, { "StudentID": 5, "WorkItemID": 4, "Grade": 6 }, { "StudentID": 1, "WorkItemID": 5, "Grade": 22 }, { "StudentID": 2, "WorkItemID": 5, "Grade": 7 }, { "StudentID": 3, "WorkItemID": 5, "Grade": 12 }, { "StudentID": 4, "WorkItemID": 5, "Grade": 10 }, { "StudentID": 5, "WorkItemID": 5, "Grade": 8 }, { "StudentID": 1, "WorkItemID": 6, "Grade": 45 }, { "StudentID": 2, "WorkItemID": 6, "Grade": 45 }, { "StudentID": 3, "WorkItemID": 6, "Grade": 21 }, { "StudentID": 4, "WorkItemID": 6, "Grade": 86 }, { "StudentID": 5, "WorkItemID": 6, "Grade": 6 }, ]; dataSource.students = students; dataSource.workItems = workItems; dataSource.grades = grades; return dataSource; } function fakeRead(data) { return function () { return Promise.resolve(data); } } function isPropertiesEqual(idNames, item, data) { for (let j = 0; j < idNames.length; j++) { const p = idNames[j]; if (item[p] != data[p]) { return false; } } return true; } function fakeUpdate(data, idNames = ['ID']) { return function (item) { for (var i = 0; i < data.length; i++) { if (isPropertiesEqual(idNames, item, data[i])) { data[i] = item; return Promise.resolve(item); } } return Promise.reject("Not found"); } } function fakeDelete(data, idNames = ['ID']) { return function (item) { for (var i = 0; i < data.length; i++) { if (isPropertiesEqual(idNames, item[0], data[i])) { data.splice(i, 1); return Promise.resolve(item); } } return Promise.reject("Not found"); } } function getFakeId(data, idName) { let max = 0; for (let i = 0, length = data.length; i < length; i++) { let id = parseInt(data[i][idName]); if (id > max) { max = id; } } return max + 1; } function fakeCreate(data, idName = 'ID') { return function (item) { item[idName] = getFakeId(data, idName); data.push(item); return Promise.resolve(item); } } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api'; }
<!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-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/orderDataSource.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.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 id="panel" class="container"></div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: calc(100% - 303px); height: 100%; overflow: hidden; float: left; } .container { width: 300px; height: 100%; float: left; border: 1px solid lightgrey; }