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 ?

refer http://stackoverflow.com/questions/7674573/programmatically-getting-the-last-filled-excel-row-using-c-sharp — sowjanya attaluri, Jul 04 '16 at 12:54
@sowjanyaattaluri That's the point . Even the accepted answer there suffers from Used Range Contraction Issue I guess. — StrugglingCoder, Jul 04 '16 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, Jul 04 '16 at 13:01

score 0 · Answer 1 · answered Jul 04 '16 at 13:01

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 04 '16 at 13:01

Mohit Diwakar

56
5

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 – Panagiotis Kanavos Jul 04 '16 at 13:02
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. – Mohit Diwakar Jul 04 '16 at 13:46
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 – Panagiotis Kanavos Jul 04 '16 at 13:48
Hi , the approach for finding the data accurately is described in the link http://stackoverflow.com/questions/27108153/excel-with-qt-find-number-last-filled-row?rq=1 . Please visit and i think it will help out. – Mohit Diwakar Jul 04 '16 at 13:55
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 – Panagiotis Kanavos Jul 04 '16 at 14:00

Get the last data filled excel row number in C#

1 Answers1