程式 - [使用版本VB2008]通訊錄存成Excel檔

[魔術王子]

設計之前要先加入Excel的參考元件,如下:

執行畫面如下:


一個工具列ToolStrip,上面有ToolStripLabel一個,ToolStripComboBox一個,ToolStripButton有2個,最後一個DataGridView

作者: Designer

Private Sub InitializeComponent()
Me.components = New System.ComponentModel.Container
Dim resources As System.ComponentModel.ComponentResourceManager = New System.ComponentModel.ComponentResourceManager(GetType(Form1))
Me.ToolStrip1 = New System.Windows.Forms.ToolStrip
Me.ToolStripLabel1 = New System.Windows.Forms.ToolStripLabel
Me.ToolStripComboBox1 = New System.Windows.Forms.ToolStripComboBox
Me.ToolStripButton1 = New System.Windows.Forms.ToolStripButton
Me.ToolStripSeparator1 = New System.Windows.Forms.ToolStripSeparator
Me.ToolStripButton2 = New System.Windows.Forms.ToolStripButton
Me.DataGridView1 = New System.Windows.Forms.DataGridView
Me.Column1 = New System.Windows.Forms.DataGridViewTextBoxColumn
Me.Column2 = New System.Windows.Forms.DataGridViewTextBoxColumn
Me.Column3 = New System.Windows.Forms.DataGridViewTextBoxColumn
Me.FolderBrowserDialog1 = New System.Windows.Forms.FolderBrowserDialog
Me.Timer1 = New System.Windows.Forms.Timer(Me.components)
Me.ToolStrip1.SuspendLayout()
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'ToolStrip1
'
Me.ToolStrip1.Items.AddRange(New System.Windows.Forms.ToolStripItem() {Me.ToolStripLabel1, Me.ToolStripComboBox1, Me.ToolStripButton1, Me.ToolStripSeparator1, Me.ToolStripButton2})
Me.ToolStrip1.Location = New System.Drawing.Point(0, 0)
Me.ToolStrip1.Name = "ToolStrip1"
Me.ToolStrip1.Size = New System.Drawing.Size(595, 25)
Me.ToolStrip1.TabIndex = 2
Me.ToolStrip1.Text = "ToolStrip1"
'
'ToolStripLabel1
'
Me.ToolStripLabel1.Name = "ToolStripLabel1"
Me.ToolStripLabel1.Size = New System.Drawing.Size(56, 22)
Me.ToolStripLabel1.Text = "存放位置"
'
'ToolStripComboBox1
'
Me.ToolStripComboBox1.DropDownWidth = 300
Me.ToolStripComboBox1.Name = "ToolStripComboBox1"
Me.ToolStripComboBox1.Size = New System.Drawing.Size(300, 25)
'
'ToolStripButton1
'
Me.ToolStripButton1.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image
Me.ToolStripButton1.Image = CType(resources.GetObject("ToolStripButton1.Image"), System.Drawing.Image)
Me.ToolStripButton1.ImageTransparentColor = System.Drawing.Color.Magenta
Me.ToolStripButton1.Name = "ToolStripButton1"
Me.ToolStripButton1.Size = New System.Drawing.Size(23, 22)
Me.ToolStripButton1.Text = "ToolStripButton1"
'
'ToolStripSeparator1
'
Me.ToolStripSeparator1.Name = "ToolStripSeparator1"
Me.ToolStripSeparator1.Size = New System.Drawing.Size(6, 25)
'
'ToolStripButton2
'
Me.ToolStripButton2.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image
Me.ToolStripButton2.Image = CType(resources.GetObject("ToolStripButton2.Image"), System.Drawing.Image)
Me.ToolStripButton2.ImageTransparentColor = System.Drawing.Color.Magenta
Me.ToolStripButton2.Name = "ToolStripButton2"
Me.ToolStripButton2.Size = New System.Drawing.Size(23, 22)
Me.ToolStripButton2.Text = "ToolStripButton2"
'
'DataGridView1
'
Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.Column1, Me.Column2, Me.Column3})
Me.DataGridView1.Dock = System.Windows.Forms.DockStyle.Fill
Me.DataGridView1.Location = New System.Drawing.Point(0, 25)
Me.DataGridView1.Name = "DataGridView1"
Me.DataGridView1.RowTemplate.Height = 24
Me.DataGridView1.Size = New System.Drawing.Size(595, 367)
Me.DataGridView1.TabIndex = 3
'
'Column1
'
Me.Column1.HeaderText = "姓名"
Me.Column1.Name = "Column1"
Me.Column1.Width = 80
'
'Column2
'
Me.Column2.HeaderText = "電話號碼"
Me.Column2.Name = "Column2"
'
'Column3
'
Me.Column3.HeaderText = "住址"
Me.Column3.Name = "Column3"
Me.Column3.Width = 300
'
'Timer1
'
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 12.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(595, 392)
Me.Controls.Add(Me.DataGridView1)
Me.Controls.Add(Me.ToolStrip1)
Me.Name = "Form1"
Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
Me.Text = "Form1"
Me.ToolStrip1.ResumeLayout(False)
Me.ToolStrip1.PerformLayout()
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
Me.PerformLayout()

End Sub
Friend WithEvents ToolStrip1 As System.Windows.Forms.ToolStrip
Friend WithEvents ToolStripLabel1 As System.Windows.Forms.ToolStripLabel
Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView
Friend WithEvents Column1 As System.Windows.Forms.DataGridViewTextBoxColumn
Friend WithEvents Column2 As System.Windows.Forms.DataGridViewTextBoxColumn
Friend WithEvents Column3 As System.Windows.Forms.DataGridViewTextBoxColumn
Friend WithEvents ToolStripComboBox1 As System.Windows.Forms.ToolStripComboBox
Friend WithEvents ToolStripButton1 As System.Windows.Forms.ToolStripButton
Friend WithEvents ToolStripSeparator1 As System.Windows.Forms.ToolStripSeparator
Friend WithEvents ToolStripButton2 As System.Windows.Forms.ToolStripButton
Friend WithEvents FolderBrowserDialog1 As System.Windows.Forms.FolderBrowserDialog
Friend WithEvents Timer1 As System.Windows.Forms.Timer

作者: Form1程式碼

Option Explicit On
Imports System.IO
Imports System.Drawing.Imaging
Imports System.Text
Imports System.Runtime.InteropServices
Imports System
Imports Microsoft.Office.Interop.Excel
Public Class Form1
'用於存放Microsoft Excel 引用的變數。
Public xlApp As Application
Public xlBook As Workbook
Public xlSheet As Worksheet
Public xlRange As Range

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim i As Single
ToolStripComboBox1.Items.Add("桌面")
ToolStripComboBox1.Items.Add("我的文件")
ToolStripComboBox1.Text = My.Computer.FileSystem.CurrentDirectory
'以下只是為了測試方便,可以省略
Randomize()
Dim n As Single, f As Single
For i = 0 To 30
Dim s As String = "00" & Format(i, "##")
n = 1 + Int(Rnd(1) * 500)
f = 1 + Int(Rnd(1) * 101)
DataGridView1.Rows.Add("阿哉" & s.Substring(s.Length - 2, 2), "00-1234" & 567 + f, "賣燜挖縣麥擱燜挖鄉賣歌債燜挖鎮" & n & "號" & f & "樓")
Next
End Sub

Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
On Error Resume Next
'#一部電腦僅執行一個Excel Application, 就算中突開啟Excel也不會影響程式執行
'#在工作管理員中只會看見一個EXCEL.exe在執行，不會浪費電腦資源
'#引用正在執行的Excel Application
xlApp = GetObject(, "Excel.Application")
'#若發生錯誤表示電腦沒有Excel正在執行，需重新建立一個新的應用程式
If Err.Number() <> 0 Then
Err.Clear()
'#執行一個新的Excel Application
xlApp = CreateObject("Excel.Application")
If Err.Number() <> 0 Then
MsgBox("電腦沒有安裝Excel")
End
End If
End If
Err.Clear()
xlBook = xlApp.Workbooks.Add()
'停用警告訊息
xlApp.DisplayAlerts = False
'設置EXCEL對象可見
xlApp.Visible = True
'設定活頁簿為焦點
xlBook.Activate()
'顯示第一個子視窗
xlBook.Parent.Windows(1).Visible = True
'引用第一個工作表
xlSheet = xlBook.Worksheets(1)
'設定工作表為焦點
xlSheet.Activate()
xlSheet.Name = "通訊錄"
xlSheet.Cells(1, 4).Value = "通訊錄"
xlSheet.Columns("A : A").ColumnWidth = 5
xlSheet.Columns("B : C").ColumnWidth = 10
xlSheet.Columns("D : D").ColumnWidth = 100
For i = 0 To DataGridView1.Rows.Count - 2
'xlRange = xlSheet.Range("A" & 2 + i * 11 & " : D" & 2 + i * 11)
xlRange = xlSheet.Range("A2 : D2")
With xlRange.Interior
.Pattern = XlPattern.xlPatternSolid
.PatternColorIndex = XlColorIndex.xlColorIndexAutomatic
.ThemeColor = XlThemeColor.xlThemeColorDark1
.TintAndShade = -0.249946592608417
.PatternTintAndShade = 0
End With
xlSheet.Cells(2, 1).Value = "NO."
xlSheet.Cells(2, 2).Value = "姓名"
xlSheet.Cells(2, 3).Value = "電話號碼"
xlSheet.Cells(2, 4).Value = "住址"
xlRange = xlSheet.Range("A" & i * 2 & " : D" & i * 2)
With xlRange.Interior
.Pattern = XlPattern.xlPatternSolid
.PatternColorIndex = XlColorIndex.xlColorIndexAutomatic
.ThemeColor = XlThemeColor.xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
xlSheet.Cells(i + 3, 1).Value = i + 1
xlSheet.Cells(i + 3, 2).Value = DataGridView1.Rows(i).Cells(0).Value
xlSheet.Cells(i + 3, 3).Value = DataGridView1.Rows(i).Cells(1).Value
xlSheet.Cells(i + 3, 4).Value = DataGridView1.Rows(i).Cells(2).Value
Next
xlBook.SaveAs(ToolStripComboBox1.Text & "\通訊錄.XLS")
'關閉當前活頁簿EXCEL
xlBook.Close()
My.Application.DoEvents()
'關閉當前活頁簿EXCEL
xlApp.Quit()
'回收excel
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing
xlBook = Nothing
xlSheet = Nothing
xlRange = Nothing
GC.Collect()
End Sub

Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
FolderBrowserDialog1.Description = "選擇路徑 :"
FolderBrowserDialog1.SelectedPath = ToolStripComboBox1.Text
If FolderBrowserDialog1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
ToolStripComboBox1.Text = FolderBrowserDialog1.SelectedPath
End If
End Sub

Private Sub ToolStripComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripComboBox1.SelectedIndexChanged
Timer1.Enabled = True
End Sub

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Select Case ToolStripComboBox1.SelectedIndex
Case 0 : ToolStripComboBox1.Text = My.Computer.FileSystem.SpecialDirectories.Desktop
Case 1 : ToolStripComboBox1.Text = My.Computer.FileSystem.SpecialDirectories.MyDocuments
End Select
Timer1.Enabled = False
End Sub
End Class

在設計時,下面的程式碼會忽略錯誤訊息,所以一但你發現資料無法傳給Excel時,很可能是程式碼有誤但被這段成是忽略了,這時可以將這段程式碼標記(REMARK)起來

On Error Resume Next
'#一部電腦僅執行一個Excel Application, 就算中突開啟Excel也不會影響程式執行
'#在工作管理員中只會看見一個EXCEL.exe在執行，不會浪費電腦資源
'#引用正在執行的Excel Application
xlApp = GetObject(, "Excel.Application")
'#若發生錯誤表示電腦沒有Excel正在執行，需重新建立一個新的應用程式
If Err.Number() <> 0 Then
Err.Clear()
'#執行一個新的Excel Application
xlApp = CreateObject("Excel.Application")
If Err.Number() <> 0 Then
MsgBox("電腦沒有安裝Excel")
End
End If
End If
Err.Clear()