EXCEL汇入.docVIP

中从datatable读取数据到Excel 最近用这个比较多，所以到网上找了些相关的资料，自己做了个简单的示例，记录下来以防以后又忘记了。。。下面是完整代码(用的是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-201104071256\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True ?? ? ? ?Dim conn As SqlConnection = New SqlConnection(connStr) ?? ? ? ?Dim sqlstr?As 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 ?? ? ?