필터

테이블 시트는 텍스트, 숫자날짜 등의 조건을 비롯하여 Excel과 유사한 필터 대화 상자를 지원할 수 있습니다. 데이터의 양이 많더라도 빠르게 필터링됩니다. 또한 allowSort, allowFilterByValue, allowFilterByList를 사용하여 특정 열에 대한 필터 및 정렬 영역 가시성 설정이 지원됩니다.

테이블 시트는 텍스트, 숫자, 날짜 등의 조건을 포함하는 Excel과 유사한 필터를 지원할 수 있습니다. 대용량의 데이터가 있을 때 필터 대화 상자를 여는 성능을 개선하기 위해 테이블 시트는 특정 필드 필터 인덱스 캐시를 만들 수 있는 옵션을 제공합니다. 경우에 따라 체크리스트 또는 필터 대화 상자의 일부분이 사용자에게 필요하지 않을 수 있으므로, 테이블 시트는 테이블 시트 필터 대화 상자의 각 부분의 가시성을 제어하기 위해 열에 여러 가지 옵션을 제공합니다. allowSort, allowFilterByValue, allowFilterByList가 모두 false인 경우에는 열 헤더의 필터 버튼이 보이지 않습니다.
/*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\/learn-spreadjs\//)[0] + 'server/api'; }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="ko-kr" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/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$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.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; }