Add Source Linked Cell Formatter

Charts apply cell number formatting using linked cells to dynamically update the format of cells to the chart elements such as the data label, axis, and tooltip. Charts in SpreadJS provide numberFormatLinked property of IDataLabels interface and IAxes interface to implement the feature.

Set Source Linked Formatter for Data Labels

When numberFormatLinked property is set to true, the ‘value’ field of data labels retains the data source cell format regardless of the formatting code linked to the data label, whereas if numberFormatLinked property is set to false, the ‘value’ field in the data label uses the specified formatter.

Example: Let’s say you want to create a performance dashboard for different browsers that tracks monthly sales figures. The dashboard includes a clustered column chart displaying the average monthly sales of each browser, and you want to ensure that the chart’s data labels dynamically update their formatting based on a user-selected preference for average sales figure. The following chart depicts sales data when numberFormatLinked property is set to true.

The following code sample depicts how to implement cell number formatting using linked cells to dynamically update the chart’s data labels.

// Get series and set DataLabel properties.
for (var i = 0; i < 3; i++) {
    var series = chart.series().get(i);
    series.dataLabels = {
        showValue: true,
        showCategoryName: false,
        showPercentage: false,
        separator: ",",
        position: GC.Spread.Sheets.Charts.DataLabelPosition.Center,
        format: "#,##0",    
        color: "white",
        backColor: "red",
        borderColor: "blue",
        borderWidth: 2
    };

    // Set source linked formatter for DataLabels.
    series.dataLabels.numberFormatLinked = true;
    chart.series().set(i, series);

Note: The Scatter chart is an exception, where the “x value”, “y value”, and the “bubble size” fields in the data label use the user-specified formatter.

Set Source Linked Formatter for Axis

When numberFormatLinked property is set to true, the chart’s axis retains the data source cell format regardless of the formatting code linked to the axis where,

• If the axis type is text axis, all axis labels are formatted from the series point’s formatter.

• If the axis type is date axis or value axis, all axis labels are formatted from the series' first point formatter.

When numberFormatLinked property is set to false, the chart’s axis uses the specified number formatter. However, for the value axis, all axis labels are formatted from the series’ first point formatter.

Example: Let’s say you want to create a performance dashboard for different browsers that tracks monthly sales figures. Your dashboard includes a clustered column chart displaying the average monthly sales of each browser, and you want to ensure that the x-axis on the chart dynamically updates its formatting based on a user-selected preference for date formatting. The following chart depicts dates on the chart’s x-axis for sales data when the numberFormatLinked property is set to true and the axis type is set to 'text'.

The following code sample depicts how to implement cell number formatting using linked cells to dynamically update the axis.

// Set source linked formatter for Axis.
var chart = activeSheet.charts.all()[0];
var axes = chart.axes();
axes.primaryCategory.categoryType = GC.Spread.Sheets.Charts.CategoryType.CategoryScale;
axes.primaryCategory.numberFormatLinked = true;
chart.axes(axes);
var series = chart.series().get(0);
series.dataLabels.numberFormatLinked = true;
chart.series().set(0, series);

Limitations

• Charts require additional support to refresh the data if the data source formatter is updated.