CODEWITHSUNDEEP
c#excellinqlinq-to-excel
Cody Hicks
Cody Hicks

Reputation: 420

Linq to Excel - only recieving data from last row in sheet1

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.

Upvotes: 1

Views: 913

Answers (2)

Andy G
Andy G

Reputation: 19367

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

Upvotes: 1

sdrzymala
sdrzymala

Reputation: 387

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.

Upvotes: 1

Related Questions