figuring out end of row in a worksheet

Question

I use EPPlus to read a worksheet. In this worksheet, the data I need is in columns B-D. In column A there are static values, that I would ignore. The values in col A are filled in till Row 1000. But, the data I need will be there only till, say for example Row 450. Currently I use

int endRow = workSheet.Dimension.End.Row;

to calculate the last row to read, but that returns me a value of 1000. Is there a way for me to get the value of endRow to be 450, ignoring col A?

Answer 1

here is an alternative, it should be a bit more efficient as it counts back from the last row

int colB = 2;
int colD = 4;

int lastRow = workSheet.Dimension.End.Row;
while(lastRow >= 1) {
    var range = sheet.Cells[row, colB , row, colD ]
    if(range.Any(c => c.value != null))) {
        break;
    }
    lastRow --;
}

Answer 2

You can alternatively check if the value is null.

if(worksheet.cells[row,column].value != null)
   {
         //ok to proceed
   }

Answer 3

I would use a Linq query to get the info you need.

using (ExcelPackage exPackage = new ExcelPackage(aFile))
        {
            ExcelWorksheet sheet = exPackage.Workbook.Worksheets["Sheet1"];

            List<String> checkCol = new List<String>();
            checkCol.Add("B");
            checkCol.Add("C");
            checkCol.Add("D");

            int longestColumn = 0;

            foreach (String col in checkCol)
            {
                var cells = from c in sheet.Cells[col + ":" + col]
                            where c.Value != null & c.Text.ToString() != ""
                            select c;

                if (cells.Count() > longestColumn) longestColumn = cells.Count();

            }


            MessageBox.Show("The endpoint is: " + longestColumn.ToString());
        }