Sub Main()
' Declare variables to hold refernces to Excel objects.
Dim workBook As Workbook
Dim sharedStrings As SharedStringTable
Dim workSheets As IEnumerable(Of Sheet)
Dim custSheet As WorksheetPart
Dim orderSheet As WorksheetPart
' Declare helper variables.
Dim custID As String
Dim orderID As String
Dim customers As List(Of Customer)
Dim orders As List(Of Order)
' Open the Excel workbook.
Using document As SpreadsheetDocument = _
SpreadsheetDocument.Open("C:\Temp\LinqSample.xlsx", True)
' References to the workbook and Shared String Table.
workBook = document.WorkbookPart.Workbook
workSheets = workBook.Descendants(Of Sheet)()
sharedStrings = _
document.WorkbookPart.SharedStringTablePart.SharedStringTable
' Reference to Excel Worksheet with Customer data.
custID = _
workSheets.First(Function(sheet) sheet.Name = "Customer").Id()
custSheet = DirectCast( _
document.WorkbookPart.GetPartById(custID), WorksheetPart)
' Load Customer data to business object.
customers = _
Customer.LoadCustomers(custSheet.Worksheet, sharedStrings)
' Reference to Excel Worksheet with Order data.
orderID = _
workSheets.First(Function(sheet) sheet.Name = "Order").Id
orderSheet = DirectCast( _
document.WorkbookPart.GetPartById(orderID), WorksheetPart)
' Load Order data to business object.
orders = Order.LoadOrders(orderSheet.Worksheet, sharedStrings)
' List all customers to the console.
' Write header information to the console.
Console.WriteLine("All Customers")
Console.WriteLine("{0, -15} {1, -15} {2, -5}", _
"Customer", "City", "State")
' LINQ Query for all Customers.
Dim allCustomers As IEnumerable(Of Customer) = _
From customer In customers _
Select customer
' Execute query and write customer information to the console.
For Each c As Customer In allCustomers
Console.WriteLine("{0, -15} {1, -15} {2, -5}", _
c.Name, c.City, c.State)
Next
Console.WriteLine()
Console.WriteLine()
' Write all Orders over $100 to the console.
' Write header information to the console.
Console.WriteLine("All Orders over $100")
Console.WriteLine("{0, -15} {1, -10} {2, 10} {3, -5}", _
"Customer", "Date", "Amount", "Status")
' LINQ Query for all Order over $100.
' Join used to display Customer information for the order.
Dim highOrders = From customer In customers _
Join order In orders On customer.Name Equals order.Customer _
Where order.Amount > 100.0R _
Select customer.Name, order.Date, order.Amount, order.Status
' Execute query and write information to the console.
For Each result In highOrders
Console.WriteLine("{0, -15} {1, -10} {2, 10} {3, -5}", _
result.Name, result.[Date].ToShortDateString(), _
result.Amount, result.Status)
Next
Console.WriteLine()
Console.WriteLine()
' Report on customer orders by status.
' Write header information to the console.
Console.WriteLine("Customer Orders by Status")
' LINQ Query for summarizing customer order information by status.
' There are two LINQ Queries.
' Internal query is used to group orders together by status and
' calculates the total order amount and number of orders.
' External query is used to join Customer information.
Dim sumoforders = _
From customer In customers _
Select New With {customer.Name, _
.statusTotals = _
From order In orders _
Where order.Customer = customer.Name _
Group order.Amount By order.Status Into Group _
Select New With {.status = Status, _
.orderAmount = Group.Sum(), _
.orderCount = Group.Count() _
} _
}
' Execute query and write information to the console.
For Each customer In sumoforders
' Write customer name to the console.
Console.WriteLine("-{0}-", customer.Name)
For Each x In customer.statusTotals
Console.WriteLine(" {0, -10}: {2,2} orders totaling {1, 7}", _
x.status, x.orderAmount, x.orderCount)
Next
Console.WriteLine()
Next
' Keep console window open.
Console.Read()
End Using
End Sub
''' <summary>
''' Used to store Customer information for analysis.
''' </summary>
Public Class Customer
'Properties.
Private _Name As String
Public Property Name() As String
Get
Return _Name
End Get
Set(ByVal value As String)
_Name = value
End Set
End Property
Private _City As String
Public Property City() As String
Get
Return _City
End Get
Set(ByVal value As String)
_City = value
End Set
End Property
Private _State As String
Public Property State() As String
Get
Return _State
End Get
Set(ByVal value As String)
_State = value
End Set
End Property
''' <summary>
''' Helper method for creating a list of Customers
''' from an Excel worksheet.
''' </summary>
Public Shared Function LoadCustomers(ByVal worksheet As Worksheet, _
ByVal sharedString As SharedStringTable) As List(Of Customer)
' Initialize Customer List.
Dim result As New List(Of Customer)()
' LINQ query to skip first row with column names.
Dim dataRows As IEnumerable(Of Row) = _
From row In worksheet.Descendants(Of Row)() _
Where row.RowIndex.Value > 1 _
Select row
For Each row As Row In dataRows
' LINQ Query to return the Row's Cell values.
' Where clause filters out any cells that do not contain a value.
' Select returns the value of the cell unless the cell has
' a Shared String.
' If the Cell contains a Shared String its value will be a
' reference id which will be used to look up the value in the
' Shared String table.
Dim textValues As IEnumerable(Of String) = _
From cell In row.Descendants(Of Cell)() _
Where cell.CellValue IsNot Nothing _
Select ( _
If(cell.DataType IsNot Nothing _
AndAlso cell.DataType.HasValue _
AndAlso cell.DataType.Value = CellValues.SharedString, _
sharedString.ChildElements( _
Integer.Parse(cell.CellValue.InnerText)).InnerText, _
cell.CellValue.InnerText) _
)
' Check to verify the row contains data.
If textValues.Count() > 0 Then
'Create a Customer and add it to the list.
Dim textArray = textValues.ToArray()
Dim customer As New Customer()
customer.Name = textArray(0)
customer.City = textArray(1)
customer.State = textArray(2)
result.Add(customer)
Else
' If no cells, you have reached the end of the table.
Exit For
End If
Next
' Return populated list of customers.
Return result
End Function
End Class
''' <summary>
''' Used to store Order information for analysis.
''' </summary>
Public Class Order
'Properties.
Private _Number As String
Public Property Number() As String
Get
Return _Number
End Get
Set(ByVal value As String)
_Number = value
End Set
End Property
Private _Date As DateTime
Public Property [Date]() As DateTime
Get
Return _Date
End Get
Set(ByVal value As DateTime)
_Date = value
End Set
End Property
Private _Customer As String
Public Property Customer() As String
Get
Return _Customer
End Get
Set(ByVal value As String)
_Customer = value
End Set
End Property
Private _Amount As Double
Public Property Amount() As Double
Get
Return _Amount
End Get
Set(ByVal value As Double)
_Amount = value
End Set
End Property
Private _Status As String
Public Property Status() As String
Get
Return _Status
End Get
Set(ByVal value As String)
_Status = value
End Set
End Property
''' <summary>
''' Helper method for creating a list of Orders
''' from an Excel worksheet.
''' </summary>
Public Shared Function LoadOrders(ByVal worksheet As Worksheet, _
ByVal sharedString As SharedStringTable) As List(Of Order)
' Initialize Order List.
Dim result As New List(Of Order)()
' LINQ query to skip first row with column names.
Dim dataRows As IEnumerable(Of Row) = _
From row In worksheet.Descendants(Of Row)() _
Where row.RowIndex.Value > 1 _
Select row
For Each row As Row In dataRows
' LINQ Query to return the value of the cells in the row.
' Where clause filters out any cells that do not contain a value.
' Select the value of the return cellunless the cell
' has a Shared String.
' If the cell has a Shared String, its value is a reference id
' which is used to look up the value in the Shared String table.
Dim textValues As IEnumerable(Of String) = _
From cell In row.Descendants(Of Cell)() _
Where cell.CellValue IsNot Nothing _
Select ( _
If(cell.DataType IsNot Nothing _
AndAlso cell.DataType.HasValue _
AndAlso cell.DataType.Value = CellValues.SharedString, _
sharedString.ChildElements( _
Integer.Parse(cell.CellValue.InnerText)).InnerText, _
cell.CellValue.InnerText) _
)
' Check to verify the row contains data.
If textValues.Count() > 0 Then
' Create an order and add it to the list.
Dim textArray = textValues.ToArray()
Dim order As New Order()
order.Number = textArray(0)
order.[Date] = New DateTime(1900, 1, 1).AddDays( _
Double.Parse(textArray(1)) - 2)
order.Customer = textArray(2)
order.Amount = [Double].Parse(textArray(3))
order.Status = textArray(4)
result.Add(order)
Else
' If no cells, then you have reached the end of the table.
Exit For
End If
Next
' Return populated list of orders.
Return result
End Function
End Class