カスタムフィルタ

設定した行フィルタは、カスタマイズすることもできます。この機能は、フィルタの動作を変更するための特定の条件があり、アプリケーションのニーズに適合させるために、このフィルタをカスタマイズしたい場合に便利です。

<p>フィルタボタンが表示されるかどうかを取得または設定するには、<strong>filterButtonVisible</strong>メソッドを使用します。次に、例を示します。</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> range = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Range(<span class="hljs-number">-1</span>, <span class="hljs-number">0</span>, <span class="hljs-number">-1</span>, <span class="hljs-number">2</span>); <span class="hljs-keyword">var</span> rowFilter = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Filter.HideRowFilter(range); sheet.rowFilter(rowFilter); rowFilter.filterButtonVisible(<span class="hljs-literal">false</span>); </code></pre> <p>行がフィルタリングされているかどうかを確認するには、<strong>isFiltered</strong>メソッドを使用します。また、<strong>isRowFilteredOut</strong>メソッドを使用すると、指定の行がフィルタリングによって除外されているかどうかを確認できます。<strong>isColumnFiltered</strong>メソッドを使用すると、指定の列がフィルタリングされているかどうかを確認できます。次に、例を示します。</p> <pre><code class="hljs js language-js"> sheet.setValue(<span class="hljs-number">0</span>,<span class="hljs-number">0</span>,<span class="hljs-number">1</span>); sheet.setValue(<span class="hljs-number">1</span>,<span class="hljs-number">0</span>,<span class="hljs-number">2</span>); sheet.setValue(<span class="hljs-number">2</span>,<span class="hljs-number">0</span>,<span class="hljs-number">3</span>); <span class="hljs-keyword">var</span> range = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Range(<span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>, <span class="hljs-number">-1</span>); sheet.rowFilter(<span class="hljs-keyword">new</span> GC.Spread.Sheets.Filter.HideRowFilter(range)); <span class="hljs-keyword">var</span> compareType = GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo; <span class="hljs-keyword">var</span> condition = <span class="hljs-keyword">new</span> GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {<span class="hljs-attr">compareType</span>: compareType, <span class="hljs-attr">expected</span>: <span class="hljs-string">'3'</span>}); <span class="hljs-keyword">var</span> rowfilter = sheet.rowFilter(); rowfilter.addFilterItem(<span class="hljs-number">0</span>, condition); rowfilter.filter(<span class="hljs-number">0</span>); rowfilter.isFiltered(); <span class="hljs-comment">// true</span> rowfilter.isColumnFiltered(<span class="hljs-number">0</span>); <span class="hljs-comment">// true</span> rowfilter.isRowFilteredOut(<span class="hljs-number">2</span>); <span class="hljs-comment">// false</span> </code></pre> <p>指定の列に対するフィルタ(条件の配列)を取得するには、<strong>getFilterItems</strong>を使用します。また、<strong>getFilteredItems</strong>を使用すると、適用済みのすべてのフィルタ条件を取得できます。次に、例を示します。</p> <pre><code class="hljs js language-js"> rowfilter.getFilterItems(<span class="hljs-number">0</span>); rowfilter.getFilteredItems(); </code></pre> <p>フィルタを適用した行を並べ替えることができます。指定の列を指定の順序で並べ替えるには、<strong>sortColumn</strong>を使用します。現在の並べ替えの状態を取得するには、<strong>getSortState</strong>を使用します。次に、例を示します。</p> <pre><code class="hljs js language-js"> rowfilter.SortColumns(<span class="hljs-number">0</span>, <span class="hljs-literal">true</span>); <span class="hljs-comment">// 昇順で並べ替えます。</span> rowfilter.getSortState() === GC.Spread.Sheets.SortState.ascending; <span class="hljs-comment">// true</span> </code></pre> <p>フィルタが不要になった場合は、特定のフィルタを削除するか、またはすべてのフィルタをクリアできます。次に、例を示します。</p> <pre><code class="hljs js language-js"> rowfilter.removeFilterItems(<span class="hljs-number">0</span>); rowfilter.unfilter(<span class="hljs-number">0</span>); rowfilter.reset(); </code></pre>
var spreadNS = GC.Spread.Sheets; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); spread.suspendPaint(); initSpread(spread); spread.resumePaint(); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.setArray(1, 1, [ ["Number"],[1],[2],[3],[4],[5],[6],[7],[8] ]) sheet.setColumnWidth(2, 120); sheet.setArray(1, 2, [ ["Date"],['01/01/2017'],['02/01/2017'],['03/01/2017'],['04/01/2017'],['05/01/2017'],['06/01/2017'],['07/01/2017'],['08/01/2017'] ]) sheet.setArray(1, 3, [ ["String"],["Abby"],["Aimee"],["Alisa"],["Angelia"],["Anne"],["Bobe"],["Jack"],["Grace"] ]) var backColorArray = ['yellow','red','green','blue','orange','purple','pink','grey']; for(var i=0;i<backColorArray.length;i++){ sheet.getCell(2+i,4).backColor(backColorArray[i]); } _getElementById("Condition1").addEventListener('change',function () { var condition = _getElementById("Condition1").value; var type = _getElementById("optEnumType1"); setEnumType(condition, type); }); _getElementById("Condition2").addEventListener('change',function () { var condition = _getElementById("Condition2").value; var type = _getElementById("optEnumType2"); setEnumType(condition, type); }); function setEnumType(condition, type) { var data=[]; switch (condition) { case "0": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "1": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "2": data = ['EqualsTo','NotEqualsTo','BeginsWith','DoesNotBeginWith','EndsWith','DoesNotEndWith','Contains','DoesNotContain']; _appendChild(type,data); break; case "3": data = ['BackgroundColor','ForegroundColor']; _appendChild(type,data); break; case "4": data = ['Empty','NonEmpty','Error','NonError','Formula']; _appendChild(type,data); break; case "5": data = ['EqualsTo','NotEqualsTo','Before','BeforeEqualsTo','After','AfterEqualsTo']; _appendChild(type,data); break; case "6": data = ['Today','Yesterday','Tomorrow','Last7Days','ThisMonth','LastMonth','NextMonth','ThisWeek','LastWeek','NextWeek','fromDay','fromMonth','fromQuarter','fromWeek','fromYear']; _appendChild(type,data); break; case "7": data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; case "8": data = ['Top','Bottom']; _appendChild(type,data); break; default: data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo']; _appendChild(type,data); break; } } function getConditionBase(condition, type, value) { var sheet = spread.getActiveSheet(); var condtionbase; var formula; if ((value != null) && (value[0] == "=")) { formula = value; value = null; } else { formula = null; if (!isNaN(value)) { value = parseFloat(value); } } switch (condition) { case "0": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, { compareType: type, expected: value, formula: formula }); break; case "1": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, { compareType: type, expected: value, formula: formula }); break; case "2": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, { compareType: type, expected: value, formula: formula }); break; case "3": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, { compareType: type, expected: value }); break; case "4": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, { customValueType: type, formula: formula }); break; case "5": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateCondition, { compareType: type, expected: value, formula: formula }); break; case "6": if (type < 10) { condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateExCondition, { expected: type, }); } else if (type == 10) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromDay(value); } else if (type == 11) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromMonth(value); } else if (type == 12) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromQuarter(value); } else if (type == 13) { condtionbase = spreadNS.ConditionalFormatting.Condition.fromWeek(value); } else { condtionbase = spreadNS.ConditionalFormatting.Condition.fromYear(value); } break; case "7": condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textLengthCondition, { compareType: type, expected: value, formula: formula }); break; case "8": var ranges = sheet.getSelections().slice(0); condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.top10Condition, { type: type, expected: value, ranges: ranges }); break; default: condtionbase = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, { compareType: type, expected: value, formula: formula }); break; } return condtionbase; } function getConditions() { var radio1 = _getElementById("rdoAND").checked; var radio2 = _getElementById("rdoOR").checked; var condition1 = _getElementById("Condition1").value; var condition2 = _getElementById("Condition2").value; var type1 = parseInt(_getElementById("optEnumType1").value); var type2 = parseInt(_getElementById("optEnumType2").value); var value1 = _getElementById("txtFormulas1").value; var value2 = _getElementById("txtFormulas2").value; var con1 = getConditionBase(condition1, type1, value1); var con2 = getConditionBase(condition2, type2, value2); var conditions; if (value2 != null || value2 != "" || value2 != undefined) { if (radio1) { conditions = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.relationCondition, { compareType: spreadNS.ConditionalFormatting.LogicalOperators.and, item1: con1, item2: con2 }); } else if (radio2) { conditions = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.relationCondition, { compareType: spreadNS.ConditionalFormatting.LogicalOperators.or, item1: con1, item2: con2 }); } else { conditions = con1; } } else { conditions = con1; } return conditions; } _getElementById("btnHideRowFilter").addEventListener('click',function () { var sheet = spread.getActiveSheet(); var sels = sheet.getSelections(); if (sels.length == 0) return; var sel = sels[0]; // set filter var _drf = new spreadNS.Filter.HideRowFilter(sel); sheet.rowFilter(_drf); var nc = getConditions(); nc.ignoreBlank(_getElementById('chkIgnoreBlank').checked); _drf.addFilterItem(sheet.getActiveColumnIndex(), nc); // filter _drf.filter((sel.col >= 0) ? sel.col : 0); sheet.invalidateLayout(); sheet.repaint(); }); _getElementById("btnClearFilter").addEventListener('click',function () { var sheet = spread.getActiveSheet(); sheet.rowFilter(null); sheet.invalidateLayout(); sheet.repaint(); }); } function _getElementById(id){ return document.getElementById(id); } function _appendChild(type,data){ type.innerHTML=''; for(var i=0;i<data.length;i++){ var option = document.createElement('option'); var value = document.createAttribute('value'); value.nodeValue = i; option.setAttributeNode(value); option.innerHTML = data[i]; type.appendChild(option); } }
<!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" class="sample-spreadsheets"></div> <div class="options-container"> <p>以下のオプションを使用して、Spreadにカスタムフィルタを作成できます。列Dのいくつかの名前を強調表示して、[フィルタを設定]をクリックすると、これらの名前に基づき、表示する行を選択するためのフィルタを作成できます。</p> <div class="option-row"> <label>条件:</label> <select id="Condition1"> <option value="0" selected>CellValueCondition</option> <option value="1">NumberCondition</option> <option value="2">TextCondition</option> <option value="3">ColorCondition</option> <option value="4">FormulaCondition</option> <option value="5">DateCondition</option> <option value="6">DateExCondition</option> <option value="7">TextLengthCondition</option> <option value="8">Top10Condition</option> </select> </div> <div class="option-row"> <label>比較の種類:</label> <select id="optEnumType1"> <option value='0' selected>EqualsTo</option> <option value='1'>NotEqualsTo</option> <option value='2'>GreaterThan</option> <option value='3'>GreaterThanOrEqualsTo</option> <option value='4'>LessThan</option> <option value='5'>LessThanOrEqualsTo</option> </select> </div> <div class="option-row"> <label>比較する値または数式:</label> <input id="txtFormulas1" type="text" /> </div> <div class="option-row"> <label></label> <input type="radio" value="And" name="relation" id="rdoAND" /> <label for="rdoAND" >And</label> <input type="radio" value="Or" name="relation" id="rdoOR" /> <label for="rdoOR">OR</label> </div> <div class="option-row"> <label>条件:</label> <select id="Condition2"> <option value="0" selected>CellValueCondition</option> <option value="1">NumberCondition</option> <option value="2">TextCondition</option> <option value="3">ColorCondition</option> <option value="4">FormulaCondition</option> <option value="5">DateCondition</option> <option value="6">DateExCondition</option> <option value="7">TextLengthCondition</option> <option value="8">Top10Condition</option> </select> </div> <div class="option-row"> <label>比較の種類:</label> <select id="optEnumType2"> <option value='0' selected>EqualsTo</option> <option value='1'>NotEqualsTo</option> <option value='2'>GreaterThan</option> <option value='3'>GreaterThanOrEqualsTo</option> <option value='4'>LessThan</option> <option value='5'>LessThanOrEqualsTo</option> </select> </div> <div class="option-row"> <label>比較する値または数式:</label> <input id="txtFormulas2" type="text" /> </div> <div class="option-row"> <label></label> <input type="checkbox" id="chkIgnoreBlank" /> <label for="chkIgnoreBlank">空白を無視</label> </div> <div class="option-row"> <input type="button" value="フィルタを設定" id="btnHideRowFilter" /> <input type="button" value="フィルタをクリア" id="btnClearFilter" /> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } p{ padding:2px 10px; background-color:lavender; } input, select { width: 100%; padding: 4px 6px; box-sizing: border-box; } label { display:block; margin-bottom: 6px; } input[type="checkbox"], input[type="radio"] { display: inline-block; width: auto; } input[type="checkbox"]+label, input[type="radio"]+label { display: inline-block; } input[type="button"] { display: block; margin: 0 0 6px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }