Listing 1

• 06/20/2011

Imports Microsoft.Office.Tools.Ribbon

Public Class Ribbon

    Private Sub Ribbon_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

    End Sub

    Private Sub chkChinook_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
        Handles chkChinook.Click
        Call UpdatePeriodDropDown()
    End Sub

    Private Sub chkNorthwind_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
        Handles chkNorthwind.Click
        Call UpdatePeriodDropDown()
    End Sub

    Private Sub UpdatePeriodDropDown()
        Dim periods As List(Of String) =
            Utility.UpdateAccountingPeriods(chkChinook.Checked, chkNorthwind.Checked)
        Me.ddlPeriod.Items.Clear()
        If (Not IsNothing(periods) AndAlso periods.Count > 0) Then
            For index = 0 To periods.Count - 1
                Dim entry = Factory.CreateRibbonDropDownItem
                entry.Label = periods(index)
                Me.ddlPeriod.Items.Add(entry)
            Next
        End If
    End Sub

    Private Sub btnLoadData_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) _
        Handles btnLoadData.Click

        Dim AccountingPeriod = ddlPeriod.SelectedItem.Label
        WriteConsolidatedSheet(AccountingPeriod)
    End Sub

    Private Sub btnTestAddWorkSheet_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnTestAddWorkSheet.Click
        Dim wsUtility As New Utility
        Dim wsNextSheetNumber = Globals.ThisWorkbook.Worksheets.Count + 1
        wsUtility.AddWorksheet(String.Format("AfterSheet{0}", wsNextSheetNumber), True)
    End Sub

    Private Sub btnTestWorksheetNoName_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnTestWorksheetNoName.Click
        Dim wsUtility As New Utility
        Dim wsNextSheetNumber = Globals.ThisWorkbook.Worksheets.Count + 1
        wsUtility.AddWorksheet(String.Format("BeforeSheet{0}", wsNextSheetNumber), False)
    End Sub

    ' FillWorkSheetWithTable("Ref_Rows", Properties.Settings.Default.Ref_Rows, "", "");

    Private Sub btnChinookDetailForPeriod_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnChinookDetailForPeriod.Click
        Dim AccountingPeriod = txtPeriod.Text
        Dim wsUtility As New Utility
        Dim wsName = String.Format("Chinook Invoices {0}", AccountingPeriod)
        wsUtility.AddWorksheet(wsName, True)
        wsUtility.AddChinookInvoicesForPeriod(wsName, AccountingPeriod)
    End Sub

    Private Sub ddlTestLocateLabel_SelectionChanged(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles ddlTestLocateLabel.SelectionChanged
        Dim LabelText = ddlTestLocateLabel.SelectedItem.Label
        Dim wsUtility As New Utility
        Dim row = wsUtility.FindFirstOccuranceRow(1, LabelText)
        MessageBox.Show(String.Format("Text '{0}' found in row {1}", LabelText, row))
    End Sub

    Public Sub WriteConsolidatedSheet(AccountingPeriod As String)
        Dim wsUtility As New Utility

        wsUtility.WriteValueToSheet("Period", 3, AccountingPeriod)
        'MessageBox.Show(String.Format("Load Data Clicked for period {0}", AccountingPeriod))

        Dim ChinookTotalSales = Data.Chinook.Data.TotalSalesForPeriod(AccountingPeriod)
        wsUtility.WriteValueToSheet("ChinookSales", 3, ChinookTotalSales.ToString)
        'MessageBox.Show("Total Sales Retrieved For Chinook")

        Dim NorthwindTotalSales = Data.Northwind.Data.TotalSalesForPeriod(AccountingPeriod)
        wsUtility.WriteValueToSheet("NorthwindSales", 3, NorthwindTotalSales.ToString)
        'MessageBox.Show("Total Sales Retrieved For Northwind")

        Dim TotalSales = ChinookTotalSales + NorthwindTotalSales
        wsUtility.WriteValueToSheet("CombinedSales", 3, TotalSales.ToString)

        Dim LargestSale = "Chinook"
        If NorthwindTotalSales > ChinookTotalSales Then LargestSale = "Northwind"

        Dim ConsolidatedDetail = wsUtility.ConsolidateDetail(AccountingPeriod)
        Dim BoldRow = 0

        For index = 0 To ConsolidatedDetail.Count - 1
            Dim row As Integer = 0
            If index = 0 Then
                row = wsUtility.FindFirstOccuranceRow(1, "FormatRow")
            ElseIf index = ConsolidatedDetail.Count - 1 Then
                row = wsUtility.FindFirstOccuranceRow(1, "DataRow")
            Else
                row = wsUtility.FindFirstOccuranceRow(1, "DataRow")
                wsUtility.InsertRowActiveSheet(row)
                'MessageBox.Show("Inserted Row")
                wsUtility.CopyRowActiveSheet(row + 1, row, False)
                'MessageBox.Show("Copied Row")
            End If

            'Should the line be bold due to highest sales for the month?
            If ConsolidatedDetail(index).LargestSale Then BoldRow = row

            wsUtility.WriteValueToSheet(row, 1, "")
            wsUtility.WriteValueToSheet(row, 2, ConsolidatedDetail(index).Company)
            wsUtility.WriteValueToSheet(row, 3, (index + 1).ToString)
            wsUtility.WriteValueToSheet(row, 4, ConsolidatedDetail(index).ItemDate.ToShortDateString)
            wsUtility.WriteValueToSheet(row, 5, ConsolidatedDetail(index).CustomerName)
            wsUtility.WriteValueToSheet(row, 6, ConsolidatedDetail(index).Amount.ToString)
            wsUtility.WriteValueToSheet(row, 8, ConsolidatedDetail(index).SalesToDate.ToString)
            row += 1
            'MessageBox.Show("Row Completed")
        Next

        'Set Largest Sale Row bold
        If BoldRow > 0 Then wsUtility.SetRowBold(BoldRow)

        'Hide Column A
        wsUtility.HideColumn(1)

        'Add a new worksheet to hold Chinook Invoices detail
        wsUtility.AddWorksheet("Chinook Detail", True)
        wsUtility.AddChinookInvoicesForPeriod("Chinook Detail", AccountingPeriod)
    End Sub

    Private Sub btnWorksheetSize_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnWorksheetSize.Click
        Dim wsUtility As New Utility
        Dim wsSize = wsUtility.GetActiveWorksheetDimensions()
        MessageBox.Show(String.Format("Active WorkSheet Size = {0} x {1}", wsSize.LastRow, wsSize.LastColumn))
    End Sub

    Private Sub txtColumnNumber_TextChanged(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles txtColumnNumber.TextChanged
        Dim colNum = txtColumnNumber.Text
        Dim wsUtility As New Utility
        Dim colLetter = wsUtility.GetColumnLetter(Integer.Parse(colNum))
        MessageBox.Show(String.Format("Column Letter for {0} is {1}", colNum, colLetter))
    End Sub

    Private Sub btnLargestOrderForPeriod_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles btnLargestOrderForPeriod.Click
        Dim AccountingPeriod = txtPeriod.Text
        Dim LargestChinook = Data.Chinook.Data.LargestSaleForPeriod(AccountingPeriod)
        Dim msg1 = String.Format("Largest Invoice for Chinook in period {0} is InvoiceID {1}", AccountingPeriod, LargestChinook)
        MessageBox.Show(msg1)
        Dim LargestNorthwind = Data.Northwind.Data.LargestSaleForPeriod(AccountingPeriod)
        Dim msg2 = String.Format("Largest Order for Chinook in period {0} is OrderID {1}", AccountingPeriod, LargestNorthwind)
        MessageBox.Show(msg2)
    End Sub
End Class