Search string (word) in excel file

Last Reply 14 days ago By dharmendr

Posted 15 days ago

Hi Team,

I want to search string (word) came from search textbox in excel file.

If the word match then i want to return a value as 'Text found' on label.

Thanks.

 

You are viewing reply posted by: dharmendr 14 days ago.
Posted 14 days ago

Hi Waghmare,

Check this example. Now please take its reference and correct your code.

HTML

<asp:TextBox runat="server" ID="txtSearchTerm" />
<asp:Button ID="Button1" Text="Search" runat="server" OnClick="Search" />
<br />
<asp:Label ID="lblText" runat="server" />

Namespaces

C#

using Excel = Microsoft.Office.Interop.Excel;

VB.Net

Imports Excel = Microsoft.Office.Interop.Excel

Code

C#

protected void Search(object sender, EventArgs e)
{
    string filePath = Server.MapPath("~/Files/Excel.xlsx");
    Excel.Application application = new Excel.Application();
    Excel.Workbook workBook = application.Workbooks.Open(filePath);
    Excel.Worksheet worksheet = (Excel.Worksheet)workBook.Worksheets[1];
    Excel.Range xlRange = worksheet.UsedRange;
    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;
    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
            string value = (xlRange.Cells[i, j] as Excel.Range).Value2.ToString();
            if (value.ToLower().Contains(txtSearchTerm.Text.ToLower().Trim()))
            {
                lblText.Text = "Text Found";
                return;
            }
        }
    }
    workBook.Close(false, Type.Missing, Type.Missing);
    application.Quit();
}

VB.Net

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Dim filePath As String = Server.MapPath("~/Files/Excel.xlsx")
    Dim application As Excel.Application = New Excel.Application()
    Dim workBook As Excel.Workbook = application.Workbooks.Open(filePath)
    Dim worksheet As Excel.Worksheet = CType(workBook.Worksheets(1), Excel.Worksheet)
    Dim xlRange As Excel.Range = worksheet.UsedRange
    Dim rowCount As Integer = xlRange.Rows.Count
    Dim colCount As Integer = xlRange.Columns.Count
    For i As Integer = 1 To rowCount
        For j As Integer = 1 To colCount
            Dim value As String = (TryCast(xlRange.Cells(i, j), Excel.Range)).Value2.ToString()
            If value.ToLower().Contains(txtSearchTerm.Text.ToLower().Trim()) Then
                lblText.Text = "Text Found"
                Return
            End If
        Next
    Next
    workBook.Close(False, Type.Missing, Type.Missing)
    application.Quit()
End Sub