日期模式绑定

这个 demo 对比了日期模式和序数模式在多种数据图表里的表现,还展示了分层日期绑定和自定义日期格式。

概述 这个 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%; }