Export Sql Tables in Excel Spread Sheet Format For Mailing

Export Sql Tables in Excel Spread Sheet Format For Mailing

Coding::

Imports System.Data.SqlClient
Imports System.IO.File
Imports System.IO.Compression

Public Class ExportExcel
    Dim dt As New DataTable
    Dim ds As New DataSet
    Dim da As SqlDataAdapter
    Dim rs As SqlDataReader
    Dim conn As New SqlConnection
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TextBox2.Text = docpath


    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Then
            MsgBox("Please Enter A File Name To Save")
            Exit Sub
        End If
        If TextBox2.Text = "" Then
            MsgBox("Please Select Path to Save the File")
            Exit Sub
        End If
        Dim attribute As System.IO.FileAttributes = FileAttribute.ReadOnly


        TextBox3.Text = TextBox2.Text & "\" & TextBox1.Text & ".xlsx"
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")


        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                For k As Integer = 1 To DataGridView1.Columns.Count
                    xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                Next
            Next
        Next

        xlWorkSheet.SaveAs(TextBox3.Text)
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
        System.IO.File.SetAttributes(TextBox3.Text, attribute)
        MsgBox("You can find the file at " & TextBox3.Text)
    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

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ListBox1.Items.Clear()
        Dim conn As New SqlConnection
        Dim cmd As New SqlCommand
        Dim ds As New DataSet
        Dim da As SqlDataAdapter
        Dim dt As New DataTable
        Dim i As Integer
        Dim n As Integer
        Dim len As Integer
        Dim dd As String = TextBox6.Text
        Dim cc As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & TextBox5.Text & ";Integrated Security=True;Connect Timeout=30;User Instance=True"
        conn.ConnectionString = cc
        'conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Sachin\Desktop\projects\Export Excel\Export Excel\Zoo.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        conn.Open()
        cmd.Connection = conn
        cmd.CommandText = "select * from INFORMATION_SCHEMA.tables"
        da = New SqlDataAdapter(cmd.CommandText, conn)
        da.Fill(ds, dd)
        len = da.Fill(ds, dd)
        While i <= len - 1
            ListBox1.Items.Add(ds.Tables(dd).Rows(i).Item(2))
            i = i + 1
            n = n + 1
        End While
        conn.Close()
    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

        DataGridView1.Refresh()
        'dt.AcceptChanges()
        Dim cc As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & TextBox5.Text & ";Integrated Security=True;Connect Timeout=30;User Instance=True"
        conn.ConnectionString = cc
        conn.Open()
        TextBox4.Text = ListBox1.SelectedItem.ToString
        TextBox1.Text = TextBox4.Text
        'ds.Reset()
        dt = New DataTable
        ds.Tables.Add(dt)
        da = New SqlDataAdapter("select * from " & TextBox4.Text & " ", conn)
        da.Fill(dt)
        DataGridView1.DataSource = dt.DefaultView   '.   WriteXmlSchema  dt.DefaultView
        conn.Close()

    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Dim i As Integer
        For i = 0 To DataGridView1.Columns.Count - 1
            DataGridView1.Columns.Remove(DataGridView1.Columns(0).Name)
        Next
        dt = Nothing
    End Sub

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
        OpenFileDialog1.Title = "Please Select a Database File"
        OpenFileDialog1.InitialDirectory = "C:"
        OpenFileDialog1.Filter = "Database Files (*.mdf)|*.mdf"
        If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Dim db As String
            db = OpenFileDialog1.FileName
            TextBox5.Text = db
            TextBox6.Text = System.IO.Path.GetFileName(OpenFileDialog1.FileName)
        End If
    End Sub


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        TextBox5.Text = dbpath
        Dim di As New IO.DirectoryInfo(TextBox5.Text)
        Dim diar1 As IO.FileInfo() = di.GetFiles("*.mdf")

        Dim dra As IO.FileInfo

        Dim count = 0
        For Each dra In diar1
            ListBox2.Items.Add(dra)
            count = count + 1
        Next

    End Sub

    Private Sub ListBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged
        TextBox5.Text = TextBox5.Text & "\" & ListBox2.SelectedItem.ToString
        TextBox6.Text = ListBox2.SelectedItem.ToString
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim MyFolderBrowser As New System.Windows.Forms.FolderBrowserDialog
        MyFolderBrowser.Description = "Please Select the Folder Containing Database Files"
        MyFolderBrowser.RootFolder = Environment.SpecialFolder.MyComputer
        Dim dlgResult As DialogResult = MyFolderBrowser.ShowDialog()
        If dlgResult = Windows.Forms.DialogResult.OK Then
            TextBox2.Text = MyFolderBrowser.SelectedPath
        End If
    End Sub
End Class