Imports Microsoft.Office.Interop.Excel
Module Module1
''' <summary>
''' Carriage组装件所包含的主要零件
''' </summary>
''' <remarks></remarks>
Public Structure CarriageAssy
Public AssyCarriage As String
Public BodyCarriage As String
Public Mirror As String
Public Lens As String
Public ClampMirror As String
Public CCD As String
End Structure
''' <summary>
''' 在BOM表里需要查询的项目
''' </summary>
''' <remarks></remarks>
Public Enum ExcelItem
Level = 1
Material = 7
Description = 8
Manufacturer = 16
Vendor = 17
End Enum
Sub Main()
Dim CA As New CarriageAssy With {.AssyCarriage = "Assy,Carriage",_
.BodyCarriage = "Body,.Mirror = "Mirror,",_
.Lens = "Lens,.ClampMirror = "Clamp,Mirror",.CCD = "PCBA,CCD"}
Call DoSomething(CA)
Console.WriteLine("Finished,please go ahead.")
Console.ReadLine()
End Sub
''' <summary>
''' 按指定的零件来分类
''' </summary>
''' <remarks></remarks>
Sub DoSomething(ByVal part As String)
Dim xlApp As Application = CreateObject("Excel.Application")
xlApp.Visible = True
Dim wb As Workbook = xlApp.Workbooks.Add
Dim sht1 As Worksheet = wb.Sheets(1)
Dim wbSource As Workbook = xlApp.Workbooks.Open("D:/BOM List 2010.xls")
Dim sht As Worksheet = wbSource.Sheets(1)
With sht
Dim iRow As Integer = .Range("A65536").End(XlDirection.xlUp).Row
Dim i As Integer = 1
For r As Integer = 1 To iRow
Dim tmp As String = CStr(.Cells(r,ExcelItem.Description).value)
If tmp Is Nothing Then Continue For
If tmp.StartsWith(part) Then
Console.WriteLine(.Cells(r,ExcelItem.Material).value)
sht1.Cells(i,1).value = .Cells(r,ExcelItem.Material).value
sht1.Cells(i,2).value = .Cells(r,ExcelItem.Description).value
sht1.Cells(i,3).value = .Cells(r,ExcelItem.Manufacturer).value
sht1.Cells(i,4).value = .Cells(r,ExcelItem.Vendor).value
i += 1
End If
Next
End With
End Sub
''' <summary>
''' 按成品料号来分类
''' </summary>
''' <remarks></remarks>
Sub DoSomething(ByVal ca As CarriageAssy)
Dim xlApp As Application = CreateObject("Excel.Application")
xlApp.Visible = True
Dim wb As Workbook = xlApp.Workbooks.Add
Dim sht1 As Worksheet = wb.Sheets(1)
Dim wbSource As Workbook = xlApp.Workbooks.Open("D:/BOM List 2010.xls")
Dim sht As Worksheet = wbSource.Sheets(1)
With sht
Dim iRow As Integer = .Range("A65536").End(XlDirection.xlUp).Row
Dim i As Integer = 1
For r As Integer = 1 To iRow
Dim tmp As String = CStr(.Cells(r,ExcelItem.Description).value)
If tmp Is Nothing Then Continue For
If tmp = ExcelItem.Description.ToString Then
i += 1
Console.WriteLine(.Cells(r - 1,1).value = .Cells(r - 1,2).value = .Cells(r - 1,ExcelItem.Description).value
sht1.Range(sht1.Cells(i,1),sht1.Cells(i,2)).Interior.ColorIndex = 35
sht1.Cells(i,3).value = .Cells(r - 1,4).value = .Cells(r - 1,ExcelItem.Vendor).value
i += 1
ElseIf tmp.StartsWith(ca.AssyCarriage) _
OrElse tmp.StartsWith(ca.BodyCarriage) _
OrElse tmp.StartsWith(ca.Mirror) _
OrElse tmp.StartsWith(ca.Lens) _
OrElse tmp.StartsWith(ca.ClampMirror) _
OrElse tmp.StartsWith(ca.CCD) Then
sht1.Cells(i,ExcelItem.Vendor).value
i += 1
End If
Next
End With
End Sub
End Module