概述
本 Demo 展示了 TableSheet 数据源模式中查询列（Lookup）的三种设置方式。通过在数据表的 schema.columns 中配置 lookup 属性，可以让单元格自动呈现为组合框或多列下拉列表，实现从固定选项或关联表中选择数据的功能。
实现思路
创建数据管理器，添加三个数据表：Students（学生表）、WorkItems（作业项表）和 Grades（成绩表）
在 WorkItems 表的 schema 中，为 Type 列设置数组形式的 lookup，提供固定选项
在 Grades 表的 schema 中，为 StudentID 和 WorkItemID 列设置关系形式的 lookup，引用关联表数据
使用 addRelationship 方法在表之间建立关系
为每个表创建视图并绑定到 TableSheet，展示查询列的自动呈现效果
代码解析
设置数组形式的查询列
这段代码在 WorkItems 表的 schema 中为 Type 列设置了数组形式的 lookup。当 TableSheet 渲染该列时，会自动应用组合框单元格类型，下拉列表中显示数组中的三个选项：Homework、Quiz 和 Exam。
设置关系名称形式的查询列
这段代码为 Grades 表的 StudentID 列设置了关系名称形式的 lookup，值为 'student'（关系名称）。通过 addRelationship 方法建立了 Grades 表和 Students 表之间的关联关系后，该列会自动呈现为多列下拉列表，显示 Students 表的默认列数据。
设置选项形式的查询列
这段代码为 WorkItemID 列设置了选项形式的 lookup。lookup 对象包含两个属性：name 指定关系名称，columns 数组定义下拉列表中显示的列。每个列可以设置 value（字段名）、width（宽度）和 caption（标题）等属性。这样可以在下拉列表中自定义显示哪些列以及它们的样式。
创建视图并绑定到 TableSheet
这段代码创建了一个自定义视图，其中 student.Name 和 workItem.Description 引用了关联表的字段。查询列的设置会自动应用到对应的单元格，用户可以通过下拉列表选择数据。
运行效果
切换到 "WorkItems" 工作表，点击 Type 列的单元格，会显示组合框下拉列表，包含 Homework、Quiz、Exam 三个选项
切换到 "Grades" 工作表，点击 Student ID 列的单元格，会显示多列下拉列表，展示 Students 表的学生信息（默认列）
点击 WorkItem ID 列的单元格，会显示多列下拉列表，展示 WorkItems 表的 ID、Description、TotalPoints 和 Type 列，其中列宽和标题已按配置自定义
所有查询列都支持用户选择，无需手动编写单元格类型代码
API 参考
addTable 方法
name：数据表名称
options.data：数据数组
options.schema.columns：列定义对象，每个列可以设置 lookup 属性
lookup 属性类型
数组形式：lookup: ["value1", "value2", ...]，自动应用组合框单元格类型
关系名称：lookup: "relationName"，显示关联表的默认列
选项形式：lookup: { name: "relationName", columns: [...] }，自定义显示列
addRelationship 方法
sourceTable：源表对象
sourceFieldName：源表字段名
sourceRelationshipName：源表中的关系名称
targetTable：目标表对象
targetFieldName：目标表字段名
targetRelationshipName：目标表中的关系名称
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
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;
var dataManager = spread.dataManager();
// add tables
var studentTable = dataManager.addTable("Students", {
data: [
{
"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 workItemTable = dataManager.addTable("WorkItems", {
data: [
{
"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"
}
],
schema: {
columns: {
Date: { dataType: "date" },
Type: { lookup: ["Homework", "Quiz", "Exam"] }
}
}
});
var gradeTable = dataManager.addTable("Grades", {
data: [
{
"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
}
],
schema: {
columns: {
StudentID: {
lookup: 'student'
},
WorkItemID: {
// lookup: { name: 'workItem', columns: ["ID", "Description", "TotalPoints", "Type"]}
lookup: {
name: 'workItem', columns: [
{ value: "ID" },
{ value: "Description", width: 150 },
{ value: "TotalPoints", width: 100 },
{ value: "Type", width: 150, caption: "WorkItem Type" }
]
}
}
}
}
});
// add relationships
dataManager.addRelationship(gradeTable, "StudentID", "student", studentTable, "ID", "grades");
dataManager.addRelationship(gradeTable, "WorkItemID", "workItem", workItemTable, "ID", "grades");
// add views
studentTable.fetch().then(function () {
var studentView = studentTable.addView("gradeView", undefined, undefined, {
defaultColumnWidth: 120
});
var studentSheet = spread.addSheetTab(0, "Students", GC.Spread.Sheets.SheetType.tableSheet);
studentSheet.setDataView(studentView);
});
workItemTable.fetch().then(function () {
let workItemView = workItemTable.addView("gradeView2", undefined, undefined, {
defaultColumnWidth: 150
});
var workItemSheet = spread.addSheetTab(0, "WorkItems", GC.Spread.Sheets.SheetType.tableSheet);
workItemSheet.setDataView(workItemView);
});
gradeTable.fetch().then(function () {
let gradeView = gradeTable.addView("gradesView", [
{ caption: "Student ID", value: "StudentID" },
{ caption: "Student Name", value: "student.Name", width: 140 },
{ caption: "WorkItem ID", value: "WorkItemID" },
{ caption: "WorkItem Description", value: "workItem.Description", width: 180 },
{ caption: "Grade", value: "Grade" }
], undefined, {
defaultColumnWidth: 120
});
var gradeSheet = spread.addSheetTab(0, "Grades", GC.Spread.Sheets.SheetType.tableSheet);
gradeSheet.setDataView(gradeView);
});
spread.resumePaint();
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<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/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="optionContainer" class="optionContainer">
</div>
</div>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
margin: 0;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
