次のサンプルコードは、addFromDataSourceメソッドを使用して、テーブルをデータマネージャーのテーブルにバインドする方法を示します。
また、テーブルを追加した後にbindメソッドを使用することもできます。以下はサンプルコードです。
一度にすべてのフィールドをテーブルにバインドしない場合、ヘッダーにフィールド名を入力するか、既存列に別のフィールド名を入力してバインドされるフィールドを変更することができます。また、ヘッダーにテーブルの構造化参照式を入力して列の数式を指定することも可能です。
シートのテーブルがデータマネージャーのテーブルにバインドされている場合、getBindingSource メソッドを使用してバインドされたテーブルを取得できます。
例：
JavaScriptオブジェクトをバインドした場合は、バインドしたデータソースが返されます。
例：
シートテーブルが何もバインドされていない場合、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\/demos\//)[0] + 'server/api';
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="ja-jp"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-datacharts-addon/dist/gc.spread.sheets.datacharts.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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;
}
