How to get the contents a particular cell using excel data reader in c#?

Question

I have a xlsx file like (it is the first sheet)

Postcode    Sales_Rep_ID    Sales_Rep_Name  Year    Value
2121            456             Jane        2011    $84,219
2092            789             Ashish      2012    $28,322
2128            456             Janet       2013    $81,879
2073            123             John        2011    $44,491

How do I get a particular value of the column named Year whose say, Sales_Rep_Name value is Janet? I'm stuck at the below code and can't figure how to do it?

FileStream stream = File.Open(@"C:\Users\Bumba\Downloads\Sample-Sales-Data.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration() {
ConfigureDataTable = (_) => new ExcelDataTableConfiguration() {
    UseHeaderRow = true
}
});

foreach (DataTable element in result.Tables) {
    Console.WriteLine(element.Columns["Year"].ToString());
    //what to do??????
}

excelReader.Close();
Console.ReadLine();

Someone help....

Perhaps finding the row where Sales_Rep_Name is your search criteria and from there find the cell value you want is a valid approach here? — Wubbler
– Wubbler, Commented Feb 11, 2018 at 14:15

CodeFuller · Accepted Answer · 2018-02-11 15:44:04Z

3

The easiest option is just to enumerate table (sheet) rows and compare the value in Sales_Rep_Name column against required value ("Janet"):

foreach (DataTable table in result.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        var salesRepName = (string)row["Sales_Rep_Name"];
        if (String.Equals(salesRepName, "Janet"))
        {
            var year = (int)(double)row["Year"];
            Console.WriteLine($"Janet's year is {year}");
        }
    }
}

You could also use DataTable.Select(string filterExpression) method to match required rows:

foreach (DataTable table in result.Tables)
{
    foreach (DataRow row in table.Select("Sales_Rep_Name = 'Janet'"))
    {
        var year = (int)(double)row["Year"];
        Console.WriteLine($"Janet's year is {year}");
    }
}

The second choice will work faster (when the data is big), however the first one is more flexible and allows to use more complex filters.

answered Feb 11, 2018 at 15:44

CodeFuller

31.4k3 gold badges72 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bumba Over a year ago

@Thanks for posting an answer....btw what if the name Janet appears multiple times in the same column and i want to get the value of the nth one ?

CodeFuller Over a year ago

In both variants there is a loop over the rows. You could just add the counter and check its current value against n.

Ybrooks · Accepted Answer · 2019-11-08 17:21:55Z

1

//Code to get an specific cell value
var asOfCell = "AB2";
var match = Regex.Match( asOfCell, @"(?<col>[A-Z]+)(?<row>\d+)" );
var colStr = match.Groups["col"].ToString();
//I rest 1 for colunm and row due to I will work with an array and it begins by zero.
var col = (int) ( colStr.Select( ( t, i ) => ( colStr[i] - 64 ) * Math.Pow( 26, colStr.Length - i - 1 ) ).Sum() ) - 1;
var rowSelected = int.Parse( match.Groups["row"].ToString() ) - 1;
var cellSelectedValue = table.Rows[rowSelected][col];

answered Nov 8, 2019 at 17:21

Ybrooks

111 bronze badge

1 Comment

Abra Over a year ago

An accompanying explanation of your code is usually helpful in providing a complete answer, since the person asking the question may not be familiar with it and therefore may find it difficult to understand which results in it not being an acceptable answer. Consider editing your answer and adding some explanation of the code you posted.

Collectives™ on Stack Overflow

How to get the contents a particular cell using excel data reader in c#?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related