カスタムフィルタ | 機能紹介 | SpreadJS（スプレッドJS）チュートリアルデモアプリケーション

フィルタボタンが表示されるかどうかを取得または設定するには、filterButtonVisibleメソッドを使用します。次に、例を示します。行がフィルタリングされているかどうかを確認するには、isFilteredメソッドを使用します。また、isRowFilteredOutメソッドを使用すると、指定の行がフィルタリングによって除外されているかどうかを確認できます。isColumnFilteredメソッドを使用すると、指定の列がフィルタリングされているかどうかを確認できます。次に、例を示します。指定の列に対するフィルタ（条件の配列）を取得するには、getFilterItemsを使用します。また、getFilteredItemsを使用すると、適用済みのすべてのフィルタ条件を取得できます。次に、例を示します。フィルタを適用した行を並べ替えることができます。指定の列を指定の順序で並べ替えるには、sortColumnを使用します。現在の並べ替えの状態を取得するには、getSortStateを使用します。次に、例を示します。フィルタが不要になった場合は、特定のフィルタを削除するか、またはすべてのフィルタをクリアできます。次に、例を示します。

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"],[new Date('01/01/2017')],[new Date('02/01/2017')],[new Date('03/01/2017')],[new Date('04/01/2017')],[new Date('05/01/2017')],[new Date('06/01/2017')],[new Date('07/01/2017')],[new Date('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 conditionType = _getElementById("Condition1").value;
var element = _getElementById("optEnumType1");
updateEnumType(conditionType, element);
});
_getElementById("Condition2").addEventListener('change',function () {
var condition = _getElementById("Condition2").value;
var element = _getElementById("optEnumType2");
updateEnumType(condition, element);
});
function updateEnumType(conditionType, element) {
var data=[];
switch (conditionType) {
case "2":
data = ['EqualsTo','NotEqualsTo','BeginsWith','DoesNotBeginWith','EndsWith','DoesNotEndWith','Contains','DoesNotContain'];
updateEnumTypeList(element, data);
break;
case "3":
data = ['BackgroundColor','ForegroundColor'];
updateEnumTypeList(element, data);
break;
case "4":
data = ['Empty','NonEmpty','Error','NonError','Formula'];
updateEnumTypeList(element, data);
break;
case "5":
data = ['EqualsTo','NotEqualsTo','Before','BeforeEqualsTo','After','AfterEqualsTo'];
updateEnumTypeList(element, data);
break;
case "6":
data = ['Today','Yesterday','Tomorrow','Last7Days','ThisMonth','LastMonth','NextMonth','ThisWeek','LastWeek','NextWeek','fromDay','fromMonth','fromQuarter','fromWeek','fromYear'];
updateEnumTypeList(element, data);
break;
case "8":
data = ['Top','Bottom'];
updateEnumTypeList(element, data);
break;
default:    // case "0", "1", "7" same items
data = ['EqualsTo','NotEqualsTo','GreaterThan','GreaterThanOrEqualsTo','LessThan','LessThanOrEqualsTo'];
updateEnumTypeList(element, data);
break;
}
}
function getCondition(conditionType, compareType, value) {
var sheet = spread.getActiveSheet();
var condition;
var formula;
if ((value != null) && (value[0] == "=")) {
formula = value;
value = null;
}
else {
formula = null;
if (!isNaN(value)) {
value = parseFloat(value);
}
}
switch (conditionType) {
case "1":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.numberCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "2":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "3":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.colorCondition, {
compareType: compareType,
expected: value
});
break;
case "4":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.formulaCondition, {
customValueType: compareType,
formula: formula
});
break;
case "5":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "6":
if (compareType < 10) {
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.dateExCondition, {
expected: compareType,
});
}
else if (compareType == 10) {
condition = spreadNS.ConditionalFormatting.Condition.fromDay(value);
} else if (compareType == 11) {
condition = spreadNS.ConditionalFormatting.Condition.fromMonth(value);
} else if (compareType == 12) {
condition = spreadNS.ConditionalFormatting.Condition.fromQuarter(value);
} else if (compareType == 13) {
condition = spreadNS.ConditionalFormatting.Condition.fromWeek(value);
} else {
condition = spreadNS.ConditionalFormatting.Condition.fromYear(value);
}
break;
case "7":
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.textLengthCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
case "8":
var ranges = sheet.getSelections().slice(0);
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.top10Condition, {
type: compareType,
expected: value,
ranges: ranges
});
break;
default:    // case "0" same
condition = new spreadNS.ConditionalFormatting.Condition(spreadNS.ConditionalFormatting.ConditionType.cellValueCondition, {
compareType: compareType,
expected: value,
formula: formula
});
break;
}
return condition;
}
function getConditions() {
var radio1 = _getElementById("rdoAND").checked;
var radio2 = _getElementById("rdoOR").checked;
var conditionType1 = _getElementById("Condition1").value;
var conditionType2 = _getElementById("Condition2").value;
var compareType1 = parseInt(_getElementById("optEnumType1").value);
var compareType2 = parseInt(_getElementById("optEnumType2").value);
var value1 = _getElementById("txtFormulas1").value;
var value2 = _getElementById("txtFormulas2").value;
var con1 = getCondition(conditionType1, compareType1, value1);
var con2 = getCondition(conditionType2, compareType2, 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 selections = sheet.getSelections();
if (selections.length == 0) return;
var selection = selections[0];
// set filter
var hideRowFilter = new spreadNS.Filter.HideRowFilter(selection);
sheet.rowFilter(hideRowFilter);
var conditions = getConditions();
conditions.ignoreBlank(_getElementById('chkIgnoreBlank').checked);
hideRowFilter.addFilterItem(sheet.getActiveColumnIndex(), conditions);
// filter
hideRowFilter.filter((selection.col >= 0) ? selection.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 updateEnumTypeList(element, data){
element.innerHTML='';
for(var i=0;i<data.length;i++){
var option = document.createElement('option');
var attribute = document.createAttribute('value');
attribute.nodeValue = i;
option.setAttributeNode(attribute);
option.innerHTML = data[i];
element.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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/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:#F4F8EB;
}
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;
}