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.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="init">
</gc-spread-sheets>
<div id="options-container" class="options-container">
<label for="dataRows">Row Count: </label>
<select id="dataRows" v-model="rowCount">
<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" v-model="createIDIndexes" />
<label for="createIdIndexes">Create ID Indexes</label>
<br>
<input type="checkbox" id="createBirthIndexes" v-model="createBirthIndexes" />
<label for="createBirthIndexes">Create Birthday Indexes</label>
<br>
</fieldset>
<fieldset>
<legend>ID Column Filter Dialog Options</legend>
<input type="checkbox" id="sortByValue_id" v-model="sortByValue_id" />
<label for="sortByValue_id">Allow Sort</label>
<br>
<input type="checkbox" id="filterByValue_id" v-model="filterByValue_id" />
<label for="filterByValue_id">Allow Filter By Value</label>
<br>
<input type="checkbox" id="listFilterArea_id" v-model="listFilterArea_id" />
<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" v-model="sortByValue_birthday" />
<label for="sortByValue_birthday">Allow Sort</label>
<br>
<input type="checkbox" id="filterByValue_birthday" v-model="filterByValue_birthday" />
<label for="filterByValue_birthday">Allow Filter By Value</label>
<br>
<input type="checkbox" id="listFilterArea_birthday" v-model="listFilterArea_birthday" />
<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" @click="initSpread" />
<fieldset style="heighy:155px;">
<legend>Performance</legend>
<textarea id="showEventArgs" style="width: 236px;height:135px;" cols="64" rows="15"
v-model="showEventArgs"></textarea>
</fieldset>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import { ref, toRaw } from "vue";
import "@mescius/spread-sheets-tablesheet";
import "@mescius/spread-sheets-vue";
const spreadRef = ref(null);
let rowCount = 1000;
let createIDIndexes = true;
let createBirthIndexes = true;
let sortByValue_id = true;
let filterByValue_id = true;
let listFilterArea_id = true;
let sortByValue_birthday = true;
let filterByValue_birthday = true;
let listFilterArea_birthday = true;
let showEventArgs = ref("");
function init(spread) {
spreadRef.value = spread;
initSpread(null, spread);
}
function initSpread(_, spread) {
spread = spread || toRaw(spreadRef.value);
spread.suspendPaint();
//1. init a sheet
spread.clearSheets();
spread.clearSheetTabs();
var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
var data = generateData(+rowCount);
var timeBeforeCreate = new Date();
var dataManager = spread.dataManager();
var employeeTable = dataManager.addTable("employeeTable", {
data: data.employees,
schema: {
columns: {
id: {
indexed: createIDIndexes
},
birth: {
indexed: createBirthIndexes
}
}
}
});
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 (!sortByValue_id) {
visibleInfo_id.sortByValue_id = false;
}
if (!filterByValue_id) {
visibleInfo_id.filterByValue_id = false;
}
if (!listFilterArea_id) {
visibleInfo_id.listFilterArea_id = false;
}
var visibleInfo_birthday = {};
if (!sortByValue_birthday) {
visibleInfo_birthday.sortByValue_birthday = false;
}
if (!filterByValue_birthday) {
visibleInfo_birthday.filterByValue_birthday = false;
}
if (!listFilterArea_birthday) {
visibleInfo_birthday.listFilterArea_birthday = false;
}
var cols = [
{ value: 'id', caption: 'ID', allowSort: visibleInfo_id.sortByValue_id, allowFilterByValue: visibleInfo_id.filterByValue_id, allowFilterByList: visibleInfo_id.listFilterArea_id },
{ 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_birthday, allowFilterByValue: visibleInfo_birthday.filterByValue_birthday, allowFilterByList: visibleInfo_birthday.listFilterArea_birthday },
{ 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;
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 getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
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;
}
</script>
<style scoped>
#app {
height: 100%;
}
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 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;
}
.field-inline {
display: inline-block;
vertical-align: middle;
}
fieldset label.field-inline {
width: 100px;
}
fieldset input.field-inline {
width: calc(100% - 100px - 12px);
}
.required {
color: red;
font-weight: bold;
}
#fields {
display: none;
}
#fields.show {
display: block;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="$DEMOROOT$/spread/source/data/departments.js" type="text/javascript"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
