テーブルシートは、組み込みのコマンドを変更することで、DOMベースの列タイプのUIを定義したり、特別な列タイプを定義したり、また組み込みの列タイプの定義を変更したりすることができます。
defineColumnCommandは、デフォルトコマンドを置き換えるコマンドです。このコマンドは、列の追加ボタンをクリックするときや、列のコンテキストメニューの項目を変更する際に定義された列の定義ダイアログを開きます。
submitDefineColumnCommandは、定義された列のオプションを送信するためのコマンドです。このコマンドは、列の定義ダイアログで保存ボタンをクリックしたときに実行されます。
特別な列のタイプを追加することや、組み込みの列タイプを更新することもできます。
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
initSpread(spread);
};
function customizingSpecifiedColumnTypes(spread, tableSheet) {
// 指定した列タイプを追加したり、組み込みの列タイプを更新したりすることができます
const items = tableSheet.options.columnTypeItems.filter((item) => item.name === 'Select' || item.name === 'Attachment' || item.name === 'Barcode' || item.name === 'CreatedTime' || item.name === 'ModifiedTime' || item.name === 'Currency');
tableSheet.options.columnTypeItems = tableSheet.options.columnTypeItems.slice(0, 4).concat(items);
tableSheet.options.columnTypeItems.unshift({ name: 'Progress', text: 'Progress', iconClass: 'gc-defined-column-type-icon-number' });
// 定義された列オプションの送信を処理します
tableSheet.options.submitDefineColumnCommand = 'submitDefineColumnCustom';
spread.commandManager().register('submitDefineColumnCustom', {
canUndo: false,
execute: function (context, options) {
if (options.command === 'DefineColumn' && options.column.type === 'Progress') {
options.column.defaultValue = 0.1;
options.column.style = {
formatter: "=HBARSPARKLINE([@" + options.column.value + "], \"#66B032\")"
};
}
options.cmd = options.command;
spread.commandManager().execute(options);
}
});
// 必要に応じてカスタムUIを提供する場合、列定義のダイアログを開く処理を行います
// それ以外の場合はこのコマンドが無視されます
const defineCommand = tableSheet.options.defineColumnCommand;
tableSheet.options.defineColumnCommand = 'defineColumnCustom';
spread.commandManager().register('defineColumnCustom', {
canUndo: false,
execute: function (context, options) {
// 列を変更または追加する際にカスタムUIを構築できます
// デフォルトUIを開きます
options.cmd = defineCommand;
spread.commandManager().execute(options);
}
});
}
function initSpread(spread) {
spread.suspendPaint();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.clearSheets();
var dataManager = spread.dataManager();
initDefineOrderTable(spread, dataManager);
spread.resumePaint();
}
function initDefineOrderTable(spread, dataManager) {
spread.options.allowDynamicArray = true;
var apiUrl = getApiUrl("DefineOrder"), apiColumnUrl = getColumnApiUrl("DefineOrder");
var orderTable = dataManager.addTable("orderTable", {
remote: {
read: {
url: apiUrl,
},
update: {
url: apiUrl,
method: 'PUT'
},
create: {
url: apiUrl,
},
delete: {
url: apiUrl,
},
getColumns: {
url: apiColumnUrl
},
addColumn: {
url: apiColumnUrl,
method: 'POST'
},
updateColumn: {
url: apiColumnUrl,
method: 'PUT'
},
removeColumn: {
url: apiColumnUrl,
method: 'DELETE'
},
// batch: {
// url: apiUrl + 'Collection'
// }
},
schema: {
columns: {
Id: { isPrimaryKey: true },
OrderDate: { dataType: 'date' },
RequiredDate: { dataType: 'date' },
ShippedDate: { dataType: 'date' },
ShipVia: {
dataType: 'number',
type: 'Select',
style: {
cellType: {
type: 'combobox',
editorValueType: 'value',
items: [
{ text: 'Speedy Express', value: 1 },
{ text: 'United Package', value: 2 },
{ text: 'Federal Shipping', value: 3 }
]
}
},
},
Freight: {
dataType: 'number',
type: 'Currency',
style: {
formatter: '[$$-409]#,##0.00'
},
},
CreatedTime: {
type: "CreatedTime",
dataType: 'date',
trigger: {
when: "onNew",
formula: "=NOW()"
},
readonly: true,
defaultValue: '=NOW()',
style: {
formatter: "m/d/yy h:mm;@"
},
},
ModifiedTime: {
type: "ModifiedTime",
dataType: 'date',
trigger: {
when: "onNewAndUpdate",
formula: "=NOW()",
fields: "*"
},
readonly: true,
style: {
formatter: "[$-409]m/d/yy h:mm AM/PM;@"
},
},
OrderAttachment: {
type: 'Attachment',
style: {
cellType: {
type: 'fileUpload'
}
},
},
OrderCode: {
type: 'Barcode',
defaultValue: '=FLOOR.MATH(RAND()*100000000)',
style: {
formatter: '=BC_GS1_128([@OrderCode],"#000000","#FFFFFF",false,,,,,,,,,,,)'
},
},
}
},
autoSync: true,
// batch: true,
});
var employeeTable = dataManager.addTable("employeeTable", {
remote: {
read: {
url: getApiUrl("Employee")
}
},
schema: {
columns: {
Id: { isPrimaryKey: true },
BirthDate: { dataType: 'date' },
HireDate: { dataType: 'date' },
}
},
autoSync: true
});
var customerTable = dataManager.addTable("customerTable", {
remote: {
read: {
url: getApiUrl("Customer")
}
},
schema: {
columns: {
Id: { isPrimaryKey: true },
}
},
autoSync: true
});
dataManager.addRelationship(orderTable, "EmployeeId", "employee", employeeTable, "Id", "orders");
dataManager.addRelationship(orderTable, "CustomerId", "customer", customerTable, "Id", "orders");
var tableSheet = spread.addSheetTab(0, "Orders", GC.Spread.Sheets.SheetType.tableSheet);
tableSheet.options.allowAddNew = true;
tableSheet.options.enableDefineColumn = true;
customizingSpecifiedColumnTypes(spread, tableSheet);
var rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions;
var options = tableSheet.rowActionOptions();
options.push(
rowActions.removeRow,
rowActions.saveRow,
rowActions.resetRow,
);
tableSheet.rowActionOptions(options);
var orderView = orderTable.addView("orderView", [
{ value: "Id", width: 50 },
{ value: "CustomerId", defaultValue: 'ALFKI', visible: false },
{ value: "EmployeeId", defaultValue: 1, visible: false },
{ value: "customer.ContactName", caption: 'Contact Name' },
{ value: '=CONCAT([@employee.FirstName], " ", [@employee.LastName])', caption: 'Employee Name', width: 160 },
{ value: "OrderDate", width: 160, style: { formatter: "m/d/yyyy" } },
{ value: "ShipVia", width: 140 },
{ value: "Freight", width: 80, defaultValue: 1 },
{ value: "ShipName" },
{ value: '=CONCAT([@ShipState], ", ", [@ShipCity], ", ", [@ShipRegion])', caption: 'Ship State', visible: false },
{ value: 'OrderAttachment', caption: 'Attachment' },
{ value: 'OrderCode', caption: 'Logistic Code', width: 160 },
{ value: 'CreatedTime', caption: 'Created Time', width: 160, visible: false },
{ value: 'ModifiedTime', caption: 'Modified Time', width: 160, visible: false },
], null, { defaultColumnWidth: 120 });
orderView.fetch().then(function () {
tableSheet.setDataView(orderView);
});
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
function getApiUrl(tableName) {
return getBaseApiUrl() + "/" + tableName;
}
function getColumnApiUrl(tableName) {
return getBaseApiUrl() + "/tables/" + tableName + "/columns";
}
<!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" />
<meta name="spreadjs culture" content="ja-jp" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill -->
<script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
<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-barcode/dist/gc.spread.sheets.barcode.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.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/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>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
