カスタムヘッダセル

SpreadJSでは、行ヘッダおよび列ヘッダでのカスタムセル型の設定がサポートされます。これにより、行および列データを効率的に処理できます。ヘッダー自体に特定の機能を追加できるので、たとえばコマンドボタンやチェックボックスを追加して、項目のフィルタ機能を複数の列や行に適用できます。

<p>カスタムヘッダセル型を作成するには、<strong>RowHeader</strong>または<strong>ColumnHeader</strong>、あるいはその他の組み込みセル型に基づき、独自のヘッダセル型を作成します。次に、例を示します。</p> <pre><code class="hljs js language-js"> <span class="hljs-function"><span class="hljs-keyword">function</span> <span class="hljs-title">TopItemsCellType</span>(<span class="hljs-params">count</span>) </span>{ GC.Spread.Sheets.CellTypes.Button.apply(<span class="hljs-keyword">this</span>); <span class="hljs-comment">// 処理を追加します。</span> <span class="hljs-keyword">this</span>.text(<span class="hljs-string">"Top "</span> + count); } TopItemsCellType.prototype = <span class="hljs-keyword">new</span> GC.Spread.Sheets.CellTypes.Button(); </code></pre> <p>作成したカスタムヘッダセル型に対し、マウスイベントを処理するための以下のメソッドを実装します。</p> <ul> <li><strong>getHitInfo: processMouseメソッドに提供されるヒット情報を含んだオブジェクトを取得します。</strong></li> <li><strong>processMouseDown: マウスボタンダウンアクションを提供します。</strong></li> <li><strong>processMouseMove: マウスボタンムーヴアクションを提供します。</strong></li> <li><strong>processMouseUp: マウスボタンアップアクションを提供します。</strong></li> <li><strong>processMouseEnter: マウスエンターアクションを提供します。</strong></li> <li><strong>processMouseLeave: マウスリーヴアクションを提供します。</strong></li> </ul> <p>次に、getHitInfoメソッドの使用例を示します。</p> <pre><code class="hljs js language-js"> TopItemsCellType.prototype.getHitInfo = <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">x, y, cellStyle, cellRect, context</span>) </span>{ <span class="hljs-keyword">var</span> self = <span class="hljs-keyword">this</span>; <span class="hljs-keyword">var</span> leftX = cellRect.x + self.marginLeft(), rightX = cellRect.x + cellRect.width - self.marginRight(), topY = cellRect.y + self.marginTop(), bottomY = cellRect.y + cellRect.height - self.marginBottom(); <span class="hljs-keyword">var</span> info = { <span class="hljs-attr">x</span>: x, <span class="hljs-attr">y</span>: y, <span class="hljs-attr">row</span>: context.row, <span class="hljs-attr">col</span>: context.col, <span class="hljs-attr">cellRect</span>: cellRect, <span class="hljs-attr">sheetArea</span>: context.sheetArea, <span class="hljs-attr">sheet</span>: context.sheet }; <span class="hljs-keyword">if</span> (leftX &lt;= x &amp;&amp; x &lt;= rightX &amp;&amp; topY &lt;= y &amp;&amp; y &lt;= bottomY) { info.isReservedLocation = <span class="hljs-literal">true</span>; } <span class="hljs-keyword">return</span> info; }; </code></pre> <p>getHitInfoメソッドをオーバーライドすると、行および列ヘッダセルのCheckBox、Button、およびHyperLinkに対し、<strong>ButtonClicked</strong>イベントが有効になります。</p> <p><strong>ButtonClicked</strong>イベントは、Spreadまたはシートに連結して処理できます。次に、例を示します。</p> <pre><code class="hljs js language-js"> spread.bind(GC.Spread.Sheets.Events.ButtonClicked, <span class="hljs-function"><span class="hljs-keyword">function</span> (<span class="hljs-params">e, args</span>) </span>{ <span class="hljs-keyword">var</span> sheet = args.sheet, sheetArea = args.sheetArea, row = args.row, col = args.col, cellType = sheet.getCellType(row, col, sheetArea); <span class="hljs-keyword">if</span> (cellType <span class="hljs-keyword">instanceof</span> TopItemsCellType) { <span class="hljs-comment">// 処理を追加します。</span> } }); </code></pre>
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { // Define highlight cell types function HighlightColumnItemsCellType() { 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; } }; function HighlightRowItemsCellType() { 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 function TopItemsCellType(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 function HeaderCheckBoxCellType(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.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 function SortHyperlinkCellType() { 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) { }; var rowCount = 50, columnCount = 20; var 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, function (e, args) { var 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) { var count = cellType.count; sheet.setTag(row, col, !tag, sheetArea); var range = new spreadNS.Range(-1, col, -1, 1); var rowFilter = new spreadNS.Filter.HideRowFilter(range); sheet.rowFilter(rowFilter); rowFilter.filterButtonVisible(false); var 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(); } } }); } ; function fillSampleData(sheet, range) { for (var i = 0; i < range.rowCount; i++) { for (var j = 0; j < range.colCount; j++) { sheet.setValue(range.row + i, range.col + j, Math.ceil(Math.random() * 300) - 100); } } } ;
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="ja-jp" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/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" style="width:100%; height:100%;border: 1px solid gray;"></div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }