Auto Extend Filter Range

Theme:

SpreadJS supports extending the filter range automatically by adding data or using copy-paste, drag-fill, and drag-drop actions below a filter range.

AutoExtendFilterRange will extend the filter range automatically below the origin filter range until there are all null values in the filter range columns.

AutoExtendFilterRange can only work in a normal worksheet filter, and will not work in table/pivotTable/tableSheet filter.

You can disable/enable the auto extend filter range by using allowAutoExtendFilterRange, which is false (disabled) by default.

When the allowAutoExtendFilterRange is true, users can get the extended filter range by rowFilter.extendedRange;

    var sheet = spread.getActiveSheet();
    spread.options.allowAutoExtendFilterRange = true; // enable the auto extend filter range.
    sheet.setArray(1,0,[
                    [1],
                    [2],
                    [3],
                    [4],
                    [5],
                    [6],
                    [7],
                    [8],
                    [9],
                ]);
    var hideRowFilter = new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(1,0,5,1));
    sheet.rowFilter(hideRowFilter);
    var basedRange = hideRowFilter.range; // {row: 1, rowCount: 5, col: 0, colCount: 1}
    var extendedRange = hideRowFilter.extendedRange; // get the extend range {row: 1, rowCount: 9, col: 0, colCount: 1}
    var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {compareType: GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo,expected: '5'}); // filterValue === 5
    hideRowFilter.addFilterItem(0,condition);
    hideRowFilter.filter(0); // the '5' is filtered.

Feedback

Submit and view feedback for

This Product This Page

AutoExtendFilterRange will extend the filter range automatically below the origin filter range until there are all null values in the filter range columns. AutoExtendFilterRange can only work in a normal worksheet filter, and will not work in table/pivotTable/tableSheet filter. You can disable/enable the auto extend filter range by using allowAutoExtendFilterRange, which is false (disabled) by default. When the allowAutoExtendFilterRange is true, users can get the extended filter range by rowFilter.extendedRange;

window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
initSpread(spread);
};
function initSpread(spread) {
var spreadNS = GC.Spread.Sheets;
spread.suspendPaint();
_bindEvent(spread);
var sheet = spread.getSheet(0);
var headerStyle = new spreadNS.Style();
headerStyle.font = "bold 11pt Calibri"
sheet.setStyle(1,1,headerStyle);
sheet.setStyle(1,2,headerStyle);
sheet.setStyle(1,3,headerStyle);
sheet.setStyle(1,4,headerStyle);
sheet.setColumnWidth(1, 120);
sheet.setColumnWidth(2, 120);
sheet.setColumnWidth(3, 120);
sheet.setColumnWidth(4, 120);
sheet.getCell(1, 1).text("First Name");
sheet.getCell(1, 2).text("Last Name");
sheet.getCell(1, 3).text("Score");
sheet.getCell(1, 4).text("Position");
sheet.getCell(2, 1).text("Alexa");
sheet.getCell(2, 2).text("Wilder");
sheet.getCell(2, 3).text("90");
sheet.getCell(2, 4).text("Web Developer");
sheet.getCell(3, 1).text("Victor");
sheet.getCell(3, 2).text("Wooten");
sheet.getCell(3, 3).text("70");
sheet.getCell(3, 4).text(".NET Developer");
sheet.getCell(4, 1).text("Ifeoma");
sheet.getCell(4, 2).text("Mays");
sheet.getCell(4, 3).text("85");
sheet.getCell(4, 4).text("Sales Manager");
var basedFilterRange = new spreadNS.Range(2,1,2,4);
var rowFilter = new spreadNS.Filter.HideRowFilter(basedFilterRange);
sheet.rowFilter(rowFilter);
spread.resumePaint();
}
function _bindEvent (spread) {
_getElementById("allowAutoExtendFilterRange").addEventListener('change', function() {
spread.options.allowAutoExtendFilterRange = _getElementById("allowAutoExtendFilterRange").checked;
});
}
function _getElementById(id){
return document.getElementById(id);
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.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>Change the state of allowAutoExtendFilterRange and open the filter dialog to see how the filter range extend works.</p>
<div class="option-group">
<input type="checkbox" id="allowAutoExtendFilterRange"/>
<label for="allowAutoExtendFilterRange">Allow Auto Extend Filter Range</label>
</div>
</div>
</div></body>
</html>

.sample {
position: relative;
height: 100%;
overflow: auto;
}
.sample::after {
display: block;
content: "";
clear: both;
}
.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;
}
.option-group {
margin-bottom: 6px;
}
label {
display: inline-block;
min-width: 90px;
margin-bottom: 6px;
}
select {
padding: 4px 6px;
}
p {
padding: 0 0 12px;
margin: 0;
}
.options-toggle {
display: none;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}