My Blog

Creating/Formatting a Chart

In continuation from here

Now we have our data all setup in the Excel file. It even looks presentable. What if we could also show a graph which depicts the expenses for the months. Wouldn’t that be wonderful?

Consider this code. This will create a Line Graph next to the data.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    '~~> Add a New Workbook
    xlWorkBook = xlApp.Workbooks.Add

    '~~> Display Excel
    xlApp.Visible = True

    '~~> Set the relebant sheet that we want to work with
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    With xlWorkSheet
        '~~> Directly type the values that we want
        .Range("A1").Value = "Month"
        .Range("A2").Value = "January"
        .Range("A3").Value = "February"
        .Range("A4").Value = "March"
        .Range("A5").Value = "April"

        .Range("B1").Value = "Money Spent"
        .Range("B2").Value = "1000.00"
        .Range("B3").Value = "1500.00"
        .Range("B4").Value = "1200.00"
        .Range("B5").Value = "1100.00"

        '~~> Set title for Total and Average Expenses
        .Range("A6").Value = "Total Expense"
        .Range("A7").Value = "Average Expense"

        '~~> Insert formulas
        .Range("B6").Formula = "=Sum(B2:B5)"
        .Range("B7").Formula = "=Average(B2:B5)"

        '~~> Change the range into a tabular format
        .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

        '~~> Format the table
        .ListObjects("Table1").TableStyle = "TableStyleLight8"

        '~~> Format the Total and Average Expenses cells
        With .Range("A6:A7")
            .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
            With .Font
                .ColorIndex = 2 '<~~ Font Color White
                .Size = 8
                .Name = "Tahoma"
                .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                .Bold = True
            End With
        End With

        '~~> Autofitting text in columns
        .Columns("A:B").EntireColumn.AutoFit()

        '~~> Inserting a Graph
        .Shapes.AddChart.Select()
        xlApp.ActiveChart.ApplyCustomType(Excel.XlChartType.xlLineMarkers)
        xlApp.ActiveChart.SetSourceData(Source:=.Range("Sheet1!$A$1:$B$5"))
    End With
End Sub

And this is the output that we will get.

What if we want to format the Chart and change it’s properties so that the chart looks like this?

This code will help us achieve it:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    '~~> Add a New Workbook
    xlWorkBook = xlApp.Workbooks.Add

    Display Excel
    xlApp.Visible = True

    '~~> Set the relebant sheet that we want to work with
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    With xlWorkSheet
        '~~> Directly type the values that we want
        .Range("A1").Value = "Month"
        .Range("A2").Value = "January"
        .Range("A3").Value = "February"
        .Range("A4").Value = "March"
        .Range("A5").Value = "April"

        .Range("B1").Value = "Money Spent"
        .Range("B2").Value = "1000.00"
        .Range("B3").Value = "1500.00"
        .Range("B4").Value = "1200.00"
        .Range("B5").Value = "1100.00"

        '~~> Set title for Total and Average Expenses
        .Range("A6").Value = "Total Expense"
        .Range("A7").Value = "Average Expense"

        '~~> Insert formulas
        .Range("B6").Formula = "=Sum(B2:B5)"
        .Range("B7").Formula = "=Average(B2:B5)"

        '~~> Change the range into a tabular format
        .ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, .Range("$A$1:$B$7"), , Excel.XlYesNoGuess.xlYes).Name = "Table1"

        '~~> Format the table
        .ListObjects("Table1").TableStyle = "TableStyleLight8"

        '~~> Format the Total and Average Expenses cells
        With .Range("A6:A7")
            .Interior.ColorIndex = 1 '<~~ Cell Back Color Black
            With .Font
                .ColorIndex = 2 '<~~ Font Color White
                .Size = 8
                .Name = "Tahoma"
                .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                .Bold = True
            End With
        End With

        '~~> Autofitting text in columns
        .Columns("A:B").EntireColumn.AutoFit()

        '~~> Inserting a Graph
        .Shapes.AddChart.Select()

        '~~> Formatting the chart
        With xlApp.ActiveChart
            '~~> Make it a Line Chart
            .ApplyCustomType(Excel.XlChartType.xlLineMarkers)

            '~~> Set the data range
            .SetSourceData(Source:=xlWorkSheet.Range("$A$1:$B$5"))

            '~~> Fill the background of the chart
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.ObjectThemeColor = _
            Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorBackground1 '<~~ Grey
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.TintAndShade = 0
            xlApp.ActiveChart.ChartArea.Format.Fill.ForeColor.Brightness = -0.150000006
            xlApp.ActiveChart.ChartArea.Format.Fill.Transparency = 0
            xlApp.ActiveChart.ChartArea.Format.Fill.Solid()

            '~~> Make the corners of the Chart Rount
            .Parent.RoundedCorners = True

            '~~> Removing lines and the back color so plot area shows char's background color
            With .PlotArea
                .Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
                .Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
            End With

            '~~> Removing the major gridlines
            .Axes(Excel.XlAxisType.xlValue).MajorGridlines.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse

            '~~> Making the series line smooth
            .SeriesCollection(1).Smooth = True

            '~~> Formatting the legend
            With .Legend
                With .Format.TextFrame2.TextRange.Font.Fill
                    .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                    .ForeColor.RGB = RGB(255, 0, 0)
                    .Transparency = 0
                    .Solid()
                End With

                With .Format.Fill
                    .Visible = Microsoft.Office.Core.MsoTriState.msoTrue
                    .ForeColor.ObjectThemeColor = Microsoft.Office.Core.MsoThemeColorIndex.msoThemeColorBackground2
                    .ForeColor.TintAndShade = 0
                    .ForeColor.Brightness = -0.25
                    .Transparency = 0
                    .Solid()
                End With
            End With

            '~~> Change the format of Y axis to show $ signs
            .Axes(Excel.XlAxisType.xlValue).TickLabels.NumberFormat = "$#,##0.00"

            '~~> Underline the Chart Title
            .ChartTitle.Format.TextFrame2.TextRange.Font.UnderlineStyle = _
            Microsoft.Office.Core.MsoLineStyle.msoLineSingle
        End With
    End With
End Sub