addFromDataSource 메서드를 사용하여 데이터 관리자 표에 직접 바인딩하는 시트 표를 추가합니다. 예:
또는 표를 추가한 후에 bind 메서드를 사용할 수도 있습니다. 예:
모든 필드가 표에 한 번에 헤더에 필드 이름을 입력하거나 이미 있는 열에 다른 필드 이름을 입력하여 바인딩 필드를 변경할 수 있습니다. 또한 헤더에 표 구조 참조 수식을 입력해서 열 데이터 수식을 지정할 수 있습니다.
데이터 관리자 표가 일괄 모드에 있는 경우 tableSubmitChanges 명령을 실행하여 변경 사항을 저장하며, 다른 항목은 자동으로 저장됩니다.
모든 워크시트 표를 데이터 관리자에 바인딩된 데이터 표로 변환하려는 경우에는 GC.Spread.Sheets.ImportXlsxOptions의 convertSheetTableToDataTable을 설정하고, GC.Spread.Sheets.ExportXlsxOptions의 includeBindingSource가 켜진 상태에서 Excel 파일로 내보내야 합니다. 참고로 열 기반 수식은 가져오거나 내보낼 때 변환되며, 셀 기반 수식은 가져올 때 제거됩니다.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue';
import { ref } from "vue";
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-slicers";
import '@mescius/spread-sheets-resources-ko';
GC.Spread.Common.CultureManager.culture("ko-kr");
const GCsheets = GC.Spread.Sheets;
const spreadRef = ref(null);
const initSpread = (spread) => {
spreadRef.value = spread;
spread.options.allowDynamicArray = true;
initDataManagerTables(spread).then(() => {
initOverview(spread);
});
}
const initDataManagerTables = (spread) => {
var dataManager = spread.dataManager();
var saleTable = dataManager.addTable("sales", {
data: saleData
});
var orderTable = dataManager.addTable("order", {
data: [
{ orderDate: '1/6/2013', item: 'Pencil', units: 1195, cost: 121.99, isDelivered: true },
{ orderDate: '4/1/2013', item: 'Binder', units: 260, cost: 14.99, isDelivered: false },
{ orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 5.99, isDelivered: false }
]
});
return Promise.race([saleTable.fetch(), orderTable.fetch()]);
}
const initOverview = (spread) => {
let sheet = spread.getActiveSheet();
sheet.suspendPaint();
let tableStyle = initOverviewDisplay(spread);
let table = sheet.tables.addFromDataSource('sales', 12, 1, 'sales', tableStyle);
initOverviewDetails(spread, sheet, table);
sheet.resumePaint();
}
const initDetails = (spread) => {
var sheet = spread.getActiveSheet();
sheet.suspendPaint();
// bind a data manager table to the sheet table when adding a table to sheet
var table = sheet.tables.addFromDataSource('tableSales', 5, 0, "order", GC.Spread.Sheets.Tables.TableThemes.medium4);
// reset the table column definitions
table.bindColumns(getColumns());
prepareAddBindField(sheet, table);
registerCommand(spread);
sheet.resumePaint();
}
const registerCommand = (spread) => {
var command = {
canUndo: false,
execute: function (context) {
let sheet = context.getActiveSheet();
return context.commandManager().execute({ cmd: "tableSubmitChanges", sheetName: sheet.name(), tableNames: sheet.tables.all().map(t => t.name()) });
}
};
var commandManager = spread.commandManager();
let isMac = navigator.platform && navigator.platform.indexOf('Mac') > -1, ctrl = !isMac, meta = isMac;
commandManager.register("saveAllTableChanges", command, 'S'.charCodeAt(0), ctrl, true, false, meta);
}
const initOverviewDisplay = (spread) => {
let sheet = spread.getActiveSheet();
sheet.setColumnWidth(0, 15);
sheet.name('Overview');
sheet.options.gridline.showHorizontalGridline = false;
sheet.options.gridline.showVerticalGridline = false;
sheet.setActiveCell(14, 1)
for (let i = 0; i < 7; i++) {
sheet.setColumnWidth(1 + i, 120);
}
var tableStyle = new GC.Spread.Sheets.Tables.TableTheme();
var tableStyleInfo = new GC.Spread.Sheets.Tables.TableStyle(
"#6e8ec2",
"#fff");
tableStyle.headerRowStyle(tableStyleInfo);
var tableStyleInfo = new GC.Spread.Sheets.Tables.TableStyle(
"#f6f9ff",
"#323232");
tableStyle.wholeTableStyle(tableStyleInfo);
return tableStyle;
}
const initOverviewDetails = (spread, sheet, table) => {
var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.numberCondition, { compareType: GC.Spread.Sheets.ConditionalFormatting.GeneralComparisonOperators.equalsTo, expected: '2021' });
var tableRowFilter = table.rowFilter();
tableRowFilter.addFilterItem(1, condition);
tableRowFilter.filter(1);
var slicer1 = sheet.slicers.add("slicer2", "sales", "Product");
slicer1.position(new GC.Spread.Sheets.Point(880, 240));
slicer1.multiSelect(true);
var slicer2 = sheet.slicers.add("slicer1", "sales", "Year");
slicer2.position(new GC.Spread.Sheets.Point(1080, 240));
sheet.setFormula(4, 1, '=GROUPBY(sales[[#Headers], [#Data], [Region]],sales[[#Headers], [#Data], [Sales]:[Return]],SUM,3,0)');
sheet.getRange(4, 1, 1, 3).setStyle(new GC.Spread.Sheets.Style("#6e8ec2", "#fff"));
sheet.setFormula(4, 5, '=GROUPBY(sales[[#Headers], [#Data], [Product]],sales[[#Headers], [#Data], [Sales]:[Return]],SUM,3,0)');
sheet.getRange(4, 5, 1, 3).setStyle(new GC.Spread.Sheets.Style("#6e8ec2", "#fff"));
sheet.setText(1, 1, 'Switch summarizing function within GROUPBY:');
let switchSummarizeFunctionStyle = new GC.Spread.Sheets.Style();
switchSummarizeFunctionStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
command: "openList",
}
];
switchSummarizeFunctionStyle.dropDowns = [
{
type: GC.Spread.Sheets.DropDownType.list,
option: {
items: [
{
text: 'SUM',
value: 'SUM'
},
{
text: 'AVERAGE',
value: 'AVERAGE'
},
{
text: 'MAX',
value: 'MAX'
},
{
text: 'MIN',
value: 'MIN'
},
{
text: 'MEDIAN',
value: 'MEDIAN'
},
],
}
}
];
sheet.setStyle(2, 1, switchSummarizeFunctionStyle);
sheet.setText(2, 1, 'SUM');
sheet.bind(GC.Spread.Sheets.Events.ValueChanged, function (sender, args) {
if (args.row === 2 && args.col === 1) {
let functionName = args.newValue;
sheet.setFormula(4, 1, `=GROUPBY(sales[[#Headers], [#Data], [Region]],sales[[#Headers], [#Data], [Sales]:[Return]],${functionName},3,0)`);
sheet.setFormula(4, 5, `=GROUPBY(sales[[#Headers], [#Data], [Product]],sales[[#Headers], [#Data], [Sales]:[Return]],${functionName},3,0)`);
}
});
spread.addSheet();
spread.getSheet(1).name('Details');
spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanged, function (sender, args) {
if (sheet.name() === args.newSheet.name()) {
args.oldSheet.tables.remove('tableSales');
args.oldSheet.clear(0, 0, args.oldSheet.getRowCount(), args.oldSheet.getColumnCount(), GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data | GC.Spread.Sheets.StorageType.style);
args.oldSheet.unbind(GC.Spread.Sheets.Events.ValueChanged);
} else {
initDetails(spread);
}
});
}
const prepareAddBindField = (sheet, table) => {
sheet.setColumnWidth(0, 100);
table.deleteColumns(3, 1);
sheet.setActiveCell(5, 4);
// It could add a bind field to the table if not all fields band at once
sheet.startEdit(false, 'cost');
sheet.setText(5, 5, ' <---Add binding field to the table by end edit');
sheet.bind(GC.Spread.Sheets.Events.EditEnded, function (e, args) {
if (args.col === 4) {
sheet.setText(5, 5, null);
sheet.setText(5, 6, ' <---Enter =[@Units]*[@cost] to the table by end edit');
setTimeout(() => {
sheet.setActiveCell(5, 5);
sheet.startEdit();
});
} else if (args.col === 5) {
sheet.setText(5, 6, null);
sheet.unbind(GC.Spread.Sheets.Events.EditEnded);
}
});
}
const getColumns = () => {
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "yyyy-mm-dd");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(3, "units", "Units", "#,##0");
var tableColumn4 = new GC.Spread.Sheets.Tables.TableColumn(4, "cost", "Cost");
var tableColumn5 = new GC.Spread.Sheets.Tables.TableColumn(5, "isDelivered", "Delivered", null, new GC.Spread.Sheets.CellTypes.CheckBox());
return [tableColumn1, tableColumn2, tableColumn3, tableColumn4, tableColumn5];
}
const getBaseApiUrl = () => {
return window.location.href.match(/http.+spreadjs\/learn-spreadjs\//)[0] + 'server/api';
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html lang="en" style="height:100%;font-size:14px;" >
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/ko/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="$DEMOROOT$/spread/source/data/table-dm/sales.js" type="text/javascript"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/ko/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.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",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-ko': 'npm:@mescius/spread-sheets-resources-ko/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-slicers': 'npm:@mescius/spread-sheets-slicers/index.js',
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
