Imports System.Data

Imports System.Data.sqlClient

Imports Excel = Microsoft.Office.Interop.Excel

Public Class excel

‘添加按钮

Private Sub Button1_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) _

Handles Button1.Click

Try

'创建连接

Dim cnn As DataAccess = New DataAccess(CONNECTION_STRING)

Dim i,j As Integer

'创建Excel对象

Dim xlApp As Microsoft.Office.Interop.Excel.Application

Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook

Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

Dim misValue As Object = System.Reflection.Missing.Value

xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Add(misValue)

' 打开某一个表单

xlWorkSheet = xlWorkBook.Sheets("sheet1")

' SQL查询

' xlWorkBook.Sheets.Select("A1:A2")

Dim sql As String = "SELECT * FROM EMP"

' sqlAdapter

Dim dscmd As New sqlDataAdapter(sql,cnn.ConnectionString)

' 定义数据集

Dim ds As New DataSet

dscmd.Fill(ds)

‘添加字段信息到Excel表的第一行

xlWorkSheet.Cells(1,1).Value = "First Name"

xlWorkSheet.Cells(1,2).Value = "Last Name"

xlWorkSheet.Cells(1,3).Value = "Full Name"

xlWorkSheet.Cells(1,4).Value = "Salary"

' 将数据导入到excel

For i = 0 To ds.Tables(0).Rows.Count - 1

'Column

For j = 0 To ds.Tables(0).Columns.Count - 1

' this i change to header line cells >>>

xlWorkSheet.Cells(i + 3,j + 1) = _

ds.Tables(0).Rows(i).Item(j)

Next

Next

'HardCode in Excel sheet

' this i change to footer line cells >>>

xlWorkSheet.Cells(i + 3,7) = "Total"

xlWorkSheet.Cells.Item(i + 3,8) = "=SUM(H2:H18)"

' 保存到Excel

xlWorkSheet.SaveAs("D:\vbexcel.xlsx")

xlWorkBook.Close()

xlApp.Quit()

releaSEObject(xlApp)

releaSEObject(xlWorkBook)

releaSEObject(xlWorkSheet)

'弹出对话框显示保存后的路径

MsgBox("You can find the file D:\vbexcel.xlsx")

Catch ex As Exception

End Try

End Sub

' Function of Realease Object in Excel Sheet

Private Sub releaSEObject(ByVal obj As Object)

Try

System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

obj = Nothing

Catch ex As Exception

obj = Nothing

Finally

GC.Collect()

End Try

End Sub

End Class