addFromDataSource 메서드를 사용하여 데이터 관리자 표에 직접 바인딩하는 시트 표를 추가합니다. 예:
또는 표를 추가한 후에 bind 메서드를 사용할 수도 있습니다. 예:
모든 필드가 표에 한 번에 헤더에 필드 이름을 입력하거나 이미 있는 열에 다른 필드 이름을 입력하여 바인딩 필드를 변경할 수 있습니다. 또한 헤더에 표 구조 참조 수식을 입력해서 열 데이터 수식을 지정할 수 있습니다.
「sheet table」이 「data manager table」에 바인딩되어 있는 경우, getBindingSource 메서드를 사용하여 바인딩된 테이블을 가져올 수 있습니다. 예를 들어:
js 객체를 바인딩하는 경우에도 바인딩 소스를 반환합니다. 예를 들어:
「sheet table」이 어떤 것도 바인딩하지 않은 일반 테이블인 경우, convertToDataTable 메서드를 사용하여 「sheet table」의 값과 열 수식을 가지는 「data manager table」을 생성하고 바인딩할 수 있습니다. 예를 들어:
「sheet table」이 「data manager table」에 바인딩되어 있는 경우, convertFromDataTable 메서드를 사용하여 「data manager table」의 바인딩을 해제할 수 있으며, 이때 「data manager」의 값과 열 수식이 「sheet table」에 설정됩니다. 예를 들어:
데이터 관리자 표가 일괄 모드에 있는 경우 tableSubmitChanges 명령을 실행하여 변경 사항을 저장하며, 다른 항목은 자동으로 저장됩니다.
모든 워크시트 표를 데이터 관리자에 바인딩된 데이터 표로 변환하려는 경우에는 GC.Spread.Sheets.ImportXlsxOptions의 convertSheetTableToDataTable을 설정하고, GC.Spread.Sheets.ExportXlsxOptions의 includeBindingSource가 켜진 상태에서 Excel 파일로 내보내야 합니다. 참고로 열 기반 수식은 가져오거나 내보낼 때 변환되며, 셀 기반 수식은 가져올 때 제거됩니다.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.fromJSON(tableBindingBaseSpreadJson);
initSpread(spread);
};
function initSpread(spread) {
spread.options.allowDynamicArray = true;
initOverview(spread);
initConverting(spread);
initDataManagerTables(spread);
}
function initOverview(spread) {
let sheet = spread.getActiveSheet();
sheet.suspendPaint();
let tableStyle = initOverviewDisplay(spread);
let table = sheet.tables.add('sales', 12, 1, 1, 7, tableStyle);
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "Year", "Year");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "Region", "Region");
var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(3, "Salesperson", "Salesperson");
var tableColumn4 = new GC.Spread.Sheets.Tables.TableColumn(4, "ProductCategory", "ProductCategory");
var tableColumn5 = new GC.Spread.Sheets.Tables.TableColumn(5, "Product", "Product");
var tableColumn6 = new GC.Spread.Sheets.Tables.TableColumn(6, "Sales", "Sales");
var tableColumn7 = new GC.Spread.Sheets.Tables.TableColumn(7, "Return", "Return");
table.bind([tableColumn1, tableColumn2, tableColumn3, tableColumn4, tableColumn5, tableColumn6, tableColumn7], undefined, 'sales');
initOverviewDetails(spread, sheet, table);
sheet.resumePaint();
}
function 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 }
]
});
Promise.all([saleTable.fetch(), orderTable.fetch()]).then(() => {
addOverviewSalesFilter(spread);
});
}
function 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();
}
function 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);
}
function convertBetweenSheetTableAndDataTable(spread, sheet) {
sheet.suspendPaint();
let table = sheet.tables.findByName('SalesTable');
if (table.getBindingSource()) {
sheet.tables.convertFromDataTable('SalesTable');
spread.dataManager().removeTable('SalesTable');
} else {
sheet.tables.convertToDataTable('SalesTable');
}
let bindingSource = table.getBindingSource();
sheet.setText(2, 8, 'Binding Table: ' + (bindingSource ? bindingSource.name : '(Blank)'));
sheet.getCell(1, 8).foreColor(bindingSource ? '#fff' : '#323232');
sheet.resumePaint();
}
function initConverting(spread) {
var sheet = spread.getSheetFromName('Convert Table and Data Table');
sheet.suspendPaint();
sheet.options.gridline.showHorizontalGridline = false;
sheet.options.gridline.showVerticalGridline = false;
spread.bind(GC.Spread.Sheets.Events.ButtonClicked, function (e, args) {
var sheet1 = args.sheet, row = args.row, col = args.col;
var cellType = sheet1.getCellType(row, col);
if (cellType instanceof GC.Spread.Sheets.CellTypes.CheckBox && sheet1.name() === sheet.name()) {
convertBetweenSheetTableAndDataTable(spread, sheet)
}
});
var convertingButtonCellType = new GC.Spread.Sheets.CellTypes.CheckBox();
convertingButtonCellType.mode('toggle');
convertingButtonCellType.caption("Inside");
convertingButtonCellType.textTrue('Convert from Data Table');
convertingButtonCellType.textFalse('Convert to Data Table')
convertingButtonCellType.textAlign(GC.Spread.Sheets.CellTypes.CheckBoxTextAlign.inside);
convertingButtonCellType.toggleOptions({
trackColorOn: '#6e8ec2',
trackColorOff: '#bfbfbf',
sliderColorOn: '#ffffff',
sliderColorOff: '#ffffff',
animationDuration: 400,
autoSize: true
});
sheet.setCellType(1, 8, convertingButtonCellType);
sheet.setValue(1, 8, 1);
sheet.setText(2, 8, 'Binding Table: (Blank)');
sheet.setSelection(1, 8, 1, 1);
sheet.setColumnWidth(8, 200);
sheet.setColumnWidth(1, 100);
sheet.setColumnWidth(2, 100);
sheet.setColumnWidth(3, 120);
sheet.setColumnWidth(4, 80);
sheet.setRowHeight(1,38)
sheet.setRowHeight(2,28)
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);
var table = sheet.tables.add('SalesTable', 16, 0, 91, 7, tableStyle);
sheet.setArray(16, 0, getValueForSheetTable());
convertBetweenSheetTableAndDataTable(spread, sheet);
sheet.resumePaint();
}
function getValueForSheetTable() {
let columns = Object.keys(saleData[0]);
let values = [columns];
for (const saleItem of saleData) {
values.push(columns.map((column) => saleItem[column]));
}
return values;
}
function 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;
}
function initOverviewDetails(spread, sheet, table) {
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.getSheet(1).name('Enter Field and Formula');
spread.getSheet(2).name('Convert Table and Data Table');
spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanged, function (sender, args) {
if (sheet.name() === args.newSheet.name() || 'Convert Table and Data Table' === args.newSheet.name()) {
if ('Enter Field and Formula' === args.oldSheet.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 if('Enter Field and Formula' === args.newSheet.name()) {
initDetails(spread);
}
});
}
function addOverviewSalesFilter(spread) {
let sheet = spread.getActiveSheet();
sheet.suspendPaint();
let table = sheet.tables.findByName('sales');
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);
sheet.resumePaint();
}
function 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);
}
});
}
function 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];
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/learn-spreadjs\//)[0] + 'server/api';
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="ko-kr"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-datacharts-addon/dist/gc.spread.sheets.datacharts.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/table-dm/sales.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/table-dm/table-binding-base-spread-json.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>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width:100%;
height: 100%;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
