Lookup Column

When a user marks a column as lookup in the data source schema, the column will be presented as a combo box cell type or a multi-column drop down list in the TableSheet.

When a column's lookup is an array in the data source schema, a combo box cell type is set to the column. This is the sample code of lookup array. When the lookup is a relationship name, a multi-column list will be set to the column after the View data is fetched. This is the sample code of lookup string. When the lookup is an option which contains a relationship name and some columns options, a multi-column list with specified columns will be set to the column. This is the sample code of lookup options.
/*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="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/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/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.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; }