用VB2008操作Excel范例

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,Carriage", .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, ExcelItem.Material).value)

                    sht1.Cells(i, 1).value = .Cells(r - 1, ExcelItem.Material).value

                    sht1.Cells(i, 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, ExcelItem.Manufacturer).value

                    sht1.Cells(i, 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, 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

 

End Module