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