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:
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import { SpreadSheets } from '@mescius/spread-sheets-react';
import './styles.css';
const useState = React.useState;
export function AppFunc() {
const [spread, setSpread] = useState(null);
const [pivotTable, setPivotTable] = useState(null);
const initSpread = (spread) => {
setSpread(spread);
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = getSource(sheet2, pivotSales);
let pt = addPivotTable(sheet1, tableName);
setPivotTable(pt);
spread.resumePaint();
}
const getSource = (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();
}
const addPivotTable = (sheet, source) => {
sheet.suspendPaint();
sheet.name("PivotTable");
sheet.setRowCount(10000);
let pt = sheet.pivotTables.add("PivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
pt.suspendLayout();
pt.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 }] };
pt.group(groupInfo);
pt.add("Years (date)", "Years (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("Quarters (date)", "Quarters (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("Months (date)", "Months (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("Days (date)", "Days (date)", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt.resumeLayout();
sheet.resumePaint();
pt.autoFitColumn();
return pt;
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
</SpreadSheets>
</div>
<div class="options-container">
<div>custom date to date Filter:</div>
<DateToDateFilter spread={spread} pivotTable={pivotTable}></DateToDateFilter>
</div>
</div>
);
}
function DateToDateFilter(props) {
const [state, setState] = useState({
startTime: "",
endTime: "",
isParallel: false,
isDynamicEndDate: false,
by: 1,
});
const setFilter = () => {
const filterInfo = state;
const spread = props.spread;
let pivotTable = props.pivotTable;
spread.suspendPaint();
let condition = {
conType: GC.Pivot.PivotConditionType.date,
operator: GC.Pivot.PivotDateFilterOperator.dateToDate,
val: [new Date(filterInfo.startTime), new Date(filterInfo.endTime)],
isDynamicEndDate: filterInfo.isDynamicEndDate,
isParallel: filterInfo.isParallel,
by: +filterInfo.by
};
let dateFilterInfo = {
condition
};
pivotTable.labelFilter("date", dateFilterInfo);
spread.resumePaint();
}
const clearFilter = () => {
const spread = props.spread;
let pivotTable = props.pivotTable;
spread.suspendPaint();
pivotTable.labelFilter("date", null);
setState(state => ({
...state,
startTime: "",
endTime: "",
isParallel: false,
isDynamicEndDate: false,
by: 1,
}));
spread.resumePaint();
}
return (
<div class="pivottable-filter date-filter">
<label class="filter-input">startTime: </label>
<input value={state.startTime} onChange={e => { setState({ ...state, startTime: e.target.value }) }} class="filter-input" type="text" id="startTime"></input>
<label class="filter-input">endTime: </label>
<input value={state.endTime} onChange={e => { setState({ ...state, endTime: e.target.value }) }} class="filter-input" type="text" id="endTime"></input>
<div class="filter-input">
<label>isDynamicEndDate: </label>
<input checked={state.isDynamicEndDate} onChange={e => { setState({ ...state, isDynamicEndDate: e.target.checked }) }} type="checkbox" id="isDynamicEndDate"></input>
</div>
<div class="filter-input">
<label>by: </label>
<select id="by" value={state.by} onChange={e => { setState({ ...state, by: e.target.value }) }}>
<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 checked={state.isParallel} onChange={e => { setState({ ...state, isParallel: e.target.checked }) }} type="checkbox" id="isParallel"></input>
</div>
<button class="filter-input set-filter" id="clearFilter" onClick={clearFilter}>clearFilter</button>
<button class="filter-input set-filter" id="setFilter" onClick={setFilter}>setFilter</button>
</div>
);
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import { SpreadSheets } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.state = {
renderChild: false
}
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
</SpreadSheets>
</div>
{this.state.renderChild ?
(<div class="options-container">
<div>custom date to date Filter:</div>
<DateToDateFilter spread={this.spread} pivotTable={this.pivotTable}></DateToDateFilter>
</div>) : ""}
</div>
);
}
componentDidMount() {
this.setState(() => ({
renderChild: true
}))
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
spread.setSheetCount(2);
let sheet1 = spread.getSheet(0);
let sheet2 = spread.getSheet(1);
let tableName = this.getSource(sheet2, pivotSales);
let pivotTable = this.addPivotTable(sheet1, tableName);
this.pivotTable = pivotTable;
spread.resumePaint();
}
getSource(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();
}
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;
}
}
class DateToDateFilter extends Component {
constructor(props) {
super(props);
this.state = {
startTime: "",
endTime: "",
isParallel: false,
isDynamicEndDate: false,
by: 1,
}
this.setFilter = this.setFilter.bind(this);
this.clearFilter = this.clearFilter.bind(this);
}
setFilter() {
const filterInfo = this.state;
const spread = this.props.spread;
let pivotTable = this.props.pivotTable;
spread.suspendPaint();
let condition = {
conType: GC.Pivot.PivotConditionType.date,
operator: GC.Pivot.PivotDateFilterOperator.dateToDate,
val: [new Date(filterInfo.startTime), new Date(filterInfo.endTime)],
isDynamicEndDate: filterInfo.isDynamicEndDate,
isParallel: filterInfo.isParallel,
by: +filterInfo.by
};
let dateFilterInfo = {
condition
};
pivotTable.labelFilter("date", dateFilterInfo);
spread.resumePaint();
}
clearFilter() {
const spread = this.props.spread;
let pivotTable = this.props.pivotTable;
spread.suspendPaint();
pivotTable.labelFilter("date", null);
this.setState({
startTime: "",
endTime: "",
isParallel: false,
isDynamicEndDate: false,
by: 1,
});
spread.resumePaint();
}
render() {
return (
<div class="pivottable-filter date-filter">
<label class="filter-input">startTime: </label>
<input value={this.state.startTime} onChange={e => { this.setState({ startTime: e.target.value }) }} class="filter-input" type="text" id="startTime"></input>
<label class="filter-input">endTime: </label>
<input value={this.state.endTime} onChange={e => { this.setState({ endTime: e.target.value }) }} class="filter-input" type="text" id="endTime"></input>
<div class="filter-input">
<label>isDynamicEndDate: </label>
<input checked={this.state.isDynamicEndDate} onChange={e => { this.setState({ isDynamicEndDate: e.target.checked }) }} type="checkbox" id="isDynamicEndDate"></input>
</div>
<div class="filter-input">
<label>by: </label>
<select id="by" value={this.state.by} onChange={e => { this.setState({ by: e.target.value }) }}>
<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 checked={this.state.isParallel} onChange={e => { this.setState({ isParallel: e.target.checked }) }} type="checkbox" id="isParallel"></input>
</div>
<button class="filter-input set-filter" id="clearFilter" onClick={this.clearFilter}>clearFilter</button>
<button class="filter-input set-filter" id="setFilter" onClick={this.setFilter}>setFilter</button>
</div>
);
}
}
<!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/react/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>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app" style="height: 100%;"></div>
</body>
</html>
.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;
}
.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-left: 15px; */
margin-top: 10px;
}
.set-filter{
width: 200px;
margin-top: 20px;
/* float: right; */
}
.dateFilterSettingItemDiv{
margin: 11px;
}
.set-filter{
width: 200px;
margin-top: 20px;
/* float: right; */
}
.filter-input{
width: 200px;
height: 20px;
display: block;
/* margin-left: 15px; */
margin-top: 10px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.whole-field{
margin-bottom: 10px;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
