Google Gantt charts… you’ve probably heard of them and maybe even used them. They are easy to toss into a Razor Page when you provide static data for the rows. But, I’m wanting to pull the data from a database table on our SQL server and populate the Gantt chart. I have read countless articles about using PHP and Json files, none have worked for me. I can get the data to populate an HTML table, so I know the data is there. I just can’t format it properly to show in the Gantt Chart. I won’t post any of my code because it’s really just a bunch of snippets of the stuff I’ve found online, including using Ajax calls to get data. Can someone please point me to a modern working example (with source code) of a Google Gantt chart pulling data from a database to successfully populate the chart? This can be in either Razor pages or Blazor. Any help would be greatly appreciated.
Also, I know there are a lot of commercial charts I can use for Razor and Blazor, but we can’t spend any money on this project, which is why I’m trying to use the Google Gantt chart.
Thank you for your time!
UPDATE:
I know I said I wouldn’t post code, but here are the generic example that works, and then the one I can’t get to work…
This one with static data in the rows works:
<h5 style="text-align: center"><u>Written Report Chart</u></h5>
<script type="text/javascript">
google.charts.load('current', {'packages':['gantt']});
google.charts.setOnLoadCallback(drawChart);
function daysToMilliseconds(days) {
return days * 24 * 60 * 60 * 1000;
}
function drawChart() {
var data = new google.visualization.DataTable();
data.addColumn('string', 'Task ID');
data.addColumn('string', 'Task Name');
data.addColumn('date', 'Start Date');
data.addColumn('date', 'End Date');
data.addColumn('number', 'Duration');
data.addColumn('number', 'Percent Complete');
data.addColumn('string', 'Dependencies');
data.addRows([
['Research', 'Find sources',
new Date(2015, 0, 1), new Date(2015, 0, 5), null, 100, null],
['Write', 'Write paper',
null, new Date(2015, 0, 9), daysToMilliseconds(3), 25, 'Research,Outline'],
['Cite', 'Create bibliography',
null, new Date(2015, 0, 7), daysToMilliseconds(1), 20, 'Research'],
['Complete', 'Hand in paper',
null, new Date(2015, 0, 10), daysToMilliseconds(1), 0, 'Cite,Write'],
['Outline', 'Outline paper',
null, new Date(2015, 0, 6), daysToMilliseconds(1), 100, 'Research']
]);
var options = {
height: 275
};
var chart = new google.visualization.Gantt(document.getElementById('WrittenReport'));
chart.draw(data, options);
}
</script>
<div id="WrittenReport" class="chart-container"></div>
<div class="page-divider"></div>
And here is the code for the one that doesn’t work:
In my ‘CSHTML’…
@page
@model ProdSchedPortal.Pages._Testing.AnotherTestModel
@{
}
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', { 'packages': ['gantt'] });
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
$.ajax({
type: "POST",
url: "?handler=ListFilter",
data: {},
contentType: "application/json;charset=utf-8",
dataType: "json",
success: function (response) {
// var data = new google.visualization.DataTable(JSON.parse(response));
var data = new google.visualization.DataTable();
data.addColumn('string', 'Task ID');
data.addColumn('string', 'Task Name');
data.addColumn('date', 'Start Date');
data.addColumn('date', 'End Date');
data.addColumn('number', 'Duration');
data.addColumn('number', 'Percent Complete');
data.addColumn('string', 'Dependencies');
const formattedRows = response.map((row, index) => {
return [
String(index + 1),
`${row["OpDesc"]} (${row["JobNum"]})`,
new Date(row["StartDate"]),
new Date(row["JobOper_DueDate"]),
null,
parseFloat(row["EstProdHours"]) || 0,
null
];
});
data.addRows(formattedRows);
// for (var i = 0; i < response.d.length; i++) {
// var taskId = response.d[i][0].toString();
// var taskname = response.d[i][1].toString();
// var resource = response.d[i][2].toString();
// var startDate = new Date(response.d[i][3]).toString();
// var enddate = new Date(response.d[i][4]).toString();
// var duration = parseInt(response.d[i][5]);
// var percentageComplete = parseInt(response.d[i][6]);
// var dependencies = response.d[i][7].toString();
// data.addRows([[taskId, taskname, resource, startDate, enddate, duration, percentageComplete, dependencies]]);
// }
var options = {
height: 400, width: 500, gantt: { trackHeight: 30 }
};
var chart = new google.visualization.Gantt(document.getElementById('chart_div'));
chart.draw(data, options);
}, error: function (response) {
alert(response.responseText);
}
});
}
</script>
<div id="chart_div" style="height: 180px; margin-top: 50px; border: 1px solid dimgray;"></div>
And in my ‘HTML’, which I made static row data for this example…
public JsonResult? OnPostListFilter()
{
// Get the DataTable from Database.
DataTable dt = new DataTable();
dt.Columns.Add("Task ID", typeof(string));
dt.Columns.Add("Task Name", typeof(string));
dt.Columns.Add("Resource", typeof(string));
dt.Columns.Add("Start Date", typeof(string));
dt.Columns.Add("End Date", typeof(string));
dt.Columns.Add("Duration", typeof(int));
dt.Columns.Add("Percent Complete", typeof(int));
dt.Columns.Add("Dependencies", typeof(string));
dt.Rows.Add("2014Spring", "Spring 2014", "spring", new DateTime(2014, 2, 22, 0, 30, 0), new DateTime(2014, 5, 20, 6, 30, 0), null, 100, null);
dt.Rows.Add("2014Summer", "Summer 2014", "summer", new DateTime(2014, 5, 21, 6, 45, 0), new DateTime(2014, 8, 20, 7, 0, 0), null, 100, null);
dt.Rows.Add("2014Autumn", "Autumn 2014", "autumn", new DateTime(2014, 8, 21, 7, 4, 0), new DateTime(2014, 11, 20, 7, 30, 0), null, 100, null);
dt.Rows.Add("2014Winter", "Winter 2014", "winter", new DateTime(2014, 11, 21, 7, 30, 0), new DateTime(2015, 2, 21, 8, 30, 0), null, 100, null);
dt.Rows.Add("2015Spring", "Spring 2015", "spring", new DateTime(2015, 2, 22), new DateTime(2015, 5, 20), null, 50, null);
dt.Rows.Add("2015Summer", "Summer 2015", "summer", new DateTime(2015, 5, 21), new DateTime(2015, 8, 20), null, 0, null);
dt.Rows.Add("2015Autumn", "Autumn 2015", "autumn", new DateTime(2015, 8, 21), new DateTime(2015, 11, 20), null, 0, null);
dt.Rows.Add("2015Winter", "Winter 2015", "winter", new DateTime(2015, 11, 21), new DateTime(2016, 2, 21), null, 0, null);
dt.Rows.Add("Football", "Football Season", "sports", new DateTime(2014, 8, 4), new DateTime(2015, 1, 1), null, 100, null);
dt.Rows.Add("Baseball", "Baseball Season", "sports", new DateTime(2015, 2, 28), new DateTime(2015, 9, 20), null, 14, null);
dt.Rows.Add("Basketball", "Basketball Season", "sports", new DateTime(2014, 9, 28), new DateTime(2015, 5, 20), null, 86, null);
dt.Rows.Add("Hockey", "Hockey Season", "sports", new DateTime(2014, 9, 8), new DateTime(2015, 5, 21), null, 89, null);
List<object> chartData = new List<object>();
for (int i = 0; i < dt.Rows.Count; i++)
{
chartData.Add(new object[] {
dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2], dt.Rows[i][3],dt.Rows[i][4],
dt.Rows[i][5]==DBNull.Value?0:dt.Rows[i][5], dt.Rows[i][6], dt.Rows[i][7]==DBNull.Value?"":dt.Rows[i][7]
});
}
return new JsonResult(chartData);
}
All I get is a blank alert and screen…
