Listing 2

Imports Data.Shared.Shared
Imports Microsoft.Office.Interop.Excel

Public Class Utility
    Public Function GetColumnLetter(ColumnNumber As Integer)
        Dim result = String.Empty
        If (ColumnNumber < 27)="" then="" result="Chr(ColumnNumber" +="" 64)="" else="" dim="" cmultiple="CInt(ColumnNumber" 26)="" dim="" csingle="CInt(ColumnNumber" -="" (cmultiple="" *="" 26))="" result="Chr(CMultiple" +="" 64)="" &="" chr(csingle="" +="" 64)="" end="" if="" return="" result="" end="" function="" public="" shared="" function="" updateaccountingperiods(chinookchecked="" as="" boolean,="" northwindchecked="" as="" boolean)="" as="" list(of="" string)="" dim="" apchinook="" as="" new="" list(of="" string)="" dim="" apnorthwind="" as="" new="" list(of="" string)="" if="" chinookchecked="" then="" apchinook="Data.Chinook.Data.AccountingPeriods()" if="" northwindchecked="" then="" apnorthwind="Data.Northwind.Data.AccountingPeriods()" dim="" result="CombinedAccountingPeriods(apChinook," apnorthwind)="" return="" result="" end="" function="" public="" sub="" addworksheet(name="" as="" string,="" afteractive="" as="" boolean)="" 'add="" a="" new="" worksheet="" to="" the="" left="" or="" right="" of="" active="" sheet,="" with="" specified="" name="" if="" afteractive="" then="" 'add="" the="" new="" sheet="" after="" this="" sheet="" dim="" result="Globals.ThisWorkbook.Worksheets.Add(After:=Globals.ThisWorkbook.ActiveSheet)" result.name="Name" else="" dim="" result="Globals.ThisWorkbook.Worksheets.Add()" result.name="Name" end="" if="" end="" sub="" public="" sub="" addchinookinvoicesforperiod(worksheetname="" as="" string,="" accountingperiod="" as="" string)="" 'exit="" if="" the="" worksheet="" cannot="" be="" found="" dim="" index="" as="" integer="WorkSheetIndex(WorkSheetName)" if="" index="">< 1="" then="" messagebox.show("utility.chinookinvoices="" :="" invalid="" worksheet="" name:="" "="" +="" worksheetname)="" return="" end="" if="" dim="" chinookinvoices="Data.Chinook.Data.InvoicesForPeriod(AccountingPeriod)" globals.thisworkbook.sheets(index).select()="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.Worksheets(index)," excel.worksheet)="" dim="" rownumber="1" ws.cells(rownumber,="" 1)="Chinook Invoice Details for Accounting Period: " +="" accountingperiod="" rownumber="" +="2" ws.cells(rownumber,="" 1)="Invoice #" ws.cells(rownumber,="" 2)="Invoice Date" ws.cells(rownumber,="" 3)="Customer" ws.cells(rownumber,="" 4)="Invoice Total" ws.cells(rownumber,="" 5)="Line #" ws.cells(rownumber,="" 6)="Track" ws.cells(rownumber,="" 7)="Unit" ws.cells(rownumber,="" 8)="Quantity" ws.cells(rownumber,="" 9)="Line Total" rownumber="" +="2" for="" invoice="0" to="" chinookinvoices.count="" -="" 1="" dim="" cinvoice="ChinookInvoices(Invoice)" 'invoice="" fields:="" a:invoiceid,="" b:invoicedate,="" c:customername,="" d:total="" ws.cells(rownumber,="" 1)="cInvoice.InvoiceId" ws.cells(rownumber,="" 2)="cInvoice.InvoiceDate.ToShortDateString()" ws.cells(rownumber,="" 3)="cInvoice.Customer.LastName" +="" ",="" "="" +="" chinookinvoices(invoice).customer.firstname="" ws.cells(rownumber,="" 4)="cInvoice.Total" rownumber="" +="1" for="" invoiceline="0" to="" cinvoice.invoicelines.count="" -="" 1="" 'e:invoicelineid,="" f:track="" name,="" g:="" unitprice,="" h:="" quanity,="" i:="" total="" dim="" clinvoice="cInvoice.InvoiceLines(InvoiceLine)" ws.cells(rownumber,="" 5)="clInvoice.InvoiceLineId" ws.cells(rownumber,="" 6)="clInvoice.Track.Name" ws.cells(rownumber,="" 7)="clInvoice.UnitPrice" ws.cells(rownumber,="" 8)="clInvoice.Quantity" ws.cells(rownumber,="" 9)="clInvoice.UnitPrice" *="" clinvoice.quantity="" rownumber="" +="1" next="" invoiceline="" rownumber="" +="2" next="" invoice="" return="" end="" sub="" 'helper="" function="" public="" function="" worksheetindex(worksheetname="" as="" string)="" as="" integer="" 'linq="" not="" available="" on="" the="" sheets="" collection="" dim="" result="0" dim="" matchname="WorkSheetName.ToLower" for="" index="1" to="" globals.thisworkbook.sheets.count="" dim="" worksheetnamelc="" as="" string="Globals.ThisWorkbook.Sheets(index).Name.ToString.ToLower" if="" (worksheetnamelc="MatchName)" then="" result="index" end="" if="" next="" return="" result="" end="" function="" public="" function="" findfirstoccurancerow(column="" as="" integer,="" searchtext="" as="" string)="" as="" integer="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" firstrow="-1" dim="" locatedcell="ws.Cells.Find(" searchtext,="" ctype(ws.cells(1,="" column),="" excel.range),="" xlfindlookin.xlvalues,="" xllookat.xlwhole,="" xlsearchorder.xlbyrows,="" xlsearchdirection.xlnext,="" false,="" false)="" dim="" result="LocatedCell.Row" return="" result="" end="" function="" public="" sub="" setrowbold(row="" as="" integer)="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" srcerange="ws.Range(" a""="" +="" row.tostring,="" "a"="" +="" row.tostring)="" srcerange.entirerow.font.bold="True" end="" sub="" public="" sub="" hidecolumn(column="" as="" integer)="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" columnletter="GetColumnLetter(column)" dim="" srcerange="ws.Range(ColumnLetter" +="" "1",="" columnletter="" +="" "1")="" srcerange.entirecolumn.hidden="True" end="" sub="" public="" sub="" writevaluetosheet(searchtext="" as="" string,="" column="" as="" integer,="" value="" as="" string)="" dim="" ws="" as="" excel.worksheet="CType(Globals.ThisWorkbook.ActiveSheet," excel.worksheet)="" dim="" row="FindFirstOccuranceRow(1," searchtext)="" if="" row=""> 0 Then ws.Cells(row, Column) = Value
        Return
    End Sub

    Public Sub WriteValueToSheet(Row As Integer, Column As Integer, Value As String)
        If Row > 0 Then
            Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)
            ws.Cells(Row, Column) = Value
        End If
        Return
    End Sub

    Public Structure WorksheetDimensions
        Dim LastRow As Integer
        Dim LastColumn As Integer
    End Structure

    Public Function GetActiveWorksheetDimensions() As WorksheetDimensions
        Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)
        Dim result As New WorksheetDimensions

        result.LastRow = ws.Cells.Find(
            "*",
            CType(ws.Cells(1, 1), Excel.Range),
            XlFindLookIn.xlValues,
            XlLookAt.xlWhole,
            XlSearchOrder.xlByRows,
            XlSearchDirection.xlPrevious,
            False,
            False).Row

        result.LastColumn = ws.Cells.Find(
            "*",
            CType(ws.Cells(1, 1), Excel.Range),
            XlFindLookIn.xlValues,
            XlLookAt.xlWhole,
            XlSearchOrder.xlByColumns,
            XlSearchDirection.xlPrevious,
            False,
            False).Column

        Return result
    End Function

    Public Class ConsolidatedRow
        Public Property Company As String
        Public Property ItemNumber As Integer
        Public Property ItemDate As DateTime
        Public Property OrderInvoiceID As Integer
        Public Property CustomerID As String
        Public Property CustomerName As String
        Public Property Amount As Decimal
        Public Property SalesToDate As Decimal
        Public Property LargestSale As Boolean
    End Class

    Public Function ConsolidateDetail(AccountingPeriod As String) As List(Of ConsolidatedRow)
        Dim result = New List(Of ConsolidatedRow)

        Dim LargestChinookInvoiceID = Data.Chinook.Data.LargestSaleForPeriod(AccountingPeriod)
        Dim ChinookCustomerSalesToDate = Data.Chinook.Data.SalesToPeriodByCustomer(AccountingPeriod)
        Dim ChinookSalesForPeriod = Data.Chinook.Data.InvoicesForPeriod(AccountingPeriod)
        Dim ChinookLargestSale = Data.Chinook.Data.LargestSaleForPeriod(AccountingPeriod)

        Dim LargestNorthwindOrderID = Data.Northwind.Data.LargestSaleForPeriod(AccountingPeriod)
        Dim NorthWindCustomerSalesToDate = Data.Northwind.Data.SalesToPeriodByCustomer(AccountingPeriod)
        Dim NorthwindSalesForPeriod = Data.Northwind.Data.OrdersForPeriod(AccountingPeriod)
        Dim NorthwindLargestSale = Data.Northwind.Data.LargestSaleForPeriod(AccountingPeriod)

        Dim ChinookLarger = ChinookLargestSale >= NorthwindLargestSale
        Dim NorthwindLarger = NorthwindLargestSale >= ChinookLargestSale

        For c = 0 To ChinookSalesForPeriod.Count - 1
            Dim cr As New ConsolidatedRow
            cr.Company = "Chinook"
            cr.ItemDate = ChinookSalesForPeriod(c).InvoiceDate
            cr.OrderInvoiceID = ChinookSalesForPeriod(c).InvoiceId
            cr.CustomerID = ChinookSalesForPeriod(c).CustomerId
            cr.CustomerName = ChinookSalesForPeriod(c).Customer.LastName + ", " + ChinookSalesForPeriod(c).Customer.FirstName
            cr.Amount = ChinookSalesForPeriod(c).Total
            If ChinookLarger AndAlso cr.OrderInvoiceID = ChinookLargestSale Then cr.LargestSale = True
            result.Add(cr)
        Next

        For n = 0 To NorthwindSalesForPeriod.Count - 1
            Dim cr As New ConsolidatedRow
            cr.Company = "Northwind"
            cr.ItemDate = NorthwindSalesForPeriod(n).OrderDate
            cr.OrderInvoiceID = NorthwindSalesForPeriod(n).OrderID
            cr.CustomerID = NorthwindSalesForPeriod(n).CustomerID
            cr.CustomerName = NorthwindSalesForPeriod(n).Customer.CompanyName
            cr.Amount = Data.Northwind.Data.GetTotalForOrder(cr.OrderInvoiceID)
            If NorthwindLarger AndAlso cr.OrderInvoiceID = NorthwindLargestSale Then cr.LargestSale = True
            result.Add(cr)
        Next

        result = (From item In result Order By item.ItemDate).ToList

        For i = 0 To result.Count - 1
            Dim cr = result(i)
            cr.ItemNumber = i + 1
            If cr.Company = "Chinook" Then
                If ChinookCustomerSalesToDate.ContainsKey(cr.CustomerID) Then
                    cr.SalesToDate = ChinookCustomerSalesToDate(cr.CustomerID) + cr.Amount
                    ChinookCustomerSalesToDate(cr.CustomerID) = cr.SalesToDate
                Else
                    ChinookCustomerSalesToDate.Add(cr.CustomerID, cr.Amount)
                End If
            Else
                If NorthWindCustomerSalesToDate.ContainsKey(cr.CustomerID) Then
                    cr.SalesToDate = NorthWindCustomerSalesToDate(cr.CustomerID) + cr.Amount
                    NorthWindCustomerSalesToDate(cr.CustomerID) = cr.SalesToDate
                Else
                    NorthWindCustomerSalesToDate.Add(cr.CustomerID, cr.Amount)
                End If
            End If
        Next
        Return result
    End Function

    'Copy an entire row to a location, optionally inserting a new row at the destination
    Public Sub CopyRowActiveSheet(SourceRowNumber As Integer, DestinationRowNumber As Integer, InsertCopiedRow As Boolean)
        Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)

        Dim wsDimensions As WorksheetDimensions = GetActiveWorksheetDimensions()
        Dim LastColumnLetter = GetColumnLetter(wsDimensions.LastColumn)

        Dim SrceRange = ws.Range("A" + SourceRowNumber.ToString, LastColumnLetter + SourceRowNumber.ToString)
        Dim DestRange = ws.Range("A" + DestinationRowNumber.ToString, LastColumnLetter + DestinationRowNumber.ToString)

        If InsertCopiedRow Then
            DestRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown)
        End If

        DestRange = ws.Range("A" + DestinationRowNumber.ToString(), LastColumnLetter + DestinationRowNumber.ToString())
        DestRange.Value = SrceRange.Value
    End Sub

    'Copy an entire row to a location, optionally inserting a new row at the destination
    Public Sub InsertRowActiveSheet(RowNumber As Integer)
        Dim ws As Excel.Worksheet = CType(Globals.ThisWorkbook.ActiveSheet, Excel.Worksheet)

        Dim wsDimensions As WorksheetDimensions = GetActiveWorksheetDimensions()
        Dim LastColumnLetter = GetColumnLetter(wsDimensions.LastColumn)

        Dim DestRange = ws.Range("A" + RowNumber.ToString, LastColumnLetter + RowNumber.ToString)
        DestRange.EntireRow.Insert(XlInsertShiftDirection.xlShiftDown)
    End Sub
End Class
comments powered by Disqus

Featured

  • Creating a Progressive Web App with Blazor WebAssembly

    Not surprisingly, it's dead easy to create an app in Blazor that runs outside of the browser window and (potentially) in an offline mode. Before you get carried away, though, there are some key design decisions to make.

  • GitLab Takes Over VS Code Extension, Plans Improvements

    DevOps specialist GitLab has officially taken over the control of a GitLab extension for Microsoft's open source, cross-platform Visual Studio Code editor.

  • VS Code Python Tool Now Does Native Notebooks

    The Python Extension for VS Code Insiders team is previewing the newest implementation of notebooks, used frequently in data science with offerings such as Jupyter Notebooks.

  • As .NET 5 Nears, Content/Documentation Reorganization Starts

    A GitHub project is seeking to reorganize documentation and developer content in advance of the November debut of .NET 5, a unification of all things .NET that combines. .NET Core and other components.

  • Windows Devs Get Cross-Platform Page, Issues Repo

    Developers doing their coding on the Windows OS have received two new resource gifts from Microsoft: a new landing page for those using cross-platform technologies and a new GitHub repo with which to report issues to Windows engineering teams.Developers doing their coding on the Windows OS have received two new resource gifts from Microsoft: a new landing page for those using cross-platform technologies and a new GitHub repo with which to report issues to Windows engineering teams.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events