概述
本 Demo 展示了如何通过代码创建自定义筛选器。用户可以在界面中选择不同的条件类型、比较类型和比较值,并组合两个筛选条件(使用 AND 或 OR 逻辑),然后点击按钮应用筛选。
实现思路
创建包含数字、日期、字符串和带背景色单元格的示例数据
根据用户选择的条件类型动态更新比较类型选项列表
创建筛选条件对象,支持多种条件类型(单元格值、数字、文本、颜色、公式、日期等)
组合两个筛选条件为复合条件(使用 AND 或 OR 逻辑)
将筛选条件添加到行筛选器并应用筛选
代码解析
创建 HideRowFilter 并设置筛选条件
这段代码首先根据用户选择的区域创建 HideRowFilter,然后调用 addFilterItem 方法添加筛选条件,最后调用 filter 方法应用筛选。
创建不同类型的筛选条件
根据用户选择的条件类型,创建对应的 Condition 对象。compareType 指定比较方式(如等于、大于、小于),expected 指定期望值,formula 指定公式(如果值以 "=" 开头则使用公式)。
组合多个筛选条件
使用 relationCondition 类型可以组合两个条件,通过 compareType 指定逻辑运算符(AND 或 OR),item1 和 item2 分别指定两个条件对象。
运行效果
表格包含四列数据:数字(1-8)、日期(2017年1-8月)、字符串(姓名)、带背景色的单元格
在界面左侧选择条件类型(如"数字条件"、"文本条件"等)
选择比较类型(如"等于"、"大于"、"包含"等)
输入比较值或公式
可选择添加第二个条件,并设置逻辑关系(AND 或 OR)
点击"设置筛选器"按钮应用筛选,符合条件的行将显示
点击"清除筛选器"按钮清除所有筛选
API 参考
HideRowFilter 构造函数
range: 筛选范围,类型为 GC.Spread.Sheets.Range
addFilterItem 方法
col: 列索引
condition: 筛选条件对象
filter 方法
col: 要筛选的列索引
Condition 构造函数
conditionType: 条件类型枚举值
args: 参数对象,包含:
compareType: 比较类型
expected: 期望值
formula: 公式(可选)
item1、item2: 复合条件的子条件(用于 relationCondition)
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="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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>单元格值条件</option>
<option value="1">数字条件</option>
<option value="2">文本条件</option>
<option value="3">颜色条件</option>
<option value="4">公式条件</option>
<option value="5">日期条件</option>
<option value="6">扩展日期条件</option>
<option value="7">文本长度条件</option>
<option value="8">前10条件</option>
</select>
</div>
<div class="option-row">
<label>比较类型：</label>
<select id="optEnumType1">
<option value='0' selected>等于</option>
<option value='1'>不等于</option>
<option value='2'>大于</option>
<option value='3'>大于或等于</option>
<option value='4'>小于</option>
<option value='5'>小于或等于</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">与</label>
<input type="radio" value="Or" name="relation" id="rdoOR" />
<label for="rdoOR">或</label>
</div>
<div class="option-row">
<label>条件：</label>
<select id="Condition2">
<option value="0" selected>单元格值条件</option>
<option value="1">数字条件</option>
<option value="2">文本条件</option>
<option value="3">颜色条件</option>
<option value="4">公式条件</option>
<option value="5">日期条件</option>
<option value="6">扩展日期条件</option>
<option value="7">文本长度条件</option>
<option value="8">前10条件</option>
</select>
</div>
<div class="option-row">
<label>比较类型：</label>
<select id="optEnumType2">
<option value='0' selected>等于</option>
<option value='1'>不等于</option>
<option value='2'>大于</option>
<option value='3'>大于或等于</option>
<option value='4'>小于</option>
<option value='5'>小于或等于</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;
}
