vb.net中从datatable读取数据到Excel
原文地址:http://hi.baidu.com/hiochou/item/5aedd2f0418056cea835a2dc 最近用这个比较多,所以到网上找了些相关的资料,自己做了个简单的示例,记录下来以防以后又忘记了。。。下面是完整代码(用的是Northwind数据库中的Products表): Imports System.Data Imports System.Data.SqlClient Imports Microsoft.Office Public Class Form1 Private Sub btnExpert_Click(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles btnExpert.Click Dim connStr As String = "Data Source=PC-201104071256SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True" Dim conn As SqlConnection = New SqlConnection(connStr) Dim sqlstrAs String = "select top 10 * from dbo.Products" Dim adapter As SqlDataAdapter = New SqlDataAdapter(sqlstr,conn) Dim ds As DataSet = New DataSet Dim myTable As DataTable adapter.Fill(ds,"productsTable") myTable = ds.Tables("productsTable") Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim chartRange As Excel.Range Dim rcount,ccount As Integer rcount = myTable.Rows.Count() ccount = myTable.Columns.Count() xlApp = New Excel.Application() xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Worksheets("sheet1") '表头 xlWorkSheet.Cells(1,1) = "产品ID" xlWorkSheet.Cells(1,2) = "产品名" xlWorkSheet.Cells(1,3) = "供应商ID" xlWorkSheet.Cells(1,4) = "分类ID" xlWorkSheet.Cells(1,5) = "单元数量" xlWorkSheet.Cells(1,6) = "单价" xlWorkSheet.Cells(1,7) = "单位库存" xlWorkSheet.Cells(1,8) = "订购单位" xlWorkSheet.Cells(1,9) = "再订购库存量" xlWorkSheet.Cells(1,10) = "停止使用" chartRange = xlWorkSheet.UsedRange For rCnt = 2 To rcount + 1 For cCnt = 1 To ccount xlWorkSheet.Cells(rCnt,cCnt) = CStr(myTable.Rows(rCnt - 2)(cCnt - 1).ToString) Next Next '格式化单元格 chartRange.HorizontalAlignment = 3 chartRange.VerticalAlignment = 3 chartRange = xlWorkSheet.Range("A1","J1") chartRange.Font.Bold = True chartRange = xlWorkSheet.Range("A2","J11") chartRange.Font.ColorIndex = 5 xlWorkSheet.SaveAs("E:Testproducts.xls") xlWorkBook.Close() xlApp.Quit() releaSEObject(xlApp) releaSEObject(xlWorkBook) releaSEObject(xlWorkSheet) MsgBox("成功保存文件products.xls在E:Test中...") End Sub 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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |