概述
这个 demo 用数据图表对比 DateMode 的不同绑定方式,既有学生阅读数据,也有股票日线数据和自定义日期格式。
实现思路
先向 DataManager 写入学生阅读记录和股票记录。
再在多个工作表里分别创建柱形图、条形图、面积图、折线图、玫瑰图、雷达图和股票图。
通过 DateMode.date/month/quarter/year 和分层 category,展示日期轴在不同粒度下的效果。
代码解析
这里把日期字段、日期粒度和坐标轴格式分开配置,所以同一份数据可以呈现不同的时间轴层级。
运行效果
第一组 sheet 会对比日期轴和序数轴在各类图表上的差异。
第二组 sheet 会展示按年、季度、月分层的日期绑定。
第三组 sheet 会把股票 OHLC 数据按日期模式画成蜡烛图。
最后一组 sheet 会显示自定义日期格式的多层分类图表。
window.onload = async () => {
const { spread, designer } = createSpreadAndDesigner();
await initDataManager(spread);
const DataChartType = GC.Spread.Sheets.DataCharts.DataChartType;
const DateMode = GC.Spread.Sheets.DataCharts.DateMode;
const sheet1 = spread.getSheet(0);
sheet1.name('日期模式绑定');
// Column
addChartToSheet(sheet1, 'DateScaleColumn', 20, 20, 500, 250, 'Date', DataChartType.column, DateMode);
addChartToSheet(sheet1, 'OrdinalScaleColumn', 540, 20, 500, 250, 'Ordinal', DataChartType.column, DateMode);
// Bar
addChartToSheet(sheet1, 'DateScaleBar', 20, 290, 500, 250, 'Date', DataChartType.bar, DateMode);
addChartToSheet(sheet1, 'OrdinalScaleBar', 540, 290, 500, 250, 'Ordinal', DataChartType.bar, DateMode);
// Area
addChartToSheet(sheet1, 'DateScaleArea', 20, 560, 500, 250, 'Date', DataChartType.area, DateMode);
addChartToSheet(sheet1, 'OrdinalScaleArea', 540, 560, 500, 250, 'Ordinal', DataChartType.area, DateMode);
// Line
addChartToSheet(sheet1, 'DateScaleLine', 20, 830, 500, 250, 'Date', DataChartType.line, DateMode);
addChartToSheet(sheet1, 'OrdinalScaleLine', 540, 830, 500, 250, 'Ordinal', DataChartType.line, DateMode);
// Rose
addChartToSheet(sheet1, 'OrdinalScaleRose', 20, 1100, 500, 250, 'Ordinal', DataChartType.rose, DateMode);
// PolarCoordinatesBar
addChartToSheet(sheet1, 'OrdinalScalePolarBar', 540, 1100, 500, 250, 'Ordinal', DataChartType.polarCoordinatesBar, DateMode);
// Radar
addChartToSheet(sheet1, 'OrdinalScaleRadar', 20, 1370, 500, 250, 'Ordinal', DataChartType.radar, DateMode);
// Waterfall
addChartToSheet(sheet1, 'OrdinalScaleWaterfall', 540, 1370, 500, 250, 'Ordinal', DataChartType.waterfall, DateMode);
spread.addSheet(1);
const sheet2 = spread.getSheet(1);
sheet2.name('分层日期模式');
// Column
addHierarchicalChart(sheet2, 'DateScaleColumn', 20, 20, 500, 250, 'Date', DataChartType.column, DateMode);
addHierarchicalChart(sheet2, 'OrdinalScaleColumn', 540, 20, 500, 250, 'Ordinal', DataChartType.column, DateMode);
// Bar
addHierarchicalChart(sheet2, 'DateScaleBar', 20, 290, 500, 250, 'Date', DataChartType.bar, DateMode);
addHierarchicalChart(sheet2, 'OrdinalScaleBar', 540, 290, 500, 250, 'Ordinal', DataChartType.bar, DateMode);
// Area
addHierarchicalChart(sheet2, 'DateScaleArea', 20, 560, 500, 250, 'Date', DataChartType.area, DateMode);
addHierarchicalChart(sheet2, 'OrdinalScaleArea', 540, 560, 500, 250, 'Ordinal', DataChartType.area, DateMode);
// Line
addHierarchicalChart(sheet2, 'DateScaleLine', 20, 830, 500, 250, 'Date', DataChartType.line, DateMode);
addHierarchicalChart(sheet2, 'OrdinalScaleLine', 540, 830, 500, 250, 'Ordinal', DataChartType.line, DateMode);
spread.addSheet(2);
const sheet3 = spread.getSheet(2);
sheet3.name('股票日期模式');
addStockChart(sheet3, 'DateScaleChart', 20, 20, 500, 300, 'Date', DataChartType.candlestick, DateMode);
addStockChart(sheet3, 'OrdinalScaleChart', 540, 20, 500, 300, 'Ordinal', DataChartType.candlestick, DateMode);
spread.addSheet(3);
const sheet4 = spread.getSheet(3);
sheet4.name('自定义日期格式');
// Single month binding: Month MMM
addDateFormatChart(sheet4, 'SingleMonthFormat', 20, 20, 500, 250, 'Ordinal', DataChartType.column,
{ field: 'Date', dateMode: DateMode.month },
[{ value: '"月份 "MMM' }],
'月度阅读汇总');
// Single quarter binding: QQQ period
addDateFormatChart(sheet4, 'SingleQuarterFormat', 540, 20, 500, 250, 'Ordinal', DataChartType.column,
{ field: 'Date', dateMode: DateMode.quarter },
[{ value: 'QQQ" 周期"' }],
'季度阅读周期汇总');
// Multi-level binding: Year yyyy > Qtr QQQ > Month M
addDateFormatChart(sheet4, 'YearQuarterMonthFormat', 20, 290, 500, 250, 'Ordinal', DataChartType.column,
{ field: 'Date', dateMode: DateMode.year, child: { field: 'Date', dateMode: DateMode.quarter, child: { field: 'Date', dateMode: DateMode.month } } },
[{ value: '"年份 "yyyy' }, { value: '"季度 "QQQ' }, { value: '"月份 "M' }],
'阅读活动日历');
// Multi-level binding: Month MMM > Day d
addDateFormatChart(sheet4, 'MonthDayFormat', 540, 290, 500, 250, 'Ordinal', DataChartType.column,
{ field: 'Date', dateMode: DateMode.month, child: { field: 'Date', dateMode: DateMode.day } },
[{ value: '"周期 "MMM' }, { value: '"日期 "d' }],
'按周期查看每日阅读明细');
spread.setActiveSheetIndex(0);
spread.getActiveSheet().dataCharts.all()[0].isSelected(true);
if (designer) {
designer.refresh();
}
}
function addChartToSheet(sheet, chartName, x, y, width, height, scaleType, chartType, DateMode) {
const AxisType = GC.Spread.Sheets.DataCharts.AxisType;
const dataChart = sheet.dataCharts.add(chartName, x, y, width, height, chartType);
const axes = [{
type: AxisType.x,
scale: { type: scaleType }
}];
dataChart.setChartConfig({
tableName: 'StudentActivity',
plots: [{
type: chartType,
encodings: {
values: [{ field: 'ReadingMinutes', aggregate: GC.Spread.Sheets.DataCharts.Aggregate.sum }],
category: { field: 'Date', dateMode: DateMode.month },
color: { field: 'Student' },
details: [{ field: 'Student' }],
},
}],
config: {
header: {
title: getScaleTitle(scaleType) + '坐标轴 - 学生阅读活动(2024)',
padding: { left: 10, right: 10, top: 10, bottom: 10 },
textStyle: {
fontSize: 14,
alignment: GC.Spread.Sheets.DataCharts.HAlign.left,
},
},
plotAreas: [{ axes: axes }],
},
});
}
function addHierarchicalChart(sheet, chartName, x, y, width, height, scaleType, chartType, DateMode) {
const AxisType = GC.Spread.Sheets.DataCharts.AxisType;
const dataChart = sheet.dataCharts.add(chartName, x, y, width, height, chartType);
const hierarchicalCategory = {
field: 'Date',
dateMode: DateMode.year,
child: {
field: 'Date',
dateMode: DateMode.quarter,
child: {
field: 'Date',
dateMode: DateMode.month
}
}
};
const axes = [{
type: AxisType.x,
scale: { type: scaleType }
}];
dataChart.setChartConfig({
tableName: 'StudentActivity',
plots: [{
type: chartType,
encodings: {
values: [{ field: 'ReadingMinutes', aggregate: GC.Spread.Sheets.DataCharts.Aggregate.sum }],
category: hierarchicalCategory,
color: { field: 'Student' },
details: [{ field: 'Student' }],
},
}],
config: {
header: {
title: getScaleTitle(scaleType) + '坐标轴 - 按年/季度/月查看学生阅读活动',
padding: { left: 10, right: 10, top: 10, bottom: 10 },
textStyle: {
fontSize: 14,
alignment: GC.Spread.Sheets.DataCharts.HAlign.left,
},
},
plotAreas: [{ axes: axes }],
},
});
}
function addStockChart(sheet, chartName, x, y, width, height, scaleType, chartType, DateMode) {
const AxisType = GC.Spread.Sheets.DataCharts.AxisType;
const dataChart = sheet.dataCharts.add(chartName, x, y, width, height, chartType);
const hierarchicalCategory = {
field: 'Date',
dateMode: DateMode.month,
child: {
field: 'Date',
dateMode: DateMode.day
}
};
const axes = [{
type: AxisType.x,
scale: { type: scaleType }
}];
dataChart.setChartConfig({
tableName: 'StockPrice',
plots: [{
type: chartType,
encodings: {
values: [{
vectors: {
open: { field: 'Open' },
high: { field: 'High' },
low: { field: 'Low' },
close: { field: 'Close' },
},
}],
category: hierarchicalCategory,
},
}],
config: {
header: {
title: getScaleTitle(scaleType) + '坐标轴 - NovaPulse 公司(NVPL,虚构)2025 年第一季度日价格',
padding: { left: 10, right: 10, top: 10, bottom: 10 },
textStyle: {
fontSize: 14,
alignment: GC.Spread.Sheets.DataCharts.HAlign.left,
},
},
plotAreas: [{ axes: axes }],
},
});
}
function addDateFormatChart(sheet, chartName, x, y, width, height, scaleType, chartType, category, formats, title) {
const AxisType = GC.Spread.Sheets.DataCharts.AxisType;
const dataChart = sheet.dataCharts.add(chartName, x, y, width, height, chartType);
const axes = [{
type: AxisType.x,
scale: { type: scaleType },
format: formats,
}];
dataChart.setChartConfig({
tableName: 'StudentActivity',
plots: [{
type: chartType,
encodings: {
values: [{ field: 'ReadingMinutes', aggregate: GC.Spread.Sheets.DataCharts.Aggregate.sum }],
category: category,
color: { field: 'Student' },
details: [{ field: 'Student' }],
},
}],
config: {
header: {
title: getScaleTitle(scaleType) + '坐标轴 - ' + title,
padding: { left: 10, right: 10, top: 10, bottom: 10 },
textStyle: {
fontSize: 14,
alignment: GC.Spread.Sheets.DataCharts.HAlign.left,
},
},
plotAreas: [{ axes: axes }],
},
});
}
function createSpreadAndDesigner() {
const demoHost = document.getElementById('demo-host');
if (window !== top) {
const spread = new GC.Spread.Sheets.Workbook('spread-host', { sheetCount: 1 });
new GC.Spread.Sheets.DataCharts.DataChartConfigPanel('panel-host', spread);
return {
spread
}
} else {
const designer = new GC.Spread.Sheets.Designer.Designer(demoHost, undefined, undefined, { sheetCount: 1 });
return {
designer,
spread: designer.getWorkbook(),
}
}
}
async function initDataManager(spread) {
const dataManager = spread.dataManager();
const activityData = [];
// 2024 Chinese elementary school calendar
// Spring semester: Jan - Jun | Fall semester: Sep - Dec
// Winter break: Feb | Summer break: Jul - Aug
const schoolMonths = [
{ month: 1, days: [8, 15, 22] },
{ month: 3, days: [4, 11, 18, 25] },
{ month: 4, days: [1, 8, 15, 22, 29] },
{ month: 5, days: [6, 13, 20, 27] },
{ month: 6, days: [3, 10, 17, 24] },
{ month: 9, days: [2, 9, 16, 23, 30] },
{ month: 10, days: [7, 14, 21, 28] },
{ month: 11, days: [4, 11, 18, 25] },
{ month: 12, days: [2, 9, 16, 23] },
];
const dates = [];
for (const { month, days } of schoolMonths) {
for (const day of days) {
dates.push(new Date(2024, month - 1, day));
}
}
const variations = [5, -3, 8, -2, 10, -5, 7, -1, 6, -4, 3, -7, 9, -3, 4, -6, 8, -2, 5, -1, 7, -4, 6, -3, 10, -5, 4, -2, 8, -6, 5, -3, 7, -1, 9, -4, 6, -2];
const studentConfigs = [
{ name: '爱丽丝', base: 35, varOffset: 0 },
{ name: '鲍勃', base: 28, varOffset: 19 },
];
for (const student of studentConfigs) {
for (let i = 0; i < dates.length; i++) {
const variation = variations[(i + student.varOffset) % variations.length];
activityData.push({
Date: dates[i],
Student: student.name,
ReadingMinutes: Math.max(10, student.base + variation),
});
}
}
await dataManager.addTable('StudentActivity', {
data: activityData,
schema: {
columns: {
Date: { dataType: 'date' },
}
}
}).fetch();
// Simulated stock data for fictional company NovaPulse Corp (NVPL)
// NASDAQ trading hours: Mon-Fri, closed on weekends and holidays
const stockData = [];
let price = 142.50;
const tradingDays = [];
// Generate Q1 2025 trading days (skip weekends)
for (let m = 0; m < 3; m++) {
const daysInMonth = new Date(2025, m + 1, 0).getDate();
for (let d = 1; d <= daysInMonth; d++) {
const date = new Date(2025, m, d);
const dow = date.getDay();
if (dow !== 0 && dow !== 6) {
// Skip New Year's Day and Presidents' Day
if (m === 0 && d === 1) continue;
if (m === 1 && d === 17) continue;
tradingDays.push(date);
}
}
}
for (const date of tradingDays) {
const change = (Math.random() - 0.48) * 6;
const open = price;
const close = price + change;
const high = Math.max(open, close) + Math.random() * 3;
const low = Math.min(open, close) - Math.random() * 3;
stockData.push({
Date: date,
Open: Math.round(open * 100) / 100,
High: Math.round(high * 100) / 100,
Low: Math.round(low * 100) / 100,
Close: Math.round(close * 100) / 100,
});
price = close;
}
await dataManager.addTable('StockPrice', {
data: stockData,
schema: {
columns: {
Date: { dataType: 'date' },
}
}
}).fetch();
}
function getScaleTitle(scaleType) {
return scaleType === 'Date' ? '日期' : '序数';
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer/styles/gc.spread.sheets.designer.light.min.css">
<link rel="stylesheet" type="text/css" href="styles.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-print/dist/gc.spread.sheets.print.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-datacharts-addon/dist/gc.spread.sheets.datacharts.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script>
const designerDependencyScripts = [
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-barcode/dist/gc.spread.sheets.barcode.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pdf/dist/gc.spread.sheets.pdf.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-reportsheet-addon/dist/gc.spread.report.reportsheet.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-ganttsheet/dist/gc.spread.sheets.ganttsheet.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-formula-panel/dist/gc.spread.sheets.formulapanel.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-io/dist/gc.spread.sheets.io.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer-resources-cn/dist/gc.spread.sheets.designer.resource.cn.min.js',
'$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer/dist/gc.spread.sheets.designer.all.min.js',
'$DEMOROOT$/spread/source/js/designer/license.js',
]
function appendScriptNode (src) {
const script = document.createElement('script');
script.src = src;
script.async = false;
script.type = 'text/javascript';
document.head.appendChild(script);
}
if (top === window) { // not in iframe
designerDependencyScripts.forEach(appendScriptNode);
}
</script>
<script src="app.js" type="text/javascript"></script>
</head>
<body>
<div class="sample-tutorial">
<div id="demo-host">
<div id="spread-host"></div>
<div id="panel-host"></div>
</div>
</div>
</body>
</html>
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
#demo-host {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
#spread-host {
position: absolute;
top: 0;
left: 0;
width: calc(100% - 380px);
height: 100%;
}
#panel-host {
position: absolute;
top: 0;
right: 0;
width: 380px;
height: 100%;
}