VB.Net - Excel 表格

• 

  简述

  VB.Net 支持 Microsoft Excel 2010 的 COM 对象模型与您的应用程序之间的互操作性。

  要在您的应用程序中利用这种互操作性，您需要导入命名空间 Microsoft.Office.Interop.Excel 在您的 Windows 窗体应用程序中。
• 

  从 VB.Net 创建 Excel 应用程序

  让我们按照 Microsoft Visual Studio 中的以下步骤，从创建 Window Forms 应用程序开始： File → New Project → Windows Forms Applications

  最后，选择 OK，Microsoft Visual Studio 创建您的项目并显示以下内容 Form1.

  在窗体中插入一个 Button 控件 Button1。

  向您的项目添加对 Microsoft Excel 对象库的引用。要做到这一点 -

  • 从项目菜单中选择添加引用。

  

  • 在 COM 选项卡上，找到 Microsoft Excel 对象库，然后单击选择。

  

  • 单击确定。

  双击代码窗口，填充Button1的Click事件，如下图。

  
  '  Add the following code snippet on top of Form1.vb
  Imports Excel = Microsoft.Office.Interop.Excel
  Public Class Form1
     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim appXL As Excel.Application
        Dim wbXl As Excel.Workbook
        Dim shXL As Excel.Worksheet
        Dim raXL As Excel.Range
        
        ' Start Excel and get Application object.
        appXL = CreateObject("Excel.Application")
        appXL.Visible = True
        
        ' Add a new workbook.
        wbXl = appXL.Workbooks.Add
        shXL = wbXl.ActiveSheet
        
        ' Add table headers going cell by cell.
        shXL.Cells(1, 1).Value = "First Name"
        shXL.Cells(1, 2).Value = "Last Name"
        shXL.Cells(1, 3).Value = "Full Name"
        shXL.Cells(1, 4).Value = "Specialization"
        
        ' Format A1:D1 as bold, vertical alignment = center.
        With shXL.Range("A1", "D1")
           .Font.Bold = True
           .VerticalMoognment = Excel.XlVMoogn.xlVMoognCenter
        End With
        
        ' Create an array to set multiple values at once.
        Dim students(5, 2) As String
        students(0, 0) = "Alex"
        students(0, 1) = "Moo"
        students(1, 0) = "Nuha"
        students(1, 1) = "Moo"
        students(2, 0) = "Arilia"
        students(2, 1) = "RamKumar"
        students(3, 0) = "Rita"
        students(3, 1) = "Jones"
        students(4, 0) = "Umme"
        students(4, 1) = "Ayman"
        
        ' Fill A2:B6 with an array of values (First and Last Names).
        shXL.Range("A2", "B6").Value = students
        
        ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
        raXL = shXL.Range("C2", "C6")
        raXL.Formula = "=A2 & "" "" & B2"
         
        ' Fill D2:D6 values.
        With shXL
           .Cells(2, 4).Value = "Biology"
           .Cells(3, 4).Value = "Mathmematics"
           .Cells(4, 4).Value = "Physics"
           .Cells(5, 4).Value = "Mathmematics"
           .Cells(6, 4).Value = "Arabic"
        End With
        
        ' AutoFit columns A:D.
        raXL = shXL.Range("A1", "D1")
        raXL.EntireColumn.AutoFit()
        
        ' Make sure Excel is visible and give the user control
        ' of Excel's lifetime.
        appXL.Visible = True
        appXL.UserControl = True
        
        ' Release object references.
        raXL = Nothing
        shXL = Nothing
        wbXl = Nothing
        appXL.Quit()
        appXL = Nothing
        Exit Sub
  Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
     End Sub
  End Class
  

  当上面的代码被执行并运行时使用 Start Microsoft Visual Studio 工具栏上可用的按钮，它将显示以下窗口 -

  

  单击按钮将显示以下 excel 表。系统将要求您保存工作簿。