My Blog

Creating/Formatting a Table

In continuation from here

What we were doing till now is putting the data in Excel and then formatting the data to look like a presentable table. Now let’s try and create the table using Excel’s inbuilt Table creating facility.

See this code:

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
    End With
End Sub


If you notice, Excel creates dropdown buttons. The main advantage of this dropdown button is that you can sort or filter the data as per your requirements. See image below.