介绍
下面通过一步一步的介绍，如何通过VB.NET来读取数据，并且将数据导入到Excel中
第一步：
打开VS开发工具，并且添加引用
然后选择
- Microsoft Excel 12.0 object library and
- Microsoft Excel 14.0 object library
<ignore_js_op>
<ignore_js_op>
第二步：
创建一个Excle在你的电脑中
<ignore_js_op>
第三步：
在VS中写入如下代码：
- 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
第四步：
看到如下导出结果
<ignore_js_op>
转至：http://www.dfwlt.com/forum.php?mod=viewthread&tid=104&extra=