vb 导出mysql到excel_在VB.NET中，将数据库里的数据导出到Excel中

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