[]
Sheets.Slicers.TableSlicerData
↳ TableSlicerData
• new TableSlicerData(table
)
Represents table slicer data.
example
//This example creates a slicer for the table.
//create table
var dataSource = [
{ Name: "Bob", City: "NewYork", Birthday: "1968/6/8" },
{ Name: "Betty", City: "NewYork", Birthday: "1972/7/3" },
{ Name: "Alice", City: "Washington", Birthday: "2012/2/15" },
];
var table = activeSheet.tables.addFromDataSource("table1", 1, 1, dataSource);
var slicerData = new GC.Spread.Sheets.Slicers.TableSlicerData(table)
//Set slicer data to item slicer.
var slicer = new GC.Spread.Sheets.Slicers.ItemSlicer("slicer", slicerData, "Name");
//Add the item slicer to the dom tree.
//The "slicerHost" is the div you want to add the slicer's dom to.
$("#slicerHost").append(slicer.getDOMElement());
Name | Type | Description |
---|---|---|
table |
Table |
The table. |
• columnNames: string
[]
Indicates the column names for the general slicer data.
• data: any
[][]
Indicates the data source for general slicer data.
▸ aggregateData(columnName
, aggregateType
, range?
): number
Aggregates the data by the specified column name.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.aggregateData('Salary', GC.Spread.Slicers.SlicerAggregateType.average));
console.log(slicerData.aggregateData('Salary', GC.Spread.Slicers.SlicerAggregateType.count, {min: 8000, max: 20000}));
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
aggregateType |
SlicerAggregateType |
The aggregate type. |
range? |
ISlicerRangeConditional |
The specific range. |
number
The aggregated data.
GeneralSlicerData.aggregateData
▸ attachListener(listener
): void
Attaches the listener. SlicerData will call the corresponding interface at the appropriate time.
example
//Define data source.
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
//Define custom slicer.
function MySlicer(container) {
this.container = container;
this.slicerData = null;
this.columnName = null;
}
MySlicer.prototype.setData = function (slicerData, columnName) {
this.slicerData = slicerData;
this.columnName = columnName;
// attach listener here
this.slicerData.attachListener(this);
this.onDataLoaded();
}
MySlicer.prototype.onDataLoaded = function () {
//create slicer dom tree.
var columnName = this.columnName,
exclusiveData = this.slicerData.getExclusiveData(columnName);
$(this.container).append($('<span>' + this.columnName + ':</span>' + '<br />'));
var domString = "";
for (var i = 0; i < exclusiveData.length; i++) {
domString += '<input type="checkbox" name="' + columnName + '" value="' + exclusiveData[i] + '">';
domString += '<span>' + exclusiveData[i] + '</span>';
domString += '<br />';
}
$(this.container).append($(domString));
//attach events to dom.
var self = this;
$("[name='" + self.columnName + "']").change(function () {
var slicer = self,
exclusiveData = slicer.slicerData.getExclusiveData(slicer.columnName),
parent = $(this).parent(),
items = parent.children(),
indexes = [];
for (var i = 0, length = items.length; i < length; i++) {
if (items[i].checked) {
var value = items[i].value;
if (!isNaN(parseInt(value))) {
value = parseInt(value);
}
indexes.push(exclusiveData.indexOf(value))
}
}
if (indexes.length === 0) {
slicer.slicerData.doUnfilter(slicer.columnName);
} else {
slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes });
}
});
};
MySlicer.prototype.onFiltered = function () {
//Sync the status if the data has been filtered by the Spread.Sheets table.
var slicerData = this.slicerData;
var exclusiveIndexes = slicerData.getFilteredIndexes(this.columnName);
$.each($("#slicerContainer").children("input"), function (i, input) {
});
}
MySlicer.prototype.onColumnsRemoved = function (columnName) {
if (columnName === this.columnName) {
this.slicerData.detachListener(this);
this.slicerData = null;
$("#slicerContainer").remove();
}
}
//create a custom slicer and add this slicer to the "slicerContainer" div.
var slicer = new MySlicer($("#slicerContainer")[0]);
slicer.setData(slicerData, 'Name');
Name | Type | Description |
---|---|---|
listener |
ISlicerListener |
The listener. |
void
GeneralSlicerData.attachListener
▸ clearPreview(): void
Clears the preview filter state. you can check whether slicer is doing filter in preview with slicerData.inPreview() API, If you set doFilter() with isPreview flag to true, (like slicerData.doFilter('Name', {exclusiveRowIndexes: [1]}, true);) you can clear the preview status by clearPreview() API. which will remove the preview filter status.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Bob', text: 'Bob' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 8000, text: '5 500' }
],
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
slicerData.doFilter('Name', {exclusiveRowIndexes: [1, 2]});
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // [0]
console.log(slicerData.inPreview()); // false
slicerData.clearPreview();
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // [0]
slicerData.doUnfilter('Name');
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
slicerData.doFilter('Name', {exclusiveRowIndexes: [1, 2]}, true);
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // [0]
console.log(slicerData.inPreview()); // true
slicerData.clearPreview();
console.log(slicerData.inPreview()); // false
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
void
GeneralSlicerData.clearPreview
▸ detachListener(listener
): void
Detaches the listener.
example
//Define data source.
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
//Define custom slicer.
function MySlicer(container) {
this.container = container;
this.slicerData = null;
this.columnName = null;
}
MySlicer.prototype.setData = function (slicerData, columnName) {
this.slicerData = slicerData;
this.columnName = columnName;
// attach listener here
this.slicerData.attachListener(this);
this.onDataLoaded();
}
MySlicer.prototype.onDataLoaded = function () {
//create slicer dom tree.
var columnName = this.columnName,
exclusiveData = this.slicerData.getExclusiveData(columnName);
$(this.container).append($('<span>' + this.columnName + ':</span>' + '<br />'));
var domString = "";
for (var i = 0; i < exclusiveData.length; i++) {
domString += '<input type="checkbox" name="' + columnName + '" value="' + exclusiveData[i] + '">';
domString += '<span>' + exclusiveData[i] + '</span>';
domString += '<br />';
}
$(this.container).append($(domString));
//attach events to dom.
var self = this;
$("[name='" + self.columnName + "']").change(function () {
var slicer = self,
exclusiveData = slicer.slicerData.getExclusiveData(slicer.columnName),
parent = $(this).parent(),
items = parent.children(),
indexes = [];
for (var i = 0, length = items.length; i < length; i++) {
if (items[i].checked) {
var value = items[i].value;
if (!isNaN(parseInt(value))) {
value = parseInt(value);
}
indexes.push(exclusiveData.indexOf(value))
}
}
if (indexes.length === 0) {
slicer.slicerData.doUnfilter(slicer.columnName);
} else {
slicer.slicerData.doFilter(slicer.columnName, { exclusiveRowIndexes: indexes });
}
});
};
MySlicer.prototype.onFiltered = function () {
//Sync the status if the data has been filtered by the Spread.Sheets table.
var slicerData = this.slicerData;
var exclusiveIndexes = slicerData.getFilteredIndexes(this.columnName);
$.each($("#slicerContainer").children("input"), function (i, input) {
});
}
MySlicer.prototype.onColumnRemoved = function (columnName) {
if (columnName === this.columnName) {
this.slicerData.detachListener(this);
this.slicerData = null;
$("#slicerContainer").remove();
}
}
//create a custom slicer and add this slicer to the "slicerContainer" div.
var slicer = new MySlicer($("#slicerContainer")[0]);
slicer.setData(slicerData, 'Name');
Name | Type | Description |
---|---|---|
listener |
ISlicerListener |
The listener. |
void
GeneralSlicerData.detachListener
▸ doFilter(columnName
, conditional
, isPreview?
): void
Filters the data that corresponds to the specified column name and exclusive data indexes.
example
var spread = new GC.Spread.Sheets.Workbook('ss');
var activeSheet = spread.getActiveSheet();
var dataSource = [
{ Name: "Bob", City: "NewYork", Birthday: "1968/6/8" },
{ Name: "Betty", City: "NewYork", Birthday: "1972/7/3" },
{ Name: "Alice", City: "Washington", Birthday: "2012/2/15" },
];
var table = activeSheet.tables.addFromDataSource("table1", 1, 1, dataSource);
var slicerData = new GC.Spread.Sheets.Slicers.TableSlicerData(table);
slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all); // []
slicerData.doFilter('Name', {exclusiveRowIndexes: [0]});
slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all); // [1, 2]
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
conditional |
ISlicerConditional |
The filter conditional. |
isPreview? |
boolean |
- |
void
▸ doUnfilter(columnName
): void
Unfilter the data that corresponds to the specified column name.
example
var spread = new GC.Spread.Sheets.Workbook('ss');
var activeSheet = spread.getActiveSheet();
var dataSource = [
{ Name: "Bob", City: "NewYork", Birthday: "1968/6/8" },
{ Name: "Betty", City: "NewYork", Birthday: "1972/7/3" },
{ Name: "Alice", City: "Washington", Birthday: "2012/2/15" },
];
var table = activeSheet.tables.addFromDataSource("table1", 1, 1, dataSource);
var slicerData = new GC.Spread.Sheets.Slicers.TableSlicerData(table);
slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all); // []
slicerData.doFilter('Name', {exclusiveRowIndexes: [0]});
slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all); // [1, 2]
slicerData.doUnfilter('Name');
slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all); // []
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
void
▸ getColumnIndex(columnName
): number
Gets the column index by the specified column name.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getColumnIndex('Name')); // 0
console.log(slicerData.getColumnIndex('Unknown')); // -1
console.log(slicerData.getColumnIndex('Salary')); // 3
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
number
The column index.
GeneralSlicerData.getColumnIndex
▸ getData(columnName
, range?
): string
[]
Gets the data by the specified column name.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getData('Name')); // ['Bob', 'Betty', 'Alice', 'Chris', 'James']
console.log(slicerData.getData('Salary')); // ['10 000', '8 000', '5 500', '6 200', '16 150']
console.log(slicerData.getData('Salary', {min: 5000, max: 10000})); // ['5 500', '6 200', '8 000', '10 000']
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
range? |
ISlicerRangeConditional |
- |
string
[]
The data that corresponds to the specified column name.
▸ getExclusiveData(columnName
): any
[]
Gets the exclusive data by the specified column name.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Bob', text: 'Bob' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 8000, text: '8 000' }
],
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getData('Name')); // ['Bob', 'Betty', 'Bob']
console.log(slicerData.getExclusiveData('Name')); // ['Bob', 'Betty']
console.log(slicerData.getData('Salary')); // ['10 000', '8 000', '8 000']
console.log(slicerData.getExclusiveData('Salary')); // ['10 000', '8 000']
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
any
[]
The exclusive data that corresponds to the specified column name.
GeneralSlicerData.getExclusiveData
▸ getExclusiveRowIndex(columnName
, rowIndex
): number
Gets the exclusive data index by the specified column name and data index.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Bob', text: 'Bob' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 8000, text: '8 000' }
],
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getRowIndexes('Name', 0)); // [0, 2]
console.log(slicerData.getExclusiveRowIndex('Name', 0)); // 0
console.log(slicerData.getExclusiveRowIndex('Name', 1)); // 1
console.log(slicerData.getExclusiveRowIndex('Name', 2)); // 0
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
rowIndex |
number |
The index of the data. |
number
The exclusive data index that corresponds to the specified column name and data index.
GeneralSlicerData.getExclusiveRowIndex
▸ getFilteredIndexes(columnName
): number
[]
Gets the filtered exclusive data indexes by the specified column name.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getFilteredIndexes('Name')); // [0, 1, 2, 3, 4]
slicerData.doFilter('Name', {exclusiveRowIndexes: [1, 2]});
console.log(slicerData.getFilteredIndexes('Name')); // [1, 2]
slicerData.doUnfilter('Name');
console.log(slicerData.getFilteredIndexes('Name')); // [0, 1, 2, 3, 4]
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
number
[]
The filtered exclusive data indexes that correspond to the specified column name.
GeneralSlicerData.getFilteredIndexes
▸ getFilteredOutIndexes(columnName
, filteredOutDataType
): number
[]
Gets the filtered out exclusive data indexes by the specified column name.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
slicerData.doFilter('Name', {exclusiveRowIndexes: [1, 2]});
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // [0, 3, 4]
slicerData.doUnfilter('Name');
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
filteredOutDataType |
FilteredOutDataType |
Indicates the kind of filtered out exclusive data index that should be included in the result. |
number
[]
The filtered out exclusive data indexes that correspond to the specified column name.
GeneralSlicerData.getFilteredOutIndexes
▸ getFilteredOutRanges(columnName
): ISlicerRangeConditional
[]
Gets the filtered out ranges by other columns.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
slicerData.doFilter('Salary', {
ranges: [
{ min: 5000, max: 10000 },
{ min: 5000, max: 200000 },
{ min: 60000, max: 61000 }
]
});
console.log(slicerData.getFilteredOutRanges('Salary')); // [{min: 60000, max: 61000}]
console.log(slicerData.getFilteredRanges('Salary')); // [{min: 5000, max: 10000}, {min: 5000, max: 200000}]
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
The filtered out ranges by other columns that correspond to the specified column name.
GeneralSlicerData.getFilteredOutRanges
▸ getFilteredOutRowIndexes(): number
[]
Gets the filtered out row indexes.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
slicerData.doFilter('Salary', {
exclusiveRowIndexes: [0, 1, 2]
});
console.log(slicerData.getFilteredOutRowIndexes('Salary')); // [3, 4]
console.log(slicerData.getFilteredRowIndexes('Salary')); // [0, 1, 2]
number
[]
The filtered out row indexes.
GeneralSlicerData.getFilteredOutRowIndexes
▸ getFilteredRanges(columnName
): ISlicerRangeConditional
[]
Gets the filtered ranges by the specified column name.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
slicerData.doFilter('Salary', {
ranges: [
{ min: 5000, max: 10000 },
{ min: 5000, max: 200000 },
{ min: 60000, max: 61000 }
]
});
console.log(slicerData.getFilteredOutRanges('Salary')); // [{min: 60000, max: 61000}]
console.log(slicerData.getFilteredRanges('Salary')); // [{min: 5000, max: 10000}, {min: 5000, max: 200000}]
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
The filtered ranges that correspond to the specified column name.
GeneralSlicerData.getFilteredRanges
▸ getFilteredRowIndexes(): number
[]
Gets the filtered row indexes.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Alice', text: 'Alice' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 5500, text: '5 500' }
],
[
{ value: 'Chris', text: 'Chris' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2001/9/2"), text: '9/2/2001' },
{ value: 6200, text: '6 200' }
],
[
{ value: 'James', text: 'James' },
{ value: 'Phoenix', text: 'Phoenix' },
{ value: new Date("1995/11/22"), text: '11/22/1995' },
{ value: 16150, text: '16 150' }
]
], ["Name", "City", "Birthday", "Salary"]
);
slicerData.doFilter('Salary', {
exclusiveRowIndexes: [0, 1, 2]
});
console.log(slicerData.getFilteredOutRowIndexes('Salary')); // [3, 4]
console.log(slicerData.getFilteredRowIndexes('Salary')); // [0, 1, 2]
number
[]
The filtered row indexes.
GeneralSlicerData.getFilteredRowIndexes
▸ getRowIndexes(columnName
, exclusiveRowIndex
): number
[]
Gets the data indexes by the specified column name and exclusive data index.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Bob', text: 'Bob' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 8000, text: '8 000' }
],
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getRowIndexes('Name', 0)); // [0, 2]
console.log(slicerData.getRowIndexes('Name', 1)); // [1]
console.log(slicerData.getRowIndexes('Name', 2)); // undefined
console.log(slicerData.getRowIndexes('Salary', 1)); // [1, 2]
Name | Type | Description |
---|---|---|
columnName |
string |
The column name. |
exclusiveRowIndex |
number |
The index of the exclusive data. |
number
[]
The data indexes that correspond to the specified column name and exclusive data index.
GeneralSlicerData.getRowIndexes
▸ getTable(): Table
Gets the table of the table slicer data.
example
var activeSheet = spread.getActiveSheet();
var dataSource = [
{ Name: "Bob", City: "NewYork", Birthday: "1968/6/8" },
{ Name: "Betty", City: "NewYork", Birthday: "1972/7/3" },
{ Name: "Alice", City: "Washington", Birthday: "2012/2/15" },
];
var table = activeSheet.tables.addFromDataSource("table1", 1, 1, dataSource);
var slicerData = table.getSlicerData(); // GC.Spread.Sheets.Slicers.TableSlicerData
console.log(slicerData.getTable() === table); // true
The table of the table slicer data.
▸ inPreview(): boolean
Gets whether the slicer is in the preview state. If you set doFilter() with isPreview flag to true, (like slicerData.doFilter('Name', {exclusiveRowIndexes: [1]}, true);) you can check whether slicer is doing filter in preview with slicerData.inPreview() API,
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
[
{ value: 'Bob', text: 'Bob' },
{ value: 'Washington', text: 'Washington' },
{ value: new Date("2012/2/15"), text: '2/15/2012' },
{ value: 8000, text: '5 500' }
],
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
slicerData.doFilter('Name', {exclusiveRowIndexes: [1, 2]});
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // [0]
console.log(slicerData.inPreview()); // false
slicerData.clearPreview();
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // [0]
slicerData.doUnfilter('Name');
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
slicerData.doFilter('Name', {exclusiveRowIndexes: [1, 2]}, true);
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // [0]
console.log(slicerData.inPreview()); // true
slicerData.clearPreview();
console.log(slicerData.inPreview()); // false
console.log(slicerData.getFilteredOutIndexes('Name', GC.Spread.Slicers.FilteredOutDataType.all)); // []
boolean
▸ onColumnNameChanged(oldName
, newName
): void
Changes a column name for the general slicer data.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getExclusiveData('Name')); // ['Bob', 'Betty']
slicerData.onColumnNameChanged('Name', 'NewName');
console.log(slicerData.getExclusiveData('Name')); // []
console.log(slicerData.getExclusiveData('NewName')); // ['Bob', 'Betty']
Name | Type | Description |
---|---|---|
oldName |
string |
The old name of the column. |
newName |
string |
The new name of the column. |
void
GeneralSlicerData.onColumnNameChanged
▸ onColumnsRemoved(colIndex
, colCount
): void
Removes columns of the general slicer data.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getData('Name')); // ['Bob', 'Betty']
slicerData.onColumnsRemoved(0, 1);
console.log(slicerData.getData('Name')); // []
Name | Type | Description |
---|---|---|
colIndex |
number |
The index of the starting column. |
colCount |
number |
The number of columns to remove. |
void
GeneralSlicerData.onColumnsRemoved
▸ onDataChanged(changedDataItems
): void
Changes data items in the data source of the general slicer data.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getData('Name')); // ['Bob', 'Betty']
slicerData.onDataChanged([{columnName: 'Name', rowIndex: 1, row: 1, data: {value: 'Alice', text: 'Alice'}}]);
console.log(slicerData.getData('Name')); // ['Bob', 'Alice']
Name | Type | Description |
---|---|---|
changedDataItems |
ISlicerDataItem |
The changed data item in the data source. |
void
GeneralSlicerData.onDataChanged
▸ onFiltered(): void
Occurs after the slicer data has been filtered.
void
▸ onRowsAdded(rowIndex
, rowCount
): void
Adds rows in the data source of the general slicer data.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getData('Name')); // ['Bob', 'Betty']
slicerData.onRowsAdded(1, 2);
console.log(slicerData.getData('Name')); // ['Bob', undefined, undefined, 'Betty']
Name | Type | Description |
---|---|---|
rowIndex |
number |
The index of the starting row. |
rowCount |
number |
The number of rows to add. |
void
▸ onRowsRemoved(rowIndex
, rowCount
): void
Removes rows in the data source of the general slicer data.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
]
], ["Name", "City", "Birthday", "Salary"]
);
console.log(slicerData.getData('Name')); // ['Bob', 'Betty']
slicerData.onRowsAdded(1, 2);
console.log(slicerData.getData('Name')); // ['Bob', undefined, undefined, 'Betty']
slicerData.onRowsRemoved(2, 1);
console.log(slicerData.getData('Name')); // ['Bob', undefined, 'Betty']
Name | Type | Description |
---|---|---|
rowIndex |
number |
The index of the starting row. |
rowCount |
number |
The number of rows to remove. |
void
GeneralSlicerData.onRowsRemoved
▸ refresh(): void
Refreshes the table slicer data. will rebuild data source of table slicer data.
void
▸ resumeFilteredEvents(): void
Resumes the onFiltered event.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
], ["Name", "City", "Birthday", "Salary"]
);
//Define custom slicer.
function MySlicer(container) {
this.container = container;
this.slicerData = null;
this.columnName = null;
}
MySlicer.prototype.setData = function (slicerData, columnName) {
this.slicerData = slicerData;
this.columnName = columnName;
// attach listener here
this.slicerData.attachListener(this);
}
MySlicer.prototype.onFiltered = function () {
console.log('filter event triggered');
}
//create a custom slicer and add this slicer to the "slicerContainer" div.
var slicer = new MySlicer($("#slicerContainer")[0]);
slicer.setData(slicerData, 'Name');
slicerData.doFilter('Name', {exclusiveRowIndexes: [1]});
// watch console log: 'filter event triggered'
slicerData.suspendFilteredEvents();
slicerData.doFilter('Name', {exclusiveRowIndexes: [0]});
// watch console log: nothing
slicerData.suspendFilteredEvents();
slicerData.doFilter('Name', {exclusiveRowIndexes: [0, 1]});
// watch console log: 'filter event triggered'
void
GeneralSlicerData.resumeFilteredEvents
▸ suspendFilteredEvents(): void
Suspends the onFiltered event.
example
var slicerData = new GC.Spread.Slicers.GeneralSlicerData(
[
[
{ value: 'Bob', text: 'Bob' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1968/6/8"), text: '6/8/1968' },
{ value: 10000, text: '10 000' }
],
[
{ value: 'Betty', text: 'Betty' },
{ value: 'NewYork', text: 'NewYork' },
{ value: new Date("1972/7/3"), text: '7/3/1972' },
{ value: 8000, text: '8 000' }
],
], ["Name", "City", "Birthday", "Salary"]
);
//Define custom slicer.
function MySlicer(container) {
this.container = container;
this.slicerData = null;
this.columnName = null;
}
MySlicer.prototype.setData = function (slicerData, columnName) {
this.slicerData = slicerData;
this.columnName = columnName;
// attach listener here
this.slicerData.attachListener(this);
}
MySlicer.prototype.onFiltered = function () {
console.log('filter event triggered');
}
//create a custom slicer and add this slicer to the "slicerContainer" div.
var slicer = new MySlicer($("#slicerContainer")[0]);
slicer.setData(slicerData, 'Name');
slicerData.doFilter('Name', {exclusiveRowIndexes: [1]});
// watch console log: 'filter event triggered'
slicerData.suspendFilteredEvents();
slicerData.doFilter('Name', {exclusiveRowIndexes: [0]});
// watch console log: nothing
slicerData.suspendFilteredEvents();
slicerData.doFilter('Name', {exclusiveRowIndexes: [0, 1]});
// watch console log: 'filter event triggered'
void