Listing 1

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
comments powered by Disqus

Featured

  • Java on Visual Studio Code Going Cloud Native

    Cloud-native development figures prominently in a new roadmap published by Microsoft's Java on Visual Studio Code dev team.

  • Speed Lines Graphic

    Quantum-Inspired Annealing Using C# or Python

    Dr. James McCaffrey of Microsoft Research explains a new idea that slightly modifies standard simulated annealing by borrowing ideas from quantum mechanics.

  • Visual Studio 2022 v17.1 Preview 3 Improves Web Tools

    Microsoft quietly shipped Visual Studio 2022 v17.1 Preview 3 with enhancements to web tools.

  • Progress Telerik Adds 20-Plus Components for Blazor, .NET MAUI and WinUI

    The R1 2022 release of Progress Telerik development tooling adds more than 20 new components to the Blazor, .NET MAUI and WinUI offerings.

Upcoming Events