Option Explicit On
Imports Microsoft.Office.Interop
Imports System.Data.SqlClient
Imports System.Data
Module ExcelExport
Sub Main()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
xlApp = New Excel.Application
xlBook = xlApp.Workbooks.Add()
xlSheet = xlBook.ActiveSheet
xlSheet.Cells(1, 1) = "Employee Name"
xlSheet.Cells(1, 2) = "Email"
xlSheet.Cells(1, 3) = "Tel (Direct)"
xlSheet.Cells(1, 4) = "Tel (Cell)"
Dim ds As DataSet = GetEmployees()
Dim dt As DataTable = ds.Tables.Item("Employees")
Dim rowEmployee As DataRow
Dim rowNo As Integer
rowNo = 2
For Each rowEmployee In dt.Rows
xlSheet.Cells(rowNo, 1) = rowEmployee.Item("Name")
xlSheet.Cells(rowNo, 2) = rowEmployee.Item("Email")
xlSheet.Cells(rowNo, 3) = rowEmployee.Item("Direct")
xlSheet.Cells(rowNo, 4) = rowEmployee.Item("Cell")
rowNo += 1
Next
xlApp.Visible = True
xlApp.UserControl = True
xlBook = Nothing
xlSheet = Nothing
xlApp = Nothing
End Sub
Function GetEmployees() As DataSet
Dim strConn = "Data Source=POWER2003;Initial Catalog=Book;" _
& "User ID=sa;Password=sa"
Dim cn As New SqlConnection(strConn)
cn.Open()
Dim strSelectSql As String = "select * From Employees"
Dim cmdSelect As New SqlDataAdapter(strSelectSql, cn)
Dim ds As New DataSet
cmdSelect.Fill(ds, "Employees")
cn.Close()
GetEmployees = ds
End Function
End Module
|