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;
}
