返回目录:excel表格制作
介绍
下面通过一步一步的介绍,如何通过VB.NET来读取数据,并且将数据导入到Excel中。
第一步:
打开VS开发工具,并且添加引用e799bee5baa6e997aee7ad94e59b9ee7ad94338。
然后选择。
Microsoft Excel 12.0 object library and。
Microsoft Excel 14.0 object library。
第二步:
创建一个Excle在你的电脑中。
第三步:
在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
复制代码。
第四步:
看到如下导出结果。
需要引百用 Microsoft Excel 11.0 Object (Excel 2003)
Dim Ex As Object
Dim ExBook As Object
Dim ExSheet As Object
private sub commandbutton1_click()
Set Ex = CreateObject("Excel.Application")
Set ExBook = Ex.Workbooks().Add
Set ExSheet = ExBook.Worksheets("Sheet1") //打开度
for i = 0 to 10
ExSheet.cells(i+1,1).Value = a(i)
next i //赋值回
ActiveWorkbook.SaveAs ("d:\my.xls") //保存
Ex.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xls = Nothing //退出答
end sub
你的“图片字段”存储的是文件名还是图片内容?
Selection使用选定range区域就行