Hi Appache,
Filter record in DataGridView and Export To Excel.
Please refer below sample.
For downloading ClosedXML library refer below article.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Windows Form
Namespaces
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Data.SqlClient;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Imports System.Data.SqlClient
Code
C#
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.AllowUserToAddRows = false;
BindDataGrid(string.Empty);
}
protected void btnSearch_Click(object sender, EventArgs e)
{
BindDataGrid(txtCountry.Text);
}
private void btnExportExcel_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
// Adding the Columns
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
dt.Columns.Add(column.HeaderText, column.ValueType);
}
// Adding the Rows
foreach (DataGridViewRow row in dataGridView1.Rows)
{
dt.Rows.Add();
foreach (DataGridViewCell cell in row.Cells)
{
dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
}
}
// Exporting to Excel
string folderPath = @"C:\";
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Export");
wb.SaveAs(folderPath + "Export.xlsx");
MessageBox.Show("Export Sucessfully.", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void BindDataGrid(string country)
{
string constring = @"Data Source=.\SQL2019;DataBase=Northwind;UID=sa;PWD=pass@123";
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers", con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
if (!string.IsNullOrEmpty(country))
{
DataRow[] drs = dt.Select("Country LIKE '" + country + "%'");
if (drs.Length > 0)
{
dt = drs.CopyToDataTable();
}
}
dataGridView1.DataSource = dt;
}
}
}
}
VB.Net
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
DataGridView1.AllowUserToAddRows = False
BindDataGrid(String.Empty)
End Sub
Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs)
BindDataGrid(txtCountry.Text)
End Sub
Private Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Dim dt As DataTable = New DataTable()
'Adding the Columns
For Each column As DataGridViewColumn In DataGridView1.Columns
dt.Columns.Add(column.HeaderText, column.ValueType)
Next
'Adding the Rows
For Each row As DataGridViewRow In DataGridView1.Rows
dt.Rows.Add()
For Each cell As DataGridViewCell In row.Cells
dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString()
Next
Next
'Exporting to Excel
Dim folderPath As String = "C:\"
If Not Directory.Exists(folderPath) Then
Directory.CreateDirectory(folderPath)
End If
Using wb As XLWorkbook = New XLWorkbook()
wb.Worksheets.Add(dt, "Enrolled")
wb.SaveAs(folderPath & "Enrolled.xlsx")
MessageBox.Show("Export Sucessfully.", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Using
End Sub
Private Sub BindDataGrid(ByVal country As String)
Dim constring As String = "Data Source=.\SQL2019;DataBase=Northwind;UID=sa;PWD=pass@123"
Using con As SqlConnection = New SqlConnection(constring)
Using cmd As SqlCommand = New SqlCommand("SELECT CustomerId, ContactName, Country FROM Customers", con)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
If Not String.IsNullOrEmpty(country) Then
Dim drs As DataRow() = dt.Select("Country LIKE '" & country & "%'")
If drs.Length > 0 Then
dt = drs.CopyToDataTable()
End If
End If
DataGridView1.DataSource = dt
End Using
End Using
End Using
End Sub
Screenshots
Filtered Records