Calculating the end of a line in a worksheet

Question

I am using EPPlus to read a worksheet. On this sheet, I need the data in the BD columns. Column A has static values ​​that I would ignore. Values ​​in col A are populated up to Row 1000 . But the data that I need will only be there until, say, the Row 450 , for example. I am currently using

 int endRow = workSheet.Dimension.End.Row; 

to calculate the last row read, but returns 1000 . Is there a way for me to have endRow set to 450 , ignoring col A ?

Answer 1

here is an alternative, it should be a little more efficient, since it is counted from the last line

 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 also check if the value is null.

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

Answer 3

I would use the Linq query to get the necessary information.

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