Just need to return distinct values from Excel using EPPlus

Question

All,

Been trying to figure this out for a day now. Did a lot of googling! I have an excel where I have 5 columns but in first column I have product numbers. I want to return DISTINCT product numbers from the excel. Using EPPlus to read in the excel. Here is my code:

string fileName = file.FileName;
                    string fileContentType = file.ContentType;
                    byte[] fileBytes = new byte[file.ContentLength];
                    var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));

                    if (file.FileName.IndexOf(".xlsx") == 0)
                    {
                        throw new Exception("Please ensure that the file has been converted to latest excel version. The file type must be .xlsx.");
                    }

                    using (var package = new ExcelPackage(file.InputStream))
                    {
                        var currentSheet = package.Workbook.Worksheets;
                        var workSheet = currentSheet.FirstOrDefault();
                        var noOfCol = workSheet.Dimension.End.Column;
                        var noOfRow = workSheet.Dimension.End.Row;
                        //lets remove all records 

                        //get a list of distinct item numbers and remove all records in preparation for upload 
                        //I need help with this statement! 

                        var result = workSheet.Cells.Select(grp => grp.First()).Distinct().ToList();

Answer 1

So I was able to figure it out by debugging. This doesnt seem to be the most efficient answer but here it goes:

var result = workSheet.Cells.Where(s => s.Address.Contains("A")).Where(v => v.Value != null).Where(vb => vb.Value.ToString() != "").GroupBy(g => g.Value.ToString()).Distinct().ToList(); 

So basically return Only column A (First column since address holds this information) then eliminate nulls and blanks, next group by the value and finally return distinct as a list.

Answer 2

Regarding your answer (sorry not enough rep to comment):

workSheet.Cells.Where(s => s.Address.Contains("A")).....

That could include ZA, AA, etc If you just want column A you could do

workSheet.Cells[1,1,workSheet.Dimension.End.Row, 1].....

This will start at A1, and just look down column A till the end. You'll still might need to filter null, blank etc, or if you need to start at row 5 here is all i needed. exmaple:

workSheet.Cells[5,1,workSheet.Dimension.End.Row, 1].GroupBy(g => g.Value.ToString()).Distinct().ToList();