테이블 시트에는 교차 열 설정을 지원하는 대화형 테이블 시트 패널이 있습니다.
다음은 테이블 시트 패널을 사용하여 교차 열을 추가/업데이트/제거하는 방법을 보여 주는 몇 가지 단계입니다.
테이블 시트 패널을 추가합니다.
데이터 소스 스키마에서 열 옵션(조회 속성을 설정해야 함)으로 표를 여러 개 추가합니다.
표의 기본 키를 지정합니다.
표 간의 관계를 추가합니다.
열 옵션을 사용하여 조회 필드가 있는 사용자 정의 보기를 추가합니다.
조회 필드를 클릭하고 다른 필드를 교차 영역으로 끌면 새 교차 열이 추가됩니다.
기존 교차 필드를 클릭하여 업데이트하거나 제거합니다.
테이블 시트 패널 만들기:
다음 샘플 코드에서는 조회 필드가 관계의 표 이름임을 보여 줍니다.
다음 샘플 코드에서는 조회 필드가 배열의 값임을 보여 줍니다.
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<div id="panel" class="container">
</div>
</template>
<script>
import Vue from "vue";
import "@mescius/spread-sheets-vue";
import GC from "@mescius/spread-sheets";
import '@mescius/spread-sheets-resources-ko';
GC.Spread.Common.CultureManager.culture("ko-kr");
import "@mescius/spread-sheets-tablesheet";
import "./styles.css";
var baseApiUrl = getBaseApiUrl();
var baseTableName = "Cross_Column_";
let App = Vue.extend({
spread: GC.Spread.Sheets.Workbook,
name: "app",
methods: {
initSpread: function (spread) {
this.spread = spread;
spread.suspendPaint();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.clearSheets();
//init a data manager
var dataManager = spread.dataManager();
var 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\/learn-spreadjs\//)[0] + 'server/api';
}
new Vue({
render: (h) => h(App),
}).$mount("#app");
</script>
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ko-kr" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/orderDataSource.js" type="text/javascript"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/ko/vue/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('./src/app.vue');
System.import('$DEMOROOT$/ko/lib/vue/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</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;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
meta: {
'*.css': { loader: 'css' },
'*.vue': { loader: 'vue-loader' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-resources-ko': 'npm:@mescius/spread-sheets-resources-ko/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'jszip': 'npm:jszip/dist/jszip.js',
'css': 'npm:systemjs-plugin-css/css.js',
'vue': 'npm:vue/dist/vue.min.js',
'vue-loader': 'npm:systemjs-vue-browser/index.js',
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'js'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
}
}
});
})(this);
