C# program that interfaces with MS Excel: Conduct a simple SEARCH operation on an Excel file

1.    Open a new Visual C# .NET windows application. Name the project SearchExcel.
2.    In Solution Explorer, right-click the References node and select Add Reference. On the COM tab, select “Microsoft Excel 10.0 Object Library”, and then click OK.
3.    Design a form. Add two Buttons and a Textbox.
4.    Set Name property of Button to “btnOpenExcel” and text property to “Open Excel File”.
5.    Set Name property of Button to “btnSearchExcel” and text property to “Search Excel”.
6.    Set Name property of Button to “txtSearch” and text property to “”.
7.    From Toolbox expand Dialogs group double click on the “OpenFileDialog”.
8.    In the code behind file import the library Microsoft Excel 10.0 Object Library using: using Excel;
9.    Before the Form constructor add the following code.

//Add a reference to excel.application

private Excel.Application exc;

      //Add a reference to workbooks

private Workbooks workbooks;

10.    Add class disposed event handler “Form1_Disposed”. Inside the form disposed event handler add the following code.

if (exc != null)

    exc = null;

11.    Double click on the Button “btnOpenExcel” and pest the following code into the “btnOpenExcel_Click” event.

try

{

  //Creating new excel.application

  exc = new Excel.Application();

  //Prepare open file dialog to only search for excel files

  this.openFileDialog1.FileName = "*.xls";

  if (this.openFileDialog1.ShowDialog() == DialogResult.OK)

  {

    //To get the workbooks collection

    Workbooks workbooks = exc.Workbooks;

    // Here is the call to Open a Workbook in Excel

    // It uses most of the default values                                     

    workbooks.Open(openFileDialog1.FileName, 0, true, 5,

           "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0,  

                  true, 0, 0);

    // Get opened excel file name

    label2.Text = openFileDialog1.FileName;

    //To make application visible

    exc.Visible = true;                    

  }

}

catch (Exception ex)

{

  MessageBox.Show(ex.Message.ToString());

}

finally

{

  exc = null;

}

12.    Double click on the Button “btnSearchExcel” and pest the following code into the “btnSearchExcel_Click” event.

    if (exc != null)

            {

                try

                {                

                    //To get the workbook

                    _Workbook workbook =  workbooks.get_Item(1);

                    //To get the worksheets collection

                    Sheets sheets = workbook.Worksheets;

                    Worksheet worksheet = (Worksheet)sheets.get_Item(1);

                    //Calling search function

                    SearchExample(worksheet, txtSearch.Text);            

                }

                catch(Exception ex)

                {

                    MessageBox.Show(ex.Message.ToString());

                }  

       }

13.    Create a method “SearchExample” with parameters i.e. private void SearchExample(Excel.Worksheet ws, string sFindMe) and pest the following code inside the method.

        Excel.Range rgFound;

            string sFirstFoundAddress;

            // Set via Excel's user interface. following settings are most

        // default value

            rgFound = ws.Cells.Find(sFindMe, ws.get_Range("A1", "A1"),

            Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,

            XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,

            false, false);

            // If Find function doesn't find anything, rgFound will be null

            if (rgFound != null)

            {

                // Save the address of the first found item -

                // it will be used in a loop terminating condition.

                sFirstFoundAddress = rgFound.get_Address(

                        true, true, Excel.XlReferenceStyle.xlA1, null, null);

                MessageBox.Show("Found match at cell " + sFirstFoundAddress);

                // Continue finding subsequent items using FindNext

                rgFound = ws.Cells.FindNext(rgFound);

                string sAddress = rgFound.get_Address(

                        true, true, Excel.XlReferenceStyle.xlA1, null, null);

                // Start a loop that calls FindNext until

                // the first found cell is found again

                while (!sAddress.Equals(sFirstFoundAddress))

                {

                    MessageBox.Show("Found match at cell " + sAddress);

                    rgFound = ws.Cells.FindNext(rgFound);

                    sAddress = rgFound.get_Address(

                        true, true, Excel.XlReferenceStyle.xlA1, null, null);

                }

            }

            MessageBox.Show("The worksheet has been searched.");

Press F5 to build and run the project.

14.    Click on the Button “Open Excel File” and select an excel file using “OpenFileDialog” window and Click on the Button “Open”. Your selected Excel file will be open.
15.    Add your search text into the Textbox “txtSearch”.
16.    Click on Button “Search”. You will be prompted when search item found and also prompted when no item found.