Display Empty Cells in Chart

While visualizing data in spreadsheets, you may want to plot blank or empty cells along with other data in the chart.

SpreadJS provides support for displaying empty cells and null values in charts just like in Excel. This feature helps plot the missing data in a chart and hence facilitates users in quickly identifying and correcting any issues with the data.

With this feature, users can display empty cells or null values as gaps, show empty cells as zero values and connect empty points with a line as per their requirements and specific preferences. By default, charts referencing empty cells in a worksheet automatically fill the missing data by connecting the empty points as a line.

Show Empty Cells As Gaps

Users can display empty cells as gaps in the chart by using the displayBlanksAs method of the Chart class.

Example - The following image depicts a chart that compares the annual sales revenues of two different companies. You may notice that the spreadsheet contains blank data in cells D2 and D3. Now, if you want to display these empty cells (D2 and D3) as gaps in the chart, simply set the value of the displayBlanksAs method as "gaps" and your chart will be displayed as shown in the below screenshot.!

Show Empty Cells As Zero Values

Users can display empty cells as zero values in the chart by using the displayBlanksAs method of the Chart class.

Example - The following image depicts a chart that compares the annual sales revenues of two different companies. You may notice that the spreadsheet contains blank data in cells D2 and D3. Now, if you want to display these empty cells (D2 and D3) as zero values in the chart, simply set the value of the displayBlanksAs method as "zero" and your chart will be displayed as shown in the below screenshot.

Connect Empty Points With Line

Users can display empty data as a connected line in the chart by using the displayBlanksAs method of the Chart class.

Example - The following image depicts a chart that compares the annual sales revenues of two different companies. You may notice that the spreadsheet contains blank data in cells D2 and D3. Now, if you want to display these empty cells (D2 and D3) as connected values in the chart, simply set the value of the displayBlanksAs method as "connected" and your chart will be displayed as shown in the below screenshot.

Supported Chart Types

All chart types support this feature. However, there are some differences in how empty cells will be displayed in various charts, as explained below:

• For line, lineMarkers, xyScatterLines, xyScatterLinesNoMarkers, xyScatterSmooth, xyScatterSmoothNoMarkers, and radar chart types; all the three options (gaps, zero and connected) are supported.

• For a combo chart, if it contains any series of types: line, lineMarkers, xyScatterLines, xyScatterLinesNoMarkers, xyScatterSmooth, xyScatterSmoothNoMarkers, and radar; then it will support all the three options (gaps, connected and zero); otherwise only two options: gaps and zero are supported.

• For sunburst, treemap, lineMarkersStacked, lineMarkersStacked100, lineStacked, and lineStacked100 chart types, only the zero option is supported (i.e. users can only show the empty cells as zero values).

• For other non-combo chart types, only gaps and zero are supported.

The following code sample shows how to display empty cells as gaps in a chart.

// Initializing Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });

// Get the activesheet
var activeSheet = spread.getActiveSheet();
activeSheet.suspendPaint();
// Prepare data for chart
activeSheet.setValue(0, 1, "Y-2013");
activeSheet.setValue(0, 2, "Y-2014");
activeSheet.setValue(0, 3, "Y-2015");
activeSheet.setValue(0, 4, "Y-2016");
activeSheet.setValue(0, 5, "Y-2017");
activeSheet.setValue(0, 6, "Y-2018");
activeSheet.setValue(1, 0, "Gradlco Corp. Ltd");
activeSheet.setValue(2, 0, "Saagiate Corp. Ltd.");
activeSheet.setColumnWidth(0, 120);
var dataArray =
[
  [2331, 4663, , 5689, 6230, 6360],
  [2284, 2030, , 1560, 2324, 1304]
];
activeSheet.setArray(1, 1, dataArray);
// Add lineMarker chart
var chart = activeSheet.charts.add('chart1', GC.Spread.Sheets.Charts.ChartType.lineMarkers, 30, 85, 400, 250, 'A1:G3');
// Configure - Displaying blank values as Gaps
chart.displayBlanksAs(GC.Spread.Sheets.Charts.DisplayBlanksAs.gaps);
// Configure Chart Title
var title = chart.title();
title.text = "Annual Sales Record";
title.fontFamily = "Cambria";
title.fontSize = 16;
title.color = "Red";
chart.title(title);
activeSheet.resumePaint();