Creating chart in Excel:

The class "cExcelChart" does everything, just use this class.

Using example:This will create graph and convert graph to image and return image path.

call this code in form:
Dim sGraphQuery As String = "SELECT Periodname,"
sGraphQuery = sGraphQuery & " Instructed,Completed,Cancelled "
'sGraphQuery = sGraphQuery & " Instructed + 8 as Instructed ,Completed + 8 as completed, Cancelled + 8 as cancelled "
sGraphQuery = sGraphQuery & " FROM vwCaseTypeInstructionsCompletionsCancellations"
sGraphQuery = sGraphQuery & " where casetypeid = 0 "


Dim arCols(3) As String
'"Period"
arCols(0) = "Period"
arCols(1) = "Cases Instructed"
arCols(2) = "Cases Completed"
arCols(3) = "Cases Cancellated"

Dim chartTitle As String = "" '"Sale Summary"
Dim xaxisTitle As String = "Period"
Dim yaxisTitle As String = "Instructions"
Dim ChartFullpath As String = System.IO.Path.Combine(chartOutputPath, "ICC_Sale.gif")

Dim sPath As String = generateGraph(sGraphQuery, ChartFullpath, chartTitle, xaxisTitle, yaxisTitle, arCols)

Public Function generateGraph(ByVal sQuery As String, Optional ByVal outputImagePath As String = "", Optional ByVal ChartTitle As String = "", Optional ByVal xAxisTitle As String = "", Optional ByVal yAxisTitle As String = "", Optional ByVal arrColumns() As String = Nothing) As String
Dim ds As DataSet = getDataset(sQuery)

Dim objChart As Object
If arrColumns.Length = 0 Then
objChart = New cExcelChart(ds)
Else
objChart = New cExcelChart(ds, arrColumns)
End If

'Dim objChart As New cExcelChart(ds)
'objChart.ColumnNames = arrColumns

objChart.ChartTitle = ChartTitle
objChart.xAxisTitle = xAxisTitle
objChart.yAxisTitle = yAxisTitle
objChart.AxisTitleFontColor = Color.Blue
objChart.chartOutputPath = outputImagePath

'settings
'ChartTitle
objChart.ChartTitleFontName = "Arial"
objChart.ChartTitleFontSize = 20
objChart.ChartTitleFontColor = Color.Blue

'axes title
objChart.AxisTitleFontColor = Color.DodgerBlue
objChart.AxisTitleFontName = "Arial"
objChart.AxisTitleFontSize = 8

'x and y and legand text colors
objChart.AxisTickLabelsFontColor = Color.DodgerBlue

Return objChart.GenerateChart
End Function


'getDataset
Public Function getDataset(ByVal sQuery As String) As DataSet
'Status Display
lblStatus.Text = "Status:" & vbCrLf & "1. Process started... "
lblStatus.Refresh()

Dim con As SqlConnection
con = New SqlConnection(My.Settings.SQLConnString)
Dim da As SqlDataAdapter = New SqlDataAdapter(sQuery, con)
da.SelectCommand.CommandType = CommandType.Text
Dim ds As DataSet = New DataSet("Report")
con.Open()
System.Threading.Thread.Sleep(1500)
da.Fill(ds)
Return ds
End Function




Imports System
Imports System.Data
Imports System.IO
Imports System.Runtime.InteropServices


Public Class cExcelChart
'constructers

#Region "Constructors"
Friend Sub New(ByVal ds As DataSet)
m_ds = ds
Dim iColumns As Integer

'default column names
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
ReDim Preserve m_ColumnNames(iColumns)
m_ColumnNames(iColumns) = m_ds.Tables(0).Columns(iColumns).ColumnName
Next
End Sub

Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String)
m_ColumnNames = ColumnNames
m_ds = ds
End Sub

'If File.Exists(Filename) Then
' File.Delete(Filename)
'End If

Friend Sub New(ByVal ds As DataSet, ByVal ColumnNames() As String, ByVal chartImageFullPath As String)
m_ColumnNames = ColumnNames
m_ds = ds
m_chartImageFullPath = chartImageFullPath
End Sub

#End Region

#Region "properties"

'properties
' Filename = AppDomain.CurrentDomain.BaseDirectory & "abc.xls"
'Private m_Filename As String = "abc.xls"
Private m_ColumnNames() As String 'column names
Private m_ChartTitle As String = "" ' Chart title
Private m_xAxisTitle As String = "" 'x axis title
Private m_yAxisTitle As String = "" 'y axis
Private m_FileFullpath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "abc.xls")
Private m_ds As DataSet 'dataset
Private m_chartImageFullPath As String = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Now.Millisecond & ".gif")

Private m_ChartTitleFontColor As Color = Color.Black
Private m_ChartTitleFontName As String = "Arial"
Private m_ChartTitleFontSize As Integer = 14

Private m_AxisTitleFontColor As Color = Color.Black
Private m_AxisTitleFontName As String = "Arial"
Private m_AxisTitleFontSize As Integer = 10

Private m_AxisTickLabelsFontColor As Color = Color.Black

Public Property ColumnNames() As Array
Get
Return m_ColumnNames
End Get
Set(ByVal value As Array)
m_ColumnNames = value
End Set
End Property

Public Property ChartTitle() As String
Get
Return m_ChartTitle
End Get
Set(ByVal value As String)
m_ChartTitle = value
End Set
End Property

Public Property ChartTitleFontColor() As Color
Get
Return m_ChartTitleFontColor
End Get
Set(ByVal value As Color)
m_ChartTitleFontColor = value
End Set
End Property


Public Property ChartTitleFontName() As String
Get
Return m_ChartTitleFontName
End Get
Set(ByVal value As String)
m_ChartTitleFontName = value
End Set
End Property

Public Property ChartTitleFontSize() As Integer
Get
Return m_ChartTitleFontSize
End Get
Set(ByVal value As Integer)
m_ChartTitleFontSize = value
End Set
End Property

Public Property AxisTitleFontColor() As Color
Get
Return m_AxisTitleFontColor
End Get
Set(ByVal value As Color)
m_AxisTitleFontColor = value
End Set
End Property

Public Property AxisTitleFontSize() As Integer
Get
Return m_AxisTitleFontSize
End Get
Set(ByVal value As Integer)
m_AxisTitleFontSize = value
End Set
End Property

Public Property AxisTitleFontName() As String
Get
Return m_AxisTitleFontName
End Get
Set(ByVal value As String)
m_AxisTitleFontName = value
End Set
End Property

Public Property AxisTickLabelsFontColor() As Color
Get
Return m_AxisTickLabelsFontColor
End Get
Set(ByVal value As Color)
m_AxisTickLabelsFontColor = value
End Set
End Property


Public Property xAxisTitle() As String
Get
Return m_xAxisTitle
End Get
Set(ByVal value As String)
m_xAxisTitle = value
End Set
End Property

'TickLabels
Public Property yAxisTitle() As String
Get
Return m_yAxisTitle
End Get
Set(ByVal value As String)
m_yAxisTitle = value
End Set
End Property

'excel filepath
Public Property chartOutputPath() As String
Get
Return m_chartImageFullPath
End Get
Set(ByVal value As String)
m_chartImageFullPath = value
End Set
End Property

Public WriteOnly Property dsDataset() As DataSet
Set(ByVal value As DataSet)
m_ds = value
End Set
End Property
#End Region

'Function GenerateChart(ByVal arrColumns() As String, ByVal ds As DataSet) As String 'Excel.Chart
Function GenerateChart() As String 'Excel.Chart
'create new excel application
Dim oexcel As Excel.Application
Dim obook As Excel.Workbook
Dim osheet As Excel.Worksheet
oexcel = CreateObject("Excel.Application")
Try
'add a new workbook
obook = oexcel.Workbooks.Add

'check total sheets in workboob
Dim S As Integer = oexcel.Application.Sheets.Count()

'leaving first sheet delete all the remaining sheets
If S > 1 Then
oexcel.Application.DisplayAlerts = False
Dim J As Integer = S
Do While J > 1
oexcel.Application.Sheets(J).delete()
J = oexcel.Application.Sheets.Count()
Loop
End If
osheet = oexcel.Worksheets(1)
oexcel.Visible = True

'column heading
Dim iColumns As Integer
For iColumns = 0 To m_ColumnNames.Length - 1
osheet.Range(getChar(iColumns + 1) & "3").Value = m_ColumnNames(iColumns)
Next

'start of row
Dim R As Integer = 3
Dim intI As Integer
For intI = 0 To m_ds.Tables(0).Rows.Count - 1
R = R + 1
'For iColumns = 0 To m_ColumnNames.Length - 1
For iColumns = 0 To m_ds.Tables(0).Columns.Count - 1
osheet.Range(getChar(iColumns + 1) & 4 + intI).Value = m_ds.Tables(0).Rows(intI).Item(iColumns)
Next
Next

'create chart objects
Dim oChart As Excel.Chart
Dim MyCharts As Excel.ChartObjects
Dim MyCharts1 As Excel.ChartObject
MyCharts = osheet.ChartObjects

'set chart location
MyCharts1 = MyCharts.Add(150, 130, 430, 250)
oChart = MyCharts1.Chart

With oChart

'set data range for chart
Dim chartRange As Excel.Range
chartRange = osheet.Range("A3", getChar(m_ds.Tables(0).Columns.Count) & R)
.SetSourceData(chartRange)

'set how you want to draw chart i.e column wise or row wise
.PlotBy = Excel.XlRowCol.xlColumns

.HasTitle = True
'.ChartTitle.Font.Bold = True
'.ChartTitle.Caption = "mix"
.ChartTitle.Font.Size = ChartTitleFontSize
.ChartTitle.Font.Name = ChartTitleFontName
.ChartTitle.Font.Color = ColorTranslator.ToOle(ChartTitleFontColor)

.ChartTitle.Text = ChartTitle

'select chart type
.ChartType = Excel.XlChartType.xlLineMarkers

'set data lables for bars
'.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowLabelAndPercent)
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowNone)

'set legend to be displayed or not
.HasLegend = True
'
''remove border
.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone

'set legend location
.Legend.Position = Excel.XlLegendPosition.xlLegendPositionRight

''format legend keys
Dim le As Excel.LegendEntry
Dim lk As Excel.LegendKey
For Each le In .Legend.LegendEntries
lk = le.LegendKey
lk.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleNone
le.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor) 'RGB(1, 2, 3)
lk.Border.Weight = Excel.XlBorderWeight.xlThin
'le.LegendKey.Border.LineStyle = Excel.XlLineStyle.xlContinuous
Next

'Dim i As Integer = 1
'For i = 1 To .SeriesCollection.count()
' .SeriesCollection(i).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)
'Next

'series colors
'.SeriesCollection(1).Border.Color = ColorTranslator.ToOle(Color.AliceBlue)

'display title for X axis and Y axis
Dim xlAxisCategory, xlAxisValue As Excel.Axes

xlAxisCategory = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True

xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Name = AxisTitleFontName
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = xAxisTitle

xlAxisValue = CType(oChart.Axes(, Excel.XlAxisGroup.xlPrimary), Excel.Axes)
xlAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True

xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Name = AxisTitleFontName
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Size = AxisTitleFontSize
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Font.Color = ColorTranslator.ToOle(AxisTitleFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = yAxisTitle

'to start x axes from zero
xlAxisValue.Item(Excel.XlAxisType.xlCategory).AxisBetweenCategories = False

'color of x and y axis labels
xlAxisValue.Item(Excel.XlAxisType.xlCategory).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).TickLabels.Font.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)

'x-axis grid lines
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlValue).MajorGridlines.Border.Weight = Excel.XlBorderWeight.xlHairline
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Select()
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)
xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(AxisTickLabelsFontColor)

'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.Color = ColorTranslator.ToOle(Color.Transparent)
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone

'xlAxisValue.Item(Excel.XlAxisType.xlValue).MinimumScale = 0

'Yaxis grid lines
'xlAxisValue.Item(Excel.XlAxisType.xlCategory).MajorGridlines.Border.Color = RGB(0, 255, 0)
End With
'oChart.Export("C:\download\GraphSheetControl\" & Now.Millisecond & ".gif", "GIF")
oChart.Export(m_chartImageFullPath, System.IO.Path.GetExtension(m_chartImageFullPath).Replace(".", ""))

osheet = Nothing
oexcel.Application.DisplayAlerts = False
obook.Close()
oexcel.Application.DisplayAlerts = True
obook = Nothing
Catch ex As Exception
Throw ex
Finally
oexcel.Quit()
oexcel = Nothing
End Try

Return m_chartImageFullPath
End Function

Function getChar(ByVal iNum As Integer) As Char
Select Case iNum
Case 1
Return "A"
Case 2
Return "B"
Case 3
Return "C"
Case 4
Return "D"
Case 5
Return "E"
Case 6
Return "F"
Case 7
Return "G"
Case 8
Return "H"
Case 9
Return "I"
Case 10
Return "J"
Case 11
Return "K"
Case 12
Return "L"
Case 13
Return "M"
Case 14
Return "N"
Case 15
Return "O"
Case 16
Return "P"
Case 17
Return "Q"
Case 18
Return "R"
Case 19
Return "S"
Case 20
Return "T"
Case 21
Return "U"
Case 22
Return "V"
Case 23
Return "W"
Case 24
Return "X"
Case 25
Return "Y"
Case 26
Return "Z"
End Select
End Function

End Class