介绍
下面通过一步一步的介绍，如何通过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=
在VB.NET中，将数据库里的数据导出到Excel中,布布扣,bubuko.com
在VB.NET中，将数据库里的数据导出到Excel中
原文：http://www.cnblogs.com/xszlo/p/3654070.html
