Find (Search) specific text with in Excel in ASP.Net using ClosedXML

Hi Greenlab,

Please refer below sample.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<br />
<asp:TextBox runat="server" ID="txtSearch" />
<br />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

Namespaces

C#

using System.IO;
using System.Data;
using System.Collections.Generic;
using ClosedXML.Excel;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports ClosedXML.Excel

Code

C#

protected void ImportExcel(object sender, EventArgs e)
{
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);

    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        DataTable dt = new DataTable();
        bool firstRow = true;
        bool isExist = false;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                    if (cell.Value.ToString().Trim().ToUpper() == txtSearch.Text.Trim().ToUpper())
                    {
                        isExist = true;
                        break;
                    }
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    if (cell.Value.ToString().Trim().ToUpper() == txtSearch.Text.Trim().ToUpper())
                    {
                        isExist = true;
                    }
                    i++;
                }
            }
        }

        if (isExist)
        {
            ClientScript.RegisterStartupScript(this.GetType(), "message", "alert('Searched word exists.');", true);
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

VB.Net

Protected Sub ImportExcel(sender As Object, e As EventArgs)
    Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(filePath)
    Using workBook As New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim dt As New DataTable()
        Dim firstRow As Boolean = True
        Dim isExist As Boolean = False
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                    If cell.Value.ToString().Trim().ToUpper() = txtSearch.Text.Trim().ToUpper() Then
                        isExist = True
                    End If
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    If cell.Value.ToString().Trim().ToUpper() = txtSearch.Text.Trim().ToUpper() Then
                        isExist = True
                    End If
                    i += 1
                Next
            End If
        Next

        If isExist Then
            ClientScript.RegisterStartupScript(Me.GetType(), "message", "alert('Searched word exists.');", True)
        End If
            GridView1.DataSource = dt
            GridView1.DataBind()
    End Using
End Sub

Screenshot