Create Column Charts Using OWC11

Imports owc11 = Microsoft.Office.Interop.Owc11
Public Class frm100PercentStackedColumn

    Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClose.Click
        Me.Close()
    End Sub

    Private Sub cmd100PercentStackedColumn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmd100PercentStackedColumn.Click
        DataGridView1.Rows.Clear()

        DataGridView1.Rows.Add()
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(0).Value = "Code Project Members"
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(1).Value = CInt(Val(DataGridView1.Columns(1).HeaderText) / Val(DataGridView1.Rows.Count))
        For j As Integer = 2 To 4
            DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j).Value = Val(DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j - 1).Value) * 4
        Next

        DataGridView1.Rows.Add()
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(0).Value = "Second Site Members"
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(1).Value = CInt(Val(DataGridView1.Columns(1).HeaderText) / Val(DataGridView1.Rows.Count))
        For j As Integer = 2 To 4
            DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j).Value = Val(DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j - 1).Value) * 3
        Next

        DataGridView1.Rows.Add()
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(0).Value = "Third Site Members"
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(1).Value = CInt(Val(DataGridView1.Columns(1).HeaderText) / Val(DataGridView1.Rows.Count))
        For j As Integer = 2 To 4
            DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j).Value = Val(DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j - 1).Value) * 2
        Next

        DataGridView1.Rows.Add()
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(0).Value = "Fourth Site Members"
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(1).Value = CInt(Val(DataGridView1.Columns(1).HeaderText) / Val(DataGridView1.Rows.Count))
        For j As Integer = 2 To 4
            DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j).Value = Val(DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j - 1).Value) * 1
        Next

        DataGridView1.Rows.Add()
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(0).Value = "Fifth Site Members"
        DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(1).Value = CInt(Val(DataGridView1.Columns(1).HeaderText) / Val(DataGridView1.Rows.Count))
        For j As Integer = 2 To 4
            DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j).Value = Val(DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(j - 1).Value) * 2
        Next

        Dim categories(3)
        For i As Integer = 0 To 3
            categories(i) = DataGridView1.Columns(i + 1).HeaderText.ToString
        Next
        Dim values(3)
        Dim chConstants

        ' Clear the contents of the chart workspace. This removes
        ' any old charts that may already exist and leaves the chart workspace
        ' completely empty. One chart object is then added.
        AxChartSpace1.Clear()
        AxChartSpace1.Charts.Add()
        chConstants = AxChartSpace1.Constants
        ' Add Five series to the chart

        AxChartSpace1.Charts(0).SeriesCollection.Add()
        AxChartSpace1.Charts(0).SeriesCollection.Add()
        AxChartSpace1.Charts(0).SeriesCollection.Add()
        AxChartSpace1.Charts(0).SeriesCollection.Add()
        AxChartSpace1.Charts(0).SeriesCollection.Add()

        Dim MaxTotal As Integer = 0

        AxChartSpace1.Charts(0).PlotArea.Interior.Color = "White"
        For j As Integer = 0 To DataGridView1.Rows.Count - 2
            AxChartSpace1.Charts(0).SeriesCollection(j).SetData(chConstants.chDimCategories, chConstants.chDataLiteral, categories)
            For i As Integer = 1 To DataGridView1.Columns.Count - 1
                values(i - 1) = Val(DataGridView1.Rows(j).Cells(i).Value)
                If values(i - 1) > MaxTotal Then
                    MaxTotal = values(i - 1)
                End If
            Next
            AxChartSpace1.Charts(0).SeriesCollection(j).SetData(chConstants.chDimValues, chConstants.chDataLiteral, values)
            AxChartSpace1.Charts(0).SeriesCollection(j).Caption = DataGridView1.Rows(j).Cells(0).Value
        Next
        AxChartSpace1.Charts(0).SeriesCollection(0).Interior.Color = "DarkOrange"
        AxChartSpace1.Charts(0).SeriesCollection(1).Interior.Color = "Cyan"
        AxChartSpace1.Charts(0).SeriesCollection(2).Interior.Color = "Yellow"
        AxChartSpace1.Charts(0).SeriesCollection(3).Interior.Color = "Red"
        AxChartSpace1.Charts(0).SeriesCollection(4).Interior.Color = "Black"

        If cboChartType.Text = "3D" Then
            AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumnStacked1003D
        Else
            AxChartSpace1.Charts(0).Type = owc11.ChartChartTypeEnum.chChartTypeColumnStacked100
        End If

        AxChartSpace1.Charts(0).HasLegend = True
        AxChartSpace1.Charts(0).Axes(chConstants.chAxisPositionBottom).MajorTickMarks = owc11.ChartTickMarkEnum.chTickMarkOutside

        AxChartSpace1.Charts(0).Axes(0).HasTitle = True
        AxChartSpace1.Charts(0).Axes(0).Title.Caption = "Time Range"
        AxChartSpace1.Charts(0).Axes(0).Title.Font.Name = "Arial"
        AxChartSpace1.Charts(0).Axes(0).Title.Font.Size = 9

        AxChartSpace1.Charts(0).Axes(1).HasTitle = True
        AxChartSpace1.Charts(0).Axes(1).Title.Caption = "Number of Members"
        AxChartSpace1.Charts(0).Axes(1).Title.Font.Name = "Arial"
        AxChartSpace1.Charts(0).Axes(1).Title.Font.Size = 9


    End Sub

    Private Sub cboChartType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboChartType.SelectedIndexChanged
        Call cmd100PercentStackedColumn_Click(Nothing, Nothing)
    End Sub

    Private Sub frm100PercentStackedColumn_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cboChartType.SelectedIndex = 0
    End Sub
End Class