Get the last data filled excel row number in C#

Question

I have been trying to get the row number effectively. But it fails where data was cleared and cells were not explicitly deleted.

I guess it is because of the "Used Range".

What I did is using EPPLUS

using (var excel = new ExcelPackage(hpf.InputStream))
{
    var ws = excel.Workbook.Worksheets["Sheet1"];

    if(ws.Dimension.End.Row > 2003)
    {
        var result = new JsonResult();
        result.Data = "FileLimitExceeded" ;

But it gives me incorrect data. How to get the last count effectively.

I found like

var Lastrow =  Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row;

But the namespaces I could not comprehend. Could anyone help me with a complete and correct answer ?

@sowjanyaattaluri That's the point . Even the accepted answer there suffers from Used Range Contraction Issue I guess. — StrugglingCoder
– StrugglingCoder, Commented Jul 4, 2016 at 12:56
What does Json have to do with Excel or EPPlus? Where is the range you use? Because the code you posted doesn't try to access any cell — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 4, 2016 at 13:01

Mohit Diwakar · Accepted Answer · 2016-07-04 13:01:06Z

0

I have come to this senario few days before, so what i did i read the data and put in data table, and then filter the data to show records when data is not null. You can go for the same approach.

answered Jul 4, 2016 at 13:01

Mohit Diwakar

545 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Panagiotis Kanavos Over a year ago

That should be a comment, not an answer. Besides, if you can read the data as a DataTable, you already know the valid data range

Mohit Diwakar Over a year ago

Tha data which i read contains the blank rows, so it is is good to eliminate the duplicate rows which are not having data. Which completely makes sense.

Panagiotis Kanavos Over a year ago

Not quite. If you specify a range, Excel will return cells whether they have data or not. You don't need to load this range into a DataTable to find the rows that actually contain data. A LINQ query over the cells will do the same job. The easiest option though would be to create named ranges/tables when generating the file, then access them by name

Mohit Diwakar Over a year ago

Hi , the approach for finding the data accurately is described in the link stackoverflow.com/questions/27108153/… . Please visit and i think it will help out.

Panagiotis Kanavos Over a year ago

I'm not the OP. I'm commenting that this approach is no better that looking over all the cells, and just not needed. You can just perform a where LINQ query over a range of cells. And the best option of all, use a named range so you don't need to search for the limits

Collectives™ on Stack Overflow

Get the last data filled excel row number in C#

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related