none
Searching for a value within a column - Excel + C# RRS feed

  • Question

  • I have an Excel table of 10 columns and there are about 100 records. I have a Windows Form App that has a text field, a label and a button. My goal is that when I enter a string in the text field and hit save, I'd like it to read just a specific row from the Excel sheet and display it in the label. I will be able to figure out the latter part myself, but I'd like to know how to search from a given column of that excel sheet.

    The sheet is saved locally.

    I tried this article: https://social.msdn.microsoft.com/Forums/office/en-US/f3abea56-2384-44ee-84e6-3b907ed27023/search-excel-sheet-data-in-cnet?forum=exceldev but it confuses me.

    Let the excel sheet be called excel.xlsx and the rows a, b, c...j.

    Please help!

    Thanks!

    Thursday, August 24, 2017 7:10 PM

Answers

  • Hi Nikhil Kenvetil,

    -> I'd like to know how to search from a given column of that excel sheet.

    You could use Range.Find to search a value in a specific range.

    Here is the example.         

                using Excel = Microsoft.Office.Interop.Excel;
    
    
    
    
                Excel.Application xlApp = new Excel.Application();//create a new Excel application
    
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\Desktop\excel.xlsx");//open the workbook
    
                Excel.Worksheet xlWorkSheet = xlWorkBook.Worksheets["Sheet1"];//get the worksheet object
    
                Excel.Range colRange = xlWorkSheet.Columns["A:A"];//get the range object where you want to search from
    
                string searchString = textBox1.Text;
    
                Excel.Range resultRange = colRange.Find(
    
                    What: searchString,
    
                    LookIn: Excel.XlFindLookIn.xlValues,
    
                    LookAt: Excel.XlLookAt.xlPart,
    
                    SearchOrder: Excel.XlSearchOrder.xlByRows,
    
                    SearchDirection: Excel.XlSearchDirection.xlNext
    
                    );// search searchString in the range, if find result, return a range
    
                if (resultRange is null)
    
                {
    
                    MessageBox.Show("Did not found " + searchString + " in column A");
    
                }
    
                else
    
                {
    
                    //then you could handle how to display the row to the label according to resultRange
    
                }
    
                xlWorkBook.Close(false);
    
                xlApp.Quit();
    
                releaseObject(xlWorkSheet);
    
                releaseObject(xlWorkBook);
    
                releaseObject(xlApp);

    You could refer to below link for more information.

    How to: Programmatically Search for Text in Worksheet Ranges

    -> I'd like it to read just a specific row from the Excel sheet and display it in the label.

    I'm wondering what do you want to do. Do you want to show data in the row where the search result cell is to the label?

    Please provide detail information so we could try to reproduce your issue.

    Best Regards,

    Terry

    Friday, August 25, 2017 8:52 AM

All replies

  • Hi,

    Could you share your project and Excel file via cloud storage such as OneDrive, Dropbox, etc?
    It will be time-saving for us who want to help you.
    (Remember to modify/edit your personal information before sharing.)

    Regards,

    Ashidacchi

    Friday, August 25, 2017 12:45 AM
  • Hi Nikhil Kenvetil,

    -> I'd like to know how to search from a given column of that excel sheet.

    You could use Range.Find to search a value in a specific range.

    Here is the example.         

                using Excel = Microsoft.Office.Interop.Excel;
    
    
    
    
                Excel.Application xlApp = new Excel.Application();//create a new Excel application
    
                Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\Desktop\excel.xlsx");//open the workbook
    
                Excel.Worksheet xlWorkSheet = xlWorkBook.Worksheets["Sheet1"];//get the worksheet object
    
                Excel.Range colRange = xlWorkSheet.Columns["A:A"];//get the range object where you want to search from
    
                string searchString = textBox1.Text;
    
                Excel.Range resultRange = colRange.Find(
    
                    What: searchString,
    
                    LookIn: Excel.XlFindLookIn.xlValues,
    
                    LookAt: Excel.XlLookAt.xlPart,
    
                    SearchOrder: Excel.XlSearchOrder.xlByRows,
    
                    SearchDirection: Excel.XlSearchDirection.xlNext
    
                    );// search searchString in the range, if find result, return a range
    
                if (resultRange is null)
    
                {
    
                    MessageBox.Show("Did not found " + searchString + " in column A");
    
                }
    
                else
    
                {
    
                    //then you could handle how to display the row to the label according to resultRange
    
                }
    
                xlWorkBook.Close(false);
    
                xlApp.Quit();
    
                releaseObject(xlWorkSheet);
    
                releaseObject(xlWorkBook);
    
                releaseObject(xlApp);

    You could refer to below link for more information.

    How to: Programmatically Search for Text in Worksheet Ranges

    -> I'd like it to read just a specific row from the Excel sheet and display it in the label.

    I'm wondering what do you want to do. Do you want to show data in the row where the search result cell is to the label?

    Please provide detail information so we could try to reproduce your issue.

    Best Regards,

    Terry

    Friday, August 25, 2017 8:52 AM
  • This worked like a charm. Thanks Terry :)
    Saturday, August 26, 2017 5:55 PM
  • Hi Nikhil,

    It seems your original issue has been solved, I would suggest your mark useful reply to help other developers use this forum efficient. Thanks for understanding.

    Best Regards,

    Terry

    Sunday, August 27, 2017 11:34 PM
  • Done that. Thanks!
    Monday, August 28, 2017 7:23 AM
  • Hi, it's worked. But thanks for looking into it :)
    Monday, August 28, 2017 7:23 AM
  • What should I do if I want to delete that entire row where the cell has the value of the string?
    Thursday, April 19, 2018 9:07 PM
  • Hello Hanyang Sun,

    This thread is long time ago and it is closed. If you have other issue, please feel free to post a new thread and detail your issue to let us know.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 23, 2018 2:52 AM