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

  • Microsoft Revamps Fledgling AutoGen Framework for Agentic AI

    Only at v0.4, Microsoft's AutoGen framework for agentic AI -- the hottest new trend in AI development -- has already undergone a complete revamp, going to an asynchronous, event-driven architecture.

  • IDE Irony: Coding Errors Cause 'Critical' Vulnerability in Visual Studio

    In a larger-than-normal Patch Tuesday, Microsoft warned of a "critical" vulnerability in Visual Studio that should be fixed immediately if automatic patching isn't enabled, ironically caused by coding errors.

  • Building Blazor Applications

    A trio of Blazor experts will conduct a full-day workshop for devs to learn everything about the tech a a March developer conference in Las Vegas keynoted by Microsoft execs and featuring many Microsoft devs.

  • Gradient Boosting Regression Using C#

    Dr. James McCaffrey from Microsoft Research presents a complete end-to-end demonstration of the gradient boosting regression technique, where the goal is to predict a single numeric value. Compared to existing library implementations of gradient boosting regression, a from-scratch implementation allows much easier customization and integration with other .NET systems.

  • Microsoft Execs to Tackle AI and Cloud in Dev Conference Keynotes

    AI unsurprisingly is all over keynotes that Microsoft execs will helm to kick off the Visual Studio Live! developer conference in Las Vegas, March 10-14, which the company described as "a must-attend event."

Subscribe on YouTube