Export To Excel Record from DataSet

Imports AppGlobals
Imports AppGlobals.AppGlobal
Imports System.Data.SqlClient
Imports System.Data
Imports Microsoft.Office.Interop.Excel


Public Class frmDeliveryDilogBox
    Private Shared objfrmDeliveryDilogBox As frmDeliveryDilogBox
    Private Shared m_objDataSet_DeliveryID As New Data.DataSet()
    Private Shared m_objDataSet_SourceType As New Data.DataSet()
    Private Shared m_objDataSet_FeatureName As New Data.DataSet()
    Private Shared m_objDataSet_SpecDocItemNumbers As New Data.DataSet()
    Private mobjDBConnect As New DbConnectPAR()
    Private m_objDataSetDelivery As New Data.DataSet()
    Private m_objDataAdapter As SqlClient.SqlDataAdapter
    Dim ds As New Data.DataSet()
    Dim strSQL As String
    Private Sub btnExportToExel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExel.Click
        Try

            ExportToExcel()
        Catch ex As Exception

        End Try
    End Sub
    Public Sub ExportToExcel()
        Dim DSDelivery As New Data.DataSet()
        Dim sqlCmd As New SqlCommand
        Dim DA As New SqlDataAdapter
        mobjDBConnect.OpenConnection()
        sqlCmd.Connection = mobjDBConnect.GetConnection
        sqlCmd.CommandType = CommandType.StoredProcedure
        sqlCmd.CommandText = "spPRInformation"
        sqlCmd.Parameters.AddWithValue("@DeliveryNo", cmbDeliveryID.SelectedValue)
        sqlCmd.Parameters.AddWithValue("@Flag", 9)
        DA.SelectCommand = sqlCmd
        Dim SQLP As SqlParameter = sqlCmd.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt)
        SQLP.Direction = ParameterDirection.ReturnValue
        '
        DA = New SqlDataAdapter(sqlCmd)
        DA.Fill(DSDelivery)
        If DSDelivery.Tables(0).Rows.Count <> 0 Then

            Dim strFilename As String = ""
            Dim intCol, intRow As Integer

            Dim Excel As Object = CreateObject("Excel.Application")
            If Excel Is Nothing Then
                MsgBox("It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.Critical)
                Return
            End If

            Dim dlgSaveFile As New System.Windows.Forms.SaveFileDialog
            dlgSaveFile.Filter = "Execl files (*.xls)|*.xls"
            dlgSaveFile.ShowDialog()

            Try
                If dlgSaveFile.FileName = "" Then
                    Exit Sub
                End If

                With Excel
                    .SheetsInNewWorkbook = 1
                    .Workbooks.Add()
                    .Worksheets(1).Select()

                    Dim intI As Integer = 1
                    For intCol = 0 To DSDelivery.Tables(0).Columns.Count - 1
                        .cells(2, intI).value = DSDelivery.Tables(0).Columns(intCol).ColumnName
                        .cells(2, intI).EntireRow.Font.Bold = True
                        intI += 1
                    Next
                    intI = 3
                    Dim intK As Integer = 1
                    For intCol = 0 To DSDelivery.Tables(0).Columns.Count - 1
                        intI = 3
                        For intRow = 0 To DSDelivery.Tables(0).Rows.Count - 1
                            .Cells(intI, intK).Value = DSDelivery.Tables(0).Rows(intRow).ItemArray(intCol)
                            intI += 1
                        Next
                        intK += 1
                    Next
                    .ActiveCell.Worksheet.SaveAs(dlgSaveFile.FileName)
                End With
                System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
                Excel = Nothing
                'MessageBox.Show("Record has been saved successfully.", g_strMessageCaption, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                MessageBox.Show("Data's are exported to Excel Succesfully in '" & dlgSaveFile.FileName & "'", g_strMessageCaption, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            ' The excel is created and opened for insert value. We most close this excel using this system
            Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
            For Each i As Process In pro
                i.Kill()
            Next

        Else
            'lblMsg.Visible = True
            'lblMsg.Text = " Approved Data's are exported only  to Excel !"
           
        End If

    End Sub


#Region "Properties"
    Public Shared Property m_GetfrmDeliveryDilogBox() As frmDeliveryDilogBox
        Get
            If objfrmDeliveryDilogBox Is Nothing _
                OrElse objfrmDeliveryDilogBox.IsDisposed Then
                objfrmDeliveryDilogBox = New frmDeliveryDilogBox()
            End If
            m_GetfrmDeliveryDilogBox = frmDeliveryDilogBox
        End Get
        Set(ByVal Value As frmDeliveryDilogBox)
            objfrmDeliveryDilogBox = Value
        End Set
    End Property
#End Region
#Region "User Defined Function"
    Public Function fnInitialize_Form() As Boolean
        Try
            fnFillDeliveryID()
            Return True
        Catch ex As Exception
            MsgBox(ex.Message.ToString, MsgBoxStyle.Critical, g_strMessageCaption)
            Return False
        End Try
    End Function
#End Region
    Public Function fnFillDeliveryID() As String
        Cursor.Current = Cursors.WaitCursor
        Try
            Dim strSQL As String
            strSQL = "select distinct PRInformation.DeliveryNo, dbo.mstDelivery.DeliveryID from dbo.PRInformation Left Outer Join dbo.mstDelivery  on dbo.mstDelivery.DeliveryNo=PRInformation.DeliveryNo where PRInformation.Status='A'"
            m_objDataSetDelivery.Clear()
            Dim sqlCmd As SqlCommand
            m_objDataAdapter = New SqlClient.SqlDataAdapter()
            mobjDBConnect.OpenConnection()
            sqlCmd = New SqlCommand(strSQL, mobjDBConnect.GetConnection)
            m_objDataAdapter.SelectCommand = sqlCmd
            m_objDataAdapter.Fill(m_objDataSetDelivery, "Delivery")
            If m_objDataSetDelivery.Tables("Delivery").Rows.Count <> 0 Then
                cmbDeliveryID.DataSource = m_objDataSetDelivery.Tables("Delivery")
                cmbDeliveryID.ValueMember = "DeliveryNo"
                cmbDeliveryID.DisplayMember = "DeliveryID"
            End If
        Catch ex As Exception
            Cursor.Current = Cursors.Default
            MsgBox(ex.Message.ToString, MsgBoxStyle.Critical, g_strMessageCaption)
        End Try

    End Function
 
    Private Sub btncancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btncancel.Click
        Try
            Me.Close()
        Catch ex As Exception

        End Try
    End Sub

    Private Sub frmDeliveryDilogBox_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyDown
        Try
            If e.KeyCode = Keys.Escape Then Me.Close()
        Catch ex As Exception

        End Try

    End Sub

 
End Class