소개
명명된 셀 템플릿 기능을 사용하면 셀이나 범위에 적용할 수 있는 재사용 가능한 셀 구성 템플릿을 정의할 수 있습니다. 각 템플릿에는 스타일, 조건부 서식, 데이터 유효성 검사, 셀 상태가 포함될 수 있습니다.
통합 문서 인스턴스를 통해 명명된 셀 템플릿 관리자를 사용할 수 있습니다.
템플릿 추가
add(name, template)를 사용하여 새 템플릿을 등록합니다.
셀에서 템플릿 만들기
createFromCell(name, sheet, row, col, options)를 사용하여 기존 셀의 구성에서 템플릿을 만듭니다.
템플릿 적용
sheet.applyNamedCellTemplate(name, address)를 사용하여 범위에 템플릿을 적용합니다.
또는 cellRange.applyNamedCellTemplate(name)을 사용할 수 있습니다.
템플릿 관리
템플릿 구조
템플릿 개체는 INamedCellTemplate 인터페이스를 따릅니다.
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
};
var NO_TEMPLATE_MESSAGE = "No built-in templates are currently registered.";
var WORKFLOW_STATUS = {
completed: "Completed",
inProgress: "In Progress",
notStarted: "Not Started",
blocked: "Blocked"
};
var PRIORITY_LEVEL = {
high: "High",
medium: "Medium",
low: "Low"
};
var YES_NO = {
yes: "Yes",
no: "No"
};
var CARD_DATA_ROW_COUNT = 10;
var CARD_SLOT_COLUMNS = 2;
var CARD_VISIBLE_COLUMNS = 1;
var CARD_SPACER_WIDTH = 8;
var CARDS_PER_ROW = 16;
var WIDE_TEMPLATE_COLS = 3;
var WIDE_TEMPLATE_SLOT_COLUMNS = WIDE_TEMPLATE_COLS + 1;
var WIDE_TEMPLATES = ["Active Row", "Hover Row", "Crosshair"];
var TOTAL_GALLERY_COLUMNS = 20;
var TOTAL_GALLERY_SPAN_COLUMNS = TOTAL_GALLERY_COLUMNS - 1;
var CARD_TITLE_ROW_HEIGHT = 24;
var CARD_DATA_ROW_HEIGHT = 22;
var CARD_ROW_HEIGHT = 13;
var GALLERY_ROW_COUNT = 120;
var GALLERY_GROUPS = createGalleryGroups();
var BUILT_IN_TEMPLATE_DEFINITIONS = createBuiltInTemplateDefinitions();
var BUILT_IN_TEMPLATE_NAMES = getBuiltInTemplateNames();
var BUILT_IN_TEMPLATE_LOOKUP = createLookup(BUILT_IN_TEMPLATE_NAMES);
function initSpread(spread) {
registerTemplates(spread);
// Create a sheet for each category
var groups = GALLERY_GROUPS;
spread.setSheetCount(groups.length + 1);
spread.options.tabStripRatio = 0.6;
for (var i = 0; i < groups.length; i++) {
var sheet = spread.getSheet(i);
sheet.name(groups[i].title);
initCategorySheet(spread, sheet, groups[i]);
}
// Playground sheet
var playgroundSheet = spread.getSheet(groups.length);
playgroundSheet.name("Playground");
initPlaygroundSheet(playgroundSheet);
bindEvents(spread);
refreshTemplateList(spread);
}
function createGalleryGroups() {
return [
{
title: "Financial",
rows: [
["Financial Amount", "Growth Rate", "Budget Variance", "Currency", "Accounting"]
]
},
{
title: "Status & Scoring",
rows: [
["Workflow Status", "Completion Bar", "Priority Level", "Due Date"],
["Grade Score", "Inventory Level", "Star Rating", "Yes/No Toggle"]
]
},
{
title: "Data",
rows: [
["Heat Map", "Editable Field", "Top/Bottom", "Duplicate Check", "Email Address"]
]
},
{
title: "Interaction",
rows: [
["Active Row", "Hover Row", "Crosshair"]
]
}
];
}
function createBuiltInTemplateDefinitions() {
var spreadNS = GC.Spread.Sheets;
var CF = spreadNS.ConditionalFormatting;
var DV = spreadNS.DataValidation;
return {
"Financial Amount": {
previewType: "single",
sampleData: [12500.50, -3200.75, 8900.00, -1500.25, 45000.00, 0, 2200.10, -875.35, 16300.80, -42.90],
template: {
style: {
formatter: "#,##0.00",
hAlign: spreadNS.HorizontalAlign.right
},
conditionalFormats: [{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.lessThan,
value1: 0,
style: { foreColor: "Accent 2" }
}],
dataValidations: [{
type: DV.CriteriaType.custom,
highlightStyle: { type: 0, color: "#FF0000" },
condition: {
conType: CF.ConditionType.formulaCondition,
customValueType: CF.CustomValueType.formula,
formula: "ISNUMBER(A1)",
ignoreBlank: true
}
}]
}
},
"Growth Rate": {
previewType: "single",
sampleData: [0.125, -0.05, 0.32, -0.15, 0.08, 0, 0.5, -0.22, 0.03, 0.18],
template: {
style: {
formatter: "0.00%",
hAlign: spreadNS.HorizontalAlign.right
},
conditionalFormats: [
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.lessThan,
value1: 0,
style: { foreColor: "Accent 2" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.greaterThan,
value1: 0,
style: { foreColor: "Accent 6" }
}
]
}
},
"Budget Variance": {
previewType: "single",
sampleData: [5000, -2500, 0, 8000, -1200, 2300, -6400, 950, -75, 12000],
template: {
style: {
formatter: "+#,##0.00;-#,##0.00;0.00",
hAlign: spreadNS.HorizontalAlign.right
},
conditionalFormats: [
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.greaterThan,
value1: 0,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.lessThan,
value1: 0,
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
}
]
}
},
"Currency": {
previewType: "single",
sampleData: [1299.99, 49.99, 899.00, 2500.00, 150.50, 75.25, 4999.95, 12.30, 108.88, 678.42],
template: {
style: {
formatter: "$#,##0.00",
hAlign: spreadNS.HorizontalAlign.right
}
}
},
"Accounting": {
previewType: "single",
sampleData: [15000, -7500, 0, 25000, -3200, 875, -150, 4100, -999.25, 18250.40],
template: {
style: {
formatter: '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)',
hAlign: spreadNS.HorizontalAlign.right
}
}
},
"Workflow Status": {
previewType: "single",
sampleData: [
WORKFLOW_STATUS.completed,
WORKFLOW_STATUS.inProgress,
WORKFLOW_STATUS.notStarted,
WORKFLOW_STATUS.blocked,
WORKFLOW_STATUS.completed,
WORKFLOW_STATUS.inProgress,
WORKFLOW_STATUS.notStarted,
WORKFLOW_STATUS.blocked,
WORKFLOW_STATUS.completed,
WORKFLOW_STATUS.inProgress
],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.center
},
conditionalFormats: [
{
ruleType: CF.RuleType.specificTextRule,
text: WORKFLOW_STATUS.completed,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
},
{
ruleType: CF.RuleType.specificTextRule,
text: WORKFLOW_STATUS.inProgress,
style: { backColor: "Accent 5 60", foreColor: "Accent 5 -50" }
},
{
ruleType: CF.RuleType.specificTextRule,
text: WORKFLOW_STATUS.notStarted,
style: { backColor: "Accent 3 60", foreColor: "Accent 3 -50" }
},
{
ruleType: CF.RuleType.specificTextRule,
text: WORKFLOW_STATUS.blocked,
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
}
],
dataValidations: [{
type: DV.CriteriaType.list,
inCellDropdown: true,
condition: {
conType: 12,
expected: [WORKFLOW_STATUS.notStarted, WORKFLOW_STATUS.inProgress, WORKFLOW_STATUS.completed, WORKFLOW_STATUS.blocked].join(",")
}
}]
}
},
"Completion Bar": {
previewType: "single",
sampleData: [0, 0.1, 0.25, 0.4, 0.5, 0.65, 0.75, 0.85, 0.95, 1],
template: {
style: {
formatter: "0%",
hAlign: spreadNS.HorizontalAlign.center
},
conditionalFormats: [{
ruleType: CF.RuleType.dataBarRule,
minType: CF.ScaleValueType.number,
minValue: 0,
maxType: CF.ScaleValueType.number,
maxValue: 1,
color: "Accent 5",
showValue: true
}],
dataValidations: [{
type: DV.CriteriaType.decimalValues,
comparisonOperator: CF.ComparisonOperators.between,
condition: {
conType: CF.ConditionType.relationCondition,
compareType: CF.LogicalOperators.and,
item1: {
conType: CF.ConditionType.numberCondition,
compareType: CF.GeneralComparisonOperators.greaterThanOrEqualsTo,
expected: 0
},
item2: {
conType: CF.ConditionType.numberCondition,
compareType: CF.GeneralComparisonOperators.lessThanOrEqualsTo,
expected: 1
}
}
}]
}
},
"Priority Level": {
previewType: "single",
sampleData: [
PRIORITY_LEVEL.high,
PRIORITY_LEVEL.medium,
PRIORITY_LEVEL.low,
PRIORITY_LEVEL.high,
PRIORITY_LEVEL.medium,
PRIORITY_LEVEL.low,
PRIORITY_LEVEL.high,
PRIORITY_LEVEL.medium,
PRIORITY_LEVEL.low,
PRIORITY_LEVEL.high
],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.center
},
conditionalFormats: [
{
ruleType: CF.RuleType.specificTextRule,
text: PRIORITY_LEVEL.high,
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
},
{
ruleType: CF.RuleType.specificTextRule,
text: PRIORITY_LEVEL.medium,
style: { backColor: "Accent 4 60", foreColor: "Accent 4 -50" }
},
{
ruleType: CF.RuleType.specificTextRule,
text: PRIORITY_LEVEL.low,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
}
],
dataValidations: [{
type: DV.CriteriaType.list,
inCellDropdown: true,
condition: {
conType: 12,
expected: [PRIORITY_LEVEL.high, PRIORITY_LEVEL.medium, PRIORITY_LEVEL.low].join(",")
}
}]
}
},
"Due Date": {
previewType: "single",
sampleData: createDueDateSampleData(),
template: {
style: {
formatter: "yyyy-mm-dd",
hAlign: spreadNS.HorizontalAlign.center,
cellButtons: [{
imageType: spreadNS.ButtonImageType.dropdown,
command: "openDateTimePicker",
useButtonStyle: false,
visibility: spreadNS.ButtonVisibility.onEditing
}],
dropDowns: [{
type: spreadNS.DropDownType.dateTimePicker,
option: {
showTime: false
}
}]
},
conditionalFormats: [
{
ruleType: CF.RuleType.formulaRule,
formula: '=AND(INT(A1)<TODAY(),A1<>"")',
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
},
{
ruleType: CF.RuleType.formulaRule,
formula: '=INT(A1)=TODAY()',
style: { backColor: "Accent 4 60", foreColor: "Accent 4 -50" }
}
],
dataValidations: [{
type: DV.CriteriaType.date,
comparisonOperator: CF.ComparisonOperators.between,
condition: {
conType: CF.ConditionType.relationCondition,
compareType: CF.LogicalOperators.and,
item1: {
conType: CF.ConditionType.dateCondition,
compareType: CF.DateCompareType.afterEqualsTo,
expected: "1900/1/1"
},
item2: {
conType: CF.ConditionType.dateCondition,
compareType: CF.DateCompareType.beforeEqualsTo,
expected: "9999/12/31"
}
}
}]
}
},
"Grade Score": {
previewType: "single",
sampleData: [95, 92, 88, 83, 77, 72, 68, 63, 58, 45],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.center
},
conditionalFormats: [
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.between,
value1: 90,
value2: 100,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.between,
value1: 80,
value2: 89,
style: { backColor: "Accent 6 80" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.between,
value1: 70,
value2: 79,
style: { backColor: "Accent 4 60" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.between,
value1: 60,
value2: 69,
style: { backColor: "Accent 2 60" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.lessThan,
value1: 60,
style: { backColor: "Accent 2", foreColor: "Background 1" }
}
],
dataValidations: [{
type: DV.CriteriaType.wholeNumber,
comparisonOperator: CF.ComparisonOperators.between,
condition: {
conType: CF.ConditionType.relationCondition,
compareType: CF.LogicalOperators.and,
item1: {
conType: CF.ConditionType.numberCondition,
compareType: CF.GeneralComparisonOperators.greaterThanOrEqualsTo,
expected: 0,
integerValue: true
},
item2: {
conType: CF.ConditionType.numberCondition,
compareType: CF.GeneralComparisonOperators.lessThanOrEqualsTo,
expected: 100,
integerValue: true
}
}
}]
}
},
"Inventory Level": {
previewType: "single",
sampleData: [50, 80, 99, 100, 180, 250, 420, 500, 650, 900],
template: {
style: {
formatter: "#,##0",
hAlign: spreadNS.HorizontalAlign.right
},
conditionalFormats: [
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.lessThan,
value1: 100,
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.between,
value1: 100,
value2: 500,
style: { backColor: "Accent 4 60", foreColor: "Accent 4 -50" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.greaterThan,
value1: 500,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
}
],
dataValidations: [{
type: DV.CriteriaType.wholeNumber,
comparisonOperator: CF.ComparisonOperators.greaterThanOrEqualsTo,
condition: {
conType: CF.ConditionType.numberCondition,
compareType: CF.GeneralComparisonOperators.greaterThanOrEqualsTo,
expected: 0,
integerValue: true
}
}]
}
},
"Star Rating": {
previewType: "single",
sampleData: [5, 4, 3, 2, 1, 5, 4, 3, 2, 1],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.center
},
conditionalFormats: [
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.equalsTo,
value1: 5,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.equalsTo,
value1: 4,
style: { backColor: "Accent 6 80" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.equalsTo,
value1: 3,
style: { backColor: "Accent 4 60" }
},
{
ruleType: CF.RuleType.cellValueRule,
operator: CF.ComparisonOperators.lessThanOrEqualsTo,
value1: 2,
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
}
],
dataValidations: [{
type: DV.CriteriaType.wholeNumber,
comparisonOperator: CF.ComparisonOperators.between,
condition: {
conType: CF.ConditionType.relationCondition,
compareType: CF.LogicalOperators.and,
item1: {
conType: CF.ConditionType.numberCondition,
compareType: CF.GeneralComparisonOperators.greaterThanOrEqualsTo,
expected: 1,
integerValue: true
},
item2: {
conType: CF.ConditionType.numberCondition,
compareType: CF.GeneralComparisonOperators.lessThanOrEqualsTo,
expected: 5,
integerValue: true
}
}
}]
}
},
"Yes/No Toggle": {
previewType: "single",
sampleData: [YES_NO.yes, YES_NO.no, YES_NO.yes, YES_NO.yes, YES_NO.no, YES_NO.no, YES_NO.yes, YES_NO.no, YES_NO.yes, YES_NO.no],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.center
},
conditionalFormats: [
{
ruleType: CF.RuleType.specificTextRule,
text: YES_NO.yes,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
},
{
ruleType: CF.RuleType.specificTextRule,
text: YES_NO.no,
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
}
],
dataValidations: [{
type: DV.CriteriaType.list,
inCellDropdown: true,
condition: {
conType: 12,
expected: [YES_NO.yes, YES_NO.no].join(",")
}
}]
}
},
"Heat Map": {
previewType: "single",
sampleData: [95, 72, 45, 88, 30, 60, 15, 55, 78, 100],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.center
},
conditionalFormats: [{
ruleType: CF.RuleType.threeScaleRule,
minType: CF.ScaleValueType.lowestValue,
minColor: "Accent 2",
midType: CF.ScaleValueType.percent,
midValue: 50,
midColor: "Accent 4",
maxType: CF.ScaleValueType.highestValue,
maxColor: "Accent 6"
}]
}
},
"Editable Field": {
previewType: "single",
sampleData: ["John Doe", "Jane Smith", "", "Bob Wilson", "", "Anna Green", "Mark Lee", "", "Sara Kim", "Notes"],
template: {
style: {
backColor: "Background 1",
borderBottom: {
style: spreadNS.LineStyle.thin,
color: "Accent 5"
}
},
cellStates: [
{
state: spreadNS.CellStatesType.hover,
style: { backColor: "Accent 5 80" }
},
{
state: spreadNS.CellStatesType.edit,
style: {
backColor: "Accent 5 60",
borderLeft: { style: spreadNS.LineStyle.thin, color: "Accent 5" },
borderTop: { style: spreadNS.LineStyle.thin, color: "Accent 5" },
borderRight: { style: spreadNS.LineStyle.thin, color: "Accent 5" },
borderBottom: { style: spreadNS.LineStyle.thin, color: "Accent 5" }
}
},
{
state: spreadNS.CellStatesType.active,
style: {
borderLeft: { style: spreadNS.LineStyle.thin, color: "Accent 1" },
borderTop: { style: spreadNS.LineStyle.thin, color: "Accent 1" },
borderRight: { style: spreadNS.LineStyle.thin, color: "Accent 1" },
borderBottom: { style: spreadNS.LineStyle.thin, color: "Accent 1" }
}
}
]
}
},
"Top/Bottom": {
previewType: "single",
sampleData: [150, 85, 230, 45, 180, 96, 275, 30, 140, 210],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.right
},
conditionalFormats: [
{
ruleType: CF.RuleType.top10Rule,
type: CF.Top10ConditionType.top,
rank: 10,
isPercent: true,
style: { backColor: "Accent 6 60", foreColor: "Accent 6 -50" }
},
{
ruleType: CF.RuleType.top10Rule,
type: CF.Top10ConditionType.bottom,
rank: 10,
isPercent: true,
style: { backColor: "Accent 2 60", foreColor: "Accent 2 -50" }
}
]
}
},
"Duplicate Check": {
previewType: "single",
sampleData: ["Apple", "Orange", "Apple", "Banana", "Orange", "Kiwi", "Banana", "Pear", "Pear", "Mango"],
template: {
conditionalFormats: [{
ruleType: CF.RuleType.duplicateRule,
style: { backColor: "Accent 4 60", foreColor: "Accent 4 -50" }
}]
}
},
"Email Address": {
previewType: "single",
sampleData: [
"john@example.com",
"jane@company.org",
"support@demo.io",
"team@spreadjs.dev",
"sales@contoso.com",
"hello@northwind.net",
"info@adventure-works.com",
"admin@site.net",
"contact@fabricam.co",
"service@wideworldimporters.biz"
],
template: {
style: {
hAlign: spreadNS.HorizontalAlign.left,
foreColor: "Accent 1"
},
dataValidations: [{
type: DV.CriteriaType.custom,
condition: {
conType: CF.ConditionType.formulaCondition,
customValueType: CF.CustomValueType.formula,
formula: 'AND(FIND("@",A1)>1,ISNUMBER(FIND(".",A1,FIND("@",A1))))'
},
showInputMessage: true,
inputTitle: "Email",
inputMessage: "Enter a valid email address"
}]
}
},
"Active Row": {
previewType: "grid",
sampleData: createInteractionSampleData(),
template: {
conditionalFormats: [{
ruleType: 14,
state: 16,
style: { backColor: "Accent 1 80" }
}]
}
},
"Hover Row": {
previewType: "grid",
sampleData: createInteractionSampleData(),
template: {
conditionalFormats: [{
ruleType: 14,
state: 1,
style: { backColor: "Accent 5 80" }
}]
}
},
"Crosshair": {
previewType: "grid",
sampleData: createInteractionSampleData(),
template: {
conditionalFormats: [
{
ruleType: 14,
state: 32,
style: { backColor: "Accent 5 80" }
},
{
ruleType: 15,
state: 32,
style: { backColor: "Accent 5 80" }
}
],
cellStates: [{
state: spreadNS.CellStatesType.selected,
style: { backColor: null }
}]
}
}
};
}
function createDueDateSampleData() {
var today = new Date();
var offsets = [-14, -7, -3, -1, 0, 1, 3, 7, 14, 30];
var result = [];
for (var i = 0; i < offsets.length; i++) {
var date = new Date(today.getFullYear(), today.getMonth(), today.getDate() + offsets[i]);
result.push(date);
}
return result;
}
function createInteractionSampleData() {
return {
cols: 3,
data: [
[85, 92, 78],
[90, 88, 95],
[76, 81, 89],
[93, 87, 82],
[88, 91, 84],
[79, 85, 90],
[96, 89, 86],
[84, 93, 80],
[91, 77, 88],
[87, 94, 83]
],
activeCell: { row: 4, col: 1 },
selection: { row: 4, col: 1, rowCount: 1, colCount: 1 }
};
}
function getBuiltInTemplateNames() {
var names = [];
var lookup = {};
for (var i = 0; i < GALLERY_GROUPS.length; i++) {
var group = GALLERY_GROUPS[i];
for (var rowIndex = 0; rowIndex < group.rows.length; rowIndex++) {
var row = group.rows[rowIndex];
for (var colIndex = 0; colIndex < row.length; colIndex++) {
var name = row[colIndex];
if (!lookup[name]) {
lookup[name] = true;
names.push(name);
}
}
}
}
return names;
}
function createLookup(names) {
var lookup = {};
for (var i = 0; i < names.length; i++) {
lookup[names[i]] = true;
}
return lookup;
}
function registerTemplates(spread) {
var existing = createLookup(getCurrentTemplateNames(spread));
for (var i = 0; i < BUILT_IN_TEMPLATE_NAMES.length; i++) {
var name = BUILT_IN_TEMPLATE_NAMES[i];
if (existing[name]) {
spread.namedCellTemplates.remove(name);
}
spread.namedCellTemplates.add(name, BUILT_IN_TEMPLATE_DEFINITIONS[name].template);
}
}
function initCategorySheet(spread, sheet, group) {
var registeredBuiltInLookup = getRegisteredBuiltInLookup(spread);
var cardPositions = {};
sheet.suspendPaint();
sheet.setRowCount(0);
sheet.setColumnCount(0);
sheet.setRowCount(GALLERY_ROW_COUNT);
sheet.setColumnCount(TOTAL_GALLERY_COLUMNS);
applyCategoryColumnWidths(sheet, group);
if (!groupHasRegisteredTemplates(group, registeredBuiltInLookup)) {
setMergedCellValue(sheet, 0, 0, 2, TOTAL_GALLERY_SPAN_COLUMNS, NO_TEMPLATE_MESSAGE, createEmptyStateStyle());
sheet.resumePaint();
return;
}
var currentRow = 0;
for (var rowIndex = 0; rowIndex < group.rows.length; rowIndex++) {
var names = group.rows[rowIndex];
var currentCol = 0;
for (var slot = 0; slot < names.length; slot++) {
var name = names[slot];
if (!registeredBuiltInLookup[name]) {
continue;
}
renderTemplateCard(sheet, name, currentRow, currentCol, cardPositions);
var isWide = WIDE_TEMPLATES.indexOf(name) !== -1;
currentCol += isWide ? WIDE_TEMPLATE_SLOT_COLUMNS : CARD_SLOT_COLUMNS;
}
currentRow += CARD_ROW_HEIGHT;
}
applyGalleryInteractionState(sheet, cardPositions);
sheet.resumePaint();
}
function applyCategoryColumnWidths(sheet, group) {
for (var rowIdx = 0; rowIdx < group.rows.length; rowIdx++) {
var names = group.rows[rowIdx];
var currentCol = 0;
for (var slot = 0; slot < names.length; slot++) {
var name = names[slot];
var isWide = WIDE_TEMPLATES.indexOf(name) !== -1;
if (isWide) {
sheet.setColumnWidth(currentCol, 60);
sheet.setColumnWidth(currentCol + 1, 60);
sheet.setColumnWidth(currentCol + 2, 60);
sheet.setColumnWidth(currentCol + 3, CARD_SPACER_WIDTH);
currentCol += WIDE_TEMPLATE_SLOT_COLUMNS;
} else {
sheet.setColumnWidth(currentCol, 140);
sheet.setColumnWidth(currentCol + 1, CARD_SPACER_WIDTH);
currentCol += CARD_SLOT_COLUMNS;
}
}
}
}
function renderTemplateCard(sheet, templateName, startRow, startCol, cardPositions) {
var definition = BUILT_IN_TEMPLATE_DEFINITIONS[templateName];
var sampleData = definition.sampleData;
var dataRow = startRow + 1;
var isWide = WIDE_TEMPLATES.indexOf(templateName) !== -1;
var dataColCount = definition.previewType === "grid" ? sampleData.cols : 1;
var titleColSpan = isWide ? WIDE_TEMPLATE_COLS : CARD_VISIBLE_COLUMNS;
setMergedCellValue(sheet, startRow, startCol, 1, titleColSpan, templateName, createCardTitleStyle());
sheet.setRowHeight(startRow, CARD_TITLE_ROW_HEIGHT);
if (definition.previewType === "grid") {
for (var row = 0; row < sampleData.data.length; row++) {
sheet.setRowHeight(dataRow + row, CARD_DATA_ROW_HEIGHT);
for (var col = 0; col < sampleData.cols; col++) {
sheet.setValue(dataRow + row, startCol + col, sampleData.data[row][col]);
}
}
} else {
for (var itemRow = 0; itemRow < sampleData.length; itemRow++) {
sheet.setRowHeight(dataRow + itemRow, CARD_DATA_ROW_HEIGHT);
sheet.setValue(dataRow + itemRow, startCol, sampleData[itemRow]);
}
}
sheet.applyNamedCellTemplate(templateName, toRangeString(dataRow, startCol, CARD_DATA_ROW_COUNT, dataColCount));
applyCardBorders(sheet, startRow, startCol, CARD_DATA_ROW_COUNT + 1, dataColCount);
cardPositions[templateName] = {
dataRow: dataRow,
dataCol: startCol,
rowCount: CARD_DATA_ROW_COUNT,
colCount: dataColCount
};
}
function applyCardBorders(sheet, row, col, rowCount, colCount) {
var lineStyle = GC.Spread.Sheets.LineStyle.thin;
var borderColor = "#D9D9D9";
var border = new GC.Spread.Sheets.LineBorder(borderColor, lineStyle);
var range = sheet.getRange(row, col, rowCount, colCount);
range.setBorder(border, { all: true });
}
function applyGalleryInteractionState(sheet, cardPositions) {
var target = cardPositions["Crosshair"] || cardPositions["Active Row"];
if (!target) {
return;
}
var activeRow = target.dataRow + 4;
var activeCol = target.dataCol + Math.min(1, target.colCount - 1);
sheet.clearSelection();
sheet.setActiveCell(activeRow, activeCol);
sheet.addSelection(activeRow, activeCol, 1, 1);
}
function groupHasRegisteredTemplates(group, registeredBuiltInLookup) {
for (var i = 0; i < group.rows.length; i++) {
for (var j = 0; j < group.rows[i].length; j++) {
if (registeredBuiltInLookup[group.rows[i][j]]) {
return true;
}
}
}
return false;
}
function initPlaygroundSheet(sheet) {
sheet.suspendPaint();
sheet.setRowCount(0);
sheet.setColumnCount(0);
sheet.setRowCount(16);
sheet.setColumnCount(10);
var headers = ["Amount", "Rate", "Variance", "Status", "Progress", "Priority", "Due Date", "Inventory", "Email", "Rating"];
var headerStyle = createHeaderStyle();
var testData = createPlaygroundData();
for (var col = 0; col < headers.length; col++) {
sheet.setValue(0, col, headers[col]);
sheet.setStyle(0, col, headerStyle);
}
sheet.setRowHeight(0, 28);
for (var row = 0; row < testData.length; row++) {
sheet.setRowHeight(row + 1, 24);
for (var cellCol = 0; cellCol < testData[row].length; cellCol++) {
sheet.setValue(row + 1, cellCol, testData[row][cellCol]);
}
}
// Auto-fit all columns
for (var col = 0; col < headers.length; col++) {
sheet.autoFitColumn(col);
}
sheet.resumePaint();
}
function createPlaygroundData() {
var dueDates = createDueDateSampleData();
return [
[8500.00, 0.18, 3200, WORKFLOW_STATUS.inProgress, 0.6, PRIORITY_LEVEL.high, dueDates[0], 78, "alice@contoso.com", 5],
[-1200.50, -0.03, -800, WORKFLOW_STATUS.completed, 0.9, PRIORITY_LEVEL.medium, dueDates[1], 120, "charlie@northwind.com", 4],
[23000.00, 0.45, 5500, WORKFLOW_STATUS.notStarted, 0.3, PRIORITY_LEVEL.low, dueDates[2], 650, "eve@adventure-works.com", 3],
[-500.25, -0.12, -3000, WORKFLOW_STATUS.blocked, 0.1, PRIORITY_LEVEL.high, dueDates[3], 45, "frank@wideworldimporters.biz", 2],
[15600.75, 0.08, 1200, WORKFLOW_STATUS.inProgress, 0.45, PRIORITY_LEVEL.medium, dueDates[4], 350, "grace@fabrikam.co", 1],
[9800.00, 0.22, -450, WORKFLOW_STATUS.completed, 0.85, PRIORITY_LEVEL.low, dueDates[5], 580, "henry@tailspintoys.com", 5],
[-4200.00, -0.07, 2800, WORKFLOW_STATUS.notStarted, 0, PRIORITY_LEVEL.high, dueDates[6], 92, "irene@contoso.com", 4],
[31000.50, 0.35, 7600, WORKFLOW_STATUS.inProgress, 0.7, PRIORITY_LEVEL.medium, dueDates[7], 410, "jack@northwind.com", 3],
[5200.40, 0.05, 950, WORKFLOW_STATUS.completed, 1, PRIORITY_LEVEL.low, dueDates[8], 805, "karen@adatum.com", 2],
[-760.10, -0.18, -120, WORKFLOW_STATUS.blocked, 0.2, PRIORITY_LEVEL.high, dueDates[9], 15, "leo@proseware.io", 1]
];
}
function getCurrentTemplateNames(spread) {
var templates = spread.namedCellTemplates.all();
var result = [];
for (var i = 0; i < templates.length; i++) {
result.push(templates[i].name);
}
return result;
}
function getRegisteredBuiltInLookup(spread) {
var currentNames = getCurrentTemplateNames(spread);
var lookup = {};
for (var i = 0; i < currentNames.length; i++) {
if (BUILT_IN_TEMPLATE_LOOKUP[currentNames[i]]) {
lookup[currentNames[i]] = true;
}
}
return lookup;
}
function setMergedCellValue(sheet, row, col, rowCount, colCount, value, style) {
sheet.addSpan(row, col, rowCount, colCount);
sheet.setValue(row, col, value);
for (var r = row; r < row + rowCount; r++) {
for (var c = col; c < col + colCount; c++) {
sheet.setStyle(r, c, style);
}
}
}
function createHeaderStyle() {
var style = new GC.Spread.Sheets.Style();
style.font = "bold 12px Calibri";
style.backColor = "#F2F2F2";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.borderBottom = new GC.Spread.Sheets.LineBorder("#CCCCCC", GC.Spread.Sheets.LineStyle.thin);
return style;
}
function createCardTitleStyle() {
var style = new GC.Spread.Sheets.Style();
style.font = "bold 11px Calibri";
style.backColor = "#F8F8F8";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
style.borderBottom = new GC.Spread.Sheets.LineBorder("#D9D9D9", GC.Spread.Sheets.LineStyle.thin);
return style;
}
function createEmptyStateStyle() {
var style = new GC.Spread.Sheets.Style();
style.font = "12px Calibri";
style.backColor = "#FFFDF0";
style.foreColor = "#7A5C00";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
return style;
}
function refreshTemplateList(spread) {
var select = document.getElementById("templateSelect");
var list = document.getElementById("templateList");
var orderedNames = getOrderedRegisteredTemplateNames(spread);
select.innerHTML = "";
list.innerHTML = "";
for (var i = 0; i < orderedNames.length; i++) {
appendOption(select, orderedNames[i]);
appendOption(list, orderedNames[i]);
}
if (orderedNames.length > 0) {
select.value = orderedNames[0];
list.selectedIndex = 0;
}
}
function getOrderedRegisteredTemplateNames(spread) {
var templates = spread.namedCellTemplates.all();
var registeredLookup = createLookup([]);
var orderedNames = [];
for (var i = 0; i < templates.length; i++) {
registeredLookup[templates[i].name] = true;
}
for (var builtInIndex = 0; builtInIndex < BUILT_IN_TEMPLATE_NAMES.length; builtInIndex++) {
var builtInName = BUILT_IN_TEMPLATE_NAMES[builtInIndex];
if (registeredLookup[builtInName]) {
orderedNames.push(builtInName);
delete registeredLookup[builtInName];
}
}
for (var templateIndex = 0; templateIndex < templates.length; templateIndex++) {
var name = templates[templateIndex].name;
if (registeredLookup[name]) {
orderedNames.push(name);
delete registeredLookup[name];
}
}
return orderedNames;
}
function getCurrentTemplateNames(spread) {
var templates = spread.namedCellTemplates.all();
var result = [];
for (var i = 0; i < templates.length; i++) {
result.push(templates[i].name);
}
return result;
}
function getRegisteredBuiltInLookup(spread) {
var currentNames = getCurrentTemplateNames(spread);
var lookup = {};
for (var i = 0; i < currentNames.length; i++) {
if (BUILT_IN_TEMPLATE_LOOKUP[currentNames[i]]) {
lookup[currentNames[i]] = true;
}
}
return lookup;
}
function appendOption(select, name) {
var option = document.createElement("option");
option.value = name;
option.textContent = name;
select.appendChild(option);
}
function bindEvents(spread) {
document.getElementById("targetRange").value = "A2:A11";
// 监听选择变化，自动更新 Target Range
spread.bind(GC.Spread.Sheets.Events.SelectionChanged, function(e, info) {
if (info.newSelections && info.newSelections.length > 0) {
var sel = info.newSelections[0];
var rangeStr = GC.Spread.Sheets.CalcEngine.rangeToFormula(sel);
document.getElementById("targetRange").value = rangeStr;
}
});
document.getElementById("applyBtn").addEventListener("click", function () {
var templateName = document.getElementById("templateSelect").value;
var targetRange = document.getElementById("targetRange").value.trim();
if (!templateName) {
alert("Please select a template.");
return;
}
if (!targetRange) {
alert("Please enter a target range (e.g. A1:A10).");
return;
}
try {
spread.getActiveSheet().applyNamedCellTemplate(templateName, targetRange);
} catch (e) {
alert("Error applying template: " + e.message);
}
});
document.getElementById("createFromCellBtn").addEventListener("click", function () {
var name = document.getElementById("newTemplateName").value.trim();
if (!name) {
alert("Please enter a template name.");
return;
}
var sheet = spread.getActiveSheet();
var selections = sheet.getSelections();
if (!selections || selections.length === 0) {
alert("Please select a cell first.");
return;
}
var sel = selections[0];
var options = {
style: document.getElementById("optStyle").checked,
conditionalFormat: document.getElementById("optConditionalFormat").checked,
dataValidation: document.getElementById("optDataValidation").checked,
cellState: document.getElementById("optCellState").checked
};
try {
spread.namedCellTemplates.createFromCell(name, sheet, sel.row, sel.col, options);
refreshTemplateList(spread);
document.getElementById("newTemplateName").value = "";
} catch (e) {
alert("Error creating template: " + e.message);
}
});
document.getElementById("removeBtn").addEventListener("click", function () {
var list = document.getElementById("templateList");
if (list.selectedIndex < 0) {
alert("Please select a template to remove.");
return;
}
var name = list.options[list.selectedIndex].value;
spread.namedCellTemplates.remove(name);
refreshTemplateList(spread);
});
document.getElementById("clearBtn").addEventListener("click", function () {
spread.namedCellTemplates.clear();
refreshTemplateList(spread);
});
}
function toRangeString(row, col, rowCount, colCount) {
var startCell = getColumnName(col) + (row + 1);
var endCell = getColumnName(col + colCount - 1) + (row + rowCount);
return startCell + ":" + endCell;
}
function getColumnName(index) {
var dividend = index + 1;
var columnName = "";
var modulo;
while (dividend > 0) {
modulo = (dividend - 1) % 26;
columnName = String.fromCharCode(65 + modulo) + columnName;
dividend = Math.floor((dividend - modulo) / 26);
}
return columnName;
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="ko-kr" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.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 class="options-container">
<div class="info-tip">The left side displays preset named templates. Switch to the Playground sheet to apply templates.</div>
<div class="section-title">Apply Template</div>
<div class="option-row">
<label for="templateSelect">Template:</label>
<select id="templateSelect"></select>
</div>
<div class="option-row">
<label for="targetRange">Target Range:</label>
<input type="text" id="targetRange" placeholder="e.g. A1:A10" />
</div>
<div class="option-row">
<input type="button" id="applyBtn" value="Apply Template" />
</div>
<div class="section-title">Create From Cell</div>
<div class="option-row">
<label for="newTemplateName">Template Name:</label>
<input type="text" id="newTemplateName" placeholder="Enter template name" />
</div>
<div class="option-row">
<label>Options:</label>
<div class="checkbox-row">
<input type="checkbox" id="optStyle" checked />
<label for="optStyle">Style</label>
</div>
<div class="checkbox-row">
<input type="checkbox" id="optConditionalFormat" checked />
<label for="optConditionalFormat">Conditional Format</label>
</div>
<div class="checkbox-row">
<input type="checkbox" id="optDataValidation" checked />
<label for="optDataValidation">Data Validation</label>
</div>
<div class="checkbox-row">
<input type="checkbox" id="optCellState" checked />
<label for="optCellState">Cell State</label>
</div>
</div>
<div class="option-row">
<input type="button" id="createFromCellBtn" value="Create From Cell" />
</div>
<div class="section-title">Template Management</div>
<div class="option-row">
<label for="templateList">Registered Templates:</label>
<select id="templateList" size="12"></select>
</div>
<div class="option-row">
<input type="button" id="removeBtn" value="Remove Selected" />
<input type="button" id="clearBtn" value="Clear All" />
</div>
</div>
</div>
</body>
</html>
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 320px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 320px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
border-left: 1px solid #e5e5e5;
}
.info-tip {
background: #e7f3ff;
border-left: 3px solid #2196f3;
border-radius: 4px;
padding: 8px 10px;
font-size: 13px;
color: #333;
line-height: 1.4;
margin-bottom: 8px;
}
.section-title {
font-weight: bold;
font-size: 13px;
border-bottom: 1px solid #ddd;
padding-bottom: 4px;
margin-top: 16px;
margin-bottom: 8px;
}
.section-title:first-child {
margin-top: 0;
}
.option-row {
font-size: 14px;
padding: 2px 0;
margin-top: 6px;
}
.option-row label {
display: block;
margin-bottom: 4px;
}
.checkbox-row {
margin: 4px 0;
}
.checkbox-row label {
display: inline;
margin-left: 4px;
}
input[type=text] {
width: 100%;
box-sizing: border-box;
padding: 4px 6px;
}
select {
width: 100%;
padding: 4px;
}
input[type=button] {
margin-top: 6px;
display: block;
width: 100%;
padding: 6px 8px;
cursor: pointer;
}
