我希望我的應用程式匯出 datagridview 上的任何內容，包括過濾器。這是我的應用程式布局的樣子：
組合框包含列的所有名稱。搜索欄搜索該列內的資料。這是代碼的樣子：
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
我想匯出過濾后的資料。pdf 或 excel 都可以。
uj5u.com熱心網友回復：
編程中有一個原則叫做 DRY。它代表不要重復自己。我想你可以看到你違反了這個原則。當您構建 Sql 字串時，就像我在這里所做的那樣，請注意空格。單詞之間需要一個空格。我試圖把它放在字串的末尾。Debug.Print 將顯示字串是否正確輸出。
當您有一系列針對相同值的 If 陳述句時，Select Case 更易于閱讀和撰寫。
沒有必要向 DataGridView 添加列標題。DataTable 到網格的系結將處理這個問題。我相信這些列會自動調整大小以適應資料。
ADO.net 中的幾個資料庫物件提供了一個 Dispose 方法，用于釋放非托管資源。如果有一個 Dispose 方法，它應該被呼叫。即使出現錯誤，Using...End Using 塊也會為我們執行此操作（它也會關閉連接）。這些物件需要在 Using 塊中使用它們的方法中宣告。
不要弄亂 DataGridView。您只需要 DataTable 即可與 Excel 互動。
關閉 Excel 的 Wrapper 可能看起來很奇怪，但 Excel 在 b_tch 中可以擺脫。我相信這里的安排是有效的。
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/338097.html
標籤：sql-server 网络