使用 addFromDataSource 方法可以直接添加绑定数据管理器表的数据表。例如：
也可以在添加表后使用 bind 方法进行绑定。例如：
如果不是一次性绑定所有字段，可以在表头输入字段名，或者在已有列上输入其他字段名以更改绑定字段。也可以在表头输入表结构引用公式来指定列数据公式。
如果表格已绑定数据管理器表，可以使用 getBindingSource 方法获取绑定的表。例如：
如果绑定的是 js 对象，也会返回绑定源。例如：
如果表格是普通表格（未绑定任何数据），可以使用 convertToDataTable 方法创建并绑定一个数据管理器表，数据和列公式来自表格。例如：
如果表格已绑定数据管理器表，可以使用 convertFromDataTable 方法解除绑定，数据和列公式会从数据管理器同步到表格。例如：
如果数据管理器表处于批处理模式，需要执行 tableSubmitChanges 命令保存更改，其他情况会自动保存：
如果想将所有工作表表格转换为绑定数据管理器的数据表，需要在 GC.Spread.Sheets.ImportXlsxOptions 中开启 convertSheetTableToDataTable，并在导出 Excel 文件时开启 GC.Spread.Sheets.ExportXlsxOptions 的 includeBindingSource。需要注意，基于列的公式在导入导出时会被转换，基于单元格的公式在导入时会被移除。
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\/SpreadJSTutorial\//)[0] + 'server/api';
}
<!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">
<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-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-datacharts-addon/dist/gc.spread.sheets.datacharts.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/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;
}
