To create a custom header cell type, create a custom header cell type based on the RowHeader or ColumnHeader, or another built-in CellType. For example:
For your custom header cell type, provide the following methods to process mouse events.
getHitInfo: Gets an object that contains hit information that will be provided to processMouse methods.
processMouseDown: Provides the action to perform for mouse button down.
processMouseMove: Provides the action to perform for mouse move.
processMouseUp: Provides the action to perform for mouse button up.
processMouseEnter: Provides the action to perform for mouse enter.
processMouseLeave: Provides the action to perform for mouse leave.
Here's a sample that uses the getHitInfo method.
The ButtonClicked event is available for CheckBox, Button, and HyperLink on row and column header cells after overriding the getHitInfo method.
You can process the ButtonClicked event by binding to Spread or Sheet. For example:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet></gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue'
import { ref } from 'vue';
import GC from '@mescius/spread-sheets';
import { HighlightColumnItemsCellType, TopItemsCellType, HeaderCheckBoxCellType, SortHyperlinkCellType, HighlightRowItemsCellType } from "./data.js";
const spreadNS = GC.Spread.Sheets;
const initSpread = (spread) => {
let rowCount = 50, columnCount = 20;
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setRowCount(rowCount);
sheet.setColumnCount(columnCount);
sheet.setColumnWidth(0, 60, spreadNS.SheetArea.rowHeader);
fillSampleData(sheet, new spreadNS.Range(0, 0, rowCount, columnCount));
sheet.setCellType(0, 1, new HighlightColumnItemsCellType(), spreadNS.SheetArea.colHeader);
sheet.setCellType(0, 3, new TopItemsCellType(10), spreadNS.SheetArea.colHeader);
sheet.setCellType(0, 5, new HeaderCheckBoxCellType(120), spreadNS.SheetArea.colHeader);
sheet.setCellType(0, 7, new SortHyperlinkCellType(), spreadNS.SheetArea.colHeader);
sheet.setCellType(9, 0, new HighlightRowItemsCellType(), spreadNS.SheetArea.rowHeader);
sheet.resumePaint();
spread.bind(spreadNS.Events.ButtonClicked, (e, args) => {
let sheet = args.sheet, sheetArea = args.sheetArea,
row = args.row, col = args.col,
tag = sheet.getTag(row, col, sheetArea),
cellType = sheet.getCellType(row, col, sheetArea);
if (cellType instanceof TopItemsCellType) {
let count = cellType.count;
sheet.setTag(row, col, !tag, sheetArea);
let range = new spreadNS.Range(-1, col, -1, 1);
let rowFilter = new spreadNS.Filter.HideRowFilter(range);
sheet.rowFilter(rowFilter);
rowFilter.filterButtonVisible(false);
let condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.top10Condition, {
type: spreadNS.ConditionalFormatting.Top10ConditionType.top,
expected: count
});
rowFilter.addFilterItem(col, condition);
if (!tag) {
rowFilter.filter();
} else {
rowFilter.unfilter();
}
}
});
};
const fillSampleData = (sheet, range) => {
for (let i = 0; i < range.rowCount; i++) {
for (let j = 0; j < range.colCount; j++) {
sheet.setValue(range.row + i, range.col + j, Math.ceil(Math.random() * 300) - 100);
}
}
};
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
height: 100%;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
import GC from "@mescius/spread-sheets";
const spreadNS = GC.Spread.Sheets;
// Define highlight cell types
export class HighlightColumnItemsCellType {
constructor() {
this.RADIUS = 10;
this.HIGHLIGHT_COLOR = "rgb(40, 171, 240)";
this.NORMAL_COLOR = "rgb(128, 255, 255)";
this.HIGHLIGHT_TIP = "Remove highlight.";
this.NORMAL_TIP = "Highlight negative numbers.";
spreadNS.CellTypes.ColumnHeader.apply(this);
}
}
HighlightColumnItemsCellType.prototype = new spreadNS.CellTypes.ColumnHeader();
HighlightColumnItemsCellType.prototype.paint = function (ctx, value, x, y, width, height, style, context) {
spreadNS.CellTypes.ColumnHeader.prototype.paint.apply(this, arguments);
var tag = context.sheet.getTag(context.row, context.col, context.sheetArea);
var RADIUS = this.RADIUS;
ctx.save();
ctx.beginPath();
ctx.arc(x + width - RADIUS, y + height / 2, RADIUS / 2, 0, Math.PI * 2);
ctx.fillStyle = (tag && tag.color) || this.NORMAL_COLOR;
ctx.fill();
ctx.restore();
};
HighlightColumnItemsCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
var RADIUS = this.RADIUS;
var centerX = cellRect.x + cellRect.width - RADIUS,
centerY = cellRect.y + cellRect.height / 2;
var hitInfo = {x: x, y: y, row: context.row, col: context.col, cellRect: cellRect, sheetArea: context.sheetArea, sheet: context.sheet};
if (Math.abs(x - centerX) <= RADIUS && Math.abs(y - centerY) <= RADIUS) {
hitInfo.isReservedLocation = true;
}
return hitInfo;
};
HighlightColumnItemsCellType.prototype.processMouseDown = function (hitInfo) {
this._hideTip();
};
HighlightColumnItemsCellType.prototype.processMouseMove = function (hitInfo) {
if (hitInfo.isReservedLocation) {
var sheet = hitInfo.sheet;
var offset = {
top:sheet.getParent().getHost().offsetTop,
left:sheet.getParent().getHost().offsetLeft
}
var tag = sheet.getTag(hitInfo.row, hitInfo.col, hitInfo.sheetArea);
this._showTip(offset.top + hitInfo.y + 20, offset.left + hitInfo.x + 20, (tag && tag.tip) || this.NORMAL_TIP);
} else {
this._hideTip();
}
};
HighlightColumnItemsCellType.prototype.processMouseUp = function (hitInfo) {
if (hitInfo.isReservedLocation) {
var sheet = hitInfo.sheet;
var tag = sheet.getTag(hitInfo.row, hitInfo.col, hitInfo.sheetArea);
if (!tag) {//first time
tag = {color: this.HIGHLIGHT_COLOR, tip: this.HIGHLIGHT_TIP};
sheet.setTag(hitInfo.row, hitInfo.col, tag, hitInfo.sheetArea);
var style = new spreadNS.Style();
style.foreColor = "red";
var ranges = [new spreadNS.Range(-1, hitInfo.col, -1, 1)];
var rule = sheet.conditionalFormats.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.lessThan, 0, 0, style, ranges);
tag.rule = rule;
tag.isHighlighed = true;
} else if (!tag.isHighlighed) {
tag.color = this.HIGHLIGHT_COLOR;
tag.tip = this.HIGHLIGHT_TIP;
sheet.conditionalFormats.addRule(tag.rule);
tag.isHighlighed = true;
} else {
tag.color = this.NORMAL_COLOR;
tag.tip = this.NORMAL_TIP;
sheet.conditionalFormats.removeRule(tag.rule);
tag.isHighlighed = false;
}
var offset = {
top:sheet.getParent().getHost().offsetTop,
left:sheet.getParent().getHost().offsetLeft
}
this._showTip(offset.top + hitInfo.y + 20, offset.left + hitInfo.x + 20, (tag && tag.tip) || this.NORMAL_TIP);
}
};
HighlightColumnItemsCellType.prototype.processMouseEnter = function (hitInfo) {
};
HighlightColumnItemsCellType.prototype.processMouseLeave = function (hitInfo) {
this._hideTip();
};
HighlightColumnItemsCellType.prototype._showTip = function (top, left, tip) {
if (!this._tipElement) {
var span = document.createElement("span");
span.style.position = "absolute";
span.style.background = "#EEEEEE";
span.style.border = "1px solid black";
span.style.boxShadow = "5px 5px 5px rgba(0,0,0,0.4)";
span.style.fontSize = "14px";
document.body.insertBefore(span, null);
this._tipElement = span;
}
var tipElement = this._tipElement;
tipElement.textContent = tip;
var spanStyle = tipElement.style;
spanStyle.top = top + "px";
spanStyle.left = left + "px";
};
HighlightColumnItemsCellType.prototype._hideTip = function () {
if (this._tipElement) {
document.body.removeChild(this._tipElement);
this._tipElement = undefined;
}
};
export class HighlightRowItemsCellType {
constructor() {
this.RADIUS = 10;
this.HIGHLIGHT_COLOR = "rgb(40, 171, 240)";
this.NORMAL_COLOR = "rgb(128, 255, 255)";
this.HIGHLIGHT_TIP = "Remove highlight.";
this.NORMAL_TIP = "Highlight negative numbers.";
spreadNS.CellTypes.RowHeader.apply(this);
}
}
HighlightRowItemsCellType.prototype = new spreadNS.CellTypes.RowHeader();
HighlightRowItemsCellType.prototype.paint = function (ctx, value, x, y, width, height, style, context) {
spreadNS.CellTypes.RowHeader.prototype.paint.apply(this, arguments);
var tag = context.sheet.getTag(context.row, context.col, context.sheetArea);
var RADIUS = this.RADIUS;
ctx.save();
ctx.beginPath();
ctx.arc(x + width - RADIUS, y + height / 2, RADIUS / 2, 0, Math.PI * 2);
ctx.fillStyle = (tag && tag.color) || this.NORMAL_COLOR;
ctx.fill();
ctx.restore();
};
HighlightRowItemsCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
var RADIUS = this.RADIUS;
var centerX = cellRect.x + cellRect.width - RADIUS, centerY = cellRect.y + cellRect.height / 2;
var hitInfo = {x: x, y: y, row: context.row, col: context.col, cellRect: cellRect, sheetArea: context.sheetArea, sheet: context.sheet};
if (Math.abs(x - centerX) <= RADIUS && Math.abs(y - centerY) <= RADIUS) {
hitInfo.isReservedLocation = true;
}
return hitInfo;
};
HighlightRowItemsCellType.prototype.processMouseDown = function (hitInfo) {
this._hideTip();
};
HighlightRowItemsCellType.prototype.processMouseMove = function (hitInfo) {
if (hitInfo.isReservedLocation) {
var sheet = hitInfo.sheet;
var offset = {
top:sheet.getParent().getHost().offsetTop,
left:sheet.getParent().getHost().offsetLeft
}
var tag = sheet.getTag(hitInfo.row, hitInfo.col, hitInfo.sheetArea);
this._showTip(offset.top + hitInfo.y + 20, offset.left + hitInfo.x + 20, (tag && tag.tip) || this.NORMAL_TIP);
} else {
this._hideTip();
}
};
HighlightRowItemsCellType.prototype.processMouseUp = function (hitInfo) {
if (hitInfo.isReservedLocation) {
var sheet = hitInfo.sheet;
var tag = sheet.getTag(hitInfo.row, hitInfo.col, hitInfo.sheetArea);
if (!tag) {//first time
tag = {color: this.HIGHLIGHT_COLOR, tip: this.HIGHLIGHT_TIP};
sheet.setTag(hitInfo.row, hitInfo.col, tag, hitInfo.sheetArea);
var style = new spreadNS.Style();
style.foreColor = "red";
var ranges = [new spreadNS.Range(hitInfo.row, -1, 1, -1)];
var rule = sheet.conditionalFormats.addCellValueRule(spreadNS.ConditionalFormatting.ComparisonOperators.lessThan, 0, 0, style, ranges);
tag.rule = rule;
tag.isHighlighed = true;
} else if (!tag.isHighlighed) {
tag.color = this.HIGHLIGHT_COLOR;
tag.tip = this.HIGHLIGHT_TIP;
sheet.conditionalFormats.addRule(tag.rule);
tag.isHighlighed = true;
} else {
tag.color = this.NORMAL_COLOR;
tag.tip = this.NORMAL_TIP;
sheet.conditionalFormats.removeRule(tag.rule);
tag.isHighlighed = false;
}
var offset = {
top:sheet.getParent().getHost().offsetTop,
left:sheet.getParent().getHost().offsetLeft
}
this._showTip(offset.top + hitInfo.y + 20, offset.left + hitInfo.x + 20, (tag && tag.tip) || this.NORMAL_TIP);
}
};
HighlightRowItemsCellType.prototype.processMouseEnter = function (hitInfo) {
};
HighlightRowItemsCellType.prototype.processMouseLeave = function (hitInfo) {
this._hideTip();
};
HighlightRowItemsCellType.prototype._showTip = function (top, left, tip) {
if (!this._tipElement) {
var span = document.createElement("span");
span.style.position = "absolute";
span.style.background = "#EEEEEE";
span.style.border = "1px solid black";
span.style.boxShadow = "5px 5px 5px rgba(0,0,0,0.4)";
span.style.fontSize = "14px";
document.body.insertBefore(span, null);
this._tipElement = span;
}
var tipElement = this._tipElement;
tipElement.textContent = tip;
var spanStyle = tipElement.style;
spanStyle.top = top + "px";
spanStyle.left = left + "px";
};
HighlightRowItemsCellType.prototype._hideTip = function () {
if (this._tipElement) {
document.body.removeChild(this._tipElement);
this._tipElement = undefined;
}
};
// Define button cell type
export class TopItemsCellType {
constructor(count) {
spreadNS.CellTypes.Button.apply(this);
count = +count || 10;
if (!count || isNaN(count) || count < 0) {
count = 10;
}
this.count = count;
this.text("Top " + count);
}
}
TopItemsCellType.prototype = new spreadNS.CellTypes.Button();
TopItemsCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
var self = this;
var leftX = cellRect.x + self.marginLeft(),
rightX = cellRect.x + cellRect.width - self.marginRight(),
topY = cellRect.y + self.marginTop(),
bottomY = cellRect.y + cellRect.height - self.marginBottom();
var info = {x: x, y: y, row: context.row, col: context.col, cellRect: cellRect, sheetArea: context.sheetArea, sheet: context.sheet};
if (leftX <= x && x <= rightX && topY <= y && y <= bottomY) {
info.isReservedLocation = true;
}
return info;
};
// Define checkbox cell type
export class HeaderCheckBoxCellType {
constructor(value) {
spreadNS.CellTypes.CheckBox.apply(this);
value = +value || 100;
if (!value || isNaN(value) || value < 0) {
value = 100;
}
this.value = value;
this.caption(">" + value);
}
}
HeaderCheckBoxCellType.prototype = new spreadNS.CellTypes.CheckBox();
var basePaint = spreadNS.CellTypes.CheckBox.prototype.paint;
HeaderCheckBoxCellType.prototype.paint = function (ctx, value, x, y, width, height, style, context) {
var tag = !!(context.sheet.getTag(context.row, context.col, context.sheetArea) || false);
basePaint.apply(this, [ctx, tag, x, y, width, height, style, context]);
};
HeaderCheckBoxCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
if (context) {
return {x: x, y: y, row: context.row, col: context.col, cellRect: cellRect, sheetArea: context.sheetArea, isReservedLocation: true, sheet: context.sheet};
}
return null;
};
HeaderCheckBoxCellType.prototype.createEditorElement = function(){
return null;
}
HeaderCheckBoxCellType.prototype.processMouseDown = function (hitInfo) {
this._isMouseDown = true;
};
HeaderCheckBoxCellType.prototype.processMouseUp = function (hitInfo) {
if (this._isMouseDown) {
this.doFilter(hitInfo);
this._isMouseDown = false;
}
return true;
};
HeaderCheckBoxCellType.prototype.doFilter = function (hitInfo) {
var value = this.value, sheet = hitInfo.sheet, row = hitInfo.row, col = hitInfo.col, sheetArea = hitInfo.sheetArea;
var tag = sheet.getTag(row, col, sheetArea);
sheet.setTag(row, col, !tag, sheetArea);
var rowFilter = new spreadNS.Filter.HideRowFilter(new spreadNS.Range(-1, col, -1, 1));
sheet.rowFilter(rowFilter);
rowFilter.filterButtonVisible(false);
var condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, {
compareType: spreadNS.ConditionalFormatting.GeneralComparisonOperators.greaterThan,
expected: value
});
rowFilter.addFilterItem(col, condition);
if (!tag) {
rowFilter.filter();
} else {
rowFilter.unfilter();
}
};
// Define hyperlink cell type
export class SortHyperlinkCellType {
constructor() {
spreadNS.CellTypes.HyperLink.apply(this);
this.text("Sort");
}
}
SortHyperlinkCellType.prototype = new spreadNS.CellTypes.HyperLink();
SortHyperlinkCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
if (context) {
return {
x: x,
y: y,
row: context.row,
col: context.col,
cellRect: cellRect,
cellStyle: cellStyle,
sheetArea: context.sheetArea,
isReservedLocation: true,
sheet: context.sheet
};
}
return null;
};
SortHyperlinkCellType.prototype.processMouseDown = function (hitInfo) {
};
SortHyperlinkCellType.prototype.processMouseMove = function (hitInfo) {
};
SortHyperlinkCellType.prototype.processMouseUp = function (hitInfo) {
var sheet = hitInfo.sheet, sheetArea = hitInfo.sheetArea,
row = hitInfo.row, col = hitInfo.col;
var tag = !(sheet.getTag(row, col, sheetArea) || false);
sheet.setTag(row, col, tag, sheetArea);
sheet.sortRange(0, 0, sheet.getRowCount(), sheet.getColumnCount(), true, [{
index: col,
ascending: tag
}]);
};
SortHyperlinkCellType.prototype.processMouseEnter = function (hitInfo) {
};
SortHyperlinkCellType.prototype.processMouseLeave = function (hitInfo) {
};
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
