How to Search a value in an Excel from .net application?

Question

I have to search for a value over 5000 records from excel.

What is the best approach to accomplish this in windows .net application?

Answer 1

Is your value a calculated value produced by one of the spreadsheet's formula's, or an actual pre-determined value?

If it's the later, you could save spreadsheets as XML and parse them appropriately
or
Depending on the indexing technology you have available, you might be able to index them, then search the index and find the appropriate value.
(This would be my preferred approach, now that I've thought of it.)

If it's the former, the only option I can think of is to open each one using the InterOp libraries and use the API.

Answer 2

Since you are trying to search thro' 5000 records, I assume you must be attempting to search thro' values. You could use the "Range.Find" from the interop API to do it.

private void OpenExcelFile()
{
     Excel.Application exlApp = new Microsoft.Office.Interop.Excel.Application();

    if (exlApp == null)
    {
        MessageBox.Show("Excel app object could not be created");
    }
    else
    {

        exlFileSelector.FileName = @"*.xls";

        if (exlFileSelector.ShowDialog() == DialogResult.OK)
        {
            Excel.Workbook wrkBook = exlApp.Workbooks.Open(exlFileSelector.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, true, true);
            Excel.Sheets sheetList = wrkBook.Sheets;

            Excel.Range search = exlApp.get_Range("A1", "C5");
            search.Find("FindMe", null, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false, null, null); 
        }
    }
}

Further reading: How to: Search for Text in Worksheet Ranges

Answer 3

SpreadsheetGear for .NET is an Excel compatible spreadsheet component for .NET which supports a Find API which is similar to Excel's Find API. You can search values or formulas, or calculate formulas and then search for the results if needed. Here is a brief sample which loads a workbook and finds the first cell which starts with "John":

// Open an xlsx workbook (can also open xls).
IWorkbook workbook = Factory.GetWorkbook(@"C:\tmp\Data.xlsx");

// Find the first cell which starts with "John".
IRange foundCell = workbook.Worksheets["Sheet1"].Cells.Find("John*", null,
    FindLookIn.Values, LookAt.Whole, SearchOrder.ByColumns, SearchDirection.Next, true);
if (foundCell != null)
    Console.WriteLine("found {0} in cell {1}", foundCell.Text, foundCell.Address);

You can download the free evaluation here.

Disclaimer: I own SpreadsheetGear LLC