概述
本 Demo 展示了如何使用集算表的交互式面板创建和管理交叉列。Demo 中包含两个示例：Grade Book 展示了基于表关系的交叉列（使用 FILTER 公式过滤数据），Payments Ledger 展示了基于数组值的交叉列配置。用户可以通过面板的可视化界面快速创建和编辑交叉列。
实现思路
启用 allowDynamicArray 选项以支持 FILTER 公式
创建数据表并设置主键
在数据源架构中为相关字段设置 lookup 属性
使用 addRelationship 方法建立表之间的关系
通过 addView 方法创建视图，配置交叉列的 cross 属性
初始化 TableSheetPanel 并绑定到集算表
监听工作表切换事件，动态切换面板关联
代码解析
创建集算表面板
这段代码创建了集算表面板实例。showSource 选项设置为 all，表示在面板中显示所有列的值来源（包括自己的字段、相关字段和计算字段）。
设置查找字段为关系名称
在 Grades 表的架构中，将 WorkItemID 字段的 lookup 属性设置为关系名称 "workItem"，表示该字段可以引用 WorkItems 表的数据，从而支持交叉列功能。
建立表之间的关系
使用 addRelationship 方法建立表之间的双向关系。参数依次为：源表、源字段名、源关系名、目标表、目标字段名、目标关系名。建立关系后，可以在视图中引用相关表的字段。
配置交叉列（基于关系）
在视图配置中使用 cross 属性创建交叉列。over 指定交叉的基准字段（必须是 lookup 字段），attributes 定义交叉列的多级表头，filter 使用 FILTER 公式过滤要显示的交叉列。FILTER 的第一个参数必须是主键，第二个参数是过滤条件。
配置交叉列（基于数组值）
当 lookup 字段的值为数组时，可以直接将该字段作为交叉列的基准。caption 属性设置交叉列的顶级表头。
工作表切换时更新面板
监听工作表切换事件，调用 detach() 解除当前面板关联，然后调用 attach() 绑定到新的活动工作表，确保面板始终显示当前工作表的字段信息。
运行效果
页面左侧显示集算表数据，右侧显示交叉列面板
在 Grade Book 工作表中，交叉列显示各个工作项的成绩，表头包含工作项类型、总分和日期
在 Payments Ledger 工作表中，交叉列按支付方式（Cash、Check、ACH、CC）分组显示金额
点击面板中的查找字段，可以查看和编辑交叉列配置
切换工作表时，面板自动更新为当前工作表的字段信息
所有交互都通过面板的可视化界面完成，无需编写代码
API 参考
TableSheetPanel 构造函数
name：面板名称
tableSheet：要绑定的集算表实例
host：面板的 HTML 容器元素
options.showSource：控制如何显示列值来源，可选值为 none、field、formula、all
addRelationship 方法
sourceTable：源表实例
sourceFieldName：源表中的字段名
sourceRelationshipName：源表中使用的关系名称
targetTable：目标表实例
targetFieldName：目标表中的字段名
targetRelationshipName：目标表中使用的关系名称
cross 属性配置
/*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\/SpreadJSTutorial\//)[0] + 'server/api';
}
<!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-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/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;
}
