In Excel how to search a value in a column and get all the values in that row using C#

Question

I am trying searching a value on column "C" and getting a matched cell name as well, for example C14, now how can I select the values in row 14. I tried as :

     private static MyObject GetRowValue(int rowNumber)
     {
        string connString = "";           
        string path = "C:\\Code\\MyFile.xls";           
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
        string query = "SELECT * FROM [Sheet1$A" + rowNumber + ":BD" + rowNumber + "]";
        using (OleDbConnection connection = new OleDbConnection(connString))
        {
            var adapter = new OleDbDataAdapter(query, connection);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
        }
     }

If row number is 10, them I am trying to get all values of 10th row only, but it is returning all the rows after 10th row.

Answer 1

Just use this formula:

string query = @"SELECT * FROM [Sheet1$"+ (rowNumber-1) + ":" + (rowNumber) + "]";

If rowNumber=10 then you get all the values from the 10th row.

Was this helpful?

Answer 2

If it were me, I'd let Excel do the work for me. You'd need the Office.Interop.Excel namespace.

private static ReadRows(string SearchValue, int StartRow)
{
    int r = StartRow;
    Excel.Application xl = new Excel.Application();
    xl.Workbooks.Open(your workbook);
    Excel.WorkSheet ws = xl.Workbooks(1).Worksheets(1);

    do
    {
        if(ws.Cells(r,3).value == SearchValue)
        {
            // read the entire row
            string colA = ws.Cells(r,1).value;
            string colB = ws.Cells(r,2).value;
            //...

            // or loop through all columns 
            int c = 1;
            do
            {
                // add cell value to some collection
                c++;
            } while (ws.Cells(r,c).Value != "");
        }
        r++;
    } while (ws.Cells(r,3).Value != "");   // 3 because you want column C
}