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 band 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 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.
<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";
const GCsheets = GC.Spread.Sheets;
const spreadRef = ref(null);
const initSpread = (spread) => {
spreadRef.value = spread;
spread.options.allowDynamicArray = true;
initOverview(spread);
initDataManagerTables(spread);
}
const 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();
}
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 }
]
});
Promise.all([saleTable.fetch(), orderTable.fetch()]).then(() => {
addOverviewSalesFilter(spread);
});
}
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 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 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();
}
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\/demos\//)[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$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/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$/en/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-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);
