SpreadJS' spreadsheet component, Spread.Sheets, has a lot of features, one of which is conditional formatting. This gives developers and users the power to format cell styles according to specific conditions that they can set. This functionality can be used to create a JavaScript Gantt chart utilizing specific formulas to set the color for specific cells.
Download the sample zip
We'll be creating a Gantt chart that looks like this:
Set up the Spread.Sheets project
To start off, we will need to first load the Spread.Sheets library and style files:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>Gantt Charts with SJS Conditional Formatting</title>
<link href="http://cdn.grapecity.com/spreadjs/hosted/css/gc.spread.sheets.excel2013white.11.0.1.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="http://cdn.grapecity.com/spreadjs/hosted/scripts/gc.spread.sheets.all.11.0.1.min.js"></script>
</head>
</html>
If you're deploying your page, you'll also need to add your license key:
<script>
GC.Spread.Sheets.LicenseKey = "<Your Deployment Key>";
</script>
Then add a DIV element to host the Spread.Sheets instance:
<body>
<div id="ss" style="width: 1200px; height: 700px; border: 1px solid gray"></div>
</body>
Next, add some variables to initialize and call functions that we'll be writing later on in this tutorial:
var spread, activeSheet, sampleTable, data, table, monthNameStyle;
window.onload = function () {
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
activeSheet = spread.getActiveSheet();
activeSheet.setColumnCount(44);
spread.suspendPaint();
LoadData();
SetGanttChart();
spread.options.allowUserEditFormula = false;
spread.resumePaint();
}
Load the data into the JavaScript Gantt chart
After setting up the workbook, we can add some data to it. In this tutorial we'll be adding data to a table in the sheet. We'll add the table, set the data columns to bind to, and format the data:
function LoadData() {
data = [
{ "task": "Strategy Meeting", "start": "2018/1/11", "end": "2018/1/11" },
{ "task": "Product Design", "start": "2018/1/12", "end": "2018/1/15" },
{ "task": "Design Check", "start": "2018/1/15", "end": "2018/1/20" },
{ "task": "Function Implementation", "start": "2018/1/20", "end": "2018/1/25" },
{ "task": "Quality Inspection", "start": "2018/1/25", "end": "2018/2/10" },
{ "task": "Release", "start": "2018/2/10", "end": "2018/2/10" }
];
table = activeSheet.tables.addFromDataSource("tableTasks", 1, 0, data);
var dataColumns = ["Task", "Start", "End"];
for (var d = 0; d < dataColumns.length; d++)
table.setColumnName(d, dataColumns[d]);
var dateFormatter = new GC.Spread.Formatter.GeneralFormatter("MM/dd/yyyy", "en-us");
for (var r = 0; r <= table.range().rowCount; r++) {
for (var c = 1; c < 3; c++) {
activeSheet.getCell(r, c).formatter(dateFormatter);
}
}
for (var c = 0; c < 3; c++) {
activeSheet.setColumnWidth(c, 180);
}
}
Create the JavaScript Gantt chart
Before we add conditionally formatted cells, we'll add the columns for the month and the day:
function SetGanttChart() {
activeSheet.addSpan(0, 3, 1, 22);
activeSheet.setValue(0, 3, "January");
activeSheet.addSpan(0, 25, 1, 10);
activeSheet.setValue(0, 25, "February");
var dayNumber = new Date(2018, 0, 10);
for (var c = 3; c < 35; c++) {
activeSheet.setValue(1, c, dayNumber);
dayNumber.setDate(dayNumber.getDate() + 1);
}
var dayFormatter = new GC.Spread.Formatter.GeneralFormatter("dd", "en-us");
for (var c = 3; c < 35; c++) {
activeSheet.getCell(1, c).formatter(dayFormatter);
}
for (var c = 3; c < 35; c++) {
activeSheet.setColumnWidth(c, 25);
}
}
Now we can add formulas to the cells, which will check if the day column falls within the specified date range in the data source. If it does, a "1" will be placed in the cell. Otherwise, it will be left blank:
var forumlaString, dayColumnRange, dayColumnRangeString, dateRange, dateRangeString = "";
for (var r = 2; r <= table.range().rowCount; r++) {
for (var c = 3; c < 35; c++) {
formulaString = "IF(";
dayColumnRange = new GC.Spread.Sheets.CellRange(activeSheet, 1, c, 1, 1);
dayColumnRangeString = GC.Spread.Sheets.CalcEngine.rangeToFormula(dayColumnRange, 0, 0, GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.colRelative, false);
formulaString += dayColumnRangeString + "=MEDIAN(";
dateRange = new GC.Spread.Sheets.CellRange(activeSheet, r, 1, 1, 2);
dateRangeString = GC.Spread.Sheets.CalcEngine.rangeToFormula(dateRange, 0, 0, GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.rowRelative, false);
formulaString += dateRangeString + "," + dayColumnRangeString + "),1,\"\")";
activeSheet.setFormula(r, c, formulaString);
}
}
Once the formulas are added, we can add some conditional formatting to set the color of the cells based on the value: if the value is "1" then set the color of that cell; otherwise, leave the cell. This creates the body of the Gantt chart:
var color = "lightBlue"
var styleBlue = new GC.Spread.Sheets.Style();
styleBlue.backColor = color;
styleBlue.foreColor = color;
activeSheet.conditionalFormats.addCellValueRule(
GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.equalsTo,
1,
undefined,
styleBlue,
[new GC.Spread.Sheets.Range(2, 3, table.range().rowCount, 33)]);
To make the chart more readable, we can add some borders around the data area and the headers, and add some formatting:
function SetBorders() {
monthNameStyle = new GC.Spread.Sheets.Style();
monthNameStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
monthNameStyle.backColor = "lightBlue";
var border = new GC.Spread.Sheets.LineBorder;
border.color = "black";
border.style = GC.Spread.Sheets.LineStyle.double;
monthNameStyle.borderLeft = border;
monthNameStyle.borderTop = border;
monthNameStyle.borderRight = border;
monthNameStyle.borderBottom = border;
for (var i = 3; i < 35; i++) {
activeSheet.setStyle(0, i, monthNameStyle);
}
activeSheet.getRange(1, 3, table.range().rowCount, 1).setBorder(border, { left: true });
activeSheet.getRange(1, 24, table.range().rowCount, 1).setBorder(border, { right: true });
activeSheet.getRange(1, 34, table.range().rowCount, 1).setBorder(border, { right: true });
activeSheet.getRange(table.range().rowCount, 3, 1, 32).setBorder(border, { bottom: true });
}
In this tutorial, we created a Gantt chart using Spread.Sheets' built-in conditional formatting. We created a conditional formatting cell rule to check the values of formulas in each of the cells so we could effectively display the chart. This is just one of the many ways that Spread.Sheets can be more than just a spreadsheet.
Download the sample zip
Try SpreadJS now
Kevin Ashley