How to read key value pairs from excel sheet using c#?

Question

I'm new to C#. I have used the code below to read data from Excel, but I need help modifying it to read key-value pairs.

public String getData(int row, int col, String var)
{
    Excel.Application excelApp = new Excel.Application();
    if (excelApp != null)
    {
        List<string> prop = new List<string>(var.Split(new string[] {"."}, StringSplitOptions.None));

        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"D:\\test.xlsx", 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets[prop[0]];
        excelWorksheet.Select(Type.Missing);

        Excel.Range range = (excelWorksheet.Cells[row, col] as Excel.Range);
        string cellValue = range.Value.ToString();

        excelWorkbook.Close();
        excelApp.Quit();
        return cellValue;
    }
    return null;
}

First coulmn with all thekeys and 2nd column with relevant values — user3879057, Apr 27 '19 at 12:24
So, what is the issue: Reading a cell, reading two cells or reading a range of pairs of cells? It really shouldn't be different from reading any other data as in many examples out there.. — TaW, Apr 27 '19 at 12:26
rather then passing row and column i want to read the value when key is passed as a parameter — user3879057, Apr 27 '19 at 12:28
Ah, ok. I wouldn't do the lookup in excel but pull all into one dictionary, but it really depends on how many data there are and how often you need to access them. For direct access [this](https://social.msdn.microsoft.com/Forums/en-US/098dc967-b21b-4f39-9bb2-2c4e35cb5946/searching-for-a-value-within-a-column-excel-c?forum=exceldev) or [this](https://stackoverflow.com/questions/24802904/in-excel-how-to-search-a-value-in-a-column-and-get-all-the-values-in-that-row-us) may help.. — TaW, Apr 27 '19 at 12:31
Is there any way to return row and column of a specific value? — user3879057, Apr 27 '19 at 12:39
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/192479/discussion-between-taw-and-user3879057). — TaW, Apr 27 '19 at 14:07

TaW · Accepted Answer · 2019-04-27T15:04:03.130

Here is an example; it assumes you have a using clause..

using Excel = Microsoft.Office.Interop.Excel;

..and prepared access to a worksheet:

Excel.Application xl = new Excel.Application();
xl.Workbooks.Open(filename);
Excel.Worksheet ws = xl.Workbooks[1].Worksheets[1];  // pick your sheet!
int keyColum = 3;

Now you can grab a Range of Cells:

Excel.Range keyRange= ws.Range["C3:C53"];

..or the whole column:

Excel.Range keyRange= ws.Range["C:C"];

And search all occurences of a search string:

Excel.Range keysFound = keyRange.Find(textBox1.Text);

Then you can access the range of found cells like this:

string msg1 = keysFound.Count + " records found.";
string msg2 = "1st in row " + keysFound.Row;
string msg3 = "value from next column is " 
            +  ws.Cells[keysFound.Row + 1, keyColum + 1].value;

notes:

indexing start with 0 in c# but not in excel (hence [keysFound.Row + 1, )
my value column is one column right of the keys. Best use named indices!
if nothing is found keysFound will be null! (do add a check!)
since you want to match a whole key, you will want to do an exact search:

Excel.Range keysFound = keyRange.Find(textBox1.Text, LookAt: Excel.XlLookAt.xlWhole);

I still think grabbing all data and stuffing them into a Dictionary will be the cleanest and fastest solution, unless, that is you only need to do one lookup..

How to read key value pairs from excel sheet using c#?

1 Answers1