介绍
下面通过一步一步的介绍，如何通过VB.NET来读取数据，并且将数据导入到Excel中

第一步：
打开VS开发工具，并且添加引用
然后选择

  • Microsoft Excel 12.0 object library and

  • Microsoft Excel 14.0 object library

123803lxdezep72lyp2s5l.jpg

123803rrjnbdr7z7njdjdw.jpg


第二步：
创建一个Excle在你的电脑中
123906y2nheccehfydrcwy.jpg


第三步：
在VS中写入如下代码：

  1. Imports System.Data

  2. Imports System.Data.SqlClient

  3. Imports Excel = Microsoft.Office.Interop.Excel


  5. Public Class excel

  6. ‘添加按钮

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

  8.        Handles Button1.Click

  9.        Try

  10.            '创建连接

  11.            Dim cnn As DataAccess = New DataAccess(CONNECTION_STRING)


  13.            Dim i, j As Integer

  14.            '创建Excel对象

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

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

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

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

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

  20.            xlWorkBook = xlApp.Workbooks.Add(misValue)

  21.            ' 打开某一个表单

  22.            xlWorkSheet = xlWorkBook.Sheets("sheet1")

  23.            ' sql查询

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


  26.            Dim sql As String = "SELECT * FROM EMP"

  27.            ' SqlAdapter

  28.            Dim dscmd As New SqlDataAdapter(sql, cnn.ConnectionString)

  29.            ' 定义数据集

  30.            Dim ds As New DataSet

  31.            dscmd.Fill(ds)

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

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

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

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

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

  37.            ' 将数据导入到excel

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

  39.                'Column

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

  41.                    ' this i change to header line cells >>>

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

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

  44.                Next

  45.            Next

  46.            'HardCode in Excel sheet

  47.            ' this i change to footer line cells  >>>

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

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

  50.            ' 保存到Excel

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

  52.            xlWorkBook.Close()

  53.            xlApp.Quit()

  54.            releaseObject(xlApp)

  55.            releaseObject(xlWorkBook)

  56.            releaseObject(xlWorkSheet)

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

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

  59.        Catch ex As Exception


  61.        End Try


  63.    End Sub

  64.    ' Function of Realease Object in Excel Sheet

  65.    Private Sub releaseObject(ByVal obj As Object)

  66.        Try

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

  68.            obj = Nothing

  69.        Catch ex As Exception

  70.            obj = Nothing

  71.        Finally

  72.            GC.Collect()

  73.        End Try

  74.    End Sub

  75. End Class

复制代码

第四步：
看到如下导出结果

124258xfushd95qh4fr5rr.jpg


相关链接