The date to date filter condition interface is shown below:
conType: the type of condition.
operator: the type of date filter to use
val: the start and end date to be filtered
isDynamicEndDate: if value is true, will use current time for endDate. if value is false, we need to customize end time for endDate.
isParallel: it means whether to use the parallel mode.
by: month/quarter/year
On this basis, we have six built-in Date To Date filters in the pivot date filter context menu.
month to date(MTD)
quarter to date(QTD)
year to date(YTD)
parallel month to date(parallel MTD)
parallel quarter to date(parallel QTD)
parallel year to date(parallel YTD)
You can use the Date To Date Filter as follows:
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<div class="options-container">
<div>custom date to date Filter:</div>
<div class="pivottable-filter date-filter">
<label class="filter-input">startTime: </label>
<input class="filter-input" type="text" id="startTime" v-model="startTimeRef">
<label class="filter-input">endTime: </label>
<input class="filter-input" type="text" id="endTime" v-model="endTimeRef">
<div class="filter-input">
<label>isDynamicEndDate: </label>
<input v-model="isDynamicEndDateRef" type="checkbox" id="isDynamicEndDate">
</div>
<div class="filter-input">
<label>by: </label>
<select id="by" v-model="byRef">
<option value=1>month</option>
<option value=2>quarter</option>
<option value=3>year</option>
</select>
</div>
<div class="filter-input">
<label>isParallel: </label>
<input type="checkbox" id="isParallel" v-model="isParallelRef">
</div>
<button class="filter-input set-filter" id="clearFilter" @click="clearPivotDateFilter">clearFilter</button>
<button class="filter-input set-filter" id="setFilter" @click="updatePivotDateFilter">setFilter</button>
</div>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-vue";
import { shallowRef } from "vue";
import "@mescius/spread-sheets-pivot-addon";
let autoGenerateColumnsRef = shallowRef(false);
let autoMergeModeRef = shallowRef(GC.Spread.Sheets.AutoMerge.AutoMergeMode.restricted);
let autoMergeSelectionModeRef = shallowRef(GC.Spread.Sheets.AutoMerge.SelectionMode.merged);
let spreadRef = shallowRef(null);
let pivotTableRef = shallowRef(null);
let startTimeRef = shallowRef('');
let endTimeRef = shallowRef('');
let isDynamicEndDateRef = shallowRef(false);
let isParallelRef = shallowRef(false);
let byRef = shallowRef(1);
async function initSpread(spread) {
spreadRef.value = spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getDataSource(sheet2, pivotSales);
let pivotTable = addPivotTable(sheet1, tableName);
pivotTableRef.value = pivotTable;
spread.resumePaint();
}
function clearPivotDateFilter () {
let spread = spreadRef.value;
let pivotTable = pivotTableRef.value;
spread.suspendPaint();
pivotTable.labelFilter("date", null);
startTimeRef.value = '';
endTimeRef.value = '';
isDynamicEndDateRef.value = false;
isParallelRef.value = false;
byRef.value = 1;
spread.resumePaint();
}
function updatePivotDateFilter () {
let spread = spreadRef.value;
let pivotTable = pivotTableRef.value;
spread.suspendPaint();
var condition = {
conType: GC.Pivot.PivotConditionType.date,
operator: GC.Pivot.PivotDateFilterOperator.dateToDate,
val: [new Date(startTimeRef.value), new Date(endTimeRef.value)],
isDynamicEndDate: isDynamicEndDateRef.value,
isParallel: isParallelRef.value,
by: +byRef.value
};
var filterInfo = {
condition
};
pivotTable.labelFilter("date", filterInfo);
spread.resumePaint();
}
function getDataSource (sheet, tableSource) {
sheet.name("DataSource");
sheet.setRowCount(1984);
sheet.setColumnWidth(0, 120);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1, 4, 0, 2).formatter("$ #,##0");
let table = sheet.tables.add('table', 0, 0, 1984, 6);
for (let i = 2; i <= 1984; i++) {
sheet.setFormula(i - 1, 5, '=D' + i + '*E' + i)
}
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
sheet.setArray(0, 0, tableSource);
return table.name();
}
function addPivotTable (sheet, source) {
sheet.suspendPaint();
sheet.name("PivotTable");
sheet.setRowCount(10000);
let pivotTable = sheet.pivotTables.add("PivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
pivotTable.suspendLayout();
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.columnField);
let groupInfo = {
originFieldName: "date",
dateGroups: [{
by: GC.Pivot.DateGroupType.years
}, {
by: GC.Pivot.DateGroupType.quarters
}, {
by: GC.Pivot.DateGroupType.months
}, {
by: GC.Pivot.DateGroupType.days
}]
};
pivotTable.group(groupInfo);
pivotTable.add("Years (date)", "Years (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Months (date)", "Months (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Days (date)", "Days (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.resumeLayout();
sheet.resumePaint();
pivotTable.autoFitColumn();
return pivotTable;
}
</script>
<style scoped>
.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;
font-size: 14px;
}
.pivottable-filter{
height: 100px;
}
.pivot-filter{
width: 200px;
height: 25px;
display: block;
margin-bottom: 10px;
float: left;
}
.filter-input{
width: 200px;
height: 20px;
display: block;
margin-top: 10px;
}
.set-filter{
width: 200px;
margin-top: 20px;
}
.dateFilterSettingItemDiv{
margin: 11px;
}
.set-filter{
width: 200px;
margin-top: 20px;
}
.filter-input{
width: 200px;
height: 20px;
display: block;
margin-top: 10px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.whole-field{
margin-bottom: 10px;
}
#app {
height: 100%;
}
</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$/spread/source/data/pivot-data-filter.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></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-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
