Filters

TableSheet supports the multiple Excel-like filter dialogs including text, number and date conditions. Filtering is fast even with large amounts of data. Also supported is setting filter and sort area visibility for certain columns using the IColumn properties: allowSort, allowFilterByValue and allowFilterByList.

Description
app.js
index.html
styles.css
Copy to CodeMine

TableSheet can support the multiple Excel-like filters including Text, Number and Date conditions.

In order to improve the performance of opening filter dialogs when there is a large amount of data, TableSheet provides an option to create a specific field filter indexes cache.

IColumn {
   name?: string; // the unique name of the column
   value?: string; // the value of the column, could be a field name of table from database, or formula which uses the fields names
   caption?: string; // the caption of the column, which is the key of row data
   width?: number; // the width of the column, support number in pixel, or star size
   style?: GC.Data.StyleOptions;
   conditionalFormats?: Array<GC.Data.CellValueRuleOptions | GC.Data.SpecificTextRuleOptions | GC.Data.FormulaRuleOptions | GC.Data.DateOccurringRuleOptions | GC.Data.Top10RuleOptions | GC.Data.UniqueRuleOptions | GC.Data.DuplicateRuleOptions | GC.Data.AverageRuleOptions | GC.Data.TwoScaleRuleOptions | GC.Data.ThreeScaleRuleOptions | GC.Data.DataBarRuleOptions | GC.Data.IconSetRuleOptions>;
   validator?: GC.Data.NumberValidatorOptions | GC.Data.DateValidatorOptions | GC.Data.TimeValidatorOptions | GC.Data.TextLengthValidatorOptions | GC.Data.FormulaValidatorOptions | GC.Data.FormulaListValidatorOptions |       GC.Data.ListValidatorOptions;
   isPrimaryKey?: boolean; // mark the column as primary key column
   readonly?: boolean; // mark the column is readonly
   required?: boolean; // mark the column is required when insert a new row
   defaultValue?: any; // provide the default value when insert a new row, could be a const or a formula
   headerStyle?: GC.Data.HeaderStyleOptions; // the column header style
   visible?: boolean; // mark the column is visible
   headerFit?: "normal" | "vertical" | "stack"; // the header fit mode, default is normal
   dataType?: "string" | "number" | "boolean" | "object" | "array" | "date" | "rowOrder" | "formula"; // the actual data type of original value, it is useful for a Date because a Date is a string in JSON data and need be converted
   dataPattern?: string; // data pattern for parsing string to value, such as formatter "dd/MM/yyyy" for a date string, truthy and falsy value pairs "Yes|No" for a boolean string, decimal separator "," for a numeric string
   dataMap?: any; // a simple map to display the original value more meaningful, its key could be a number or string, and its value could be a number, string or Date
   indexed?: boolean; // weather need to create the filter cache while creating the table.
   allowSort?: boolean; // whether show sort after opening filer dialog.
   allowFilterByValue?: boolean; // whether show filter by value after opening filer dialog.
   allowFilterByList?: boolean; // whether show filter by list after opening filer dialog. If allowSort, allowFilterByValue and allowFilterByList are all false, not show the filter button in this column.
}
var productTable = dataManager.addTable("productTable", {
     data: dataSource,
     schema: {
         columns: {
              sid: {indexed: true}, // create the "sid" field filter indexes
              YearStart: {dataType: "date"},
              created_meta: {dataType: "date"}
         }
     }
});

For some cases, users may not need the checklist or some parts of the filter dialog, so TableSheet provides several options in columns to control the visibility of each part in the TableSheet filter dialog.

myTable.fetch().then(function () {
    var view = myTable.addView("myView", [
        {value: 'orderId',caption: "Order", width: 45, allowSort: false, allowFilterByValue: false, allowFilterByList: false},
        {value: 'customerId', caption: "Customer", width: 50, allowSort: false},
        {value: 'employeeId', caption: "Employee", width: 20, allowFilterByValue: false},
        {value: 'orderDate', caption: "orderDate", width: 136, allowFilterByList: false },
    ]);
    tablesheet.setDataView(view);
});

If the allowSort, allowFilterByValue and allowFilterByList are all false, the filter button in column header is invisible.

TableSheet can support the multiple Excel-like filters including Text, Number and Date conditions. In order to improve the performance of opening filter dialogs when there is a large amount of data, TableSheet provides an option to create a specific field filter indexes cache. For some cases, users may not need the checklist or some parts of the filter dialog, so TableSheet provides several options in columns to control the visibility of each part in the TableSheet filter dialog. If the allowSort, allowFilterByValue and allowFilterByList are all false, the filter button in column header is invisible.
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ var tableName = "Employee"; var baseApiUrl = getBaseApiUrl(); var apiUrl = baseApiUrl + "/" + tableName; var sheet, spread; window.onload = function () { spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); //register self-defined row action command initSpread(); bindEvents(); }; function initSpread() { spread = GC.Spread.Sheets.findControl(document.getElementById("ss")); spread.suspendPaint(); //1. init a sheet spread.clearSheets(); spread.clearSheetTabs(); sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet); var data = generateData(+getElementById("dataRows").value); var timeBeforeCreate = new Date(); var dataManager = spread.dataManager(); var employeeTable = dataManager.addTable("employeeTable", { data: data.employees, schema: { columns: { id: { indexed: getProperty("createIdIndexes", 'checked') }, birth: { indexed: getProperty("createBirthIndexes", 'checked') } } } }); var departmentTable = dataManager.addTable("departmentTable", { data: data.departments }); dataManager.addRelationship(employeeTable, "dept", "department", departmentTable, "dept_no", "employees"); dataManager.addRelationship(departmentTable, "leader_id", "manager", employeeTable, "id", "a"); spread.resumePaint(); var numericStyle = new GC.Spread.Sheets.Style(); numericStyle.formatter = "$ #,##0.00"; var formatStringStyle = new GC.Spread.Sheets.Style(); formatStringStyle.formatter = 'yyyy-mm-dd'; var visibleInfo_id = {}; if (!getProperty("sortByValue_id", 'checked')) { visibleInfo_id.sortByValue = false; } if (!getProperty("filterByValue_id", 'checked')) { visibleInfo_id.filterByValue = false; } if (!getProperty("listFilterArea_id", 'checked')) { visibleInfo_id.listFilterArea = false; } var visibleInfo_birthday = {}; if (!getProperty("sortByValue_birthday", 'checked')) { visibleInfo_birthday.sortByValue = false; } if (!getProperty("filterByValue_birthday", 'checked')) { visibleInfo_birthday.filterByValue = false; } if (!getProperty("listFilterArea_birthday", 'checked')) { visibleInfo_birthday.listFilterArea = false; } var cols = [ { value: 'id', caption: 'ID', allowSort: visibleInfo_id.sortByValue, allowFilterByValue: visibleInfo_id.filterByValue, allowFilterByList: visibleInfo_id.listFilterArea}, { value: 'firstName', caption: 'First Name', width: 100}, { value: 'lastName', caption: 'Last Name', width: 100}, { value: 'birth', caption: 'Birthday', width: 100, style: formatStringStyle, allowSort: visibleInfo_birthday.sortByValue, allowFilterByValue: visibleInfo_birthday.filterByValue, allowFilterByList: visibleInfo_birthday.listFilterArea}, { value: 'state', caption: 'State', width: 100}, { value: 'dept', caption: 'Department No', width: 130}, { value: 'title', caption: 'Title', width: 120}, { value: 'salary', caption: 'Salary', style: numericStyle, width: 100}, ]; var employeeView = employeeTable.addView("employeeView", cols, undefined); employeeView.fetch().then(function (args) { sheet.suspendPaint(); sheet.setDataView(employeeView); sheet.resumePaint(); var timeGap = new Date() - timeBeforeCreate; getElementById('showEventArgs').value = ("Fetch data and paint - " + (timeGap) + " ms"); }); initMultiFilterSample(spread); } function initMultiFilterSample (spread) { spread.suspendPaint(); var dataManager = spread.dataManager(); var table = dataManager.addTable("CourseTable", { data: [ { Course: "Calculus", Term: 1, Credit: 5, Score: 80, Teacher: "Nancy Feehafer" }, { Course: "P.E.", Term: 1, Credit: 3.5, Score: 85, Teacher: "Andrew Cencini" }, { Course: "Political Economics", Term: 1, Credit: 3.5, Score: 95, Teacher: "Jan Kotas" }, { Course: "Basic of Computer", Term: 1, Credit: 2, Score: 85, Teacher: "Steven Thorpe" }, { Course: "Micro-Economics", Term: 1, Credit: 4, Score: 62, Teacher: "Jan Kotas" }, { Course: "Linear Algebra", Term: 2, Credit: 5, Score: 73, Teacher: "Nancy Feehafer" }, { Course: "Accounting", Term: 2, Credit: 3.5, Score: 86, Teacher: "Nancy Feehafer" }, { Course: "Statistics", Term: 2, Credit: 5, Score: 85, Teacher: "Robert Zare" }, { Course: "Marketing", Term: 2, Credit: 4, Score: 70, Teacher: "Laura Giussani" } ], schema: { type: 'json' } }); var sheet = spread.addSheetTab(1, "Course", GC.Spread.Sheets.SheetType.tableSheet); table.fetch().then(function () { var myView = table.addView("CourseTable", [ { value: "Course", width: 130 }, { value: "Term", width: 100 }, { value: "Credit", width: 100 }, { value: "Score", width: 100 }, { value: "Teacher", width: 120 }, ]); spread.suspendPaint(); sheet.setDataView(myView); spread.resumePaint(); }); initMultiFilterHierarchySample(spread, dataManager); spread.resumePaint(); } function initMultiFilterHierarchySample(spread, dataManager) { var table = dataManager.addTable("Table", { remote: { read: { url: getBaseApiUrl() + "/Hierarchy_Formula" } }, schema: { hierarchy: { type: 'Parent', column: 'parent', summaryFields: { 'budget':'=SUM(CHILDREN(1,"budget"))' } }, columns: { id: { isPrimaryKey: true, }, }, } }); var sheet = spread.addSheetTab(2, "Budget", GC.Spread.Sheets.SheetType.tableSheet); sheet.options.allowAddNew = false; table.fetch().then(function () { var myView = table.addView("myView", [ { value: '=CONCAT([@department]," (L",LEVEL(),"-",LEVELROWNUMBER(),")")', caption: 'Department', width: 265, outlineColumn: true }, { value: "budget", width: 100, caption: 'Budget' }, { value: '=IF(LEVEL()=0,"",[@budget]/PARENT(1,"budget"))', width: 120, caption: 'Percentage', style: { formatter: '0.00%' } }, { value: "location", width: 100, caption: 'Location' }, { value: "phone", width: 150, caption: 'Phone' }, { value: "country", width: 100, caption: 'Country' }, ]); spread.suspendPaint(); sheet.setDataView(myView); spread.resumePaint(); }); } function randomFromList(list) { return list[~~(Math.random() * list.length)]; } function generateData(itemCount) { var data = {employees:[], departments: departments}; var states = ["Texas", "New York", "Florida", "Washington", "Ohio"]; var department_id = ["D001", "D002", "D003", "D004", "D005", "D006", "D007", "D008", "D009"]; var title = ["Senior Engineer", "Staff", "Engineer", "Senior Staff", "Assistant Engineer", "Technique Leader", "Manager"]; for (var i = 0; i < itemCount; i++) { var date = new Date(parseInt(Math.random() * 12052666) * 24 * 3600); //The timestamp date.setHours(0,0,0,0); var item = { id: i + 1, firstName: randomFromList(firstNames), lastName: randomFromList(lastNames), birth: date, state: randomFromList(states), dept: i < 9 ? department_id[i] : randomFromList(department_id), title: i < 9 ? "Manager" : randomFromList(title), salary: 3000 + parseInt(Math.random() * 100) * 500, }; data.employees.push(item); } return data; } function bindEvents() { var showButton = document.getElementById('setDataSource'); showButton.addEventListener('click', function () { initSpread(); }); var sortStart; spread.bind(GC.Spread.Sheets.Events.RangeSorting, function(e,args) { sortStart = new Date(); }); spread.bind(GC.Spread.Sheets.Events.RangeSorted, function(e,args) { getElementById('showEventArgs').value = (getElementById('showEventArgs').value + "\r\nSort - " + (new Date()-sortStart) + " ms"); }); var filterStart; spread.bind(GC.Spread.Sheets.Events.RangeFiltering, function(e,args) { filterStart = new Date(); }); spread.bind(GC.Spread.Sheets.Events.RangeFiltered, function(e,args) { getElementById('showEventArgs').value = (getElementById('showEventArgs').value + "\r\nFilter - " + (new Date()-filterStart) + " ms"); }); } function setTooltip(options, tooltip) { options.tooltip = tooltip; return options; } function getProperty(domId, prop) { return getElementById(domId)[prop]; } function getElementById (domId) { return document.getElementById(domId); } function setProperty(domId, prop, value) { getElementById(domId)[prop] = value; } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api'; }
<!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"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/departments.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 id="options-container" class="options-container"> <label for="dataRows">Row Count: </label> <select id="dataRows"> <option value="1000" selected="selected">1000</option> <option value="3000">3000</option> <option value="10000">10000</option> <option value="30000">30000</option> <option value="100000">100000</option> <option value="300000">300000</option> <option value="1000000">1000000</option> </select> <br> <fieldset> <legend>Create Filter Indexes</legend> <input type="checkbox" id="createIdIndexes" checked/> <label for="createIdIndexes">Create ID Indexes</label> <br> <input type="checkbox" id="createBirthIndexes" checked/> <label for="createBirthIndexes">Create Birthday Indexes</label> <br> </fieldset> <fieldset> <legend>ID Column Filter Dialog Options</legend> <input type="checkbox" id="sortByValue_id" checked/> <label for="sortByValue_id">Allow Sort</label> <br> <input type="checkbox" id="filterByValue_id" checked/> <label for="filterByValue_id">Allow Filter By Value</label> <br> <input type="checkbox" id="listFilterArea_id" checked/> <label for="listFilterArea_id">Allow Filter By List</label> <br> </fieldset> <fieldset> <legend>Birthday Column Filter Dialog Options</legend> <input type="checkbox" id="sortByValue_birthday" checked/> <label for="sortByValue_birthday">Allow Sort</label> <br> <input type="checkbox" id="filterByValue_birthday" checked/> <label for="filterByValue_birthday">Allow Filter By Value</label> <br> <input type="checkbox" id="listFilterArea_birthday" checked/> <label for="listFilterArea_birthday">Allow Filter By List</label> <br> </fieldset> <input type="button" style="margin-left: 30px; margin-top: 10px;width:200px;" id="setDataSource" value="Set DataSource"/> <fieldset style="height: 155px;"> <legend>Performance</legend> <textarea id="showEventArgs" style="width: 236px;height: 135px;" cols="64" rows="15"></textarea> </fieldset> </div> </div> </html>
body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } fieldset { padding: 6px; margin: 0; margin-top: 10px; } .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; } fieldset span, fieldset input, fieldset select { display: inline-block; text-align: left; } fieldset span { width: 50px; } fieldset input[type=text] { width: calc(100% - 58px); } fieldset input[type=button] { width: 100%; text-align: center; } fieldset select { width: calc(100% - 50px); } .field-line { margin-top: 4px; }