Use the addFromDataSource method to add a sheet table which binds a data manager table directly. For example:
Or it could use the bind method after a table added. For example:
If not all fields bound to the table at once, it could enter the field name on the header, or it could enter another field name on the exist column to change binding field. And it could enter a table structure reference formula on the header to specify the column data formula.
If the sheet table has bound to a data manager table, it could use the getBindingSource method to get the binding table. For example:
If binding a js object, it will return the binding source too. For example:
If the sheet table is a regular table which doesn't bind anything, it could use the convertToDataTable method to create and bind a data manager table whose values and column formulas from the sheet table. For example:
If the sheet table has bound to a data manager table, it could use the convertFromDataTable method to unbind the data manager table which will set the values and column formulas from the data manager to the sheet table. For example:
If the data manager table be in the batch mode, it should execute the tableSubmitChanges Command to save the changes, the others will save automatically:
If want to convert all worksheet tables to data tables which bind to the data manager, it should turn on the convertSheetTableToDataTable of the GC.Spread.Sheets.ImportXlsxOptions, and export to Excel file with includeBindingSource of the GC.Spread.Sheets.ExportXlsxOptions turned on. It should be known that the column based formulas will be converted when import or export, the cell based formulas will be removed when import.
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);
// set value to the table
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 bound 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\/demos\//)[0] + 'server/api';
}
<!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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-datacharts-addon/dist/gc.spread.sheets.datacharts.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;
}
