Create workbook and read from it : Excel « Windows System « VB.Net

1. VB.Net
2. Windows System
3. Excel

Create workbook and read from it

  

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

   
    
  

Related examples in the same category