1

After the headache of attempting to query an Excel spreadsheet with ADO.NET, I've decided to try Linq to Excel in my project.

I have created a method that is supposed to return the value of column B when I provided it with the corresponding value of column A (Both in the same row). Sheet1 has a header row or at least a row indicating what Columns A and B are.

When I combine my code with a basic linq query to retrieve data, I only get data from the last row, no matter what value in column A I ask for. It's alway the last row. There are something like 1159 rows in my sheet. The spreadsheet is Excel 2010, so the data engine should be ACE I presume.

Here is my code...

    public static string ReturnDefinition(string remCode)
    {
        string strReturnMessage = "";
        string pathToExcelFile = @"MEDICARE.xlsx";
        string sheetName = "sheet1";

        var excelFile = new ExcelQueryFactory(pathToExcelFile);
        var codeDescriptions = from a in excelFile.Worksheet(sheetName) select a;

        try
        {
            foreach (var a in codeDescriptions)
            {
                if (a["Code"].ToString().Contains(remCode))
                {
                    strReturnMessage = a["Description"].ToString();
                }
                else
                {
                    strReturnMessage = "No Data";
                }
            }
            return strReturnMessage;

        }
        catch (Exception ex)
        {
            return "Error: " + ex.Message;
        }
        finally
        {

        }
    }

Any Ideas?

Update

It seems that I am not returning the result when it is found. This has something to do with not breaking out of the loop when the result is found. The loop continues to the last result each time.

Cody Hicks
  • 420
  • 9
  • 24

2 Answers2

1

I would like to recommand another usefull package:

http://nugetmusthaves.com/Package/EPPlus

As for me the best one. Inthe case ou yours i have no expirience.

sdrzymala
  • 387
  • 1
  • 10
  • As a backup plan, I'll take a look into this possibility. I've seen in in my searches for a solution but haven't tried it yet. – Cody Hicks Jun 02 '14 at 15:41
1

When you find what you are looking for you should return the value straight-away, otherwise it will just keep looping, and the value will either be the last one it finds or 'No Data':

strReturnMessage = a["Description"].ToString();
return strReturnMessage;

You don't need the else; only if the loop completes without finding the value would you return "No Data":

        strReturnMessage = a["Description"].ToString();
        return strReturnMessage;
    } // end of if
} // end of loop

return "No Data";

If you want the LAST Description, where Code contains the search-text, then:

        strReturnMessage = a["Description"].ToString();
    } // end of if
} // end of loop

if (strReturnMessage == "") {
    return "No Data";
} else {
    return strReturnMessage;
}
Andy G
  • 19,232
  • 5
  • 47
  • 69
  • This is most likely going to be my solution. I'll come back in a few moments after I fix my code. – Cody Hicks Jun 02 '14 at 15:39
  • This solution gets me closer to what I'm looking for. If I omit `return strReturnMessage;` after the loop, VS now throws "Not all code paths return a value". Any suggestions on what I can return after the loop? – Cody Hicks Jun 02 '14 at 15:49
  • I added to my answer, with the last line `return "No Data";`. – Andy G Jun 02 '14 at 15:50
  • I'm certainly getting different results but instead of always getting the last row, now I only get the 2nd row (just below the header). I updated the code in the op – Cody Hicks Jun 02 '14 at 15:59
  • I've added further to my answer, but you should not modify the original question, you should have added further information below the original question. The answers and comments will make no sense to anyone new coming to this topic. – Andy G Jun 02 '14 at 16:05
  • It seems that both suggestions return either the second or last row in the sheet. I apologize for the confusion. I'm trying to get the second column from the row containing the Code. Should I get away from the loop and try another method all together? – Cody Hicks Jun 02 '14 at 16:39
  • What is an example of the text you are searching for and what rows does it occur in? But if you want to go another direction that's up to you. You can do this in the LINQ expression, probably using `Last`. – Andy G Jun 02 '14 at 16:46
  • I understand and I appreciate your help. The sheet I want to query has two columns. Column A contains Codes and Column B contains descriptions of those codes. When I provide the method with a know code, I am aiming to return a description of that code from the spreadsheet. In column A for example, I might provide the method with code M27 and expect to get a description of that code to apply elsewhere. Instead of getting that description, I get the description from either the very first or the very last code in the sheet. I've tried both suggestions. – Cody Hicks Jun 02 '14 at 16:58
  • suppose I add a where clause in the Linq query. Maybe that would insure I get the result in the loop. Kind of hacky but worth a shot I suppose. – Cody Hicks Jun 02 '14 at 17:11
  • I'm going to go ahead and let you have this one and continue but playing with the Linq query some. I appreciate your help. – Cody Hicks Jun 02 '14 at 17:13
  • 1
    You are welcome. It is not hacky, it is making effective use of LINQ. If you use the Last() as well you may not need your loop at all. I should say that the order of results returned is not guaranteed, although it is very likely that they will be returned in the spreadsheet order. Have a go with LINQ and then you might post a new question. – Andy G Jun 02 '14 at 17:14