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