We are trying to move away from Silverlight based chart controls to more plugin-less controls. We like HighCharts and are now testing implementing it into our existing codebase. We have found DotNet.HighCharts as a possible contender to quickly create charts without having to parse out all the javascript.
My question is how do you pass in data to be used in the chart when it is contained within a DataTable? Following along with the answer here I have seen that Linq might be the way to go. However I am having a hard time getting the code to actually build. The documentation for DotNet.HighCharts does not show any examples of pulling non-static data so no luck there. This is a snippet of my code so far:
Dim da3 As New SqlDataAdapter(sql3, conn3)
da3.Fill(dt3)
da3.Dispose()
conn3.Close()
Dim chart3 As Highcharts = New Highcharts("chart").InitChart(New Chart() With { _
.PlotShadow = False _
}).SetTitle(New Title() With { _
.Text = "Browser market shares at a specific website, 2010" _
}).SetTooltip(New Tooltip() With { _
.Formatter = "function() { return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %'; }" _
}).SetPlotOptions(New PlotOptions() With { _
.Column = New PlotOptionsColumn With { _
.AllowPointSelect = True, _
.Cursor = Cursors.Pointer, _
.DataLabels = New PlotOptionsColumnDataLabels() With { _
.Color = ColorTranslator.FromHtml("#000000"), _
.Formatter = "function() { return '<b>'+ this.point.name +'</b>: '+ this.percentage +' %'; }" _
} _
} _
}).SetSeries(New Series() With { _
.Type = ChartTypes.Column, _
.Name = "Browser share", _
.Data = New Helpers.Data(dt3.Select(Function(x) New Options.Point() With {.X = x.periodyear, .Y = x.rate}).ToArray()) _
})
ltrChart3.Text = chart3.ToHtmlString()
I am importing the following:
Imports DotNet.Highcharts
Imports DotNet.Highcharts.Options
Imports System.Data
Imports System.Data.SqlClient
Imports DotNet.Highcharts.Enums
Imports System.Drawing
Imports System.Collections.Generic
Imports System.Linq
The error I get is on the .Data = New Helpers.Data(dt3.Select(Function(x) New Options.Point() With {.X = x.periodyear, .Y = x.rate}).ToArray()) _ line. I am getting Lambda expression cannot be converted to 'String' because 'String' is not a delegate type as my error on the Function....rate} bit.
EDIT: First attempt at modifying existing code
Dim chart1 As Highcharts = New DotNet.Highcharts.Highcharts("test")
Dim series As Series = New Series()
series.Name = "CO Rates"
For i As Integer = 0 To dt.Rows.Count - 1
series.Data = New Helpers.Data(New Object() {dt.Rows(i)("periodyear"), dt.Rows(i)("rate")})
Next
chart1.SetSeries(series).InitChart(New Chart() With { _
.Type = ChartTypes.Column})
This just produces 2 columns the first is at x-position 0 and its value is 2011 and the other is at x-position 1 and its value is 8.3. This is supremely odd as it looks to me like it is taking the last point in the dataTable and then make its {x, y} value ({2011, 8.3}) into 2 distinct points with the x and y values both being the y value like {0, x} and {1, y}. I need to get:
- A single series with {periodyear, rate} taken from the dataTable.
- In the future I want to have a series for each location with its {periodyear, rate} taken from the dataTable.
EDIT 2:
Okay, I have it converting the results to a datadictionary and I am getting the chart to display all points. Only issue now is converting the dt.Rows(i)("periodyear") into a DateTime value. This is the code so far (which fails on the PaseExact method):
Dim series As Series = New Series()
series.Name = "CO Rates"
Dim xDate As DateTime
Dim data As New Dictionary(Of DateTime, Decimal)
For i As Integer = 0 To dt.Rows.Count - 1
xDate = DateTime.ParseExact(dt.Rows(i)("periodyear").ToString, "YYYY", Globalization.CultureInfo.InvariantCulture)
data.Add(xDate, dt.Rows(i)("rate"))
Next
Dim chartData As Object(,) = New Object(data.Count - 1, 1) {}
Dim x As Integer = 0
For Each pair As KeyValuePair(Of DateTime, Decimal) In data
chartData.SetValue(pair.Key, x, 0)
chartData.SetValue(pair.Value, x, 1)
x += 1
Next
Dim chart1 As Highcharts = New Highcharts("chart1").InitChart(New Chart() With { _
.Type = ChartTypes.Column _
}).SetTitle(New Title() With { _
.Text = "Chart 1" _
}).SetXAxis(New XAxis() With { _
.Type = AxisTypes.Linear _
}).SetSeries(New Series() With { _
.Data = New Helpers.Data(chartData) _
})
If I change this to just the string value (for example 1980) and then change the AxisTypes to Linear for the x-axis I do display data. It is just off somehow - 1980 looks like 1,980. Baby steps...
EDIT N: This is the final working solution. This seems like it could use some cleaning up. For example I am not sure I need to create a dictionary to put in my X/Y values and then iterate over the dictionary to add the data to my chartData object. This seems like double work.
Dim stfipsList = (From r In dt.AsEnumerable() Select r("stfips")).Distinct().ToList()
Dim SeriesList As New List(Of Series)(stfipsList.Count)
Dim seriesItem(stfipsList.Count) As Series
Dim xDate As DateTime
Dim fakeDate As String
Dim sX As Integer
sX = 1
For Each state In stfipsList
Dim data As New Dictionary(Of DateTime, Decimal)
Dim stateVal As String = state.ToString
Dim recCount As Integer = dt.Rows.Count - 1
For i As Integer = 0 To recCount
If dt.Rows(i)("stfips").ToString = stateVal Then
fakeDate = "1/1/" + dt.Rows(i)("periodyear").ToString
xDate = DateTime.Parse(fakeDate)
data.Add(xDate.Date, dt.Rows(i)("unemprate"))
End If
Next
Dim chartData As Object(,) = New Object(data.Count - 1, 1) {}
Dim x As Integer = 0
For Each pair As KeyValuePair(Of DateTime, Decimal) In data
chartData.SetValue(pair.Key, x, 0)
chartData.SetValue(pair.Value, x, 1)
x += 1
Next
seriesItem(sX) = New Series With {
.Name = state.ToString, _
.Data = New Helpers.Data(chartData)
}
SeriesList.Add(seriesItem(sX))
sX = sX + 1
Next
Dim chart1 As Highcharts = New Highcharts("chart1").InitChart(New Chart() With { _
.Type = ChartTypes.Line _
}).SetTitle(New Title() With { _
.Text = "Annual Unemployment Rate" _
}).SetTooltip(New Tooltip() With { _
.Formatter = "function() { return '<b>'+ this.series.name + ': ' + Highcharts.dateFormat('%Y', this.x) +'</b>: '+ this.y +' %'; }" _
}).SetXAxis(New XAxis() With { _
.Type = AxisTypes.Datetime _
}).SetYAxis(New YAxis() With { _
.Min = 0, _
.Title = New YAxisTitle() With { _
.Text = "Unemployment Rate", _
.Align = AxisTitleAligns.High _
} _
}).SetSeries(SeriesList.[Select](Function(s) New Series() With { _
.Name = s.Name, _
.Data = s.Data _
}).ToArray())
ltrChart1.Text = chart1.ToHtmlString()
