用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,.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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!