Exporting Filtered Datagridview to excel or pdf (SQL Server)-CodePudding

I want my application to export whatever is on the datagridview, that includes the filters. This is what the layout of my application looks like:

The combo box contains all the name of the columns. The search bar searches for data thats inside that column. This is what the code looks like:

Imports System.Data.SqlClient
Imports System.IO
Imports ClosedXML.Excel
Imports System.Data.DataSetExtensions.dll
Public Class export
Dim con As New SqlConnection("Data Source=ICECANDY;Initial Catalog=RestaurantDatabase;integrated security=true")

Sub DGVSETPROPERTY()
    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "TaskID"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "Name"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "TaskCategory"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "TaskAssigned"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "TimeIssued"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "TargetTime"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "StartTime"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "FinishTime"

    DataGridView1.Columns(0).Width = 40
    DataGridView1.Columns(0).HeaderText = "Status"
End Sub
Sub view()
    Try
        Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks]", con)
        Dim bumrah As New DataSet()
        singh.Fill(bumrah)
        DataGridView1.DataSource = bumrah.Tables(0)
        DGVSETPROPERTY()

    Catch ex As Exception
        MessageBox.Show(ex.ToString())
    End Try
End Sub
Private Sub export_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    view()
End Sub

Private Sub export_Resize(sender As Object, e As EventArgs) Handles Me.Resize
    Panel1.Left = (Me.Width - Panel1.Width) / 2
End Sub

Private Sub txtsearch_TextChanged(sender As Object, e As EventArgs) Handles txtsearch.TextChanged
    Try
        If ComboBox.Text = "TaskID" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory]
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where TaskID Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        ElseIf ComboBox.Text = "Name" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where Name Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        ElseIf ComboBox.Text = "TaskCategory" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where TaskCategory Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        ElseIf ComboBox.Text = "TimeIssued" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where TimeIssued Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        ElseIf ComboBox.Text = "TargetTime" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where TargetTime Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        ElseIf ComboBox.Text = "StartTime" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where StartTime Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        ElseIf ComboBox.Text = "FinishTime" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where FinishTime Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        ElseIf ComboBox.Text = "Status" Then
            Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks] where Status Like '"   txtsearch.Text   "%'", con)
            Dim bumrah As New DataSet()
            singh.Fill(bumrah)
            DataGridView1.DataSource = bumrah.Tables(0)

        End If

    Catch ex As Exception
        MessageBox.Show(ex.ToString())
    End Try
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Dim singh As New SqlDataAdapter("SELECT TOP (1000) [TaskID]
  ,[Name]
  ,[TaskCategory] 
  ,[TaskAssigned]
  ,[TimeIssued]
  ,[TargetTime]
  ,[StartTime]
  ,[FinishTime]
  ,[Status]
FROM [RestaurantDatabase].[dbo].[Tasks]", con)
        Dim bumrah As New DataSet()
        singh.Fill(bumrah)
        DataGridView1.DataSource = bumrah.Tables(0)
        DGVSETPROPERTY()
    Catch ex As Exception
        MessageBox.Show(ex.ToString())
    End Try
End Sub

End Class

I wanted to export the filtered data. Either pdf or excel is fine.

CodePudding user response：

There is principle in programming called DRY. It stands for Don't Repeat Yourself. I think you can see that you have violated this principle. When you are building Sql strings, like I did here, be careful of spaces. You need a space between words. I tried to put it at the end of the strings. The Debug.Print will show you if the string came out correctly.

When you have a series of If statements that target the same value, a Select Case is easier to read and write.

It is not necessary to add column headers to the DataGridView. The binding of the DataTable to the grid will handle this. I believe the columns will auto-size to accomidate the data.

Several of the database objects in ADO.net provide a Dispose method where they release unmanaged resources. If there is a Dispose method it should be called. Using...End Using blocks do this for us even if there is an error (it will also close the connection). These objects need to be declared in the method where they are used in a Using block.

Don't mess with the DataGridView. You only need the DataTable to interact with Excel.

The Wrapper to close Excel might seem odd but Excel in a b_tch to get rid of. I believe the arrangement here works.

Private Sub txtsearch_TextChanged(sender As Object, e As EventArgs) Handles txtsearch.TextChanged
    Dim Field = ComboBox1.Text
    Dim strSQL = "SELECT TOP (1000) 
                [TaskID],
                [Name],
                [TaskCategory],
                [TaskAssigned],
                [TimeIssued],
                [TargetTime],
                [StartTime],
                [FinishTime],
                [Status]
                FROM [RestaurantDatabase].[dbo].[Tasks] 
                WHERE "

    Select Case Field
            Case "TaskID"
                strSQL &= "TaskID "
            Case "Name"
                strSQL &= "Name "
            Case "TaskCategory"
                strSQL &= "TaskCategory "
            Case "TimeIssued"
                strSQL &= "TimeIssued "
            Case "TargetTime"
                strSQL &= "TargetTime "
            Case "StartTime"
                strSQL = "StartTime "
            Case "FinishTime"
                strSQL &= "FinishTime "
            Case "Status"
                strSQL &= "Status "
        End Select
    strSQL &= "Like @Search;"
    Debug.Print(strSQL)
    Dim dt As New DataTable
    Try
        Using cn As New SqlConnection(OPConStr),
                        cmd As New SqlCommand(strSQL, cn)
            cmd.Parameters.Add("@Search", SqlDbType.VarChar).Value = txtsearch.Text & "%"
            cn.Open()
            Using reader = cmd.ExecuteReader
                dt.Load(reader)
            End Using
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.ToString())
    End Try
    DataGridView1.DataSource = dt
    WrapperToGetRidOfExcel(dt)
End Sub

Private Sub WrapperToGetRidOfExcel(dt As DataTable)
    FillExcelFromDataTable(dt)
    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()
End Sub

Private Sub FillExcelFromDataTable(dt As DataTable)
    Dim oExcel As New Excel.Application
    Dim oBook = oExcel.Workbooks.Add
    Dim oSheet = DirectCast(oBook.Worksheets.Add, Excel.Worksheet)
    Dim ColumnIndex = 1 'in the Excel worksheet
    For Each col As DataColumn In dt.Columns 'This loop adds the header row
        oSheet.Cells(1, ColumnIndex) = col.ColumnName
        ColumnIndex  = 1
    Next
    ColumnIndex = 1 'The columns and rows in the spreadsheet
    Dim RowIndex = 2 'The columns and rows in the spreadsheet
    For rowI = 0 To dt.Rows.Count - 1
        For Each col As DataColumn In dt.Columns
            oSheet.Cells(RowIndex, ColumnIndex) = dt(rowI)(col)
            ColumnIndex  = 1
        Next
        ColumnIndex = 1 'Reset back to the first column
        RowIndex  = 1
    Next
    oBook.Save()
    oBook.SaveAs(Filename:="ExcelDat.xlsx") 
    oBook.Close()
    oExcel.Quit()
End Sub