CODEWITHSUNDEEP
c#excel
user3879057
user3879057

Reputation: 45

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

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;
}

Upvotes: 1

Views: 2013

Answers (1)

TaW
TaW

Reputation: 54433

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..

Upvotes: 1

Related Questions