Cross columns can be set using the addView
method with these options:
To show cross columns, there's an important rule that only the fields that are defined as lookup fields in the schema can be used. There are 4 steps to follow:
Add multiple tables with column options in the data source schema
Specify the primary key of the tables
Add a relationship between the tables
Add a custom view with the cross column options
This is the sample code for adding cross columns with related tables.
To filter the specified cross columns, enable the allowDynamicArray
property.
The first argument of the FILTER must specify the primary key that was used when adding the relationships, and the second argument is the conditions.
The cross column header can be formatted by using the option formatter.
The values can be calculated by a formula that looks like: =SUM(FILTER([@grades.Grade],[@grades.workItem.Type]="Homework"))
This is the sample code for using cross columns with a list.
The caption option can be used in order to define the header for the cross column.
This is the sample code for combining the cross column.
When cross columns with same cross option are combined, it's better to show the different values by using showCrossValueHeader
.
/*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();
initStudentGradeTableSheet(spread, dataManager);
initPaymentTableSheet(spread, dataManager);
initInspectionReportTableSheet(spread, dataManager);
initStudentsGradesTableSheet(spread, dataManager);
spread.resumePaint();
}
function initStudentGradeTableSheet(spread, dataManager) {
// 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.Grade",
cross: {
over: 'grades.WorkItemID',
attributes: ['grades.workItem.Type', 'grades.workItem.TotalPoints', { value: 'grades.workItem.Date', formatter: 'dd-MMM' }], // format the value
filter: '=FILTER([grades.workItem.ID],[grades.workItem.Description]<>"HW 20")', // Filter the WorkItems table
}
},
{ value: '=SUM(FILTER([@grades.Grade],[@grades.workItem.Type]="Homework"))', caption: 'Total Grade of Homework', width: 190 },
{ value: '=SUM(FILTER([@grades.Grade],[@grades.workItem.Type]="Quiz"))', caption: 'Total Grade of Quiz', width: 150 },
{ value: '=SUM(FILTER([@grades.Grade],[@grades.workItem.Type]="Exam"))', caption: 'Total Grade of Exam', width: 150 },
],
undefined,
{ defaultColumnWidth: 80 }
);
gradeView.fetch().then(function () {
gradeSheet.setDataView(gradeView);
});
}
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: "Amount",
cross: {
over: 'PmtMethod',
caption: 'Payment Method', // setting the pre-defined header for cross columns
},
style: { formatter: '$#,##0' }
},
], undefined, { defaultColumnWidth: 100 });
paymentView.fetch().then(function () {
paymentSheet.setDataView(paymentView);
})
}
function initInspectionReportTableSheet(spread, dataManager) {
var itemTableName = "Items";
var itemTableApiUrl = baseApiUrl + "/" + baseTableName + itemTableName;
var itemTable = dataManager.addTable(itemTableName, {
remote: {
read: {
url: itemTableApiUrl
},
update: {
url: itemTableApiUrl,
method: 'PUT'
},
create: {
url: itemTableApiUrl
},
delete: {
url: itemTableApiUrl
},
batch: {
url: itemTableApiUrl + "Collection"
}
},
autoSync: true,
schema: {
columns: {
Id: { dataType: "number" },
Name: { dataType: "string" },
}
}
});
itemTable.primaryKey('Id');
var subItemTableName = "SubItems";
var subItemTableApiUrl = baseApiUrl + "/" + baseTableName + subItemTableName;
var subItemTable = dataManager.addTable(subItemTableName, {
remote: {
read: {
url: subItemTableApiUrl
},
update: {
url: subItemTableApiUrl,
method: 'PUT'
},
create: {
url: subItemTableApiUrl
},
delete: {
url: subItemTableApiUrl
},
batch: {
url: subItemTableApiUrl + "Collection"
}
},
autoSync: true,
schema: {
columns: {
Id: { dataType: "number" },
ItemId: { dataType: "number" },
SubItemId: { dataType: "string" },
Description: { dataType: "string" },
}
}
});
subItemTable.primaryKey('Id');
var detailTableName = "Details";
var detailTableApiUrl = baseApiUrl + "/" + baseTableName + detailTableName;
var detailTable = dataManager.addTable(detailTableName, {
remote: {
read: {
url: detailTableApiUrl
},
update: {
url: detailTableApiUrl,
method: 'PUT'
},
create: {
url: detailTableApiUrl
},
delete: {
url: detailTableApiUrl
},
batch: {
url: detailTableApiUrl + "Collection"
}
},
autoSync: true,
schema: {
columns: {
Id: { dataType: "number" },
ItemId: { dataType: "number" },
SubItemId: { dataType: "string" },
Workshop: {
dataType: "string",
lookup: ['Workshop1', 'Workshop2', 'Workshop3']
},
Result: { dataType: "boolean", dataPattern: "1|0" },
Problem: { dataType: "string" },
}
}
});
detailTable.primaryKey('Id');
dataManager.addRelationship(detailTable, "SubItemId", "subItem", subItemTable, "SubItemId", "details");
dataManager.addRelationship(subItemTable, "ItemId", "item", itemTable, "Id", "subItems");
var inspectionReportSheet = spread.addSheetTab(2, "Inspection Report", GC.Spread.Sheets.SheetType.tableSheet);
inspectionReportSheet.options.allowAddNew = false;
var rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions;
var options = inspectionReportSheet.rowActionOptions();
options.push(
rowActions.saveRow,
rowActions.resetRow,
);
inspectionReportSheet.rowActionOptions(options);
var inspectReportView = subItemTable.addView("inspectReportView", [
{ value: '=RIGHT([@SubItemId])&" "&[@Description]', caption: 'Description', width: 300 },
{
value: "details.Result",
caption: "Pass?",
cross: "workshopResult",
width: 120,
style: { cellType: new GC.Spread.Sheets.CellTypes.CheckBox(), hAlign: 'center', vAlign: 'center' }
},
{
value: "details.Problem",
caption: "Note",
cross: "workshopResult",
width: 120
},
],
undefined,
{
cross: { // combined the cross column together
workshopResult: {
over: 'details.Workshop',
},
},
showCrossValueHeader: true // show the headers specify by the property value
}
);
inspectReportView.fetch().then(function () {
inspectionReportSheet.setDataView(inspectReportView);
inspectionReportSheet.groupBy([
{ field: 'item.Id', caption: 'No.', width: 70 },
{ field: 'item.Name', caption: 'Category', width: 250 },
]);
})
}
function initStudentsGradesTableSheet (spread, dataManager) {
// enable allowDynamicArray to using FILTER formula for cross column
spread.options.allowDynamicArray = true;
let templateSheet = initTemplateSheet(spread);
var dataSource = prepareStudentsGradesData();
var studentTable = dataManager.addTable("Students2", {
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 classesTable = dataManager.addTable("Classes", {
autoSync: true,
remote: {
read: fakeRead(dataSource.classes),
update: fakeUpdate(dataSource.classes),
create: fakeCreate(dataSource.classes),
delete: fakeDelete(dataSource.classes),
},
schema: {
columns: {
ID: { dataType: "number" },
ClassName: { dataType: "string" },
Type: { dataType: "string" },
}
}
});
classesTable.primaryKey("ID");
var gradeTable = dataManager.addTable("Grades2", {
autoSync: true,
remote: {
read: fakeRead(dataSource.grades),
update: fakeUpdate(dataSource.grades, ['StudentID', 'ClassID']),
create: fakeCreate(dataSource.grades),
delete: fakeDelete(dataSource.grades, ['StudentID', 'ClassID']),
},
schema: {
columns: {
StudentID: { dataType: "number" },
ClassID: { dataType: "number", lookup: "class" },
Grade: {},
}
}
});
gradeTable.primaryKey("StudentID,ClassID");
dataManager.addRelationship(gradeTable, "StudentID", "student", studentTable, "ID", "grades");
dataManager.addRelationship(gradeTable, "ClassID", "class", classesTable, "ID", "grades");
var studentsGradesSheet = spread.addSheetTab(0, "Students Grades", GC.Spread.Sheets.SheetType.tableSheet);
studentsGradesSheet.options.allowAddNew = false;
studentsGradesSheet.options.defaultStackRowHeight = 30;
studentsGradesSheet.setDefaultRowHeight(120, 1);
studentsGradesSheet.setDefaultRowHeight(50);
var rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions;
var options = studentsGradesSheet.rowActionOptions();
options.push(
rowActions.saveRow,
rowActions.resetRow,
);
studentsGradesSheet.rowActionOptions(options);
var cellType = new GC.Spread.Sheets.CellTypes.RangeTemplate(templateSheet, 0, 0, 1, 2);
var rangeTemplateStyle = new GC.Spread.Sheets.Style(); //StyleOptions doesn't support RangeTemplateCellType, so keep use Style here
rangeTemplateStyle.cellType = cellType;
var formulaRule = {
ruleType: "formulaRule",
formula: "AND(@<60,@>0)",
style: {
backColor: "rgb(254,89,88)"
}
};
var gradeView = studentTable.addView("gradeView", [
{ value: '=[@]', caption: "Name", width: 200, headerStyle: {backColor: "#3CC200", foreColor: "white"}, style: {foreColor: "#66474E", backColor: "white", cellType: cellType}},
{ value: '=IFS(AVERAGE(FILTER([@grades.Grade],[@grades.class.Type]="Tests"))>80,"A",AVERAGE(FILTER([@grades.Grade],[@grades.class.Type]="Tests"))>70,"B",AVERAGE(FILTER([@grades.Grade],[@grades.class.Type]="Tests"))>60,"C", AVERAGE(FILTER([@grades.Grade],[@grades.class.Type]="Tests"))>0, "E")', caption: 'Tests', width: 50, headerFit: "stack", headerStyle: {backColor: "#FBD0DA", foreColor: "#66474E", hAlign: "center", font: "bold 12pt Arial"}, style: {backColor: "#FBD0DA", foreColor: "#66474E", hAlign: "center", vAlign: "center", font: "bold 11pt Arial"} },
{
value: "grades.Grade",
cross: {
over: 'grades.ClassID',
attributes: ['grades.class.ClassName'], // format the value
filter: '=FILTER([grades.class.ID],[grades.class.Type]="Tests")', // Filter the Tests table
},
headerFit: "vertical",
width: 45,
headerStyle: {backColor: "#FDEDF4", foreColor: "#66474E", vAlign: "bottom"},
style: {backColor: "#FDEDF4", foreColor: "#66474E", vAlign: "center", hAlign: "center"},
conditionalFormats: [formulaRule]
},
{
value: "grades.Grade",
cross: {
over: 'grades.ClassID',
attributes: ['grades.class.ClassName'], // format the value
filter: '=FILTER([grades.class.ID],[grades.class.Type]="Projects")', // Filter the Projects table
},
headerStyle: {textOrientation: -90, backColor: "#FFFCEA", foreColor: "#66474E", vAlign: "bottom"},
width: 45,
style: {backColor: "#FFFCEA", foreColor: "#66474E", vAlign: "center", hAlign: "center"}
},
{ value: '=AVERAGE(FILTER([@grades.Grade],[@grades.class.Type]="Homeworks"))', caption: 'Homeworks', width: 50, headerFit: "stack", headerStyle: {backColor: "#FEF8C4", foreColor: "#66474E", hAlign: "center", font: "bold 12pt Arial"}, style: {backColor: "#FEF8C4", foreColor: "#66474E", vAlign: "center", hAlign: "center", font: "bold 11pt Arial"} },
{
value: "grades.Grade",
cross: {
over: 'grades.ClassID',
attributes: ['grades.class.ClassName'], // format the value
filter: '=FILTER([grades.class.ID],[grades.class.Type]="Homeworks")', // Filter the Homeworks table
},
headerFit: "vertical",
width: 45,
headerStyle: {backColor: "#FDECB3", foreColor: "#66474E", vAlign: "bottom"},
style: {backColor: "#FDECB3", foreColor: "#66474E", vAlign: "center", hAlign: "center"}
},
],
undefined,
{ defaultColumnWidth: 45 }
);
var style = new GC.Spread.Sheets.Style();
let lineBorder = new GC.Spread.Sheets.LineBorder();
lineBorder.style = 1;
lineBorder.color = "lightgrey";
style.borderBottom = lineBorder;
style.borderRight = lineBorder;
style.borderLeft = lineBorder;
style.borderTop = lineBorder;
studentsGradesSheet.options.alternatingRowOptions = {
style: style,
step: [1,0]
};
var hoverStyle = {
backColor: "#E8F7FF",
}
gradeView.addStyleRule("hover-row", hoverStyle, {
state: GC.Data.RowColumnStates.hover,
direction: GC.Data.StateRuleDirection.row
});
gradeView.fetch().then(function () {
studentsGradesSheet.setDataView(gradeView);
});
}
function initTemplateSheet (spread) {
var templateSheet = new GC.Spread.Sheets.Worksheet("TemplateSheet");
spread.addSheet(0, templateSheet);
var style = new GC.Spread.Sheets.Style();
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
templateSheet.setStyle(-1, 1, style);
templateSheet.suspendPaint();
templateSheet.setRowHeight(0, 50);
templateSheet.setColumnWidth(0, 60);
templateSheet.setColumnWidth(1, 100);
templateSheet.getCell(0,0).bindingPath("Image").formatter("=IMAGE(@)");
templateSheet.getCell(0,1).bindingPath("Name").font("bold 14px Arial");
templateSheet.options.gridline.showHorizontalGridline = false
templateSheet.options.gridline.showVerticalGridline = false
templateSheet.visible(false);
spread.setActiveSheetTab(0);
templateSheet.resumePaint();
return templateSheet;
}
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 prepareStudentsGradesData() {
var dataSource = {};
var students = [
{
"ID": 1,
"Name": "Ellen Robinson",
"Image": "$DEMOROOT$/spread/source/images/avatar/63.png",
},
{
"ID": 2,
"Name": "Jerry Williams",
"Image": "$DEMOROOT$/spread/source/images/avatar/91.png",
},
{
"ID": 3,
"Name": "Steven Kunes",
"Image": "$DEMOROOT$/spread/source/images/avatar/20.png",
},
{
"ID": 4,
"Name": "Lisa Williamsburg",
"Image": "$DEMOROOT$/spread/source/images/avatar/95.png",
},
{
"ID": 5,
"Name": "Donald Draglin",
"Image": "$DEMOROOT$/spread/source/images/avatar/58.png",
},
{
"ID": 6,
"Name": "Cathy Mahon",
"Image": "$DEMOROOT$/spread/source/images/avatar/12.png",
},
{
"ID": 7,
"Name": "Jerry Frizzell",
"Image": "$DEMOROOT$/spread/source/images/avatar/77.png",
},
{
"ID": 8,
"Name": "John Fair",
"Image": "$DEMOROOT$/spread/source/images/avatar/10.png",
},
{
"ID": 9,
"Name": "Jonathan Weiss",
"Image": "$DEMOROOT$/spread/source/images/avatar/60.png",
},
{
"ID": 10,
"Name": "Michael Peterson",
"Image": "$DEMOROOT$/spread/source/images/avatar/20.png",
},
{
"ID": 11,
"Name": "Rebecca Kemp",
"Image": "$DEMOROOT$/spread/source/images/avatar/95.png",
},
{
"ID": 12,
"Name": "Johnson Smith",
"Image": "$DEMOROOT$/spread/source/images/avatar/23.png",
}
];
var classes = [
{
"ID": 1,
"ClassName": "Test1",
"Type": "Tests"
},
{
"ID": 2,
"ClassName": "Test2",
"Type": "Tests"
},
{
"ID": 3,
"ClassName": "Test3",
"Type": "Tests"
},
{
"ID": 4,
"ClassName": "Project1",
"Type": "Projects"
},
{
"ID": 5,
"ClassName": "Project2",
"Type": "Projects"
},
{
"ID": 6,
"ClassName": "Homework1",
"Type": "Homeworks"
},
{
"ID": 7,
"ClassName": "Homework2",
"Type": "Homeworks"
},
{
"ID": 8,
"ClassName": "Homework3",
"Type": "Homeworks"
},
{
"ID": 9,
"ClassName": "Homework4",
"Type": "Homeworks"
}
];
var grades = [
{
"StudentID": 1,
"ClassID": 1,
"Grade": 95
},
{
"StudentID": 1,
"ClassID": 2,
"Grade": 68
},
{
"StudentID": 1,
"ClassID": 4,
"Grade": "C"
},
{
"StudentID": 1,
"ClassID": 6,
"Grade": 84
},
{
"StudentID": 1,
"ClassID": 7,
"Grade": 57
},
{
"StudentID": 1,
"ClassID": 8,
"Grade": 78
},
{
"StudentID": 1,
"ClassID": 9,
"Grade": 57
},
{
"StudentID": 2,
"ClassID": 1,
"Grade": 66
},
{
"StudentID": 2,
"ClassID": 2,
"Grade": 78
},
{
"StudentID": 2,
"ClassID": 4,
"Grade": "B"
},
{
"StudentID": 2,
"ClassID": 6,
"Grade": 90
},
{
"StudentID": 2,
"ClassID": 7,
"Grade": 69
},
{
"StudentID": 2,
"ClassID": 8,
"Grade": 63
},
{
"StudentID": 2,
"ClassID": 9,
"Grade": 69
},
{
"StudentID": 3,
"ClassID": 1,
"Grade": 78
},
{
"StudentID": 3,
"ClassID": 2,
"Grade": 78
},
{
"StudentID": 3,
"ClassID": 4,
"Grade": "A"
},
{
"StudentID": 3,
"ClassID": 6,
"Grade": 80
},
{
"StudentID": 3,
"ClassID": 7,
"Grade": 88
},
{
"StudentID": 3,
"ClassID": 8,
"Grade": 65
},
{
"StudentID": 3,
"ClassID": 9,
"Grade": 88
},
{
"StudentID": 4,
"ClassID": 1,
"Grade": 76
},
{
"StudentID": 4,
"ClassID": 2,
"Grade": 55
},
{
"StudentID": 4,
"ClassID": 4,
"Grade": "B"
},
{
"StudentID": 4,
"ClassID": 6,
"Grade": 80
},
{
"StudentID": 4,
"ClassID": 7,
"Grade": 78
},
{
"StudentID": 4,
"ClassID": 8,
"Grade": 66
},
{
"StudentID": 4,
"ClassID": 9,
"Grade": 78
},
{
"StudentID": 5,
"ClassID": 1,
"Grade": 86
},
{
"StudentID": 5,
"ClassID": 2,
"Grade": 63
},
{
"StudentID": 5,
"ClassID": 4,
"Grade": "C"
},
{
"StudentID": 5,
"ClassID": 6,
"Grade": 84
},
{
"StudentID": 5,
"ClassID": 7,
"Grade": 45
},
{
"StudentID": 5,
"ClassID": 8,
"Grade": 66
},
{
"StudentID": 5,
"ClassID": 9,
"Grade": 45
},
{
"StudentID": 6,
"ClassID": 1,
"Grade": 45
},
{
"StudentID": 6,
"ClassID": 2,
"Grade": 43
},
{
"StudentID": 6,
"ClassID": 4,
"Grade": "C"
},
{
"StudentID": 6,
"ClassID": 6,
"Grade": 70
},
{
"StudentID": 6,
"ClassID": 7,
"Grade": 55
},
{
"StudentID": 6,
"ClassID": 8,
"Grade": 67
},
{
"StudentID": 6,
"ClassID": 9,
"Grade": 55
},
{
"StudentID": 7,
"ClassID": 1,
"Grade": 29
},
{
"StudentID": 7,
"ClassID": 2,
"Grade": 45
},
{
"StudentID": 7,
"ClassID": 4,
"Grade": "B"
},
{
"StudentID": 7,
"ClassID": 6,
"Grade": 59
},
{
"StudentID": 7,
"ClassID": 7,
"Grade": 53
},
{
"StudentID": 7,
"ClassID": 8,
"Grade": 64
},
{
"StudentID": 7,
"ClassID": 9,
"Grade": 53
},
{
"StudentID": 8,
"ClassID": 1,
"Grade": 55
},
{
"StudentID": 8,
"ClassID": 2,
"Grade": 78
},
{
"StudentID": 8,
"ClassID": 4,
"Grade": "C"
},
{
"StudentID": 8,
"ClassID": 6,
"Grade": 70
},
{
"StudentID": 8,
"ClassID": 7,
"Grade": 72
},
{
"StudentID": 8,
"ClassID": 8,
"Grade": 63
},
{
"StudentID": 8,
"ClassID": 9,
"Grade": 72
},
{
"StudentID": 9,
"ClassID": 1,
"Grade": 94
},
{
"StudentID": 9,
"ClassID": 2,
"Grade": 45
},
{
"StudentID": 9,
"ClassID": 4,
"Grade": "A"
},
{
"StudentID": 9,
"ClassID": 6,
"Grade": 58
},
{
"StudentID": 9,
"ClassID": 7,
"Grade": 59
},
{
"StudentID": 9,
"ClassID": 8,
"Grade": 64
},
{
"StudentID": 9,
"ClassID": 9,
"Grade": 59
},
{
"StudentID": 10,
"ClassID": 1,
"Grade": 15
},
{
"StudentID": 10,
"ClassID": 2,
"Grade": 19
},
{
"StudentID": 10,
"ClassID": 4,
"Grade": "B"
},
{
"StudentID": 10,
"ClassID": 6,
"Grade": 70
},
{
"StudentID": 10,
"ClassID": 7,
"Grade": 73
},
{
"StudentID": 10,
"ClassID": 8,
"Grade": 55
},
{
"StudentID": 10,
"ClassID": 9,
"Grade": 73
},
{
"StudentID": 11,
"ClassID": 1,
"Grade": 36
},
{
"StudentID": 11,
"ClassID": 2,
"Grade": 45
},
{
"StudentID": 11,
"ClassID": 4,
"Grade": "E"
},
{
"StudentID": 11,
"ClassID": 6,
"Grade": 76
},
{
"StudentID": 11,
"ClassID": 7,
"Grade": 74
},
{
"StudentID": 11,
"ClassID": 8,
"Grade": 58
},
{
"StudentID": 11,
"ClassID": 9,
"Grade": 74
},
{
"StudentID": 12,
"ClassID": 1,
"Grade": 88
},
{
"StudentID": 12,
"ClassID": 2,
"Grade": 79
},
{
"StudentID": 12,
"ClassID": 4,
"Grade": "B"
},
{
"StudentID": 12,
"ClassID": 6,
"Grade": 68
},
{
"StudentID": 12,
"ClassID": 7,
"Grade": 78
},
{
"StudentID": 12,
"ClassID": 8,
"Grade": 80
},
{
"StudentID": 12,
"ClassID": 9,
"Grade": 79
},
];
dataSource.students = students;
dataSource.classes = classes;
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>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
