Introduction
The Named Cell Templates feature allows you to define reusable cell configuration templates that can be applied to cells or ranges. Each template can contain style, conditional formats, data validations, and cell states.
You can access the named cell templates manager through the workbook instance:
Add a Template
Use add(name, template) to register a new template:
Create Template from Cell
Use createFromCell(name, sheet, row, col, options) to create a template from an existing cell's configuration:
Apply Template
Use sheet.applyNamedCellTemplate(name, address) to apply a template to a range:
Or use cellRange.applyNamedCellTemplate(name):
Manage Templates
Template Structure
The template object follows the INamedCellTemplate interface:
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="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$/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;
}
