How to get the last non-empty row number using EPPlus & c#?

Question

How can I programtically find out the last non-empty row of a particular column (say column 1) using EPPlus & c#?

For testing I've create a test.xlsx file (shown below)

ID	Name	Address	Status
002	John	USA

So, in this case the last non-empty row of column 1 should be 2. I've tried the below code but it shows me 10 instead of 2 (I think since I've formatted the cells till cell D10 like added some border?)

var myFile = new ExcelPackage(new FileInfo(@"test.xlsx"));

Excelsheet sheet = myFile.Workbook.sheets[0];

MessageBox.Show(sheet.Dimension.End.Row.ToString());

So I tried

var lastRowOfCol1 = sheet.Cells.Where(c=>c.Start.Column == 1 && c.End.Column == 1).GroupBy(x=>x.Value.ToString() !="").Select(p=>p.Last());

MessageBox.Show(lastRowOfCol1.ToString());

Now I'm getting

Can anyone help me on this?

Satya Karki · Answer

You can try the below sample code and modify it according to your excel data. public bool WriteSize(string strPath) { bool retBol = false; try { if ((System.IO.File.Exists(strPath) == true)) { using (ExcelPackage package = new ExcelPackage(new System.IO.FileInfo(strPath), false)) { ExcelWorksheet mainSheet = package.Workbook.Worksheets.First(); for (int i = 2; i

Leon D · Answer

Alternatively, you can install Free Spire.XLS via nuget to achieve this task. http://nuget.org/packages/FreeSpire.XLS/ Here is a base example: Workbook workbook = new Workbook(); workbook.LoadFromFile(@ "test.xlsx" ); Worksheet sheet = workbook.Worksheets[0]; int maxRow = sheet.LastRow; MessageBox.Show(maxRow.ToString());

Yazhini MP · Answer

This is the sample example, You can set a bool in the for loop at row level. Then loop all the cells and change the bool when a cell is not empty. //loop through rows and columns for (int row = 1; row { //create a bool bool RowIsEmpty = true; for ( int col =1; col { //check if the cell is empty or not if (worksheet.Cells[row, col].Value != null) { RowIsEmpty = false; } } //dispaly result if (RowIsEmpty) { Label1.Text += "Row " + row + " is empty. "; } }